How Do I Delete Selected Rows From MySQL Table?

[Volitics]

I am trying to figure out how to delete selected rows from a MySQL database table.

The web site that I am working on has a page that will extract rows, using the "SELECT * FROM .. etc." statement from a database and output them in a table - like the one below (bottom part). I've got that part figured out.

What I am trying to figure out is if the user puts a checkmark in one of the checkboxes (below) how would I write the PHP code to delete one or two of the rows from the MySQL table?

I would appreciate if anyone could provide assistance and show me what to do.

if ($_POST[Submit]){
require_once "./conf/config.inc.php";

//----- Code for MySQL database below here - need help with this part.
$TableName = "Customers";
$Query = "DELETE * FROM $TableName";
$Result = mysql_db_query ($db_info[dbname], $Query, $db_connection);
//--------------------------------
}else{
//------ Form and HTML below here - also need help with this part.
//------ The table below is the output from MySQL "SELECT * FROM $Tablename" code.
echo "<form action=\"$PHP_SELF\" method=\"post\">";
echo "<table>";
echo "<tr>"; //Row one of the table here.
echo "<td><input type=\"checkbox\" name=\"checkbox1\" value=\"delete\"></td>";
echo "<td><input type=\"hidden\" name= \"FirstName\" value=\"John\">John</td>";
echo "<td><input type=\"hidden\" name= \"LastName\" value=\"Doe\">Doe</td><td>&nbsp;</td>";
echo "</tr>";

echo "<tr>"; //Row two of the table here.
echo "<td><input type=\"checkbox\" name=\"checkbox2\" value=\"delete\"></td>";
echo "<td><input type=\"hidden\" name= \"FirstName\" value=\"Fred\">Fred</td>";
echo "<td><input type=\"hidden\" name= \"LastName\" value=\"Black\">Black</td>";
echo "</tr>";

echo "<tr>"; //Row three of the table here.
echo "<td><input type=\"checkbox\" name=\"checkbox2\" value=\"delete\"></td>";
echo "<td><input type=\"hidden\" name= \"FirstName\" value=\"Fred\">Fred</td>";
echo "<td><input type=\"hidden\" name= \"LastName\" value=\"Black\">Black</td>";
echo "</tr>";
echo "</table>";
echo "<input type=\"submit\" name=\"Submit\" value=\"Submit To Delete\">";
echo "</form>";
}

Thanking you in advance.

Volitics

[seby]

something like this:

if ( $_POST['FirstName'] )
{
	$field = 'FirstName';
} elseif( $_POST['LastName'] )
{
	$field = 'LastName';
}

$result = @mysql_query("DELETE FROM $TableName WHERE your_field = '$field'"); 

you have to change each <input to have its own name, example:

FirstName1
FirstName2
FirstName3

etc, etc...

[Volitics]

seby;

Thank you for your kind help.

Best Regards;

Volitics

[Oersoep]

That can be done a lot better...

The form:
(Too bad javascript can't deal with these field-names properly)

<form method='post'>
   <input type='checkbox' name='selection[]' value='1'>

   <input type='hidden' name='firstname[1]' value='...'>
   <input type='hidden' name='lastname[1]' value='...'>

   <input type='checkbox' name='selection[]' value='2'>

   <input type='hidden' name='firstname[2]' value='...'>
   <input type='hidden' name='lastname[2]' value='...'>

   <input type='checkbox' name='selection[]' value='3'>

   <input type='hidden' name='firstname[3]' value='...'>
   <input type='hidden' name='lastname[3]' value='...'>

   <input type='submit' name='delete' value='delete'>
   <input type='submit' name='update' value='update'>

</form>

The queries:

if( $delete ){ // if posted

   for( $i= 0 ;$i < sizeof( $selection); $i++ ){

  $query = "delete from table where id = '".$selection[$i]."'";
  $result = mysql_result( $query, $connection );
  if( !$result){
      // start whining
  }

  }  

}

if( $update ){
  // do something else
}

This way you can do this without hardcoding the number of items to be deleted or do anything with variable variables.
I'm wondering: Why store all data in hidden fields when you're going to delete them anyway?

[The_Chancer]

Always good practice to add LIMIT 1 at the end of the query though - just in case...

[Oersoep]

Disclaimer:
Of course my code will only work if the numbers are indeed the id's in the database...
If you got register_globals = off, you need to use $_POST['fieldname']