How to Create a Multiple Item Delete Admin Script
November 12, 2003 - written by Steven Moseley
Administration of a database can be a pain in the arse if you have to delete dozens or hundreds of items one by one by one. If you have an interface like this, you may want to make one where you can delete multiple rows from a MySQL table all in one shot. This tutorial will show you how to do exactly that and provide you a script by which you can easily set up such an interface for yourself using a checkbox list.
The concept is based upon two principles.
In SQL, multiple items can be deleted from a table using the WHERE IN selection criterion as shown below.
In HTML, a series of checkboxes with the same "name" attribute value will submit a string value of "1, 2, 3", or an array of strings, such as ["1", "2", "3"] to the next page.
DELETE FROM table WHERE integer_value IN (1, 2, 3);
DELETE FROM table WHERE varchar_value IN ('A', 'B', 'C');
HTML Multiple Item Form Example:
<input type="checkbox" name="deleted_items[]" value="1" />
<input type="checkbox" name="deleted_items[]" value="2" />
<input type="checkbox" name="deleted_items[]" value="3" />
With those concepts in place, one should realize that concatenating a single DELETE query to handle multiple rows of data should be a snap. All we have to do is check the boxes corresponding to the rows we want to delete and concatenate the comma-delimited post value into the DELETE query and execute the query.
The following script does just that (the rest of my comments regarding this tutorial are in the script itself):
<?php
/**
* Title: PHP Multi-Delete Administration Script
* Author: Steven Moseley
* Source: www.transio.com
*/
/*
* You will need to change some values in this section to set up the
* script to work with your database and table. Follow the directions
* below and change the necessary values (there aren't many) as
* indicated by the comments (//)
*/
// Set your database connection information
$serverurl = "localhost";
$username = "username";
$password = "password";
// Set this to your database name
$database = "db_catalog";
// Set this to the name of the table you want to delete from
$table_name = "table_name";
// Set this to the name of the primary key column of that table
$table_pk = "id";
// Set this to the name of the descriptive column of that table
$table_desc = "transio_name";
/**
* That's all you have to do! You have now set up your script.
* If you want, you can add a header and footer or put some
* HTML after this PHP code block, but the script will work
* just like this!
*/
// Establish a connection with the database
mysql_connect($serverurl, $username , $password);
mysql_select_db($database);
// If the form was submitted, delete the items selected from the database
if ($_POST["deleted_items"]) {
$deleted_items = join(', ', $_POST["deleted_items"]);
$query = "DELETE FROM $table_name WHERE $table_pk IN ($deleted_items)";
$result = mysql_query($query);
header("Location: ".$PHP_SELF);
}
?>
<!--
Title: PHP Multi-Delete Administration Script
Author: Steven Moseley
Source: www.transio.com
This free script allows a user to delete multiple columns from a single table with a
single-column integer primary key. The script is free for use or modification.
Please leave this header in place to credit authors.
-->
<html>
<head>
<title>PHP Multi-Delete Administration Script</title>
</head>
<body>
<form action="<?php echo $PHP_SELF; ?>" method="post">
<table cellpadding="5" cellspacing="0" border="1">
<tr>
<th><?php echo $table_desc; ?></th>
<th>Delete?</th>
</tr>
<?php
// Get a list of items in the table
$query = "SELECT $table_pk, $table_desc FROM $table_name ORDER BY $table_desc ASC";
$result = mysql_query($query);
// Display the form so the user can delete one or more items from the table
while ($row = mysql_fetch_assoc($result)) {
echo "\t\t\t<tr>";
echo "<td>".$row[$table_desc]."</td>";
echo "<td><input type=\"checkbox\" name=\"deleted_items[]\" ";
echo "value=\"".$row[$table_pk]."\" /></td>";
echo "</tr>\n";
}
?>
</table>
<input type="submit" value="Delete Selected" />
</form>
</body>
</html>
That's about it! You can apply this script to just about any table with a single-column integer primary key (won't work for varchar id's because of the single-quote requirement for generating a SQL query with varchars). Try it out and post feedback and comments !!!