Deleting Records

The sripts on this page use the example table from the previous page.

Deleting a record from a table will involve the following steps,

  • The user needs a way to select the record or records to be deleted
  • The user needs a chance to confirm or cancel the operation
  • The record needs to be deleted
  • The user needs to be informed of the result

Selecting The Record For Deletion

There are lots of ways you can do this, this is just one of them. In this script, we make a table of all of the records. The last cell of each row contains a little form with a button. There is a hidden field in the form storing the ID of the record we want to delete.

<?php
// database connection
$h = "localhost";
$u = "root";
$p = "";
$connection = mysql_connect($h,$u,$p);
if (!$connection)
{
   die("Could not connect to the database");
}
mysql_select_db("test");


// display all records
$query = "SELECT * FROM person";
$result = mysql_query($query);
$num = mysql_num_rows($result);
if($num>0)
{
   echo "<table><tr><th>Person Id</th><th>Surname</th><th>Forename</th><th>Description</th><th>&nbsp;</th></tr>\n";
   while ($row = mysql_fetch_array($result))
   {
      extract($row);
      echo "<tr><td>".$personid."</td><td>".$surname."</td><td>".$forename."</td><td>".$description."</td><td>\n";
      echo "<form action='delete.php' method='POST'><input type='hidden' name='todel' value='".$personid."'>
      <input type='submit' value='DELETE'></form>\n
      </td></tr>";
   }
echo "</table>";
}
else
{
   echo "<p>No records in the table.</p>";
}
?>

Confirming Deletion

Before deleting the record, we need to let the user confirm that they haven't made a mistake. The code is now re-arranged with some IF statements.

<?php
// database connection
$h = "localhost";
$u = "root";
$p = "";
$connection = mysql_connect($h,$u,$p);
if (!$connection)
{
   die("Could not connect to the database");
}
mysql_select_db("test");

if (isset($_POST['todel']))
{
   // user has chosen to delete, ask them to confirm
   echo "<h1>Are You Sure</h1>";
   $getridof = $_POST['todel'];
   echo "<form action='delete.php' method='POST'>Do you really want to get rid of record ".$getridof."?<br>";
   echo "<input type='hidden' name='remove' value='".$getridof."'><input type='submit' name='yes' value ='Yes'> || <input type='submit' name='no' value ='No'></form>";
}
else
{
   // display all records
   $query = "SELECT * FROM person";
   $result = mysql_query($query);
   $num = mysql_num_rows($result);
   if($num>0)
   {
      echo "<table><tr><th>Person Id</th><th>Surname</th><th>Forename</th><th>Description</th><th>&nbsp;</th></tr>\n";
      while ($row = mysql_fetch_array($result))
      {
         extract($row);
         echo "<tr><td>".$personid."</td><td>".$surname."</td><td>".$forename."</td><td>".$description."</td><td>\n";
         echo "<form action='delete.php' method='POST'><input type='hidden' name='todel' value='".$personid."'>
         <input type='submit' value='DELETE'></form>\n
         </td></tr>";
      }
      echo "</table>";
   }
   else
   {
      echo "<p>No records in the table.</p>";
   }
}
?>

Deleting The Record

Once the user has selected and confirmed that they want to delete a particular record, we need to make it happen.

<?php
// database connection
$h = "localhost";
$u = "root";
$p = "";
$connection = mysql_connect($h,$u,$p);
if (!$connection)
{
   die("Could not connect to the database");
}
mysql_select_db("test");

if (isset($_POST['todel']))
{
   // user has chosen to delete, ask them to confirm
   echo "<h1>Are You Sure</h1>";
   $getridof = $_POST['todel'];
   echo "<form action='delete.php' method='POST'>Do you really want to get rid of record ".$getridof."?<br>";
   echo "<input type='hidden' name='remove' value='".$getridof."'><input type='submit' name='yes' value ='Yes'> || <input type='submit' name='no' value ='No'></form>";
}
elseif (isset($_POST['remove']))
{
   // check the confirmation
   if (isset($_POST['yes']))
   {
      // delete it
      $toremove = $_POST['remove'];
      $query = "DELETE FROM person WHERE personid=".$toremove;
      $result = mysql_query($query);
      echo "<h1>Deleted</h1><p>Record removed. <a href='delete.php'>Do more damage</a>.</p>";
   }
   else
   {
      // cancel
      echo "<h1>Bail Out</h1><p>Operation Cancelled.</p>";
   }
}
else
{
   // display all records
   $query = "SELECT * FROM person";
   $result = mysql_query($query);
   $num = mysql_num_rows($result);
   if($num>0)
   {
      echo "<table><tr><th>Person Id</th><th>Surname</th><th>Forename</th><th>Description</th><th>&nbsp;</th></tr>\n";
      while ($row = mysql_fetch_array($result))
      {
         extract($row);
         echo "<tr><td>".$personid."</td><td>".$surname."</td><td>".$forename."</td><td>".$description."</td><td>\n";
         echo "<form action='delete.php' method='POST'><input type='hidden' name='todel' value='".$personid."'>
         <input type='submit' value='DELETE'></form>\n
         </td></tr>";
      }
      echo "</table>";
   }
   else
   {
      echo "<p>No records in the table.</p>";
   }
}
?>