PHP and MySQL - Updating Data

To amend a record, the SQL 'update' statement needs to be used as follows.

UPDATE table_name
SET column_name='value'
WHERE where_condition

As with the 'select' and 'insert' statements, a comma can be used where more than one field needs to be updated.

UPDATE table_name
SET column_name1='value1', column_name2='value2', column_name3='value3'
WHERE where_condition

The 'where' condition might be the 'id' of a particular record for example. The example below uses the same 'person' table from the previous examples for selecting and inserting data. It updates the 'lastname' and 'title' for the record with an 'id' of 5.


   // Connect to the database.
   // Query parameters.
   $lastName = "Bloggs";
   $title = "Mrs";
   $id = 5;
      // Prepare the query.
      $results = $connect->prepare("UPDATE person
                                    SET lastname = ?, title = ?
                                    WHERE id = ?");
      // Bind the parameters.
      $results->bindParam(1, $lastName);
      $results->bindParam(2, $title);
      $results->bindParam(3, $id);
      // Execute the query.
   } catch(Exception $e) {
      // If query fails, display an error and exit.
      echo "Error updating person information.";

   // Display a message saying person updated successfully.
   echo "Person updated successfully.";


The contents of the 'person' table, following the update, can now be seen below.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
2 George Jones Mr 1997-12-15
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Bloggs Mrs 1985-05-19