| A common practice with database-driven Web sites is having a system in place so that you can easily edit existing records. This concept seems daunting to many beginning programmers, but it's surprisingly straightforward. For the following example, editing registered user records, the process combines three skills you should already have: Making sticky forms Using hidden inputs Registering new users
This next example is generally very similar to delete_user.php and will also be linked from the view_users.php script (when a person clicks Edit). A form will be displayed with the user's current information, allowing for those values to be changed. Upon submitting the form, an UPDATE query will be run to update the database. To edit an existing database record 1. | Create a new PHP document in your text editor or IDE (Script 8.4).
<?php # Script 8.4 - edit_user.php $page_title = 'Edit a User'; include ('./includes/header.html');
Script 8.4. The edit_user.php page first displays the user's current information in a form. Upon submission of the form, the record will be updated in the database. 
  
| 2. | Check for a valid user ID value.
if ( (isset($_GET['id'])) &&  (is_numeric($_GET['id])) ) { $id = $_GET['id']; } elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id])) ) { $id = $_POST['id']; } else { echo '<h1 >Page  Error</h1> <p >This page has been accessed in error.</p> <p><br /><br /></p>'; include ('./includes/footer. html); exit(); }
This validation routine is exactly the same as that in delete_user.php, confirming that a numeric user ID has been received.
| 3. | Include the MySQL connection script and begin the main submit conditional.
require_once ('../mysql_connect. php); if (isset($_POST['submitted'])) { $errors = array();
Like the registration examples you have already done, this script makes use of an array to track errors.
| 4. | Validate the form data.
if (empty($_POST['first_name'])) { $errors[] = 'You forgot to enter your first name.'; } else { $fn = escape_data($_POST ['first_name]); } if (empty($_POST['last_name'])) { $errors[] = 'You forgot to enter your last name.'; } else { $ln = escape_data($_POST ['last_name]); } if (empty($_POST['email'])) { $errors[] = 'You forgot to enter your email address.'; } else { $e = escape_data($_POST['email']); }
The form (Figure 8.7) is like a registration form but without the password fields. The form data can therefore be validated using the same methods used in the registration scripts.
Figure 8.7. The form for editing a user's record. 
| 5. | If there were no errors, check that the submitted email address is not already in use.
if (empty($errors)) { $query = "SELECT user_id FROM users WHERE email='$e AND user_id != $id; $result = mysql_query($query); if (mysql_num_rows($result) == 0) {
The integrity of the database and of the application as a whole partially depends upon having unique email address values in the users table. This guarantees that the login system (developed in the next chapter) works. Because the form allows for altering the user's email address (see Figure 8.7), special steps have to be taken to ensure uniqueness. Consider two possibilities...
In the first, the user's email address is being changed. In this case you just need to run a query making sure that that particular email address isn't already registered (i.e., SELECT user_id FROM users WHERE email='$e').
In the second possibility, the user's email address will remain the same. In this case, it's okay if the email address is already in use, because it's already in use for this user.
To write one query that will work for both possibilities, don't check to see if the email address is being used, but rather see if it's being used by anyone else: SELECT user_id FROM users WHERE email='$e' AND user_id != $id.
| 6. | Update the database.
$query = "UPDATE users SET first_name='$fn, last_name='$ln', email='$e WHERE user_id=$id"; $result = @mysql_query ($query); if (mysql_affected_rows() == 1) { echo '<h1 >Edit a User</h1> <p>The user has been edited.</p> <p><br /><br /></p>'; } else { echo '<h1 >System Error</h1> <p >The user could not be edited due to a system error. We apologize for any inconvenience.</p>'; echo '<p>' . mysql_error() . '<br /><br />Query: ' . $query . '</p>'; include ('./includes/footer. html); exit(); }
The UPDATE query is similar to examples you may have seen in Chapter 4, "Introduction to SQL and MySQL." The query updates all three fieldsfirst name, last name, and email addressusing the values submitted by the form. This system works because the form is preset with the existing values. So, if you edit the first name in the form but nothing else, the first name value in the database is updated using this new value, but the last name and email address values are "updated" using their current values. This system is much easier than trying to determine which form values have changed and updating just those in the database.
The mysql_affected_rows() function is used to report upon the success of the operation.
| 7. | Complete the email, $errors, and submit conditionals.
} else { echo '<h1 > Error!</h1> <p >The email address has already been registered.</p>'; } } else { echo '<h1 > Error!</h1> <p >The following error(s) occurred:<br />'; foreach ($errors as $msg) { echo " - $msg<br />\n"; } echo '</p><p>Please try again. </p><p><br /></p>'; } }
This first else completes the conditional that checked if an email address was already being used by another user. The second else is used to report any errors in the form (namely, a lack of a first name, last name, or email address). The final closing brace completes the main submit conditional.
In this example, the form will be displayed whenever the page is accessed. So after submitting the form, the database will be updated, and the form will be shown again, now displaying the latest information.
| 8. | Retrieve the information for the user being edited.
$query = "SELECT first_name, last_name, email FROM users WHERE user_id=$id; $result = @mysql_query ($query); if (mysql_num_rows($result) == 1) {
This query is similar to the one in delete_user.php, although three individual values are retrieved from the database. Again, the conditionalchecking that a single row was returnedensures that a valid user ID was provided.
| 9. | Display the form.
$row = mysql_fetch_array ($result, MYSQL_NUM); echo '<h2>Edit a User</h2> <form action="edit_user.php" method="post> <p>First Name: <input type="text" name="first_name size="15" maxlength="15 value="' . $row[0] . '" /></p> <p>Last Name: <input type="text" name="last_name size="15" maxlength="30 value="' . $row[1] . '" /></p> <p>Email Address: <input type="text name="email" size="20" maxlength="40 value="' . $row[2] . '" /> </p> <p><input type="submit" name="submit value="Submit" /></p> <input type="hidden" name="submitted value="TRUE" /> <input type="hidden" name="id" value="' . $id . '" /> </form>';
The form has but three text inputs, each of which is made sticky using the data retrieved from the database. Again, the user ID ($id) is stored as a hidden form input so that the handling process can also access this value.
| 10. | Complete the mysql_num_rows() conditional.
} else { echo '<h1 >Page Error</h1> <p >This page has been accessed in error.</p><p> <br /><br /></p>'; }
If no record was returned from the database, because an invalid user ID was submitted, this message is displayed.
| 11. | Complete the PHP page.
mysql_close(); include ('./includes/footer.html'); ?>
| 12. | Save the file as edit_user.php and upload it to your Web server.
| 13. | Run the page by first clicking an Edit link in the view_users.php page (Figures 8.8 and 8.9).
Figure 8.8. The new values are displayed in the form after successfully updating the database (compare with the form values in Figure 8.7). 
Figure 8.9. If you try to change a record to an existing email address or if you omit an input, errors are reported. 
|
 Tips
This edit page does not include the functionality to change the password. That concept was already demonstrated in password.php (Script 7.8). If you would like to incorporate that functionality here, keep in mind that you cannot display the current password, as it is encrypted. Instead, just present two boxes for changing the password (the new password input and a confirmation). If these values are submitted, update the password in the database as well. If these inputs are left blank, do not update the password in the database. The mysql_affected_rows() function will return a value of 0 if an UPDATE command successfully ran but didn't actually affect any records. So if you submit this form without changing any of the form values, a system error is displayed, which may not technically be correct. Once you have this script effectively working, you could change the error message to indicate that no alterations were made in such a case.
|
