MySQL/ PHP delete row

[PsychicTide]

Hey guys, I'm having some trouble setting up a form to delete a specific row in my database table.

Here is the idea: I have one page that makes looping calls to MySQL for a query result, at the same time I print out the html as I need to using row->column calls for each item (pretty much display every row in the database on the web-page how I choose fit). next to each of these items I have a checkbox with a variable for the value field (an auto incrementing variable I use as the primary key id

easier shown that explained...

snippet from page described above

<?php
/*connect to and select database*/

$num_rows = mysql_num_rows($result);
$i = 0;
while ($i < $num_rows)
{
  $row = mysql_fetch_array($result);
  $num = $row['id'];
?>
<form action="delete.php" method="post">
...
<div><input type="checkbox" name="number" value="<? $num ?>" /><br /><? echo $num ?></div>
...
/*HTML here which displays the rest of the PHP column variables I have set up*/
<? $i++;} ?>

The action file for the previous code/form

<?php
/*connect to and select database*/

$id = $_POST[number];
mysql_query("DELETE FROM home WHERE id='$id'") 
or die(mysql_error()); 
?>

I can see the id being echoed on the output page, but it doesn't seem to do anything when I attempt to delete it

I can change it to something like

mysql_query("DELETE FROM home WHERE category='Something'")

and it works that way I just can't seem to get a vairable to work out

if anyone has any ideas what I'm doing wrong (or even just some ideas) please let me know.

[urtrivedi]

first change name of checkbox to number[] (i have done that change only). this action will send checkbox array to processing page.

<?php
/*connect to and select database*/

$num_rows = mysql_num_rows($result);
$i = 0;
while ($i < $num_rows)
{
  $row = mysql_fetch_array($result);
  $num = $row['id'];
?>
<form action="delete.php" method="post">
...
<div><input type="checkbox" name="number[]" value="<? $num ?>" /><br /><? echo $num ?></div>
...
/*HTML here which displays the rest of the PHP column variables I have set up*/
<? $i++;} ?>

then join array values with comma using implode function

<?php
/*connect to and select database*/

$id = implode(",",$_POST[number]);
mysql_query("DELETE FROM home WHERE id in ({$id})") 
or die(mysql_error()); 
?>

[PsychicTide]

Thanks for the quick response urtrivedi. After trying out your idea, it seems I'm getting a syntax error.

I changed the checkbox name to number[] and copied your MySQL Query/implode lines in lue of my own and it say this...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

I've tried to modify the syntax around both the MySQL call and the implode function (with no luck)... on a side note I can type something like

$id = 12;
/*using the line of code you provided...*/
mysql_query("DELETE FROM home WHERE id IN ({$id})")

and it works, but this is the same problem I had before where I can't use an input variable

I'll keep playing around with it...

Any ideas on how I can fix this?

[urtrivedi]

echo certain things as i have written below and see what comes in id

echo "<pre>";
print_r($_POST['number']);
echo "</pre>";

$id = implode(",",$_POST[number]);
echo $id;
.
.
.

[PsychicTide]

Hey urtrivedi, Thanks again for the quick response. I've copied the lines of code you provided...

echo "<pre>";
print_r($_POST['number']);
echo "</pre>";

$id = implode(",",$_POST[number]);
echo $id;

This is the result that I got

Array
(
[0] =>
)

Now I'm thoroughly confused... any ideas why there is no value? If you want I can post the entire code I'm using again (since it has been modified)

[PsychicTide]

Success!!! I changed the the line of code in my html form from

<div style="text-align:left"><input type="checkbox" name="number[]" value="<? $num ?>" /><br /><? echo $num ?></div>

to (adding echo $num)

<div style="text-align:left"><input type="checkbox" name="number[]" value="<? echo $num ?>" /><br /><? echo $num ?></div>

it printed out this (I chose the last item in the database - #126)

Array
(
[0] => 126
)
126

I then changed my code back to the array/implode code you provided and it works!

Thanks you very very much for helping me out urtrivedi!