CRUD operation using PHP and MySQLi

In this tutorial, we will learn how to create CRUD operation using PHP and MySQLi.

CRUD Stands for create, read, update and delete record in the database.

Files includes in this tutorials

phpcrud.sql: Contain the database table structure.
dbconnection.php: Used for database connection.
index.php: Used to fetch the record from database.
read.php: Used to fetch the record of particular user.
edit.php: Used to edit the record.
insert.php: Used to insert the new record.

Step 1– Create a database

Open browser type http://localhost/phpmyadmin, create a database named ‘phpcrud’. After creating database run the SQL script or import the SQL file.

MySQL Table tblusers structure

CREATE TABLE `tblusers` (
  `ID` int(10) NOT NULL,
  `FirstName` varchar(200) DEFAULT NULL,
  `LastName` varchar(200) DEFAULT NULL,
  `MobileNumber` bigint(10) DEFAULT NULL,
  `Email` varchar(200) DEFAULT NULL,
  `Address` mediumtext DEFAULT NULL,
  `CreationDate` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tblusers` (

`ID` int(10) NOT NULL,

`FirstName` varchar(200) DEFAULT NULL,

`LastName` varchar(200) DEFAULT NULL,

`MobileNumber` bigint(10) DEFAULT NULL,

`Email` varchar(200) DEFAULT NULL,

`Address` mediumtext DEFAULT NULL,

`CreationDate` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Step 2– Create a database connection file(dbconnection.php)

<?php
$con=mysqli_connect("localhost", "root", "", "phpcrud");
if(mysqli_connect_errno())
{
echo "Connection Fail".mysqli_connect_error();
}
?>

<?php

$con=mysqli_connect("localhost", "root", "", "phpcrud");

if(mysqli_connect_errno())

{

echo "Connection Fail".mysqli_connect_error();

}

Step 3– Create a HTML form for data insertion

<form  method="POST">
<h2>Fill Data</h2>
<p class="hint-text">Fill below form.</p>
 <div class="form-group">

<div class="row">
<div class="col"><input type="text" class="form-control" name="fname" placeholder="First Name" required="true"></div>
<div class="col"><input type="text" class="form-control" name="lname" placeholder="Last Name" required="true"></div>
</div>        	
</div>

<div class="form-group">
<input type="text" class="form-control" name="contactno" placeholder="Enter your Mobile Number" required="true" maxlength="10" pattern="[0-9]+">
        </div>

<div class="form-group">
<input type="email" class="form-control" name="email" placeholder="Enter your Email id" required="true">

</div>

<div class="form-group">
<textarea class="form-control" name="address" placeholder="Enter Your Address" required="true"></textarea>
</div>        

<div class="form-group">
<button type="submit" class="btn btn-success btn-lg btn-block" name="submit">Submit</button>
</div>
</form>

<p class="hint-text">Fill below form.</p>

</div>

</div>

</div>

</div>

<button type="submit" class="btn btn-success btn-lg btn-block" name="submit">Submit</button>

</div>

</form>

Step 4– Now write PHP Code for data insertion and put this code at the top of the insert.php file.

Once the user filled all the data and click on the submit button then data will be saved in the database using the below code:

<?php 
//Databse Connection file
include('dbconnection.php');
if(isset($_POST['submit']))
  {
  	//getting the post values
    $fname=$_POST['fname'];
    $lname=$_POST['lname'];
    $contno=$_POST['contactno'];
    $email=$_POST['email'];
    $add=$_POST['address'];
   
  // Query for data insertion
     $query=mysqli_query($con, "insert into tblusers(FirstName,LastName, MobileNumber, Email, Address) value('$fname','$lname', '$contno', '$email', '$add' )");
    if ($query) {
    echo "<script>alert('You have successfully inserted the data');</script>";
    echo "<script type='text/javascript'> document.location ='index.php'; </script>";
  }
  else
    {
      echo "<script>alert('Something Went Wrong. Please try again');</script>";
    }
}
?>

<?php

//Databse Connection file

include('dbconnection.php');

if(isset($_POST['submit']))

{

//getting the post values

$fname=$_POST['fname'];

$lname=$_POST['lname'];

$contno=$_POST['contactno'];

$email=$_POST['email'];

$add=$_POST['address'];

// Query for data insertion

$query=mysqli_query($con, "insert into tblusers(FirstName,LastName, MobileNumber, Email, Address) value('$fname','$lname', '$contno', '$email', '$add' )");

if ($query) {

echo "<script>alert('You have successfully inserted the data');</script>";

echo "<script type='text/javascript'> document.location ='index.php'; </script>";

}

else

{

echo "<script>alert('Something Went Wrong. Please try again');</script>";

}

Step 5– Read / Fetch the record from the database(index.php)

In this step we will fetch all data from the database.

    <table class="table table-striped table-hover">
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Name</th>                       
                        <th>Email</th>
                        <th>Mobile Number</th>
                        <th>Created Date</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>
                     <?php
$ret=mysqli_query($con,"select * from tblusers");
$cnt=1;
$row=mysqli_num_rows($ret);
if($row>0){
while ($row=mysqli_fetch_array($ret)) {

?>
<!--Fetch the Records -->
                    <tr>
                        <td><?php echo $cnt;?></td>
                        <td><?php  echo $row['FirstName'];?> <?php  echo $row['LastName'];?></td>
                        <td><?php  echo $row['Email'];?></td>                        
                        <td><?php  echo $row['MobileNumber'];?></td>
                        <td> <?php  echo $row['CreationDate'];?></td>
                        <td>
<a href="read.php?viewid=<?php echo htmlentities ($row['ID']);?>" class="view" title="View" data-toggle="tooltip"><i class="material-icons">&#xE417;</i></a>
<a href="edit.php?editid=<?php echo htmlentities ($row['ID']);?>" class="edit" title="Edit" data-toggle="tooltip"><i class="material-icons">&#xE254;</i></a>                       <a href="index.php?delid=<?php echo ($row['ID']);?>" class="delete" title="Delete" data-toggle="tooltip" onclick="return confirm('Do you really want to Delete ?');"><i class="material-icons">&#xE872;</i></a>
</td>
 </tr>
<?php 
$cnt=$cnt+1;
} } else {?>
<tr>
<th style="text-align:center; color:red;" colspan="6">No Record Found</th>
</tr>
<?php } ?>                 
              
</tbody>
 </table>

<thead>

<tr>

<th>Email</th>

<th>Mobile Number</th>

<th>Created Date</th>

<th>Action</th>

</tr>

</thead>

<tbody>

<?php

$ret=mysqli_query($con,"select * from tblusers");

$cnt=1;

$row=mysqli_num_rows($ret);

if($row>0){

while ($row=mysqli_fetch_array($ret)) {

<tr>

<td>

<a href="read.php?viewid=<?php echo htmlentities ($row['ID']);?>" class="view" title="View" data-toggle="tooltip"><i class="material-icons"></i></a>

<a href="edit.php?editid=<?php echo htmlentities ($row['ID']);?>" class="edit" title="Edit" data-toggle="tooltip"><i class="material-icons"></i></a> <a href="index.php?delid=<?php echo ($row['ID']);?>" class="delete" title="Delete" data-toggle="tooltip" onclick="return confirm('Do you really want to Delete ?');"><i class="material-icons"></i></a>

</td>

</tr>

<?php

$cnt=$cnt+1;

} } else {?>

<tr>

<th style="text-align:center; color:red;" colspan="6">No Record Found</th>

</tr>

<?php } ?>

</tbody>

</table>

Step 6–Read / Fetch the particular record (read.php)

create read.php file. For fetching a record we have to get the row id of that record and store in $vid. We access the $_GET[‘viewid’] variable to do it.
Code for gets a record based on the given id.

<table cellpadding="0" cellspacing="0" border="0" class="display table table-bordered" id="hidden-table-info">
               
<tbody>
<?php
$vid=$_GET['viewid'];
$ret=mysqli_query($con,"select * from tblusers where ID =$vid");
$cnt=1;
while ($row=mysqli_fetch_array($ret)) {

?>
 <tr>
    <th>First Name</th>
    <td><?php  echo $row['FirstName'];?></td>
    <th>Last Name</th>
    <td><?php  echo $row['LastName'];?></td>
  </tr>
  <tr>
    <th>Email</th>
    <td><?php  echo $row['Email'];?></td>
    <th>Mobile Number</th>
    <td><?php  echo $row['MobileNumber'];?></td>
  </tr>
  <tr>
    <th>Address</th>
    <td><?php  echo $row['Address'];?></td>
    <th>Creation Date</th>
    <td><?php  echo $row['CreationDate'];?></td>
  </tr>
<?php 
$cnt=$cnt+1;
}?>
                 
</tbody>
</table>

<tbody>

<?php

$vid=$_GET['viewid'];

$ret=mysqli_query($con,"select * from tblusers where ID =$vid");

$cnt=1;

while ($row=mysqli_fetch_array($ret)) {

<tr>

<th>First Name</th>

</tr>

<tr>

<th>Email</th>

<th>Mobile Number</th>

</tr>

<tr>

<th>Address</th>

<th>Creation Date</th>

</tr>

<?php

$cnt=$cnt+1;

}?>

</tbody>

</table>

Step 7 –Edit/ Update the particular record (edit.php)

Step 7.1 Fetch the data in the HTML Form.

create edit.php file. For updating a record we have to get the row id of that record and store in $eid. We access the $_GET[‘editid’] variable to do it.
Code for gets a record based on the given id. Through this way, we can get data autofill-data in HTML Form.

<form  method="POST">
 <?php
$eid=$_GET['editid'];
$ret=mysqli_query($con,"select * from tblusers where ID='$eid'");
while ($row=mysqli_fetch_array($ret)) {
?>
<h2>Update </h2>
<p class="hint-text">Update your info.</p>
<div class="form-group">
<div class="row">
<div class="col"><input type="text" class="form-control" name="fname" value="<?php  echo $row['FirstName'];?>" required="true"></div>
				<div class="col"><input type="text" class="form-control" name="lname" value="<?php  echo $row['LastName'];?>" required="true"></div>
	</div>        	
        </div>

<div class="form-group">
<input type="text" class="form-control" name="contactno" value="<?php  echo $row['MobileNumber'];?>" required="true" maxlength="10" pattern="[0-9]+">
        </div>

<div class="form-group">
<input type="email" class="form-control" name="email" value="<?php  echo $row['Email'];?>" required="true">
</div>
		
<div class="form-group">
<textarea class="form-control" name="address" required="true"><?php  echo $row['Address'];?></textarea>
</div>        
<?php 
}?>
<div class="form-group">
<button type="submit" class="btn btn-success btn-lg btn-block" name="submit">Update</button>
        </div>
    </form>

<?php

$eid=$_GET['editid'];

$ret=mysqli_query($con,"select * from tblusers where ID='$eid'");

while ($row=mysqli_fetch_array($ret)) {

<h2>Update </h2>

<p class="hint-text">Update your info.</p>

<div class="col"><input type="text" class="form-control" name="fname" value="<?php echo $row['FirstName'];?>" required="true"></div>

<div class="col"><input type="text" class="form-control" name="lname" value="<?php echo $row['LastName'];?>" required="true"></div>

</div>

<input type="text" class="form-control" name="contactno" value="<?php echo $row['MobileNumber'];?>" required="true" maxlength="10" pattern="[0-9]+">

</div>

<input type="email" class="form-control" name="email" value="<?php echo $row['Email'];?>" required="true">

</div>

</div>

<?php

}?>

<button type="submit" class="btn btn-success btn-lg btn-block" name="submit">Update</button>

</div>

</form>

Step 7.1 Code for update the particular record. Put this code on the top of the edit.php

<?php 
//Database Connection
include('dbconnection.php');
if(isset($_POST['submit']))
  {
  	$eid=$_GET['editid'];
	//Getting Post Values
    $fname=$_POST['fname'];
    $lname=$_POST['lname'];
    $contno=$_POST['contactno'];
    $email=$_POST['email'];
    $add=$_POST['address'];

    //Query for data updation
     $query=mysqli_query($con, "update  tblusers set FirstName='$fname',LastName='$lname', MobileNumber='$contno', Email='$email', Address='$add' where ID='$eid'");
     
    if ($query) {
    echo "<script>alert('You have successfully update the data');</script>";
    echo "<script type='text/javascript'> document.location ='index.php'; </script>";
  }
  else
    {
      echo "<script>alert('Something Went Wrong. Please try again');</script>";
    }
}
?>

<?php

//Database Connection

include('dbconnection.php');

if(isset($_POST['submit']))

{

$eid=$_GET['editid'];

//Getting Post Values

$fname=$_POST['fname'];

$lname=$_POST['lname'];

$contno=$_POST['contactno'];

$email=$_POST['email'];

$add=$_POST['address'];

//Query for data updation

$query=mysqli_query($con, "update tblusers set FirstName='$fname',LastName='$lname', MobileNumber='$contno', Email='$email', Address='$add' where ID='$eid'");

if ($query) {

echo "<script>alert('You have successfully update the data');</script>";

echo "<script type='text/javascript'> document.location ='index.php'; </script>";

}

else

{

echo "<script>alert('Something Went Wrong. Please try again');</script>";

}

Step 8 – Code for data deletion from the database

<?php
//database connection  file
include('dbconnection.php');
//Code for deletion
if(isset($_GET['delid']))
{
$rid=intval($_GET['delid']);
$sql=mysqli_query($con,"delete from tblusers where ID=$rid");
echo "<script>alert('Data deleted');</script>"; 
echo "<script>window.location.href = 'index.php'</script>";     
} 
?>

<?php

//database connection file

include('dbconnection.php');

//Code for deletion

if(isset($_GET['delid']))

{

$rid=intval($_GET['delid']);

$sql=mysqli_query($con,"delete from tblusers where ID=$rid");

echo "<script>alert('Data deleted');</script>";

echo "<script>window.location.href = 'index.php'</script>";

}

View Demo

Download Source Code (CRUD operation using PHP and MySQLi)

Size: 9.31 KB

Version: V 1

Download Now!

CRUD operation using PHP and MySQLi

Recommended Tutorials for you

You may also like...

Latest Tutorials

Tutorials Categories

SQL Tutorials

CRUD operation using PHP and MySQLi

Step 1– Create a database

Step 2– Create a database connection file(dbconnection.php)

Step 3– Create a HTML form for data insertion

Step 4– Now write PHP Code for data insertion and put this code at the top of the insert.php file.

Step 5– Read / Fetch the record from the database(index.php)

Step 6–Read / Fetch the particular record (read.php)

Step 7 –Edit/ Update the particular record (edit.php)

Step 8 – Code for data deletion from the database

Recommended Tutorials for you

You may also like...

How to fetch data from mysql using PHP

How to delete multiple records in PHP

PHP Regular Expressions

Latest Tutorials

Tutorials Categories

SQL Tutorials