...

View Full Version : Delete multiple rows from table



auriaks
04-02-2010, 04:48 PM
Hi,

I know how to delete one row from mysql table by using


<input type="radio" name="ID" value="'.$IDE.'" id="e" >
and
$delete = mysql_query("DELETE FROM `my_options` WHERE id = '$IDE'");

But now I need to delete multiple rows by one pushed button. Also, then I will need checkboxes to select multiple items...

But how to delete selected??

Thanks for your time.

abduraooft
04-02-2010, 04:58 PM
But how to delete selected?? You could make a list of selected ids like
(1,2,3,4,5 ...) and use that list, like

"DELETE FROM `my_options` WHERE id IN (1,2,3,4,5)"

auriaks
04-02-2010, 06:13 PM
this list is an array or what?

Dormilich
04-03-2010, 06:46 AM
no, it’s a string (like any value in a SQL string)

Phil Jackson
04-03-2010, 10:07 AM
<form action="./" method="post">
<input type="checkbox" name="foo[]" value="1" />
<input type="checkbox" name="foo[]" value="2" />
<input type="checkbox" name="foo[]" value="3" />
<input type="checkbox" name="foo[]" value="4" />
<input type="checkbox" name="foo[]" value="5" />
<input type="submit" name="submit">
</form>
<?php
if(isset($_POST['submit'])){
if(isset($_POST['foo'])){
foreach( $_POST['foo'] as $checkBoxValue ) {
mysql_query("DELETE FROM `my_options` WHERE id = '" . $checkBoxValue . "'") or die ( mysql_error() );
}
}
}
?>

abduraooft
04-03-2010, 10:12 AM
foreach( $_POST['foo'] as $checkBoxValue ) {
mysql_query("DELETE FROM `my_options` WHERE id = '" . $checkBoxValue . "'") or die ( mysql_error() );
}It's not a good practice to run query inside a loop, as it'll add overheads to the DB server.

Phil Jackson
04-03-2010, 10:18 AM
foreach( $_POST['foo'] as $checkBoxValue ) {
$q = mysql_query("DELETE FROM `my_options` WHERE id = '" . $checkBoxValue . "'") or die ( mysql_error() );
mysql_free_result( $q );
}

Phil Jackson
04-03-2010, 10:22 AM
<?php
$str = "(" . implode( ", ", $_POST['foo']) . ")";
$q = mysql_query("DELETE FROM `my_options` WHERE id IN " . $str ) or die ( mysql_error() );
mysql_free_result( $q );
?>

auriaks
04-03-2010, 01:26 PM
<?php
$str = "(" . implode( ", ", $_POST['foo']) . ")";
$q = mysql_query("DELETE FROM `my_options` WHERE id IN " . $str ) or die ( mysql_error() );
mysql_free_result( $q );
?>


you changed your script twice.... which is correct?

met
04-03-2010, 01:43 PM
both are, technically

one is more resource efficient than the other as it doesn't make use of a query inside a loop, which as abduraooft stated, creates (unnecessary) overhead

auriaks
04-03-2010, 04:04 PM
thanks for answers :)

MattF
04-03-2010, 05:54 PM
No sanitisation/validation of the id's has been done in those examples, however. Do the necessary checks.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum