Blog

crud operations in php | simple crud operations in php using mysqli | php crud with login

CRUD stand for Create, Read, Update and Delete basic function in computer programming language.

In other words are sometimes used when defining the four basic functions of CRUD, such as retrieve instead of read, modify instead of update, insert instead of create ,or destroy instead of delete.

Table structure of CRUD

OperationsSQLHTTPRestFulMongoDB
CreateINSERTPUT/POSTPOSTInsert
Read (Retrieve)SELECT GET       GET       Find
Update (Modify)UPDATEPUT / POST / PATCHwrite     Update
Delete (Destroy)DELETEDELETEDELETERemove

Without, at least these four operations, the software cannot be considered complete.

  • create or add new entries;
  • read, retrieve, search, or view existing entries;
  • update or edit existing entries;
  • delete, deactivate, or remove existing entries.

In this article, We will perform following operations

  • Login
  • Create User
  • Update User (Own) Profile
  • View/Update/edit/delete Other user profile
  • Logged out Feature
  • Change password
  • Session management(Without logging can’t view page directly )

Download Source code and setup on your local computer.

  • Download all source code here
  • Extract zip code and put it into your server root directory. e.g. if you are using XAMPP then put it into htdocs folder.
  • Import Database into your local computer in phpmyadmin, make sure you must have created database before importing sql file.

Related : http://www.learnwebtech.in/install-php-apache-mysql-in-windows-10/

Before, start CRUD Operations we should keep below points in mind.

  • Create Database
  • Create Table in DB
  • Create PHP file to connect PHP to MySql DB

Create Database

  • Login into phpmyadmin e.g. localhost/phpmyadmin
  • Default user name : root , and password : (null, nothing, just hit enter)
  • Click on Database and enter the name of database and click on create
Create Database
Create Database

Create table in below structure

filename: db_connection.php

<?php 
	$servername = "localhost";
	$database = "crud_operation";
	$username = "root";
	$password = "";

	// Create connection
	$conn = new mysqli($servername, $username, $password,$database);

	// Check connection
	if ($conn->connect_error) {
	die("Connection failed: " . $conn->connect_error);
	}
?>

filename: session_check.php it check user is loggedin or not. if not and redirect to login page.

<?php
session_start();
if(empty($_SESSION["userData"]))
{
	$_SESSION["message"] = "Please login to view this page.";
	
	header("Location:index.php");
}
?>

1. User Registration Form

Registration form

file name : register-user.php

<!DOCTYPE html>
<html>
<head><title>Register User</title>
</head>
<body>

<form action="create_user.php" method="post" enctype="multipart/form-data">

<table border="1" width="40%" align="center" >

	<tr>
	 <td width="30%"> Name </td>
	 <td width="10%" align="center"> : </td>
	 <td width="30%"><input class="text-style" required="required" type="text" name="name" id="name"> </td>	
    </tr>
  
    <tr>
	 <td> Email/User Id </td>
	 <td width="10%" align="center"> : </td>
	 <td><input class="text-style" required="required" type="text" name="email" id="email"></td>	
    </tr>

    <tr>
	 <td> Password </td>
	 <td width="10%" align="center"> : </td>
	 <td><input class="text-style" required="required" type="password" name="password" id="password"></td>	
    </tr>
    <tr>
	 <td> Mobile </td>
	 <td width="10%" align="center"> : </td>
	 <td><input class="text-style" required="required" type="text" name="mobile" id="mobile"> </td>	
    </tr>
    
    <tr>
	 <td> Gender </td>
	 <td width="10%" align="center"> : </td>
	 <td>
	 	<input required="required" type="radio" name="gender" id="gender" value="Male"> Male
	 	<input required="required" type="radio" name="gender" id="gender" value="Female"> Female

	 </td>	
    </tr>

    <tr>
	 <td> Subject </td>
	 <td width="10%" align="center"> : </td>
	 <td>
	 	<input  type="checkbox" name="subject[]" value="PHP"> PHP
	 	<input  type="checkbox" name="subject[]" value="MySql"> MySql 
	 	<input  type="checkbox" name="subject[]" value="Druapl"> Druapl
	 	<input  type="checkbox" name="subject[]" value="Java"> Java
	 </td>	
    </tr>

     <tr>
	 <td> State </td>
	 <td width="10%" align="center"> : </td>
	 <td>
	<select name="state" id="state" >
		<option value="Delhi">Delhi</option>
		<option value="Jharkhand">Jharkhand</option>
		<option value="Madhya Pradesh">Madhya Pradesh</option>
		<option value="Maharashtra">Maharashtra</option>
		<option value="Uttar Pradesh">Uttar Pradesh</option>		
	</select>
	 </td>	
    </tr>

    <tr>
	 <td> Profile Picture </td>
	 <td width="10%" align="center"> : </td>
	 <td><input required="required" type="file" name="profile_pic" id="profile_pic"> </td>	
    </tr>

    <tr>

	 <td colspan="3" align="center"> <input type="submit" value="Submit" name="submit"> </td>
	</tr>

	  
</form>

</table>

<p align="center">already  have account, please  login from <a href="index.php"> here </a> </p>

</body>
</html>

filename : create_user.php

<?php session_start();

if(isset($_POST))
{
	include_once'db_connection.php';
	
	$name    =	$_POST['name'];
	$email   =	$_POST['email'];
	$password=	$_POST['password'];
	$mobile  =	$_POST['mobile'];
	$gender	 =	$_POST['gender'];

	//subject field is check box type, we stored into db by comma seperate.

	$subject = 	implode(",", $_POST['subject']);
	
	$state 	 =	$_POST['state'];

	$to_upload_path = "";
	
	if(isset($_FILES) && !empty($_FILES))
	{
		$filename = $_FILES["profile_pic"]["name"];
		$to_upload_path = "uploads/".$filename;
		move_uploaded_file($_FILES["profile_pic"]["tmp_name"], $to_upload_path);		
	}
	

	// write sql query for inserting data into users table.	
	  $sql = "INSERT INTO  users  set 
	    name    =  '$name',
	   password = '$password',
	    email   =  '$email',
	    mobile  =  '$mobile',
	    gender  =  '$gender',
	    subject =  '$subject',
	    state   =   '$state',
	    profile_pic = '$to_upload_path' ";

	if ($conn->query($sql)) {		
		$_SESSION['message'] = "User Successfully Created";
		header("Location:index.php");
	} else {
		$_SESSION['message'] = "Error, Please try again";
		header("Location:register_user.php");
	}
		
	$conn->close();
} 
?>

2. login form, php crud with login

Login

filename : index.php

<?php session_start();?>
<!DOCTYPE html>
<html>
<head><title>User Login</title>
</head>
<body>

<p align="center">

<?php if(isset($_SESSION['message'])){echo $_SESSION['message'];
unset($_SESSION['message']);}?>


</p>

<form action="login_controller.php" method="post" enctype="multipart/form-data">

<table border="1" width="40%" align="center" >

	<tr>
	 <td width="30%">Email/User Id</td>
	 <td width="10%" align="center"> : </td>
	 <td width="30%"><input class="text-style" required="required" type="text" name="email" id="email"> </td>	
    </tr>
  
    <tr>
	 <td> Password </td>
	 <td width="10%" align="center"> : </td>
	 <td><input class="text-style" required="required" type="password" name="password" id="password"></td>	
    </tr>
    
    <tr>
	 <td colspan="3" align="center"> <input type="submit" value="Login" name="submit"> </td>
	</tr>	
</form>
</table>
<p align="center">Don't have account, please  register <a href="register-user.php"> here </a> </p>
</body>
</html>

filename : login_controller.php

<?php
if(isset($_POST))
{	
	session_start();
	include_once'db_connection.php';
	
	$email   =	$_POST['email'];
	$password=	$_POST['password'];
	
	// write sql query for inserting data into users table.	
	  $sql = "SELECT * FROM users  WHERE email   =  '$email' AND password =  '$password' ";	  
	  // Record found 
	  
	  $foundRecord = $conn->query($sql)->num_rows;

	 if ($foundRecord) {
	 	// run again query and store data into session to display who is logged in.
		$result = $conn->query($sql);
		$_SESSION["userData"] = $result->fetch_assoc();
		
		// redirect to user dashboard page.
		header("Location:update-user-profile.php");

	} else {		
		// redirect to login page.
		$_SESSION["message"] = "Invalid user id and password, please try again"; 
		header("Location:index.php");
	}
		
	$conn->close();
} 
?>

3. Dashboard View

Dashboard Screen

filename: update-user-profile.php

<?php
//check of user id logged in otherwise sent on login page.
include 'session_check.php';

// to get database connection.
include 'db_connection.php';

$userId = $_SESSION['userData']['id'];
$isOwnProfile = 'yes';
if(isset($_GET['id']) && !empty($_GET['id']) )
{
	$userId = $_GET['id'];	
	$isOwnProfile = 'no';
}

$sel_query = "SELECT * FROM users WHERE id = '$userId'";
$data = $conn->query($sel_query)->fetch_assoc();
?>

Welcome <b> <?php echo $_SESSION['userData']['name'];?>

<?php /* you can write this too*/ //echo $data['name'];?> </b>

<div class="div_width" id="textbox">
  <div class="alignleft">
  	<?php include 'left_panel.php';?>
  </div>
  <div class="alignright">
  	<?php if(isset($_SESSION['message'])){echo "<p align='center' > ".$_SESSION['message']."</p>";unset($_SESSION['message']);}?>
  	<p align="center">Profile Details </p>
  	 <form action="update.php" method="post" enctype="multipart/form-data">

<table border="1" width="40%" align="center" >

	<tr>
	 <td width="30%"> Name </td>
	 <td width="10%" align="center"> : </td>
	 <td width="30%"><input class="text-style" value="<?php echo $data['name'];?>" required="required" type="text" name="name" id="name"> </td>	
    </tr>
  
    <tr>
	 <td> Email/User Id </td>
	 <td width="10%" align="center"> : </td>
	 <td><input class="text-style" required="required" value="<?php echo $data['email'];?>" type="text" name="email" id="email"></td>	
    </tr>

    <tr>
	 <td> Mobile </td>
	 <td width="10%" align="center"> : </td>
	 <td><input class="text-style" value="<?php echo $data['mobile'];?>" required="required" type="text" name="mobile" id="mobile"> </td>	
    </tr>
    
    <tr>
	 <td> Gender </td>
	 <td width="10%" align="center"> : </td>
	 <td>
	 	<input required="required" type="radio" <?php if($data['gender']=='Male'){echo "checked";}?> name="gender" id="gender" value="Male"> Male
	 	<input required="required" <?php if($data['gender']=='Female'){echo "checked";}?> type="radio" name="gender" id="gender" value="Female"> Female

	 </td>	
    </tr>

    <tr>
	 <td> Subject </td>
	 <td width="10%" align="center"> : </td>
	 <td>
	 	<?php $sub = explode(",",$data['subject']);?>
	 	<input <?php if(in_array('PHP',$sub)){echo "checked";}?> type="checkbox" name="subject[]" value="PHP"> PHP
	 	<input   <?php if(in_array('MySql',$sub)){echo "checked";}?> type="checkbox" name="subject[]" value="MySql"> MySql 
	 	<input  <?php if(in_array('Druapl',$sub)){echo "checked";}?>  type="checkbox" name="subject[]" value="Druapl"> Druapl
	 	<input  <?php if(in_array('Java',$sub)){echo "checked";}?> type="checkbox" name="subject[]" value="Java"> Java
	 </td>	
    </tr>

     <tr>
	 <td> State </td>
	 <td width="10%" align="center"> : </td>
	 <td>
	<select name="state" id="state" >
		<option <?php if($data['state']=='Delhi'){echo "SELECTED";}?> >Delhi</option>
		<option <?php if($data['state']=='Jharkhand'){echo "SELECTED";}?> value="Jharkhand">Jharkhand</option>
		<option <?php if($data['state']=='Madhya Pradesh'){echo "SELECTED";}?> value="Madhya Pradesh">Madhya Pradesh</option>
		<option <?php if($data['state']=='Maharashtra'){echo "SELECTED";}?> value="Maharashtra">Maharashtra</option>
		<option <?php if($data['state']=='Uttar Pradesh'){echo "SELECTED";}?> value="Uttar Pradesh">Uttar Pradesh</option>		
	</select>
	 </td>	
    </tr>

    <tr>
	 <td> Profile Picture </td>
	 <td width="10%" align="center"> : </td>
	 <td><input  type="file" name="profile_pic" id="profile_pic"> 
      
      <?php if(!empty($data['profile_pic'])) { ?>       	
      	<img src="<?php echo $data['profile_pic'];?>" height="50">      	
      <?php }?>
      <input type="hidden" name="old_image" value="<?php echo $data['profile_pic'];?>">      
      <input type="hidden" name="id" value="<?php echo $data['id'];?>">      
      <input type="hidden" name="redirect_page" value="<?php echo $isOwnProfile;?>">      
      </td>	
    </tr>

    <tr>

	 <td colspan="3" align="center"> <input type="submit" value="Update" name="submit"> </td>
	</tr>

	  
</form>

</table>

  </div>
</div>

filename: update.php

<?php
//check of user id logged in otherwise sent on login page.
include 'session_check.php';

// to get database connection.
include 'db_connection.php';

if(isset($_POST))
{
	//print_r($_POST);die('tesitng');

	$userId  =	$_POST['id'];
	$name    =	$_POST['name'];
	$email   =	$_POST['email'];
	$mobile  =	$_POST['mobile'];
	$gender	 =	$_POST['gender'];
	$redirect_page = $_POST['redirect_page'];

	//subject field is check box type, we stored into db by comma seperate.

	$subject = 	implode(",", $_POST['subject']);
	
	$state 	 =	$_POST['state'];
	
	$to_upload_path = "";
	if(isset($_FILES) && !empty($_FILES['profile_pic']['name']))
	{
		$filename = $_FILES["profile_pic"]["name"];
		$to_upload_path = "uploads/".$filename;
		move_uploaded_file($_FILES["profile_pic"]["tmp_name"], $to_upload_path);
	}
	else{
		$to_upload_path = $_POST['old_image'];
	}
	
	// write sql query for inserting data into users table.	
	$sql  = "UPDATE users SET 
	name    =  '$name',
	email   =  '$email',
	mobile  =  '$mobile',
	gender  =  '$gender',
	subject =  '$subject',
	state   =   '$state',
	profile_pic = '$to_upload_path'
	where id = '$userId' ";

	if ($conn->query($sql) === TRUE) {
		 
		  $_SESSION['message'] = "Successfully Updated";       
			if($redirect_page == 'no'){
			header("Location:view_all_user.php");
			}else{
			header("Location:update-user-profile.php");
			}
	
	} else {

   $_SESSION['message'] = "Error, Please try again";
   if($redirect_page == 'no'){
       	header("Location:view_all_user.php");
       }else{
		header("Location:update-user-profile.php");
		}
	}

$conn->close();
} 
?>

4. Change Password

Change Password
Change Password

filename: change-password.php

<?php
//check of user id logged in otherwise sent on login page.
include 'session_check.php';

// to get database connection.
include 'db_connection.php';

$userId = $_SESSION['userData']['id'];
$sel_query = "SELECT * FROM users WHERE id = '$userId'";
$data = $conn->query($sel_query)->fetch_assoc();
?>

Welcome <b> <?php echo $_SESSION['userData']['name'];?>

<?php /* you can write this too*/ //echo $data['name'];?> </b>

<div class="div_width" id="textbox">
  <div class="alignleft">
  	<?php include 'left_panel.php';?>
  </div>
 
  <div class="alignright">
  	<?php if(isset($_SESSION['message'])){echo "<p align='center' > ".$_SESSION['message']."</p>";unset($_SESSION['message']);}?>

  	<p align="center">Change password section </p>

<form action="change_password_controller.php" method="post" enctype="multipart/form-data">

<table border="1" width="60%" align="center" >
	
	<tr>
	 <td width="30%"> Old Password </td>
	 <td width="10%" align="center"> : </td>
	 <td width="30%"><input required="required" type="password" name="oldpassword" id="oldpassword"></td>	
    </tr>

	<tr>
	 <td width="30%"> Password </td>
	 <td width="10%" align="center"> : </td>
	 <td width="30%"><input required="required" type="password" name="password" id="password"></td>	
    </tr>

    <tr>
	 <td width="30%"> Confirm Password </td>
	 <td width="10%" align="center"> : </td>
	 <td width="30%"><input required="required" type="password" name="cnpassword" id="cnpassword"></td>	
    </tr>

	<tr>
	 <td colspan="3" align="center"> <input type="submit" value="Update" name="submit"> </td>
	</tr>

</table>

</form>

</div>
</div>

filename: change_password_controller.php

<?php
//check of user id logged in otherwise sent on login page.
include 'session_check.php';

// to get database connection.
include 'db_connection.php';
$userId = $_SESSION['userData']['id'];

if(isset($_POST))
{	
	$password   =	$_POST['password'];
	$cnpassword =	$_POST['cnpassword'];

	  $sql = "SELECT password FROM users  WHERE id   =  '$userId' ";
	  $data = $conn->query($sql)->fetch_assoc();
	  
	  if($data['password'] != $_POST['oldpassword'])
	  {
	  	$_SESSION['message'] = "old password, doesn't match, Please try again";
  		 header("Location:change-password.php");
	  }
	else if($password != $cnpassword)
	{
		 $_SESSION['message'] = "password, and confirm password doesn't match, Please try again";
  		 header("Location:change-password.php");
    }
  else{	
		// write sql query for inserting data into users table.	
		$sql  = "UPDATE users SET 
		password = '$password'
		where id = '$userId' ";

		if ($conn->query($sql) === TRUE) {
			 
			  $_SESSION['message'] = "Successfully Updated";       
				header("Location:change-password.php");
		
		} else {
	   		$_SESSION['message'] = "Error, Please try again";
	   		header("Location:change-password.php");
		}

	}	

$conn->close();
} 
?>

5. View all user

View all user
View all user

filename: view_all_user.php

<?php
//check of user id logged in otherwise sent on login page.
include 'session_check.php';

// to get database connection.
include 'db_connection.php';
$userId = $_SESSION['userData']['id'];

$sel_query = "SELECT * FROM users";
$runQuery = $conn->query($sel_query);
?>

Welcome <b> <?php echo $_SESSION['userData']['name'];?></b>

<div class="div_width" id="textbox">
  <div class="alignleft">
  	<?php include_once'left_panel.php';?>
  </div>

  <div class="alignright">
  	
  	<?php if(isset($_SESSION['message'])){echo "<p align='center' > ".$_SESSION['message']."</p>";unset($_SESSION['message']);}?>

  	<p align="center">User List</p>
  	
	<table border="1" width="60%" align="center" >
		<tr>
		 <th> Name </th>
		 <th> Email/User Id </th>
		 <th> Mobile </th>
		 <th> Gender </th>
		 <th> Subject </th>
		 <th> State </th>
		 <th> Profile Picture </th>
		 <th>Action</th>
	</tr>

<?php while($data = $runQuery->fetch_assoc()) {?>		
	<tr>
		 <td><?= $data['name']?></td>
		 <td> <?= $data['email']?></td>
		 <td> <?= $data['mobile']?> </td>
		 <td> <?= $data['gender']?> </td>
		 <td> <?= $data['subject']?> </td>
		 <td> <?= $data['state']?> </td>
		 <td> 
		 
		 <?php if(!empty($data['profile_pic'])) { ?> 		 		
		 	<img src="<?= $data['profile_pic']?>" width="50" />
		 <?php }else {echo "N/A";}?>
		 
		 </td>
		 
		 <td> <a href="delete.php?id=<?= $data['id']?>"> Delete </a> | <a href="update-user-profile.php?id=<?= $data['id']?>"> Edit </a></td>
	</tr>
<?php }?>

</table>

  </div>
</div>
filename: delete.php
<?php
//check of user id logged in otherwise sent on login page.
include 'session_check.php';

// to get database connection.
include 'db_connection.php';

if(isset($_GET['id']))
{
	$userId = $_GET['id'];
	// write sql query for inserting data into users table.	
	$sql = "delete from users where id = '$userId'";

	if ($conn->query($sql) === TRUE) {
	$_SESSION['message'] = "User Deleted Successfully";	
	header("Location:view_all_user.php");
	} else {
	$_SESSION['message'] = "Error, User Not Deleted";		
	header("Location:view_all_user.php");
	}
	$conn->close();

} 
?>

6. Logout

filename: logout.php

<?php session_start();
unset($_SESSION['userData']);
$_SESSION["message"] = "Successfully Logout.";
header("Location:index.php");
?>

Watch Video

You can watch video, how to setup on your local system.
Tags: , , ,

Pradip Mehta

I am a well-organized professional in Drupal Development and PHP web development with strong script handling knowledge or automation process with PHP. I have advanced computer skills and am a proficient multitasker.