deleting records using an array

[digitalbart2k]

I have a problem when trying to delete records from a database. I have an array $_POST['list'] created by checkboxes from a form.

I would like to simply loop through this array and delete any records that were checked.

I get errors though with the code below, but it echos the correct information to the screen.

I have tried putting $_POST['list'][$i] in the sql statement and i still get errors.

for( $i = 0; $i < sizeof( $_POST['list'] ); $i++ ){

$a = each ($_POST['list']);

$query = "DELETE FROM send WHERE id = '$a' ";

echo $query;


$run = mysql($query);

echo mysql_error();

print( $_POST['list'][$i] . " was deleted<br>" );


// Delete the row from the database, etc ...
}

[rinjani]

You could do something like this assuming your connection is already established:

while($x = each($_post['list'])) {

$query = "DELETE FROM send WHERE id = '$x[1]' ";

$run = mysql($query);

}

or make a list and do a delete using IN

while($x = each($edit_id))
{

$id_list = $id_list . " '" . $x[1] . "',";

}

//remove the ending comma
$id_list = substr("$id_list", 0, -1);

$query = "DELETE from send WHERE id IN ($id_list)";

Just off the top of my head so check it for errors

rinjani

[ahundiak]

Another problem is that id may be an integer column in which case don't use quotes around the value.
$sql = "delete from send where id = $a";

[digitalbart2k]

thanks i removed the quotes since it is an interger and i am getting this error:

Warning: Wrong parameter count for mysql()

[keith73]

Originally posted by digitalbart2k
thanks i removed the quotes since it is an interger and i am getting this error:

Warning: Wrong parameter count for mysql()

that's because the call should be to mysql_query()

$run = mysql_query($query);

• keith

[digitalbart2k]

Thanks for all your help that fixed it. mysql_query I wish i would have saw that sooner.

[devinemke]

for better performance, you should definitley contruct one big SQL query rather than executing several individual queries in a loop.

rinjani has the right idea: loop thru the array to create a big string that can be used in the IN() clause of your delete query.

[digitalbart2k]

I would but i don't understand one part of what the code is doing:

$id_list = $id_list . " '" . $x[1] . "',"; 

} 

//remove the ending comma 
$id_list = substr("$id_list", 0, -1); 

$query = "DELETE from send WHERE id IN ($id_list)"; 

what does " $id_list = substr("$id_list", 0, -1); " this do ?

[devinemke]

well, let's back up a minute. here's the way i would do this:
assuming that $POST['list'] is an array of primary keys of records that you want to delete

$sql = 'DELETE FROM send WHERE id IN (' . implode (',', $_POST['list']) . ')';
mysql_query ($sql);

obviously, i've striped out all of your error checking code but you get the idea. the implode() function creates a string out of the elements of an array with a defined sepeartor (a comma in this case). so we are creating an SQL query with all of our id's inside the IN() clause separated by commas.

[rinjani]

devinemke has a much more elegant way of creating the string. I wasn't thinking straight as I modified the original for loop into a while loop.

The implode is of course the way to go. Thanks for cleaning that up.

digitalbart2k the little extra piece of code I made was to knock off the extra comma that my while loop added while making the string by appending each element of the array.

More coffee is necessary.

rinjani

[digitalbart2k]

thanks for all your help! The code works good and is much more efficient.