Mysql delete not working

[Buttero]

<?php

$host="localhost"; // Host name
$username="user"; // Mysql username
$password="pass"; // Mysql password
$db_name="dbname"; // Database name
$tbl_name="tblname"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
$count=mysql_num_rows($result);

?>

<div id="nav">
<? include("nav.php"); ?>
</div>

<form name="mysql" method="post" action="">

<h1>Delete multiple rows in mysql</h1>

<?php
while($rows=mysql_fetch_array($result)){
?>

<table width="100%" border="1">

<tr>

<td valign="top"></td>
<td valign="top"><strong class="title">Title</strong></td>
<td valign="top"><strong class="title">Date</strong></td>
<td valign="top"><strong class="title">Author</strong></td>

</tr>

<tr>

<td valign="top"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
<td valign="top"><? echo $rows['title']; ?></td>
<td valign="top"><? echo $rows['date']; ?></td>
<td valign="top"><? echo $rows['user']; ?></td>

</tr>

</table>

<?php
}
?>

<input name="delete" type="submit" id="delete" value="Delete">

<?
// Check if delete button active, start this

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

// if successful redirect to delete_multiple.php
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=index.php\">";
}
}
mysql_close();
?>

</form>

 

Its a simple checkbox, if you check it and press the delete button, its meant to wipe if from the table, but noting happens  :'( Can anyone help???

[The Little Guy]

Change this line:

$result = mysql_query($sql);

 

To this line:

$result = mysql_query($sql)or die(mysql_error());

 

Post back when you run this if you get an error please post the error

[alpine]

On this part

<?php

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

// if successful redirect to delete_multiple.php
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=index.php\">";
}
}

?>

 

Change to

<?php

if(isset($_POST['delete'])){
foreach($_POST['checkbox'] as $no => $del_id){
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
$result = mysql_query($sql);
}
// if successful redirect to delete_multiple.php
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=index.php\">";
}
}

?>

 

should get you going

[Buttero]

ok it now looks like it does something, it successfully goes to the index page, but never deletes anytihng, heres the code:

 

<?php

$host="localhost"; // Host name
$username="#"; // Mysql username
$password="#"; // Mysql password
$db_name="#"; // Database name
$tbl_name="#"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result = mysql_query($sql)or die(mysql_error());
$count=mysql_num_rows($result);

?>

<div id="nav">
<? include("nav.php"); ?>
</div>

<form name="mysql" method="post" action="">

<h1>Delete multiple rows in mysql</h1>

<?php
while($rows=mysql_fetch_array($result)){
?>

<table width="100%" border="1">

<tr>

<td valign="top"></td>
<td valign="top"><strong class="title">Title</strong></td>
<td valign="top"><strong class="title">Date</strong></td>
<td valign="top"><strong class="title">Author</strong></td>

</tr>

<tr>

<td valign="top"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
<td valign="top"><? echo $rows['title']; ?></td>
<td valign="top"><? echo $rows['date']; ?></td>
<td valign="top"><? echo $rows['user']; ?></td>

</tr>

</table>

<?php
}
?>

<input name="delete" type="submit" id="delete" value="Delete">

<?php

if(isset($_POST['delete'])){
foreach($_POST['checkbox'] as $no => $del_id){
$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
$result = mysql_query($sql);
}
// if successful redirect to delete_multiple.php
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=index.php\">";
}
}

?>

</form>

[alpine]

Try to set up some debugging by echoing id's etc while testing to see if it reveals anything. You are however constantly overwriting the $result during foreach, so the redirection is purely based on the last id delete

 

New example:

<?php

if(isset($_POST['delete'])){
if(isset($_POST['checkbox'])){

$fail = array();
$ok = array();

foreach($_POST['checkbox'] as $no => $del_id){

  $sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
  $result = mysql_query($sql) or die(mysql_error());
  
  if(mysql_affected_rows() == 1){
    $ok[] = "OK: ".$del_id;
  }
  else{
    $fail[] = "Failed: ".$del_id;
  }
}

if(empty($fail)){
  echo "<meta http-equiv=\"refresh\" content=\"0;URL=index.php\">";
}
else{
  echo "Following id's failed to delete:<br />";
  echo implode($fail, '<br />');
  echo "These id's returned ok and should be deleted:<br />";
  echo implode($ok, '<br />');
}
}
else{
  echo "No rows where selected to be deleted";
}
}

?>

[HuggieBear]

Try this code.  I've taken it from what you posted so it should drop straight in.

 

<?php
// DB connection options
$host="localhost"; // Host name
$username="#"; // Mysql username
$password="#"; // Mysql password
$db_name="#"; // Database name
$tbl_name="#"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Main query
$sql="SELECT * FROM $tbl_name";
$result = mysql_query($sql) or die(mysql_error());
$count = mysql_num_rows($result);
?>

<div id="nav">
<?php include("nav.php"); ?>
</div>

<form name="mysql" method="post" action="">
<h1>Delete multiple rows in mysql</h1>

<?php
while($rows = mysql_fetch_array($result)){
?>

<table width="100%" border="1">
<tr>
<td valign="top"></td>
<td valign="top"><strong class="title">Title</strong></td>
<td valign="top"><strong class="title">Date</strong></td>
<td valign="top"><strong class="title">Author</strong></td>
</tr>
<tr>
<td valign="top"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<?php echo $rows['id']; ?>"></td>
<td valign="top"><?php echo $rows['title']; ?></td>
<td valign="top"><?php echo $rows['date']; ?></td>
<td valign="top"><?php echo $rows['user']; ?></td>
</tr>
</table>

<?php
}
?>

<input name="delete" type="submit" id="delete" value="Delete">

<?php
/*
* This code is a bad idea as it's executing a query again and again and again.
* See the code I've posted below

if(isset($_POST['delete'])){
   foreach($_POST['checkbox'] as $no => $del_id){
      $sql = "DELETE FROM $tbl_name WHERE id='$del_id'";
      $result = mysql_query($sql);
   }

   // if successful redirect to delete_multiple.php
   if($result){
      echo "<meta http-equiv=\"refresh\" content=\"0;URL=index.php\">";
   }
}
*/

if (isset($_POST['delete']) && isset($_POST['checkbox'])){ // don't attempt to delete just because the hit delete, make sure they checked a box first
   $id_string = implode("','", $_POST['checkbox']);
   $sql = "DELETE FROM $tbl_name WHERE id IN '$id_string'";
   if(mysql_query($sql)){
      echo "Success! (" . mysql_affected_rows() . " were deleted)"; // once you know it's working, then put your meta redirect in
   }
   else {
      echo "Failed to delete (" . mysql_error() . ")";
   }
}
?>

</form>