Post
Ask Question

CRUD Operation On A User Profile In PHP/ MySQL

Introduction

In this article, I’ll show you how to perform the CRUD operation on a user profile in PHP/MySQL, such as - insert, update, delete, retrieve. This application will work for admins where the admin can add member’s information like username, profile picture, and description etc., i the same way as insertion admin can update member information and also can delete members where only admin can delete member record.

The purpose or aim of this application is to create and manage the work of the user easily by identifying each and every person. This application is created with Bootstrap, PHP/MySQL.

PHP

How to create a database in MySQL?

For creating a database, you should follow some steps as given below.

We will create two tables - one for admin and another one for users.

  1. CREATE TABLE `admin` (  
  2.   `id` int(11) NOT NULL,  
  3.   `username` varchar(25) NOT NULL,  
  4.   `password` varchar(25) NOT NULL  
  5. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  
  6.   
  7.   
  8. CREATE TABLE `users` (  
  9.   `userid` int(11) NOT NULL,  
  10.   `username` varchar(20) NOT NULL,  
  11.   `description` varchar(50) NOT NULL,  
  12.   `userprofile` varchar(200) NOT NULL  
  13. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;  
  14.   
  15.   
  16.    
  17.   
  18. -- Table structure for table `admin`  
  19. --  
  20.   
  21. CREATE TABLE `admin` (  
  22.   `id` int(11) NOT NULL,  
  23.   `username` varchar(25) NOT NULL,  
  24.   `password` varchar(25) NOT NULL  
  25. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  
  26.   
  27. --  
  28. -- Dumping data for table `admin`  
  29. --  
  30.   
  31. INSERT INTO `admin` (`id`, `username`, `password`) VALUES  
  32. (1, 'admin', 'admin');  
  33.   
  34. -- --------------------------------------------------------  
  35.   
  36. --  
  37. -- Table structure for table `users`  
  38. --  
  39.   
  40. CREATE TABLE `users` (  
  41.   `userid` int(11) NOT NULL,  
  42.   `username` varchar(20) NOT NULL,  
  43.   `description` varchar(50) NOT NULL,  
  44.   `userprofile` varchar(200) NOT NULL  
  45. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;  
  46.   
  47. --  
  48. -- Dumping data for table `users`  
  49. --  
  50.   
  51. INSERT INTO `users` (`userid`, `username`, `description`, `userprofile`) VALUES  
  52. (52, 'Dave Doe', 'Member3', '384680.png'),  
  53. (53, 'March Doe', 'Member4', '930734.png'),  
  54. (44, 'John Doe', 'Member1', '871406.png'),  
  55. (51, 'Jean Doe', 'Member2', '48895.png');  

PHP

Now, we will start our PHP code.

Home.php

  1. <div class="container">  
  2. <h1 align="center">PHP/MySQL Add, Edit, Delete, With User Profile.</h1>  
  3.     <div class="page-header">  
  4.         <h1 class="h2">  List of Members<a class="btn btn-success" href="addmember.php" style="margin-left: 770px;"><span class="glyphicon glyphicon-user"></span>  Add Member</a></h1><hr>  
  5.     </div>  
  6. <div class="row">  
  7. <?php  
  8.     $stmt = $DB_con->prepare('SELECT userid, username, description, userprofile FROM users ORDER BY userid DESC');  
  9.     $stmt->execute();  
  10. if($stmt->rowCount() > 0)  
  11. {  
  12.     while($row=$stmt->fetch(PDO::FETCH_ASSOC))  
  13.     {  
  14.         extract($row);  
  15.         ?>  
  16.         <div class="col-xs-3">  
  17.             <h3 class="page-header" style="background-color:cadetblue" align="center"><?php echo $username."<br>".$description; ?></h3>  
  18.             <img src="uploads/<?php echo $row['userprofile']; ?>" class="img-rounded" width="250px" height="250px" /><hr>  
  19.             <p class="page-header" align="center">  
  20.             <span>  
  21.             <a class="btn btn-primary" href="editform.php?edit_id=<?php echo $row['userid']; ?>"><span class="glyphicon glyphicon-pencil"></span> Edit</a>   
  22.             <a class="btn btn-warning" href="?delete_id=<?php echo $row['userid']; ?>" title="click for delete" onclick="return confirm('Are You Sure You Want To Delete This User?')"><span class="glyphicon glyphicon-trash"></span> Delete</a>  
  23.             </span>  
  24.             </p>  
  25.         </div>         
  26.         <?php  
  27.     }  
  28. }  
  29. else  
  30. {  
  31.     ?>  
  32.     <div class="col-xs-12">  
  33.         <div class="alert alert-warning">  
  34.             <span class="glyphicon glyphicon-info-sign"></span>  No Data Found.  
  35.         </div>  
  36.     </div>  
  37.     <?php  
  38. }  
  39. ?>  
  40. </div>  
  41. </div>  

This code is used for deleting a user.

  1. <?php  
  2.     require_once 'dbcon.php';  
  3.    
  4.     if(isset($_GET['delete_id']))  
  5.     {  
  6.         $stmt_select = $DB_con->prepare('SELECT userprofile FROM users WHERE userid =:uid');  
  7.         $stmt_select->execute(array(':uid'=>$_GET['delete_id']));  
  8.         $imgRow=$stmt_select->fetch(PDO::FETCH_ASSOC);  
  9.         unlink("user_images/".$imgRow['userprofile']);  
  10.         $stmt_delete = $DB_con->prepare('DELETE FROM users WHERE userid =:uid');  
  11.         $stmt_delete->bindParam(':uid',$_GET['delete_id']);  
  12.         $stmt_delete->execute();   
  13.         header("Location: index.php");  
  14.     }  
  15. ?>  

Editform.php

  1. <?php  
  2.     error_reporting( ~E_NOTICE );  
  3.     require_once 'dbcon.php';  
  4.    
  5.     if(isset($_GET['edit_id']) && !empty($_GET['edit_id']))  
  6.     {  
  7.         $id = $_GET['edit_id'];  
  8.         $stmt_edit = $DB_con->prepare('SELECT username, description, userprofile FROM users WHERE userid =:uid');  
  9.         $stmt_edit->execute(array(':uid'=>$id));  
  10.         $edit_row = $stmt_edit->fetch(PDO::FETCH_ASSOC);  
  11.         extract($edit_row);  
  12.     }  
  13.     else  
  14.     {  
  15.         header("Location: index.php");  
  16.     }  
  17.     if(isset($_POST['btn_save_updates']))  
  18.     {  
  19.         $username = $_POST['user_name'];  
  20.         $description = $_POST['description'];         
  21.         $imgFile = $_FILES['user_image']['name'];  
  22.         $tmp_dir = $_FILES['user_image']['tmp_name'];  
  23.         $imgSize = $_FILES['user_image']['size'];  
  24.         if($imgFile)  
  25.         {  
  26.             $upload_dir = 'uploads/';  
  27.             $imgExt = strtolower(pathinfo($imgFile,PATHINFO_EXTENSION));  
  28.             $valid_extensions = array('jpeg', 'jpg', 'png', 'gif');  
  29.             $userprofile = rand(1000,1000000).".".$imgExt;  
  30.             if(in_array($imgExt, $valid_extensions))  
  31.             {             
  32.                 if($imgSize < 5000000)  
  33.                 {  
  34.                     unlink($upload_dir.$edit_row['userprofile']);  
  35.                     move_uploaded_file($tmp_dir,$upload_dir.$userprofile);  
  36.                 }  
  37.                 else  
  38.                 {  
  39.                     $errMSG = "Sorry, Your File Is Too Large To Upload. It Should Be Less Than 5MB.";  
  40.                 }  
  41.             }  
  42.             else  
  43.             {  
  44.                 $errMSG = "Sorry, only JPG, JPEG, PNG & GIF Extension Files Are Allowed.";        
  45.             }     
  46.         }  
  47.         else  
  48.         {  
  49.             $userprofile = $edit_row['userprofile'];  
  50.         }  
  51.         if(!isset($errMSG))  
  52.         {  
  53.             $stmt = $DB_con->prepare('UPDATE users SET username=:uname, description=:udes, userprofile=:upic WHERE userid=:uid');  
  54.             $stmt->bindParam(':uname',$username);  
  55.             $stmt->bindParam(':udes',$description);  
  56.             $stmt->bindParam(':upic',$userprofile);  
  57.             $stmt->bindParam(':uid',$id);  
  58.    
  59.             if($stmt->execute()){  
  60.                 ?>  
  61.                 <script>  
  62.                 alert('Successfully Updated...');  
  63.                 window.location.href='home.php';  
  64.                 </script>  
  65.                 <?php  
  66.             }  
  67.             else{  
  68.                 $errMSG = "Sorry User Could Not Be Updated!";  
  69.             }  
  70.         }             
  71.     }  
  72. ?>  

Addmember.php

  1. <?php  
  2.     error_reporting( ~E_NOTICE );  
  3.     require_once 'dbcon.php';  
  4.    
  5.     if(isset($_GET['edit_id']) && !empty($_GET['edit_id']))  
  6.     {  
  7.         $id = $_GET['edit_id'];  
  8.         $stmt_edit = $DB_con->prepare('SELECT username, description, userprofile FROM users WHERE userid =:uid');  
  9.         $stmt_edit->execute(array(':uid'=>$id));  
  10.         $edit_row = $stmt_edit->fetch(PDO::FETCH_ASSOC);  
  11.         extract($edit_row);  
  12.     }  
  13.     else  
  14.     {  
  15.         header("Location: index.php");  
  16.     }  
  17.     if(isset($_POST['btn_save_updates']))  
  18.     {  
  19.         $username = $_POST['user_name'];  
  20.         $description = $_POST['description'];         
  21.         $imgFile = $_FILES['user_image']['name'];  
  22.         $tmp_dir = $_FILES['user_image']['tmp_name'];  
  23.         $imgSize = $_FILES['user_image']['size'];  
  24.         if($imgFile)  
  25.         {  
  26.             $upload_dir = 'uploads/';  
  27.             $imgExt = strtolower(pathinfo($imgFile,PATHINFO_EXTENSION));  
  28.             $valid_extensions = array('jpeg', 'jpg', 'png', 'gif');  
  29.             $userprofile = rand(1000,1000000).".".$imgExt;  
  30.             if(in_array($imgExt, $valid_extensions))  
  31.             {             
  32.                 if($imgSize < 5000000)  
  33.                 {  
  34.                     unlink($upload_dir.$edit_row['userprofile']);  
  35.                     move_uploaded_file($tmp_dir,$upload_dir.$userprofile);  
  36.                 }  
  37.                 else  
  38.                 {  
  39.                     $errMSG = "Sorry, Your File Is Too Large To Upload. It Should Be Less Than 5MB.";  
  40.                 }  
  41.             }  
  42.             else  
  43.             {  
  44.                 $errMSG = "Sorry, only JPG, JPEG, PNG & GIF Extension Files Are Allowed.";        
  45.             }     
  46.         }  
  47.         else  
  48.         {  
  49.             $userprofile = $edit_row['userprofile'];  
  50.         }  
  51.         if(!isset($errMSG))  
  52.         {  
  53.             $stmt = $DB_con->prepare('UPDATE users SET username=:uname, description=:udes, userprofile=:upic WHERE userid=:uid');  
  54.             $stmt->bindParam(':uname',$username);  
  55.             $stmt->bindParam(':udes',$description);  
  56.             $stmt->bindParam(':upic',$userprofile);  
  57.             $stmt->bindParam(':uid',$id);  
  58.    
  59.             if($stmt->execute()){  
  60.                 ?>  
  61.                 <script>  
  62.                 alert('Successfully Updated...');  
  63.                 window.location.href='home.php';  
  64.                 </script>  
  65.                 <?php  
  66.             }  
  67.             else{  
  68.                 $errMSG = "Sorry User Could Not Be Updated!";  
  69.             }  
  70.         }             
  71.     }  
  72. ?>  

Output Screen

Output Screen
Next Recommended Reading How to Generate JSON Data from a MySQL Database using PHP

Codingvila
Codingvila is an educational website, developed to help tech specialists/beginners.