Delete selected rows from table with checkboxes

[chrisdee]

Thanks to many of you here I have the script below wich lists out the contents of a mysql table. It also allows me to sort both ascending and descending. I have also made update and delete links in the far right column.

Next step I would like to do is to let me delete several rows at a time with checkboxes. More spesifically to delete all the checked rows.

Im thinking I will need the script to check wich rows are marked/checked for deletion and have an global delete button/link ? Problem is I don't know where to put it or how to run a check for this.

Any ideas are greatly apreciated.

Here is the script so far.

<?php echo "<a href=./>Home</a> | <a href=insert.php>Insert</a><br>";
// connect to db
$connect = mysqli_connect('localhost','user','pass','db');

// set the default sort
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'id';

if(isset($_GET['sort_by'])) {
 if($_GET['sort_by'] == 'asc') {
  $sort_order = 'desc';
 } else {
  $sort_order = 'asc';
 }
}

// html table
echo "<table border='1'>";

// query mysql table column keys

$query_keys = mysqli_query($connect,("SELECT * FROM pdb"));
$fetch_keys = mysqli_fetch_array($query_keys, MYSQLI_ASSOC);

// print out table columns keys
echo "<tr>";
foreach($fetch_keys as $key => $value) {
 switch($sort) {
  case $key :
   $order_by = $key;
   break;
 }
 if($sort==$key) {
  echo "<td bgcolor=lightgreen><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 } else {
  echo "<td bgcolor=lightblue><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 }
}
echo "<td>Update | Delete</tr>";
echo "</tr>";
// free query keys
mysqli_free_result($query_keys);

// pagination
$rows_per_page    = 20;
$query_rows    = mysqli_query($connect, "SELECT id FROM pdb");
$num_rows    = mysqli_num_rows($query_rows);
$num_of_pages    = ceil($num_rows / $rows_per_page);
$current_page    = 1;

if (isset($_GET['current_page']) && $_GET['current_page'] >= 1 && $_GET['current_page'] <= $num_of_pages) {
 $current_page = (int)$_GET['current_page'];
}

$offset        = ($current_page -1) * $rows_per_page;
mysqli_free_result($query_rows);

// query mysql table content
$query_content    = mysqli_query($connect,("SELECT * FROM pdb ORDER BY $sort $sort_order LIMIT $offset, $rows_per_page"));

// print out table contents
while($row = mysqli_fetch_array($query_content, MYSQLI_NUM)) {
 echo "<tr>";
 foreach($row as $rows) {
  echo "<td>" . $rows . "</td>";
 }
 echo "<td><a href=update.php?id=$row[0]>Update</a> | <input type='checkbox' name='list[]' value='$row[0]'><a href=shure.php?id=$row[0]>Delete</a></td>";
 echo "</tr>";
}
// free query content
mysqli_free_result($query_content);

echo "</table>";

// echo pagination
for ($i = 1; $i <= $num_of_pages; ++$i) {
 if($i==$current_page) {
  echo "<a href='?current_page=$i'><font color=red>$i</font></a> ";
 }
 else {
  echo "<a href='?current_page=$i'>$i</a> ";
 }
} 

// close connection
mysqli_close($connect);
?>

[vivek151189]

echo "<td><a href=update.php?id=$row[0]>Update</a> | <input type='checkbox' name='list[]' value='$row[0]'><a href=shure.php?id=$row[0]>Delete</a></td>";

in this code you are genrating the dynamic checkboxes

for deleting multiple value you need to have the submit button and all that inside th <form> tag
after that if you selected the multiple checkboxes and hit the submit button then you will get the array of checked checkboxes in the $_POST variable

there you can fire the query in teh loop so as to delete the row from the table where id matches

[chrisdee]

Thanks. With a little help from another forum I have managed to interate an form that deletes several rows in my script below . Problem is that the $x in the for loop doesnt correspond to the id values in my mysql table. When rows 0 to 10 are deleted all rows with id above 10 are not.

Also this code comes outside my table so it looks a bit messy.
Is there a way to make your code delete based on id value instead of numbers from 0 to 10, and also echo it in the far right column of my table (beside update) ?

Here is the updated code with your code working in beween.

<?php echo "<a href=./>Home</a> | <a href=insert.php>Insert</a><br>";
// connect to db
$connect = mysqli_connect('localhost','user','pass','db');

// set the default sort
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'id';

if(isset($_GET['sort_by'])) {
 if($_GET['sort_by'] == 'asc') {
  $sort_order = 'desc';
 } else {
  $sort_order = 'asc';
 }
}

// html table
echo "<table border='1'>";

// query mysql table column keys

$query_keys = mysqli_query($connect,("SELECT * FROM pdb"));
$fetch_keys = mysqli_fetch_array($query_keys, MYSQLI_ASSOC);

// print out table columns keys
echo "<tr>";
foreach($fetch_keys as $key => $value) {
 switch($sort) {
  case $key :
   $order_by = $key;
   break;
 }
 if($sort==$key) {
  echo "<td bgcolor=lightgreen><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 } else {
  echo "<td bgcolor=lightblue><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 }
}
echo "<td>Update | Delete</tr>";
echo "</tr>";
// free query keys
mysqli_free_result($query_keys);

// pagination
$rows_per_page    = 20;
$query_rows    = mysqli_query($connect, "SELECT id FROM pdb");
$num_rows    = mysqli_num_rows($query_rows);
$num_of_pages    = ceil($num_rows / $rows_per_page);
$current_page    = 1;

if (isset($_GET['current_page']) && $_GET['current_page'] >= 1 && $_GET['current_page'] <= $num_of_pages) {
 $current_page = (int)$_GET['current_page'];
}

$offset        = ($current_page -1) * $rows_per_page;
mysqli_free_result($query_rows);

// query mysql table content
$query_content    = mysqli_query($connect,("SELECT * FROM pdb ORDER BY $sort $sort_order LIMIT $offset, $rows_per_page"));


# Form with array
echo "<form name='delete' method='post'>";
  for ($x=0; $x<=$num_rows; $x++) {
    echo "<input type='checkbox' name='list[]' value='$x'>$x<br>";
  }
  echo "<input type='submit' name='submit' value='Delete'>";
echo "</form><hr>";

#print "<pre>";
#print_r($_POST);
#print "</pre><hr>";


$checkbox_array = $_POST['list'];
foreach ($checkbox_array as $value) {
  $delete_result = mysqli_query($connect, "DELETE FROM pdb WHERE id=$value");
}



// print out table contents
while($row = mysqli_fetch_array($query_content, MYSQLI_NUM)) {
 echo "<tr>";
 foreach($row as $rows) {
  echo "<td>" . $rows . "</td>";
 }
 echo "<td><a href=update.php?id=$row[0]>Update</a> | ";
 echo "<input type='checkbox' name='list[]' value='$row[0]'>";

 echo "</td>";
 echo "</tr>";
}
// free query content
mysqli_free_result($query_content);


echo "<tr><td colspan=10><input type='submit' name='submit' value='delete'></form></td></tr>";

echo "</table>";

// echo pagination
for ($i = 1; $i <= $num_of_pages; ++$i) {
 if($i==$current_page) {
  echo "<a href='?current_page=$i'><font color=red>$i</font></a> ";
 }
 else {
  echo "<a href='?current_page=$i'>$i</a> ";
 }
} 

// close connection
mysqli_close($connect);
?>

[bpat1434]

Instead of using "list[]"as the name of the checkbox input you can specify an array key there that means something to you. So you could make the name be

list[<?php echo $row[0];? >]

In your pup you could then iterated over $_POST

• and the keys would be the id of the MySQL row.

[chrisdee]

bpat1434;11005221 wrote:

Instead of using "list[]"as the name of the checkbox input you can specify an array key there that means something to you. So you could make the name be

list[<?php echo $row[0];? >]

In your pup you could then iterated over $_POST

• and the keys would be the id of the MySQL row.

Thank you. I will try that.

[chrisdee]

Finally got it to work🙂

However, even though it works I get an warning message from php.

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /var/www/pdb/index.php on line 106
row(s) deleted!

Any idea why ?

<?php echo "<a href=./>Home</a> | <a href=insert.php>Insert</a><br>";
// connect to db
$connect = mysqli_connect('localhost','user','pass','db');

// set the default sort
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'id';

if(isset($_GET['sort_by'])) {
 if($_GET['sort_by'] == 'asc') {
  $sort_order = 'desc';
 } else {
  $sort_order = 'asc';
 }
}

// html table
echo "<table border='1'>";

// query mysql table column keys
$query_keys = mysqli_query($connect,("SELECT * FROM pdb"));
$fetch_keys = mysqli_fetch_array($query_keys, MYSQLI_ASSOC);

// print out table columns keys
echo "<tr>";
foreach($fetch_keys as $key => $value) {
 switch($sort) {
  case $key :
   $order_by = $key;
   break;
 }
 if($sort==$key) {
  echo "<td bgcolor=lightgreen><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 } else {
  echo "<td bgcolor=lightblue><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 }
}
echo "<td>Update | Delete</tr>";
echo "</tr>";

// free query keys
mysqli_free_result($query_keys);

// pagination
$rows_per_page    = 20;
$query_rows    = mysqli_query($connect, "SELECT id FROM pdb");
$num_rows    = mysqli_num_rows($query_rows);
$num_of_pages    = ceil($num_rows / $rows_per_page);
$current_page    = 1;

if (isset($_GET['current_page']) && $_GET['current_page'] >= 1 && $_GET['current_page'] <= $num_of_pages) {
 $current_page = (int)$_GET['current_page'];
}

$offset        = ($current_page -1) * $rows_per_page;
mysqli_free_result($query_rows);

// query mysql table content
$query_content = mysqli_query($connect,("SELECT * FROM pdb ORDER BY $sort $sort_order LIMIT $offset, $rows_per_page"));
$del_query     = mysqli_query($connect, "SELECT * FROM pdb");

# print out table contents
echo "<form name='delete' method='post'>";
while($fetch_assoc = mysqli_fetch_assoc($query_content)) {
 echo "<tr>";
 foreach($fetch_assoc as $rows) {
  echo "<td>" . $rows . "</td>";
 }
 echo "<td><a href=update.php?id=$fetch_assoc[id]>Update</a> | <input type='checkbox' name='list[]' value='$fetch_assoc[id]'>Delete</a>";

 echo "</td>";
 echo "</tr>";
}
echo "<tr><td colspan=10><input type='submit' name='submit' value='Delete'></form></tr></table>";

# free query content
mysqli_free_result($query_content);

# echo pagination
for ($i = 1; $i <= $num_of_pages; ++$i) {
 if($i==$current_page) {
  echo "<a href='?current_page=$i'><font color=red>$i</font></a> ";
 }
 else {
  echo "<a href='?current_page=$i'>$i</a> ";
 }
} 


# Delete multiple rows
$del_query = mysqli_query($connect, "SELECT * FROM pdb");
if(mysqli_num_rows($del_query)==0) {
  echo "No rows deleted";
}
else {
  echo "<br><br><form name='delete' method='post'>";
}

if(empty($_POST)) {
 return false;
}
else {
  $foo = $_POST['list'];
  foreach ($foo as $value) {
    $delete_result = mysqli_query($connect, "DELETE FROM pdb WHERE id={$value}");
 }
 $numrows = mysqli_num_rows($delete_result);
 echo $numrows." row(s) deleted!";
}

// close connection
mysqli_close($connect);
?>

[chrisdee]

Nevermind. This solved it :

 if($numrows==0) { } else {
 $numrows = mysqli_num_rows($delete_result);
 echo $numrows." row(s) deleted!";
}}

[Derokorian]

For delete, update, and insert statements you should use: [man]mysql_affected_rows[/man].

[chrisdee]

Derokorian;11005429 wrote:

For delete, update, and insert statements you should use: [man]mysql_affected_rows[/man].

Thanks🙂 I will do that.