Deleting records with image from MySQL table database

While displaying records we will add one Delete link to each record. This delete link will pass the value of product id (p_id ) to our JQuery script.
While displaying records, to each row we will assign one unique id by using the value of p_id. The Delete Image at right side carries the id value of p_id.

while ($row = $stmt->fetch_assoc()) {
echo "<div class='row align-middle' id=d_$row[p_id] >
<div class='col-md-3'>
<img src=images/$row[img] class='rounded-circle' alt='$row[p_name]'>
</div>
<div class='col-md-2'>$row[p_name]</div>
<div class='col-md-1'>$row[p_id]</div>
<div class='col-md-1'>$row[price]</div>
<div class='col-md-1'><span id=$row[p_id] class=del>Del</span></div>
</div>";
}

As we define Delete button within one span tag with class=del , so by clicking any of the Delete buttons the JQuery code below will be triggered.

$('.del').click(function(){
var id=$(this).attr('id');
$.get('delete-record.php',{'p_id':id,'todo':'delete'},
function(return_data){
if(return_data.records_affected == 1){
$("#d_"+id).hide();
}	
$("#msg_display").html(return_data.msg);     
$("#msg_display").show();
setTimeout(function() { $("#msg_display").fadeOut('slow'); }, 4000);
},"json");
})

Within the JQuery code by using http GET method we will send the p_id value to our backend PHP script delete-record.php ( for MySQLi connection type ) .

$.get('delete-record.php',{'p_id':id,'todo':'delete'},
function(return_data){
	....
}

If the record deletion process is successful at backend script then hide the row ( record ) with same id.

if(return_data.records_affected == 1){
$("#d_"+id).hide();
}

This backend script delete-record.php collects the p_id value and after processing the delete query , post back the outcome of the script to front end JQuery function. The same message is displayed to the user by using id=msg_display div tag.

$("#msg_display").html(return_data.msg);     
$("#msg_display").show();
setTimeout(function() { $("#msg_display").fadeOut('slow'); }, 4000);

delete-record.php Backend PHP script.

This script first receives the posted data i.e the value of p_id .

$p_id=$_GET['p_id'];
$todo=$_GET['todo'];
$elements=array("p_id"=>"$p_id","db_status"=>"","msg"=>"",
"records_affected"=>"");

After getting the value of p_id , it will collect the image name from the matching record .

if($stmt = $connection->prepare("SELECT img FROM plus2_db_images 
 WHERE p_id=?")){
  $stmt->bind_param('i',$p_id);
  $stmt->execute();
   
   $result = $stmt->get_result();
   //echo "No of records : ".$result->num_rows."<br>";
   $row=$result->fetch_object();
   $file_name=$row->img;
}else{
  $elements['msg'].=$connection->error;
}

Now let us delete the record matching with p_id value.

$query="DELETE FROM plus2_db_images WHERE p_id=?";
$stmt = $connection->prepare($query);
if ($stmt) {
$stmt->bind_param('i', $p_id);
$stmt->execute();
$elements['msg'].=" Record Deleted <br>";
$elements['records_affected']=$stmt->affected_rows;
}else{
$elements['msg'].=$connection->error;
}

If the record is deleted then delete the image from directory using unlink()

if($elements['records_affected']==1){
if(@unlink("images/$file_name")) {
$elements['msg'].=" File Deleted ";
}else{
$elements['msg'].=" File Not Deleted ";
}
}

Post back the success or failure message to front end JQuery.

echo json_encode($elements);

Downloading Zip file

Download the zip file and use this instructions to run the file.

How to Install and test

Download the zip file at the end of this page. plus2_db_images_v2.zip
include\config.php : Add your database login details ( MySQLi connection ).
include\config-pdo.php : Add your database login details ( PDO connection ).
index.php : PHP script to show images from database using MySQLi.
index-pdo.php : PHP script to show images from database using PDO.
add-record.php : Add image with details to database.
delete-record.php : Delete image & database record.
sql_dump.txt : MySQL dump to create database table.

(only Display Image )

( With Add and Delete Image )

PHP MySQL Collecting single record from MySQL PHP Code generator for PDO & mysqli

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Deleting record with image from MySQL table

delete-record.php Backend PHP script.

Downloading Zip file

Subhendu Mohapatra