How to delete multiple rows from a database table at once?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
insight
Forum Commoner
Posts: 52
Joined: Tue Jul 07, 2009 9:12 am

How to delete multiple rows from a database table at once?

Post by insight »

I was wondering how I could delete multiple rows from a database table at once instead of deleting them one at a time. I already know how to delete them one by one. But don't know how to delete all (or some) at once. Is there a way to do it via the $_GET method?
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: How to delete multiple rows from a database table at once?

Post by Mark Baker »

Supply a where clause to the delete statement that includes all the rows you want to delete.

You're probably already doing:

Code: Select all

DELETE FROM TABLE
WHERE id = 'xyz'
Try

Code: Select all

DELETE FROM TABLE
WHERE id IN ('xyz','abc')
insight
Forum Commoner
Posts: 52
Joined: Tue Jul 07, 2009 9:12 am

Re: How to delete multiple rows from a database table at once?

Post by insight »

Mark Baker wrote:Supply a where clause to the delete statement that includes all the rows you want to delete.

You're probably already doing:

Code: Select all

DELETE FROM TABLE
WHERE id = 'xyz'
Try

Code: Select all

DELETE FROM TABLE
WHERE id IN ('xyz','abc')
How can I make that dynamic? instead of having to add the id of each link every time I try to delete something. I want to make it so that users can just check a checkbox and for each checkbox they check it ads the id of every one of them to the WHERE clause.

What I'm doing so far is:

Code: Select all

$sql=mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die ("Unable to connect to the database because: " . mysql_error());
mysql_select_db(DB_NAME, $sql) or die ("Unable to select the database '$dbname' because: " . mysql_error());
 
$menu = mysql_query("SELECT * FROM insight_menu") or die ('Error : ' . mysql_error());
 
$count = 1;
 
while($fetchmenu = mysql_fetch_array($menu)) 
{
 
$id = $fetchmenu['id'];
$title = $fetchmenu['title'];
$link = $fetchmenu['weblink'];
$module = $fetchmenu['module'];
$level = $fetchmenu['level'];
 
echo "<tr align=\"center\"><td class=\"tbl\">";
 
echo $count++; 
 
echo "</td><td class=\"tbl\"><input type=\"checkbox\" name=\"check_list\" value=\"1\"></td><td align=\"left\" class=\"tbl\" style=\"padding-left: 5px;\"><a href=\"index.php?file=Admin&page=Menu&function=Edit&id=" . $id . "\">" . $title . "</a></td><td class=\"tbl\">Position</td><td class=\"tbl\">URL</td><td class=\"tbl\">" . $level . "</td><td class=\"tbl\">" . $id . "</td><td class=\"tbl\"><a href=\"index.php?file=Admin&page=Menu&function=Edit&id=" . $id . "\"><img border=\"0\" src=\"themes/default/images/edit_f2.png\" alt=\"\" /></a></td><td class=\"tbl\"><a href=\"index.php?file=Admin&page=Menu&function=Delete&id=" . $id . "\"><img border=\"0\" src=\"themes/default/images/cancel_f2.png\" alt=\"\" /></a></td></tr>\n";
}
 
echo "</table>\n"
.   "</fieldset>\n"
.   "</div>\n";
?>
Using $id to get the id of every link on every row of the database table and want to use:

Code: Select all

$eid = $_GET['id'];
So if I do:

Code: Select all

DELETE FROM TABLE insight_menu WHERE $id = $eid
Will that work?
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: How to delete multiple rows from a database table at once?

Post by mattpointblank »

Not like you want it to. You need to create a checkbox array, signified with the [ ] characters:

Code: Select all

 
<input type="checkbox" name="articles[]" value="123" />Item 1
<input type="checkbox" name="articles[]" value="456" />Item 2
<input type="checkbox" name="articles[]" value="789" />Item 3
 
Then in your PHP page:

Code: Select all

 // this turns your array of selected items into a comma-separated list, eg '123, 789'
$articleIDs = implode(", ", $_POST['articles']); 
$query = "DELETE FROM articles WHERE articleID IN (' . $articleIDs . ')";
$result = mysql_query($query) or die(mysql_error . ': ' . $query); // show an error and your query if it fails
insight
Forum Commoner
Posts: 52
Joined: Tue Jul 07, 2009 9:12 am

Re: How to delete multiple rows from a database table at once?

Post by insight »

EDIT: whoops, wrong thread :P
Post Reply