Page 1 of 1
How to delete multiple rows from a database table at once?
Posted: Thu Jul 30, 2009 8:26 am
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?
Re: How to delete multiple rows from a database table at once?
Posted: Thu Jul 30, 2009 8:39 am
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')
Re: How to delete multiple rows from a database table at once?
Posted: Thu Jul 30, 2009 9:07 am
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:
So if I do:
Code: Select all
DELETE FROM TABLE insight_menu WHERE $id = $eid
Will that work?
Re: How to delete multiple rows from a database table at once?
Posted: Thu Jul 30, 2009 10:01 am
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
Re: How to delete multiple rows from a database table at once?
Posted: Thu Jul 30, 2009 10:24 am
by insight
EDIT: whoops, wrong thread
