m7md m7md - 28 days ago 8
MySQL Question

Delete multiple rows from mysql database

I want to delete multiple rows from mysql database using php code this my code but i have this message error how to solve this ?


You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'on' at line 1


$files_query=mysqli_query($conn,"select * from tbl_files where db_isdeleted='1'")or die(mysqli_error($conn));
if(mysqli_num_rows($files_query)>0){
echo"<table class='table table-hover table-responsive table-bordered'><tr>";
echo"<td style='background:#f7ac01;font-size:16px;text-align: center;'><input type='checkbox' class='allcb' data-child='chk'/></td>
<td style='background:#f7ac01;font-size:16px;text-align: center;'>FileName</td>
<td style='background:#f7ac01;font-size:16px;text-align: center;'>Date</td>";
echo"</tr>";
while($row=mysqli_fetch_array($files_query)){
$id=$row['db_id'];
$name=$row['name'];
$date=$row['db_date'];
echo"<tr>";
echo'<form method="post" action="">';
echo"<input type='hidden' name='txt_table' value='tbl_files'>";
echo"<input type='hidden' name='txt_action' value='files'>";
echo"<td style='text-align: center;'><input type='checkbox' name='item[]' class='chk'/></td>";

echo"<td style='text-align: center;'>$name</td>";

echo"<td style='text-align: center;'>$date</td>";

}
echo"</tr>";
echo"</table>";
echo"<input type='submit' name='delete' value='Delete' class='btn btn-danger'>&nbsp;";
echo"<input type='submit' name='restore' value='Restore' class='btn btn-success'>";
echo"</form>";
}


/************************

<?php
if(isset($_POST['delete'])){
$table=$_POST['txt_table'];
$action=$_POST['txt_action'];
foreach($_REQUEST['item'] as $id) {
// delete the item with the id $id
$delete_query=mysqli_query($conn,"DELETE FROM {$table} WHERE db_id=$id")or die(mysqli_error($conn));
header("location:recyclebin.php?action=$action&msg=1");
}
}
?>

Answer

sorry for my english. :) your code has many "problems" :), but...

Three steps to make it works:

First, form must be created before while cycle:

echo'<form method="post" action="">';    
 while($row=mysqli_fetch_array($files_query)){

Next, you must provide IDs of db rows (add value="$id" to checkboxes):

echo"<td style='text-align: center;'><input type='checkbox' name='item[]'  value="$id" class='chk'/></td>";

and in form "action" php file, you are redirecting after FIRST iteration. Move redirect AFTER foreach cycle:

  foreach($_REQUEST['item'] as $id) {
        // delete the item with the id $id
    $delete_query=mysqli_query($conn,"DELETE FROM {$table} WHERE db_id=$id")or die(mysqli_error($conn));

    }
header("location:recyclebin.php?action=$action&msg=1");

But your code is really bad and unsafe, read this: https://en.wikipedia.org/wiki/SQL_injection