Create, Read, Update, Delete (CRUD) operations in php using MySQLi 


In this tutorial, you will learn how to perform CRUD operations in PHP with MySQLi.


What is CRUD operations in PHP 

PHP CRUD operation stands for Create, Read, Update, Delete. If you know insert, read, delete, update query of SQL then you can perform Crud operations in PHP using MySQLi easily. If you don't know SQL then learn it from here MySQL full course

Now follow all the below steps to perform CRUD operation.


Step 1 - Create Database 

Create a database in phpMyAdmin using create database query.


For example

CREATE DATABASE demo_database;



Step 2 - Create Database Table

Now you need to create a table in a created database using create table query. we use this table to do all our CRUD operation.


For example 

CREATE TABLE demo_table (

    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

    name VARCHAR(100) NOT NULL ,

    email VARCHAR(100) NOT NULL , 

    address VARCHAR(255) NOT NULL , 

    phone VARCHAR(100 ) NOT NULL

    );



Step 3 - Create a config file

Create a config file to connect PHP script with MySQLi database.


For example 

<?php

 

$server_name= "localhost";

$user_name= "root";

$password= "";

$database_name= "demo_database";

 

$conn= mysqli_connect($ server_name ,  $user_name ,  $ password ,  $ database_name) ; 

 

if ($ conn) { 

echo "connected" ; 

}

 

?>


Explanation: -

$server_name = "localhost" 

In this, we create a variable ( $server_name ) to put the name of the server ( "localhost" ) inside it.

Note- You can take any variable name but the server name ( "localhost" ) depend on the server which are you using. basically, the server name "localhost" is used for all online server and obviously for offline server.


$user_name = "root"

In this, we create a variable ($user_name) to put the user name ( "root" ) of the server inside it.

Note- You can take any variable name but the user name  ( "root" ) is set in the offline server by default (you can change user name) but in case of online server the user name is set by you during the online server setup.


$password = " "

In this, we create a variable ($password) to put the password ( " " ) inside it.

Note- You can take any variable name but the password is blank in the offline server by default (you can change the password) but in case of  online server the password is set by you during the online server setup.


$database_name = "demo_database"

In this, we create a variable ( $database_name ) to put database name ( "demo_database" ) inside it.

Note- You can take any variable name but you need to write database name ( "demo_database" ) which you are taken during creating a database in phpMyAdmin. In my case database name is demo_database.


Create operation 

It is use to insert data from form to the database.


Step 1 - Create a data input form 

You need to create a form to get data from users and insert it into the database.


For example

DOCTYPE html > 

<html  lang ="en">

<head>

    <meta  charset="UTF-8" >

    <meta  http-equiv="X-UA-Compatible"  content="IE=edge" >

    <meta  name="viewport"  content="width=device-width, initial-scale=1.0" >

    <link  rel="stylesheet"  href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.1/dist/css/bootstrap.min.css" >

    <title >Form</title >

</head>

<body>

<div  class ="container"   style ="position:relative;top:25vh;" > 

  <form  action ="data-insert.php"   method ="POST" > 

    <h2 > Data input form</h2 >

    <div   class="row" >

      <div  class="col-md-6" >

        <div  class="form-group" >

          <label  for="first" >Full name</label >

          <input  type="text"  name="name"  class="form-control"  placeholder=""  id="first" >

        </div >

      </div >

      <!--  col-md-6   -->

 

      <div  class="col-md-6" >

        <div  class="form-group" >

          <label  for="last" >Email Address</ label >

          <input  type="text"  name="email"  class="form-control"  placeholder=""  id="last" >

        </div >

      </div >

      <!--  col-md-6   -->

    </div >

 

    <div   class="row" >

      <div  class="col-md-6" >

        <div  class="form-group" >

          <label  for="company" >Address</label >

          <input  type="text"  name="address"  class="form-control"  placeholder=""  id="company" >

        </div >

 

         <!--  row   -->

 

      </div >

      <!--  col-md-6   -->

 

      <div  class="col-md-6" >

 

        <div  class="form-group" >

          <label  for="phone" >Phone Number</ label >

          <input  type="tel"  name="phone"  class="form-control"  id="phone"  placeholder="phone number" >

        </div >

      </div >

      <!--  col-md-6   -->

    </div >

    <!--  row   -->

 

<br>

    <button  type="submit"  class="btn btn-primary" >Submit</button >

  </form > 

</div>

</body>

</html



php-crud-operations

Explanation:- 

<form action="data-insert.php" method="POST">

You need to add action and method attribute inside the form opening tag.

Action attribute used for send data to the page in which we write SQL insert query to insert data in a database,

Method attribute used for sent data by POST method.

To know more about POST or GET method read this - PHP tutorial


Name="name"  Name="email"  Name="address"  Name="phone"

This attributes use to specifies name of a form.

Name attribute use as a reference when data submitted.

Using name attribute you can get data from a form in a page in which we write SQL insert query to send data to the database. 


Step 2 - Create  a data input page

You need to create a page with a name that you wrote inside the action attribute. It means that when you click on submit bottom then you redirect from the data input form page to data input page.


For example 

<?php

include ("connection.php" ); 

 

$name =  $_POST['name' ]; 

$email =  $_POST['email' ]; 

$address =  $_POST['address' ]; 

$phone =  $_POST['phone' ]; 

 

$sql=  "INSERT  INTO `demo_table`(`name` , `email` , `address` , `phone`) VALUE  (' {$name} ' , ' {$email } ' , ' {$address } ' , ' {$phone } ')" ; 

 mysqli_query($ conn , $sql);

 

?>


Explanation:- 

include ("connection.php"); 

This line is used to include the connection page on this page.


$name = $_POST['name'];  $email = $_POST['email'];  $address = $_POST['address']  Sphone = $_POST['phone'];

Here $name , $email , $address and $phone is a variable created by you which contain form data into $_POST['name'] , $_POST['email'] , $_POST['address'] , $_POST['phone'] from data input form.


$sql="INSERT INTO `demo_table`(`name` , `email` ,  `address`  ,  `phone`) VALUE ( '{$name}' , '{$email}' , '{$address}' , '{$phone}' )" ;

Here $sql is a variable which store insert query inside it.


mysqli_query($conn , $sql); 

mysqli query is used to make connection between database and insert query.


Read operation

It is use to fetch data from the database table.


Fetch data from table and display it 

First of all you need to create connection in this page or you can include connection page like create operation page.

Now select all data from table using sql select query and then make connection of database and sql select query using sqli_query( ).

Then you need to fetch data from table using mysqli_fetch_assoc ( )


For example 

<?php

include ("connection.php" ); 

 

$sql= "SELECT *  FROM `demo_table`" ; 

$result = mysqli_query($conn ,  $ sql);

 

?>

<!DOCTYPE  html>

<html  lang="en">

<head>

    <meta  charset="UTF-8" > 

    <meta  http-equiv="X-UA-Compatible"  content="IE=edge" > 

    <meta  name="viewport"  content="width=device-width, initial-scale=1.0" > 

    <title >Document</title > 

</head>

<body>

    <table  border="1" > 

<tr>

    <td >Full Name</td > 

    <td >Email</td > 

    <td >Address</td > 

    <td >Phone no</td > 

</tr>

 

<?php

while ($ fetch=mysqli_fetch_assoc ( $ result)){

echo "<tr>" ; 

    echo  "<td>{$fetch['name'] }</td>";

    echo  "<td>{$fetch['email'] }</td>";

    echo  "<td>{$fetch['address'] }</td>";

    echo  "<td>{$fetch['phone'] }</td>";

    echo  "</tr>"; }

?>

    </table > 

</body>

</html>



php-crud-operations

Explanation:- 

include ("connection.php"); 

It is used to include connection page in this to connect this page with database.


$sql="SELECT * FROM `demo_table`"; 

Here $sql is a variable which store all the data selected from demo_table using sql select query ( SELECT * FROM `demo_table` )


$result=mysqli_query($conn , $sql); 

mysqli_query used to make connection between database and select query and then all data store in $result


while($fetch=mysli_fetch_assoc($result); 

In this, data fetch from $result and stored in $fetch then we use while loop to display all row data.


Delete Operation 

It is used to delete any data from the database table.


Step 1 - Add delete button

You need to add a delete button using ancor tag in the page in which we fetch data (read data) from the database.

Here ancor tag use to send data (unique data) to another page where we use delete query to perform delete operation.


For example 


<?php

include ("connection.php" ); 

 

$sql= "SELECT *  FROM `demo_table`" ; 

$result = mysqli_query($conn ,  $ sql);

 

?>

<!DOCTYPE  html>

<html  lang="en">

<head>

    <meta  charset="UTF-8" > 

    <meta  http-equiv="X-UA-Compatible"  content="IE=edge" > 

    <meta  name="viewport"  content="width=device-width, initial-scale=1.0" > 

    <title >Document</title > 

</head>

<body>

    <table  border="1" > 

<tr>

    <td >Full Name</td > 

    <td >Email</td > 

    <td >Address</td > 

    <td >Phone no</td > 

    <td >action</td > 

</tr>

 

<?php

while ($ fetch=mysqli_fetch_assoc ( $ result)){

echo "<tr>" ; 

    echo  "<td>{$fetch['name'] }</td>";

    echo  "<td>{$fetch['email'] }</td>";

    echo  "<td>{$fetch['address'] }</td>";

    echo  "<td>{$fetch['phone'] }</td>";

    echo  "<td><a href=\"delete.php?var= {$ fetch['id']} \" >Delete</a></td>";

    echo  "</tr>"; }

?>

    </table > 

</body>

</html>

 



php-crud-operations

Explanation:- 

include ("connection.php"); 

It is used to include a connection page to connect this page with the database.


$sql="SELECT * FROM `demo_table`"; 

Here $sql is a variable which store all the data selected from demo_table using sql select query ( SELECT * FROM `demo_table` )


$result=mysqli_query($conn , $sql); 

mysqli_query used to make connection between database and select query and then all data store in $result


while($fetch=mysli_fetch_assoc($result); 

In this, data fetch from $result and stored in $fetch then we use while loop to display all row data.


<td><a href=\"delete.php?var={$fetch['id']}\"> Delete</ a>  </td> 

Here delete.php is a page where delete query will be written and in  ?var={$fetch['id']}, ?var is a variable in which id will stored and when you click on ancor tag then the id goes to the another page(delete.php).


Step 2 - Write delete query

Now write delete query in the page which we wrote in the above ancor tag  (delete.php).


For example 

<?php

include ("connection.php" ); 

 

   $data =$_GET['var' ]; 

   

$sql= "DELETE FROM  `demo_table` WHERE `id`  =  $data " ;

$result= mysqli_query($conn ,  $ sql);

 

if($ result){ 

    header( "location:user_details.php");

}

 

?>


Explanation:- 

include ("connection.php");

It is used to include a connection page to connect this page with the database.


$data = $_GET['var'];

$_GET['var'] is use to get data from the add delete button page and then is store in a variable ($data).


$sql="DELETE FROM `demo_table` WHERE `id` = $data"; 

Here delete query is written and data stored in variable $sql.


$result=mysqli_query(conn , sql );

Mysqli_query used to make the connection between the database and delete query then it stored data in the $result.


Update Operation 

It is used to update any data of a table in a database.


Step 1 - Add update button

You need to add a delete button using ancor tag in the page in which we fetch data (read data) from the database.

Here ancor tag use to send data (unique data) to another page where we use delete query to perform delete operation.


For example 


<?php

include ("connection.php" ); 

 

$sql= "SELECT *  FROM `demo_table`" ; 

$result = mysqli_query($conn ,  $ sql);

 

?>

<!DOCTYPE  html>

<html  lang="en">

<head>

    <meta  charset="UTF-8" > 

    <meta  http-equiv="X-UA-Compatible"  content="IE=edge" > 

    <meta  name="viewport"  content="width=device-width, initial-scale=1.0" > 

    <title >Document</title > 

</head>

<body>

    <table  border="1" > 

<tr>

    <td >Full Name</td > 

    <td >Email</td > 

    <td >Address</td > 

    <td >Phone no</td > 

    <td >action</td > 

</tr>

 

<?php

while ($ fetch=mysqli_fetch_assoc ( $ result)){

echo "<tr>" ; 

    echo  "<td>{$fetch['name'] }</td>";

    echo  "<td>{$fetch['email'] }</td>";

    echo  "<td>{$fetch['address'] }</td>";

    echo  "<td>{$fetch['phone'] }</td>";

    echo  "<td><a href=\"update_done.php?var= {$ fetch['id']} \" >Update</a></td>";

    echo  "</tr>"

   }

?>

    </table > 

</body>

</html>

 



php-crud-operations

Explanation:- 

include ("connection.php"); 

It is used to include a connection page to connect this page with the database.


$sql="SELECT * FROM `demo_table`"; 

Here $sql is a variable which store all the data selected from demo_table using sql select query ( SELECT * FROM `demo_table` )


$result=mysqli_query($conn , $sql); 

mysqli_query used to make connection between database and select query and then all data store in $result


while($fetch=mysli_fetch_assoc($result); 

In this, data fetch from $result and stored in $fetch then we use while loop to display all row data.


<td><a href=\"update_done.php?var={$fetch['id']}\"> Update</ a>  </td> 

Here update_done.php is a page where update query will be written and in  ?var={$fetch['id']}, ?var is a variable in which id will stored and when you click on ancor tag then the id goes to the another page(update_done.php).


Step 2 - Create an update form 

You need to create an update form to update data in a table.


For example 

<?php

include ("connection.php" ); 

 

$var= $_GET['var' ];

 

$sql= "SELECT *  FROM `demo_table`  WHERE  `id` =   $ var";

$result= mysqli_query($conn ,  $ sql);

 

$fetch= mysqli_fetch_assoc($result) ;

 

?>

<!DOCTYPE  html>

<html  lang="en">

<head>

    <meta  charset="UTF-8" > 

    <meta  http-equiv="X-UA-Compatible"  content="IE=edge" > 

    <meta  name="viewport"  content="width=device-width, initial-scale=1.0" > 

    <title >Update form</title >

</head>

<body>

    <form  action="update_done.php"  method="POST" > 

   <label >Full name</label >

   <input  type="text"  name="name"  value=" <?php  echo "{$fetch ['name']}" ?>"   > < br ><br>

 

   <label >Email</label >

   <input  type="email"  name="email"  value="<?php  echo"{$fetch ['email'] } " ?>" >   <br>< br > 

 

   <label >Address</label >

   <input  type="text"  name="address"  value="<?php  echo"{$fetch ['address'] }" ?>" >   <br>< br > 

 

   <input  type="hidden"  name="id                  "  value="<?php  echo"{$fetch ['id'] }" ?> " >

 

   <label >Phone</label >

   <input  type="text"  name="phone"  value="<?php  echo " {$ fetch ['phone']}" ?> " >  <br >< br > 

 

   <button  type="submit"  class="btn btn-danger ml-5"  >Submit</ button > 

</form>

</body>

</html>


Explanation:- 

include ("connection.php");

It is used to include a connection page to connect this page with the database.


$var = $_GET['var'];

$_GET['var'] is use to get data from the   delete button page and then is store in a variable ($data).


$sql="SELECT * FROM `demo_table` WHERE `id` = $var"; 

Here SELECT query is written and data stored in variable $sql.


$result=mysqli_query(conn , sql );

Mysqli_query used to make the connection between the database and delete query then it stored data in the $result.


$fetch=mysqli_fetch_assoc($result); 

mysqli_fetch_assoc($result) is fetching data from $result and store it into $fetch.

After fetch data, you need to conflict data related data into input value.

Don't forget to add input in the form for id but make it hidden because using this id you can get a reference to the page on which you write an SQL update query.


value="<?php echo"{$fetch['address']} " ?>" 

It is used to implementing the data inside the value attribute.


Step 3 - Write update query

Here you write an update query to update any data from a table in the database.


For example

<?php

include ("connection.php" );

 

$name= $_POST['name' ];

$email= $_POST['email' ];

$address= $_POST['address' ];

$phone= $_POST['phone' ];

$id= $_POST['id' ];

 

print_r ($ id);

 

$sql= "UPDATE `demo_table`  SET  `name` = '". $ name."'  , `email` =  '". $email."' , `address`  =  '".$address ."' , `phone`  ='".$ phone ."' WHERE `id` = $id " ;

$result= mysqli_query($conn ,  $ sql);

 

if($ result){

    header( "location:user_details.php");

}

?>


Explanation:- 

include ("connection.php"); 

This line is used to include the connection page on this page.


$name = $_POST['name'];  $email = $_POST['email'];  $address = $_POST['address']  Sphone = $_POST['phone'];   Sid = $_POST['id'];

Here $name , $email , $address and $phone is a variable created by you which contain form data into $_POST['name'] , $_POST['email'] , $_POST['address'] , $_POST['phone']  , $_POST['id'] from data input form.

Here id is only for reference to perform the update operation.


$sql="UPDATE `demo_table` SET `name` = '".$name."'  , `email` = '".$email."' , `address` = '".$address."' , `phone` ='".$phone."' WHERE `id`=$id ";

Here $sql is a variable that store update query inside it.


mysqli_query($conn , $sql); 

mysqli query is used to make connection between database and update query.