Updating Items in a Database

Fairly often we will need to update existing information in a database. Consider a simple contact database you may have written to track friends and family members. If a contact moved, or changed phone numbers, you would want to update that information so it stayed current.

The UPDATE command in SQL allows you to modify an existing row in the database. It follows the general format of:

UPDATE table_name
 SET column1=value, column2=value2,...
 WHERE some_column=some_value

Don't Forget the WHERE

If the WHERE clause is left out, then all of the rows in the database table will get the same value.

Usually, updates come from a user manipulating a single existing record. So it is quite common to have a page which has an "edit" button on it. When the edit button is pressed, it takes the user to a page with a single record on it, in a form to edit the existing information.

Consider the following code.

<?php
include('dbconnect.php'); 
if(isset($_POST['submit'])) {
  $sql = 'UPDATE guestbook SET name=' .SQLValueString($_POST['name'], 'text')
     .', entry=' .SQLValueString($_POST['entry'], 'text')
     .' WHERE id=' .SQLValueString($_POST['id'], 'int');
  $result = mysql_query($sql, $db_connection) or die('Error: ' .mysql_error());
  echo '<p class="notice">Database Updated</p>';
}


$sql = 'SELECT `id`, `name`, `entry`, DATE_FORMAT(`entered`, "%M %e, %Y") AS formated_date '
    .'FROM guestbook'   ." WHERE `id`=" .SQLValueString($_GET['id'], 'int');
$result = mysql_query($sql, $db_connection) or die('Error: ' .mysql_error());

if($row = mysql_fetch_assoc($result)) {
  echo '<form method="post"><ul>'
    .'<li><label for="name">Commenter\'s Name</label><input name="name" id="name" value="'
    .stripslashes($row['name']) .'" /></li>'
    .'<li><label for="entry">Comment</label><textarea name="entry" id="entry">'
    .stripslashes($row['entry']) .'</textarea></li>'
    .'<li><input name="submit" type="submit" id="submit" value="Update Comment" /></li>'
    .'</ul><input type="hidden" name="id" value="' .SQLValueString($_GET['id'], 'int')
    .'" /></form>';
} else {
  echo '<h1 class="error">ERROR</h1><p>No record found to be able to update.</p>';
}
?>

The PHP Code Explained

if(isset($_POST['submit'])) { } - We've seen this before, it's to determine if we are going to run the code inside or not.

$sql = - We build the SQL string using inputs and the standard SQL UPDATE command.

echo '<p class="notice">... - We give the user information so they know what has happened, and don't think there was a glitch in the system which just refreshed the screen.

$sql = '... WHERE `id`=" .SQLValueString($_GET['id'], 'int'); - The WHERE clause limits the records from the database to a specific row. In this case, we are pulling it in via a value passed in the URL string.

echo '<form... - We create a form tag, with all of the appropriate values that we should edit. It places the values of the existing row into our default value of the HTML tags.

stripslashes() - This is a special PHP function which is used to remove any slashes from our output that might have been added by the addslashes command (which is in our user function SQLValueString() ).

else { ... } - We generate and display a simple error message if someone tries to edit a record that doesn't exist.

Note the Programming Logic

If you will notice, we first check to see if we need to update the database record. If so, we perform the update.