PHP

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
Ebook Download
View all
Learn
View all
Get Started

You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

Become a sharper developer and jumpstart your career.

Basic

$0

$

. 00

monthly

For Basic members:

  • Standard Profile
  • Access learning courses
  • Access free e-books
  • 1x point
Premium

$20

$

. 00

monthly

For Premium members:

  • Earn 2x reward points
  • Premium profile with Verified check
  • Premium Avatar with status update
  • Learning courses
  • Unlimited Certifications and badges on profile
  • Unlimited Book downloads
  • Access to Premium content
  • Online Training
  • Unlimited PDF conversions and downloads
  • Professional Resume Writing
  • Interview Preparations Guide
  • 25 messages per month
  • 1 "Kodzu" avatar, basic
Elite

$45

$

. 00

monthly

For Elite members:

  • Bundle E-books
  • Access to all Learn Series
  • Unlimited Apply Jobs