Deleting record with image from MySQL table

How to display images from MySQL database records
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 )




plus2net.com




Post your comments , suggestion , error , requirements etc here .




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer