[SOLVED] Delete from database problem

[sford999]

Hi,

I`m using the following code to delete things from a db table, but when I click on the delete button, the page just refreshes and doesn`t delete the checked entries.

Theres 3 files,
index.php which uses a query string such as index.php?cmd=delgloss
inc/del_gloss.php which contains:

[code]<?php

delete_from_glossary();

?>[/code]

And functions.php which has the function below

[code]<?php

function delete_from_glossary()
{
$sql = "SELECT * FROM glossary";
$result = mysql_query($sql);

$count = mysql_num_rows($result);

$color1 = "#D1DCEB";
$color2 = "#DFEDFF";
$row_count = 0;

echo "<form name=\"DeleteFromGlossary\" method=\"post\" action=\"\">
<table width=\"100%\" border=\"0\" class=\"tables\">
  <tr>
    <td width=\"2%\" class=\"maintitle\">ID</td>
    <td width=\"14%\" class=\"maintitle\">Name</td>
    <td width=\"14%\" class=\"maintitle\">Email</td>
    <td width=\"15%\" class=\"maintitle\">IP</td>
    <td width=\"20%\" class=\"maintitle\">Date</td>
    <td width=\"25%\" class=\"maintitle\">Title</td>
<td width=\"5%\" class=\"maintitle\">Auth?</td>
    <td width=\"5%\" class=\"maintitle\">Delete?</td>
  </tr>";

while($row = mysql_fetch_array($result))
{
extract($row);
$row_color = ($row_count % 2) ? $color1 : $color2;
echo "<tr>
    <td width=\"4%\" align=\"center\" bgcolor=\"$row_color\" class=\"tables\">$id</td>
    <td width=\"13%\" bgcolor=\"$row_color\" class=\"tables\">$name</td>
    <td width=\"13%\" bgcolor=\"$row_color\" class=\"tables\">$email</td>
    <td width=\"15%\" bgcolor=\"$row_color\" class=\"tables\">$ip</td>
    <td width=\"25%\" bgcolor=\"$row_color\" class=\"tables\">$date</td>
    <td width=\"20%\" bgcolor=\"$row_color\" class=\"tables\">$title</td>
<td width=\"5%\" bgcolor=\"$row_color\" align=\"center\" class=\"tables\">$auth</td>
    <td width=\"5%\" bgcolor=\"$row_color\" align=\"center\" class=\"tables\"><input name=\"checkbox[]\" type=\"checkbox\" id=\"checkbox[]\" value=\"$id\"</td>
  </tr>";
$row_count++;
}

echo "<tr>
    <td colspan=\"8\" align=\"center\" class=\"tables\"><input name=\"delete\" type=\"submit\" id=\"delete\" class=\"button\" value=\"Delete\"></td>
  </tr>";

if(isset($delete))
{
for($i=0;$i<$count;$i++)
{
$del_id = $checkbox[$i];
$sql = "DELETE FROM glossary WHERE id='$del_id'";
$result = mysql_query($sql);
}

if($result)
{
echo "Success";
}
else
{
echo "Fail";
}
}

echo "</table>";

}

?>[/code]

[img]http://www.carpfishinguk.net/form.jpg[/img]

I think I`ve missed passing a variable somewhere, but I can`t figure it out.

Thanks in advance

[switchdoc]

Two things I can think of

1) echo out $del_id so you know its getting set correctly.

2) (and I may be wrong about this) can the action for your form really be "" ?

-Switch

[btherl]

There's a few oddities in there..

Firstly, you are using the count for the total number of rows in the database to iterate through $checkbox.  Probably you should count $checkbox instead.

Secondly, you are displaying the table before doing the deletion.  This means the table will look the same in the request in which you delete some rows, even though you are deleting some of the rows.

As far as working out what's going wrong goes, try doing a var_dump() or print_r() of $checkbox, and also try printing out each $sql that you use for deleting rows.  That should give you an idea of what's going wrong.

[exoskeleton]

hi..."for statement" is not suited in this case...
you can use "foreach statement" to make easier...

for example:

[code]
<?php

$faq_id=$_POST['faq'];

foreach ($faq_id as $faq_delete) {

mysql_query ("DELETE FROM tbl_faq WHERE faq_id='$faq_delete'");

}

?>
[/code]

Hope this helps...Good Luck!!!

More Power...

[sford999]

Right I`ve got it working somewhat (in other words I re-wrote it), but its only deleting one entry even if I check multiple entries, and I`m not sure why

[code]<?php

function delete_from_glossary()
{
$color1 = "#D1DCEB";
$color2 = "#DFEDFF";
$row_count = 0;
$query = "SELECT COUNT(*) as records FROM glossary";

// If no one checkbox is checked $_POST['DeleteGlossary'] is not set
// so we have to check existence of $_POST['DeleteGlossary'] as well

if (isset($_POST['Delete']) && isset($_POST['DeleteGlossary']))
{
$delArr = $_POST['DeleteGlossary'];
for ($k=0; $k < count($delArr); $k++)
{
$query = "DELETE FROM glossary WHERE id = " . $delArr[$k];

mysql_query($query) or die(sql_error());

echo "<p><div align=\"center\"><strong>Thank You the selected item(s) was successfully deleted.</strong>";
echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"3; URL=index.php?cmd=delgloss\">";
echo "<br />You will be redirected in just a moment.</p></div>";
exit();
}
}

echo "<form action=\"index.php?cmd=delgloss\" method=\"post\">";
$query = "SELECT * FROM glossary ORDER BY id";

// Execute a query
$result=mysql_query($query) or die(sql_error());

$num_records = mysql_num_rows($result);

if($num_records == 0)
{
echo "<table width=\"100%\" border=\"1\" cellspacing=\"1\" style=\"border-collapse: collapse\" bordercolor=\"#006\" width=\"100%\" cellpadding=\"5\">
  <tr>
    <td colspan=\"2\"><p><div align=\"center\">Sorry, there are no glossary entries in the database.</div></p></td>
  </tr></table>";
exit();
}
else
{
echo "<table border=\"1\" cellspacing=\"1\" style=\"border-collapse: collapse\" width=\"100%\" cellpadding=\"5\" align=\"center\">
      <tr>
    <td width=\"2%\" class=\"maintitle\"><div align=\"center\">ID</div></td>
    <td width=\"14%\" class=\"maintitle\">Name</td>
    <td width=\"14%\" class=\"maintitle\">Email</td>
    <td width=\"15%\" class=\"maintitle\">IP</td>
    <td width=\"20%\" class=\"maintitle\">Date</td>
    <td width=\"25%\" class=\"maintitle\">Title</td>
    <td width=\"5%\" class=\"maintitle\"><div align=\"center\">Auth?</div></td>
    <td width=\"5%\" class=\"maintitle\"><div align=\"center\">Delete?</div></td>
  </tr>";

for ($i=0; $i<$num_records; $i++)
{
while ($row = mysql_fetch_array($result))
{
extract($row);
$row_color = ($row_count % 2) ? $color1 : $color2;
echo "  <tr>
    <td width=\"2%\" bgcolor=\"$row_color\" class=\"tables\"><div align=\"center\">$id</div></td>
    <td width=\"15%\" bgcolor=\"$row_color\" class=\"tables\">$name</td>
    <td width=\"15%\" bgcolor=\"$row_color\" class=\"tables\">$email</td>
    <td width=\"10%\" bgcolor=\"$row_color\" class=\"tables\">$ip</td>
    <td width=\"25%\" bgcolor=\"$row_color\" class=\"tables\">$date</td>
    <td width=\"25%\" bgcolor=\"$row_color\" class=\"tables\">$title</td>
    <td width=\"4%\" bgcolor=\"$row_color\" class=\"tables\"><div align=\"center\">$auth</div></td>
    <td width=\"4%\" bgcolor=\"$row_color\" class=\"tables\"><div align=\"center\"><input name=\"DeleteGlossary[]\" type=\"checkbox\" id=\"DeleteGlossary[]\" value=\"".$id."\"></div></td>
  </tr>";
$row_count++;
}
}
}
echo "<tr>
      <td colspan=\"8\" class=\"tables\"><div align=\"center\"><input name=\"Delete\" type=\"submit\" id=\"Delete\" value=\"Delete Checked Entries\" class=\"button\"></div></td>
    </tr></table></form>";
}

?>[/code]

[blear]

[code]if (isset($_POST['Delete']) && isset($_POST['DeleteGlossary']))
{
$delArr = $_POST['DeleteGlossary'];
for ($k=0; $k < count($delArr); $k++)
{
$query = "DELETE FROM glossary WHERE id = " . $delArr[$k];

mysql_query($query) or die(sql_error());

echo "<p><div align=\"center\"><strong>Thank You the selected item(s) was successfully deleted.</strong>";
echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"3; URL=index.php?cmd=delgloss\">";
echo "<br />You will be redirected in just a moment.</p></div>";
exit();
}
}[/code]

Your mysql_query($query) is only a few lines above a refresh and exit() call, and in the same loop structure. Thus, as soon as it deletes the first row, it refreshes the page, never getting to the rest of the for() loop.

[sford999]

Thanks, I figured it out