Deleting data in table

[markandkelle]

Hi,
I have a simple script that connects to my MySQL database, and shows me the data that I have requested in a table layout.

I have searched a few guides and I am struggling with the next bit though, I want to display a checkbox at the end of each row and select and then a button to delete. Any ideas on how I can do this?

here is my code so far that calls the information

<?
mysql_connect("","","") or die(mysql_error());
mysql_select_db("") or die(mysql_error());

$query = "SELECT * FROM "; 

echo "<table style='width:100%'><tr>";
echo "<td>Title</td>";
echo "<td>Name</td>";
echo "<td>Company</td>";
echo "<td>Address1</td>";
echo "<td>Address2</td>";
echo "<td>Town</td>";
echo "<td>County</td>";
echo "<td>Postcode</td>";
echo "</tr>";

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){

echo "<td>".$row['title']."</td>";
echo "<td>".$row['name']."</td>";
echo "<td>".$row['company']."</td>";
echo "<td>".$row['address1']."</td>";
echo "<td>".$row['address2']."</td>";
echo "<td>".$row['town']."</td>";
echo "<td>".$row['county']."</td>";
echo "<td>".$row['postcode']."</td>";
echo "</tr>";
}

echo "</table>";
echo "<br />";
?>

[scragar]

you need some form of ID in the database(I'm assuming auto increment called 'ID'), then you add a form over the entire table and your checkboxes:

echo "<form action='?' method='POST'>

<table style='width:100%'><tr>
<th>delete</th>
<td>Title</td>
<td>Name</td>
<td>Company</td>
<td>Address1</td>
<td>Address2</td>
<td>Town</td>
<td>County</td>
<td>Postcode</td>
</tr>"; 

$result = mysql_query($query) or die(mysql_error()); 
while($row = mysql_fetch_array($result)){ 
  echo "<td><input type='checkbox' name='rm[]' value='{$row['ID']}' /></td>
<td>{$row['title']}</td>
<td>{$row['name']}</td>
<td>{$row['company']}</td>
<td>{$row['address1']}</td>
<td>{$row['address2']}</td>
<td>{$row['town']}</td>
<td>{$row['county']}</td>
<td>{$row['postcode']}</td>
</tr>"; 
} 

echo "</table></form>"; 
echo "<br />"; 
?> 

then a relevant delete statement:

foreach($_POST['rm[]'] as $key => $val){// check all results
  if(!is_int($val))// if the result is not a number
    unset($_POST['rm'][$key]);// remove it from listings.
};
// run remove:
mysql_query("DELETE FROM tbl WHERE ID IN (".implode($_POST['rm[]'], ', ').")"); 

[markandkelle]

Hi Thanks for your help, I think I am nearly there, so I have set up the ID in MySQL and added the checkboxes, the form action I have set as delete.php and then created this file as;

<?
foreach($_POST['rm[]'] as $key => $val){// check all results 
  if(!is_int($val))// if the result is not a number 
    unset($_POST['rm'][$key]);// remove it from listings. 
}; 
// run remove: 
mysql_query("DELETE FROM tbl WHERE ID IN (".implode($_POST['rm[]'], ', ').")");  ?>

however I get these errors

Warning: Invalid argument supplied for foreach() in /home/ayoocouk/public_html/mywebsite.co.uk/delete.php on line 7

Warning: implode() [function.implode]: Bad arguments. in /home/ayoocouk/public_html/mywebsite.co.uk/delete.php on line 12

any idea what I am doing wrong?

thanks again

[scragar]

<?
if(isset($_POST['rm[]'])){
  foreach($_POST['rm[]'] as $key => $val){// check all results 
    if(!is_int($val))// if the result is not a number 
      unset($_POST['rm'][$key]);// remove it from listings. 
  };
  // run remove: 
  if(count($_POST['rm[]'])){
    mysql_query("DELETE FROM tbl WHERE ID IN (".implode($_POST['rm[]'], ', ').")");
    echo mysql_affect_rows()." results deleted.";
  };
};
?>

forgot checking, sorry.

and replace tbl in the query with your table name.

[scragar]

<? 
if(isset($_POST['rm'])){ 
  foreach($_POST['rm'] as $key => $val){// check all results 
    if(!is_int($val))// if the result is not a number 
      unset($_POST['rm'][$key]);// remove it from listings. 
  }; 
  // run remove: 
  if(count($_POST['rm'])){ 
    mysql_query("DELETE FROM tbl WHERE ID IN (".implode($_POST['rm'], ', ').")"); 
    echo mysql_affect_rows()." results deleted."; 
  }; 
}; 
?>

after a bit of testing it works out that PHP5(and possibly PHP4) submits as an array without the [] in it's name, use the version above if you have any problems.