PHP Lessons - Lesson 3.3 - Working with MySQL DB. Updating data UPDATE.

We have already learned MySQL operators like SELECT and INSERT INTO. Now it's time to learn how to update existing data in the database using the SQL UPDATE operator. But first, let's modify our index.php file and add a new route. Find this code:

if ($_GET['admin'] == 1) { 
  print $obj->display_admin(); 
} else {
  print $obj->display_public(); 
}

And replace it with the following code:

switch ($_GET['admin']) {
  case 'add':
    print $obj->display_admin();
    break;
  case 'update':
    print $obj->display_update();
    break;
  default:
    print $obj->display_public();
}

I've replaced the if statement with a switch statement to handle multiple values of $_GET['admin']. We’ll be adding delete later. For now, I’ve changed 1 to add and added another case for update. Since we changed the value from 1 to add, we also need to update the link output in simpleCMS.php:

$content .= '
Add Message
';

Replace it with:

$content .= '
Add Message
';

Now, let's add a new method display_update(), which will be triggered when the admin parameter in the GET request has the value update:

public function display_update() {
  
}

We’ve now defined a route that calls our new display_update() method. Let’s add links on the page to trigger it and then implement the method.

Before that, let’s consider how we’ll implement record updates. We'll need to execute an UPDATE query with a WHERE clause to target a specific record. This means passing the mid (message ID) value to the method. This can be done using a GET parameter like so:

http://test/index.php?admin=update&mid=3

Or by using a parameter inside the method:

public function display_update($mid) {
  
}

And calling it like this:

switch ($_GET['admin']) {
  case 'add':
    print $obj->display_admin();
    break;
  case 'update':
    $mid = 1; // or $_GET['mid']
    print $obj->display_update($mid);
    break;
  default:
    print $obj->display_public();
}

We’ll go with the first option and extract mid from the GET parameter:

public function display_update() {
  $message_id = $_GET['mid'];
}

But we need to check whether mid exists in the GET request:

public function display_update() {
  $message_id = $_GET['mid'];
  if (!empty($message_id)) {

  } else {
    $content .= '
No mid value provided!
';
    $content .= '
Return to homepage
';
  }
  return $content;
}

Now if we go to admin=update without a mid, we’ll be redirected to a message.

http://test/index.php?admin=update

Now, let’s add links to trigger edit mode. Modify the display_public() method:

public function display_public() {
  $content = '';
  $sql = 'SELECT * FROM Messages ORDER BY mid DESC';
  $result = mysql_query($sql) or die(mysql_error());
  while ($row = mysql_fetch_array($result)) {
    $content .= '
';
    $content .= '#' . $row['mid'] . ' on ' . date('d-m-Y', $row['created']) . '
' . $row['title'] . '
';
    $content .= '
' . $row['bodytext'] . '
';
    $content .= '
Edit message';
    $content .= '
';
  }
  $content .= '
Add Message
';
  return $content;
}

Now each message will have a link to edit it. Let’s implement the display_update() form. The form will be similar to the add form but will contain existing values and a hidden mid input:

public function display_update() {
  $message_id = $_GET['mid'];
  if (!empty($message_id)) {
    $result = mysql_query('SELECT * FROM Messages WHERE mid=' . $message_id);
    $message = mysql_fetch_object($result);
    $content = '';
    $content .= '
';
    $content .= 'Name:
';
    $content .= '';
    $content .= '
';
    $content .= '';
    $content .= 'Message:
';
    $content .= '' . $message->bodytext . '';
    $content .= '
';
    $content .= '';
    $content .= '
';
    $content .= '
Return to homepage
';
  } else {
    if (!empty($_POST)) {
      mysql_query('UPDATE Messages SET title="' . $_POST["title"] . '", bodytext="' . $_POST["bodytext"] . '" WHERE mid=' . $_POST["mid"]);
      $content .= '
Message updated!';
      $content .= '
Go to entry
';
    } else {
      $content .= '
No mid value provided!
';
      $content .= '
Return to homepage
';
    }
  }
  return $content;
}

Here’s our full display_update() method. Let’s now understand the SQL UPDATE query structure:

First comes the UPDATE operator, followed by the table name. Then comes SET with the column assignments. Finally, the WHERE clause ensures only the record with the correct mid is updated. I’ll prepare a summary of all MySQL operators later. This lesson is primarily aimed at PHP/MySQL beginners.