Multiple checkbox delete with MySQL

[Anon]

I have a table that generates a list of table rows and populates them with data from a MySQL table. Each of the table rows has a checkbox next to them to allow for multiple row deletions from the database. How do I get the values for $id so I can delete multiples from the database? The code below only allows for single row deletion or if you check multiple boxes it only deletes the last checked row from the database.
Thanks,
Troy

The table data for the checkbox row is:
echo "<td><input type=\"checkbox\" name=\"id\" value=\"$id\"></td>";

Code snippet of delete section.
if ($delete) {
db_connect();
$result = mysql_query ("DELETE FROM table WHERE id=$id") or die("Couldn't execute SQL query");
if ($result) {
echo "Thanks the record has been deleted";
}
if (!$result) {
echo "Not found in the database";
}
}

[Anon]

thats because you are using same NAME value in your INPUT tag of checkbox for every row. so the last one is always what is taken.

give different names to the NAME for each row. if the number of rows are static then you simply say id1, id2 etc. or if you are generating the rows dynamically then you need for loop with gives the value to NAME with increment. e.g. id1, id2, id3.

now for SQL part. you will need to put the query in seperate variable. like $sql, and then have a for loop for your WHERE part, so that it can be WHERE id='$id1' AND id='$id2' AND id='$id3' and so on....

i know its a bit tricky thing to do, on SQL query part. but lets see what happens. i might try this myself too.

regards,
Daarius...

[Anon]

The table rows are generated dynamically so I guess what I could do is put a [] next to the id name. So its: name=\"id[]\" then its parsed as an array.

The question then because the SQL part and finding out how to write the loop to delete the mutiple selections from the table.

Troy

[Anon]

for ($i=0; $i < $num_of_rows; $i++) {
$sql_id = $sql_id + "id='$id' AND ";
}

then you can use $sql_id in your WHERE as:
"DELETE FROM table WHERE $sql_id";

mind my syntax in for loop. i think in PHP its . to add up, something like .= i guess. please check that out.

[Anon]

Thanks for the help! You led me in the right direction and I finally was able to solve it with the following:
for ($i=0;$i < count($id); $i++)
{
$sql_id = $sql_id ."id=$id[$i]" . " OR ";
}
#Chop off the last OR statement on $sql_id
$sql_id = substr($sql_id, 0, -3);
$result = mysql_query ("DELETE FROM table WHERE $sql_id") or die("Couldn't execute SQL query");

Here is another tricky one which I don't even know if it is possible or not. can PHP functions interact with Javascript? What I would like to do is throw up a confirm dialog box when the delete button is checked.

<script language="JavaScript">
function deleteRecords() {
if (confirm("Are you sure you want to delete these records?")) {
//Whatever code that would execute a PHP function to delete records.
}

Any ideas on how this could be done to work together?

Thanks,
Troy

[Anon]

well, how about an 'alert' function of JavScript, which pops up the warning window, when you click on Submit button.

i dont know if you can do that with clicking on the selectbox. But, i think after you make your selection, and then when you click on Submit button, you can simply call OnClick event with alert.

try it...

[Anon]

If you wanted to delete multiple ids from the tables, your sql code will have to be 'or' in the where clause.

WHERE id='$id1' OR id='$id2' OR id='$id3' and so on....

because you'll never have a record with all those id values, so and will not work.

[Anon]

after review all the tread, i found a new way to delete multiple checkbox where you can use it to delete multiple selection like in hotmail, yahoo! mail... etc.

the code like this.
in HTML, <- the form.
<?$i=0; then do the loop?>
<input type="checkbox" name="$deleteuser[$i]" value=$value">
<?$i++;?>

in the database part.
<?
$r1 = mysql_query("SELECT * FROM test", $conn);
for($i=0; $i<mysql_num_rows($r1); $i++)
{
if($deleteuser[$i]=="") $deleteuser[$i] = "''";
$sqlstm = $sqlstm."id = $deleteuser[$i]"." OR ";
}
$sqlstm = substr($sqlstm, 0, -3);

mysql_query("DELETE FROM test WHERE $sqlstm");
?>

i think this is complete enough to delete multiple checkbox.. mail me if u have any questions

[Anon]

You wrote:
$sql_id = substr($sql_id, 0, -3);

I know this must me a stoopid question, but
can anyone tell me what the 0 and the -3 stands for?

I think the 0 has something to do with initializing the ."id=$id[$i]" Am I right? But where did the -3 come from?

Thanks.
C.

[Anon]

$sqlstm = substr($sqlstm, 0, -3);
to remove the last 'OR' after the loop finish.. somehow like that.. since today is september 11 already.. that code i wrote at july 29... sorry.