Hariom
Prajapati
02 Jul 2022
PHPIn this tutorial, you will learn how to perform CRUD operations in PHP with MySQLi.
Overview
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
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>
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>
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>
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.
