PHP Lessons - Lesson 3.4 - Working with MySQL DB. DELETE Deletion Queries.

In the previous lesson, we learned how to add new methods to our management class simpleCMS. Now let’s add one more method to delete a record: delete().

We’ll add the method as usual:

public function delete($mid) {
    
}

As you can see, we pass a parameter $mid – the ID of our record. If you recall the previous lesson, we used a different approach to pass the parameter via a GET request. This time we’ll try passing the parameter another way.

So, we’ve defined the method. Let’s now write the path handler in the index.php file:

switch ($_GET['admin']) {
  case 'add':
    print $obj->display_admin(); // if variable equals "add", display the add form
    break;
  case 'update':
    print $obj->display_update();  // if variable equals "update", display the update form
    break;
  case 'delete':
    if ($_GET['mid']) { // if variable equals "delete", check for mid
      $obj->delete($_GET['mid']); // call the method to delete a message
      print $obj->display_public(); // display messages after deletion
    } else {
      print '
No mid selected!
';
      print $obj->display_public(); // display messages list
    }
    break;
  default:
    print $obj->display_public(); // if no variable, display messages
}

To make sure everything works correctly and deletes the message, the URL should look like this:

http://test/index.php?admin=delete&mid=7

This will delete the message with mid=7. Now let’s implement our delete() method:

public function delete($mid) {
  mysql_query('DELETE FROM Messages WHERE mid=' . $mid) or die(mysql_error());
}

Let’s break down how the MySQL DELETE query works. First comes the word DELETE, then the FROM keyword. After FROM, we specify the table from which to delete records (⚠️ WARNING: if you don’t specify a condition, all records in the table will be deleted!), and then we add the WHERE clause. We are interested in the row with mid, whose value we pass as a parameter to the method. Finally, we add an error check.

As mentioned earlier, our method will be triggered when the URL contains two parameters: admin=delete and mid=7. So let’s add links below each message by modifying the display_public() method:

public function display_public() { // method to display messages
  $content = '';
  $sql = 'SELECT * FROM Messages ORDER BY mid DESC'; // selection query
  $result = mysql_query($sql) or die(mysql_error());  // store the result in a variable
  while ($row = mysql_fetch_array($result)) { // process result array
    $content .= '
'; // wrap message in div
    $content .= '#' . $row['mid'] . ' from ' . date('d-m-Y', $row['created']) . '
' . $row['title'] . '
'; // date and title
    $content .= '
' . $row['bodytext'] . '
'; // message text
    $content .= '
';
    $content .= 'Edit message'; // edit link
    $content .= 'Delete message'; // delete link
    $content .= '
';
    $content .= '
'; // close div
  }
  $content .= '
Add message
';
  return $content;
}

Now when we click the delete message link, the message will be deleted as expected. This allows us to quickly remove unnecessary messages from the site directly, without using PhpMyAdmin.

Let’s add a small margin between the edit and delete links:

.post a {
  padding-right: 5px;
}

I think this wraps up the lesson. In the next lesson, we’ll explore another important operator for retrieving data from multiple tables: JOIN.