CRUD operation with Image Using PHP and MySQLi

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

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 the database.
insert.php: Used to insert the new record.
read.php: Used to fetch the record of the particular user.
edit.php: Used to edit the record.
change-image.php: Used to change the profile pic.

Step 1– Create a database

Open browser type http://localhost/phpmyadmin, create a database named ‘phpcrud’. After creating the 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,
  `ProfilePic` varchar(200) 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,

`ProfilePic` varchar(200) 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 an HTML form for data insertion. (insert.php)

<form  method="POST" enctype="multipart/form-data" >
<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">
<input type="file" class="form-control" name="profilepic"  required="true">
<span style="color:red; font-size:12px;">Only jpg / jpeg/ png /gif format allowed.</span>
</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>

<span style="color:red; font-size:12px;">Only jpg / jpeg/ png /gif format allowed.</span>

</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.

<?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'];
   $ppic=$_FILES["profilepic"]["name"];
// get the image extension
$extension = substr($ppic,strlen($ppic)-4,strlen($ppic));
// allowed extensions
$allowed_extensions = array(".jpg","jpeg",".png",".gif");
// Validation for allowed extensions .in_array() function searches an array for a specific value.
if(!in_array($extension,$allowed_extensions))
{
echo "<script>alert('Invalid format. Only jpg / jpeg/ png /gif format allowed');</script>";
}
else
{
//rename the image file
$imgnewfile=md5($imgfile).time().$extension;
// Code for move image into directory
move_uploaded_file($_FILES["profilepic"]["tmp_name"],"profilepics/".$imgnewfile);
// Query for data insertion
$query=mysqli_query($con, "insert into tblusers(FirstName,LastName, MobileNumber, Email, Address,ProfilePic) value('$fname','$lname', '$contno', '$email', '$add','$imgnewfile' )");
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'];

$ppic=$_FILES["profilepic"]["name"];

// get the image extension

$extension = substr($ppic,strlen($ppic)-4,strlen($ppic));

// allowed extensions

$allowed_extensions = array(".jpg","jpeg",".png",".gif");

// Validation for allowed extensions .in_array() function searches an array for a specific value.

if(!in_array($extension,$allowed_extensions))

{

echo "<script>alert('Invalid format. Only jpg / jpeg/ png /gif format allowed');</script>";

}

else

{

//rename the image file

$imgnewfile=md5($imgfile).time().$extension;

// Code for move image into directory

move_uploaded_file($_FILES["profilepic"]["tmp_name"],"profilepics/".$imgnewfile);

// Query for data insertion

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

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 read/fetch all data from the database including images.

 <table class="table table-striped table-hover">
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Profile Pic</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><img src="profilepics/<?php  echo $row['ProfilePic'];?>" width="80" height="80"></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']);?>&&ppic=<?php echo $row['ProfilePic'];?>" 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>Profile Pic</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)) {

<tr>

<td><img src="profilepics/<?php echo $row['ProfilePic'];?>" width="80" height="80"></td>

<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']);?>&&ppic=<?php echo $row['ProfilePic'];?>" 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 it in $vid. We access the $_GET[‘viewid’] variable to do it.
Code for gets a record based on the given id.

  <div class="table-title">
                <div class="row">
                    <div class="col-sm-5">
                        <h2>User <b>Details</b></h2>
                    </div>
<?php
$vid=$_GET['viewid'];
$ret=mysqli_query($con,"select * from tblusers where ID =$vid");
$cnt=1;
while ($row=mysqli_fetch_array($ret)) {

?>

<div class="col-sm-7" align="right">
<a href="edit.php?editid=<?php echo htmlentities ($row['ID']);?>" class="btn btn-primary"><span>Edit User Details</span></a>
</div>
</div>
</div>

<table cellpadding="0" cellspacing="0" border="0" class="display table table-bordered" id="hidden-table-info">
               
<tbody>
<tr>
<th width="200">Profile Pic</th>
<td><img src="profilepics/<?php  echo $row['ProfilePic'];?>" width="80" height="80"></td>
<th width="200">Creation Date</th>
<td><?php  echo $row['CreationDate'];?></td>
</tr>

 <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>
 
  </tr>
<?php 
$cnt=$cnt+1;
}?>
                 
</tbody>
</table>

<h2>User <b>Details</b></h2>

</div>

<?php

$vid=$_GET['viewid'];

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

$cnt=1;

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

<a href="edit.php?editid=<?php echo htmlentities ($row['ID']);?>" class="btn btn-primary"><span>Edit User Details</span></a>

</div>

<tbody>

<tr>

<th width="200">Profile Pic</th>

<td><img src="profilepics/<?php echo $row['ProfilePic'];?>" width="80" height="80"></td>

<th width="200">Creation Date</th>

</tr>

<tr>

<th>First Name</th>

</tr>

<tr>

<th>Email</th>

<th>Mobile Number</th>

</tr>

<tr>

<th>Address</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. In 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">
<img src="profilepics/<?php  echo $row['ProfilePic'];?>" width="120" height="120">
<a href="change-image.php?userid=<?php echo $row['ID'];?>">Change Image</a>
</div>

<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>

<img src="profilepics/<?php echo $row['ProfilePic'];?>" width="120" height="120">

<a href="change-image.php?userid=<?php echo $row['ID'];?>">Change Image</a>

</div>

<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 7.3 create a page for image updation. (change-image.php)

create change-image.php file. For updating a profile pic we have to get the row id of that record and store it in $eid. We access the $_GET['userid'] variable to do it.

<form  method="POST" enctype="multipart/form-data">
<?php
$eid=$_GET['userid'];
$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 profile pic.</p>
<input type="hidden" name="oldpic" value="<?php  echo $row['ProfilePic'];?>">
<div class="form-group">
<img src="profilepics/<?php  echo $row['ProfilePic'];?>" width="120" height="120">
</div>

<div class="form-group">
<input type="file" class="form-control" name="profilepic"  required="true">
<span style="color:red; font-size:12px;">Only jpg / jpeg/ png /gif format allowed.</span>
</div> 

<div class="form-group">
<button type="submit" class="btn btn-success btn-lg btn-block" name="submit">Update</button>
</div>
<?php }?>
</form>

<?php

$eid=$_GET['userid'];

$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 profile pic.</p>

<img src="profilepics/<?php echo $row['ProfilePic'];?>" width="120" height="120">

</div>

<span style="color:red; font-size:12px;">Only jpg / jpeg/ png /gif format allowed.</span>

</div>

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

</div>

<?php }?>

</form>

Step 7.4 Code for update the particular user profile pic. Put this code on the top of the change-image.php

<?php 
//Database Connection
include('dbconnection.php');
if(isset($_POST['submit']))
  {
$uid=$_GET['userid'];
//getting the post values
 $ppic=$_FILES["profilepic"]["name"];
$oldppic=$_POST['oldpic'];
$oldprofilepic="profilepics"."/".$oldppic;
// get the image extension
$extension = substr($ppic,strlen($ppic)-4,strlen($ppic));
// allowed extensions
$allowed_extensions = array(".jpg","jpeg",".png",".gif");
// Validation for allowed extensions .in_array() function searches an array for a specific value.
if(!in_array($extension,$allowed_extensions))
{
echo "<script>alert('Invalid format. Only jpg / jpeg/ png /gif format allowed');</script>";
}else{
//rename the image file
$imgnewfile=md5($imgfile).time().$extension;
// Code for move image into directory
move_uploaded_file($_FILES["profilepic"]["tmp_name"],"profilepics/".$imgnewfile);
// Query for data insertion
$query=mysqli_query($con, "update tblusers set ProfilePic='$imgnewfile' where id='$uid' ");
if ($query) {
//Old pic deletion
unlink($oldprofilepic);
echo "<script>alert('Profile pic updated successfully');</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']))

{

$uid=$_GET['userid'];

//getting the post values

$ppic=$_FILES["profilepic"]["name"];

$oldppic=$_POST['oldpic'];

$oldprofilepic="profilepics"."/".$oldppic;

// get the image extension

$extension = substr($ppic,strlen($ppic)-4,strlen($ppic));

// allowed extensions

$allowed_extensions = array(".jpg","jpeg",".png",".gif");

// Validation for allowed extensions .in_array() function searches an array for a specific value.

if(!in_array($extension,$allowed_extensions))

{

echo "<script>alert('Invalid format. Only jpg / jpeg/ png /gif format allowed');</script>";

}else{

//rename the image file

$imgnewfile=md5($imgfile).time().$extension;

// Code for move image into directory

move_uploaded_file($_FILES["profilepic"]["tmp_name"],"profilepics/".$imgnewfile);

// Query for data insertion

$query=mysqli_query($con, "update tblusers set ProfilePic='$imgnewfile' where id='$uid' ");

if ($query) {

//Old pic deletion

unlink($oldprofilepic);

echo "<script>alert('Profile pic updated successfully');</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 a record from the database

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

<?php

//database conection file

include('dbconnection.php');

//Code for deletion

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

{

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

$profilepic=$_GET['ppic'];

$ppicpath="profilepics"."/".$profilepic;

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

unlink($ppicpath);

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

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

}

Download Source Code (php-mysqli-crud-with-image)

Size: 201 KB

Version: V 1.0

Download Script Now

CRUD operation with Image Using PHP and MySQLi

Recommended Tutorials for you

You may also like...

Latest Tutorials

Tutorials Categories

SQL Tutorials

CRUD operation with Image Using PHP and MySQLi

Step 1– Create a database

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

Recommended Tutorials for you

You may also like...

Conditional Statement

PHP – Data Types

User Signup and Sign in using HTML and PHP

Latest Tutorials

Tutorials Categories

SQL Tutorials