Updating Records with PHP


The last technique I'll introduce in this chapter is how to update database records with a PHP script. Doing so requires use of the UPDATE query, and its successful execution can be verified with PHP's mysql_affected_rows() function.

While the mysql_num_rows() function will return the number of rows generated by a SELECT query, mysql_affected_rows() returns the number of rows affected by an INSERT, UPDATE, or DELETE query. It's used like so:

 $num = mysql_affected_rows($dbc); 

The one argument the function takes is the database connection ($dbc), not the results of the previous query ($result). This argument is optional, though, so I'll often omit it in order to avoid problems (like inadvertently using $result).

The following example will be a script that allows registered users to change their password. It'll demonstrate two important ideas:

  • Checking a submitted username and password against registered values (the key to a login system as well)

  • Updating database records using the primary key as a reference point

To update records with PHP

1.

Create a new PHP script in your text editor (Script 7.8).

 <?php # Script 7.8 - password.php $page_title = 'Change Your Password'; include ('./includes/header.html'); 

Script 7.8. The change_password.php script runs an UPDATE query on the database and uses the mysql_affected_rows() function to confirm the change.


2.

Create the main conditional.

 if (isset($_POST['submitted'])) { 

Since this page both displays and handles the form, I'll use my standard submit conditional.

3.

Include the database connection and write the escape_data() function.

 require_once ('../mysql_connect.   php); function escape_data ($data) {   global $dbc;   if (ini_get('magic_quotes_gpc')) {     $data = stripslashes($data);   }   return mysql_real_escape_string     (trim($data), $dbc); } 

The initial part of this script will mimic the registration form, including the safeguarding of submitted data with the escape_data() function. In order for any registration-login process to work, the data must be managed in the same way for both steps.

4.

Check the submitted data.

 $errors = array(); if (empty($_POST['email'])) {   $errors[] = 'You forgot to enter   your email address.'; } else {   $e = escape_data($_POST['email']); } if (empty($_POST['password'])) {   $errors[] = 'You forgot to enter    your existing password.'; } else {   $p = escape_data($_POST     ['password]); } if (!empty($_POST['password1'])) {   if ($_POST['password1'] != $_POST     ['password2]) {      $errors[] = 'Your new password        did not match the confirmed        new password.';  } else {    $np = escape_data($_POST      ['password1]);  } } else {   $errors[] = 'You forgot to enter     your new password.'; } 

The processes themselves are exactly like those in register.php. The form will have four inputs: the email address, the existing password, the new password, and a confirmation of the new password.

5.

If all the tests are passed, retrieve the user's ID.

 if (empty($errors)) {   $query = "SELECT user_id FROM     users WHERE (email='$e AND     password=SHA('$p) )";   $result = mysql_query($query);   $num = mysql_num_rows($result);   if (mysql_num_rows($result) == 1) {     $row = mysql_fetch_array       ($result, MYSQL_NUM); 

This first query will return just the user_id field for the record that matches the submitted email address and password. To compare the submitted password against the stored one, encrypt it again with the SHA() function. If the user is registered and has correctly entered both the email address and password, exactly one row will be selected (since the email value must be unique across all rows). Finally, this one record is assigned as an array (of one element) to the $row variable.

6.

Update the database.

 $query = "UPDATE users SET password=   SHA('$np) WHERE user_id=$row[0]"; $result = @mysql_query ($query); 

This query will change the passwordusing the new submitted valuewhere the user_id column is equal to the number retrieved from the previous query.

7.

Check the results of the query.

 if (mysql_affected_rows() == 1) {   echo '<h1 >Thank     you!</h1>   <p>Your password has been updated.     In Chapter 9 you will actually     be able to log in!</p><p><br     /></p>';   include ('./includes/footer.     html);   exit(); } else {   echo '<h1 >System     Error</h1>   <p >Your password     could not be changed 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(); } 

This part of the script again works similar to register.php. In this case, if mysql_affected_rows() returns the number 1, the record has been updated, a message will be printed, the footer included, and the script concluded. If not, the database error is printed.

8.

Complete the conditionals.

 } else {   echo '<h1 >Error!     </h1>   <p >The email address     and password do not match those     on file.</p>'; } 

If mysql_num_rows() does not equal 1, then the submitted email address and password do not match those on file and this error is printed.

9.

Print any error messages and complete the PHP.

  } 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>';  }  mysql_close(); } ?> 

10.

Display the form.

 <h2>Change Your Password</h2> <form action="password.php"   method="post>   <p>Email Address: <input type=     "text name="email" size="20"     maxlength="40 value="<?php     if (isset($_POST['email])) echo     $_POST['email]; ?>"  /> </p>   <p>Current Password: <input type=     "password name="password"     size="10 maxlength="20" /></p>   <p>New Password: <input type=     "password name="password1"     size="10 maxlength="20" /></p>   <p>Confirm New Password: <input     type="password     name="password2 size="10"     maxlength="20 /></p>   <p><input type="submit" name=     "submit value="Register"     /></p>   <input type="hidden" name=     "submitted value="TRUE" /> </form> 

Again, the form takes three different password inputsthe current password, the new one, and a confirmation of the new passwordand the email address. The email address input is sticky (password inputs cannot be).

11.

Include the footer file.

 <?php include ('./includes/footer.html'); ?> 

12.

Save the file as password.php, upload to your Web server, and test in your Web browser (Figures 7.17, 7.18, and 7.19).

Figure 7.17. The form for changing a user's password.


Figure 7.18. The password was changed in the database.


Figure 7.19. If the entered email address and password don't match those on file, the password will not be updated.


Tips

  • If you delete every record from a table, mysql_affected_rows() will return 0, even if the query was successful and every row was removed. This is just a quirk.

  • If an UPDATE query runs but does not actually change the value of any column (for example, a password is replaced with the same password), mysql_affected_rows() will return 0.

  • The mysql_affected_rows() conditional used here could (and maybe should) also be applied to the register.php script to see if one record was added. That would be a more exacting condition to check than if ($result).


Looking Ahead

This chapter is a basic introduction to using PHP and MySQL together. Although all of the techniques covered here are fundamental and all of the scripts were practical, real-world examples, there's still much to be learned.

In the next chapter you'll see three important concepts:

  • How to delete data from a database using PHP.

  • How to update the data in the database by first retrieving the current values.

  • How to paginate a query's results over multiple pages.

In Chapter 11, "Extended Topics," you'll be introduced to the new Improved MySQL Extension interface. These functions provide a system for taking advantage of the new features in MySQL 4.1 and later. Furthermore, there are speed and security benefits to using the mysqli_* functions instead of the mysql_* ones. (Improved MySQL support must be built into PHP, and you must be using version 4.1.3 or later of MySQL to use these functions, which is why the older, standard functions are discussed here first.)

Finally, Chapter 12, "ExampleContent Management," introduces one last important MySQL function, mysql_insert_id(). This function returns the automatically-incremented primary key of freshly added records.

Of course, the PHP manual includes documentation on every MySQL-related function, so don't forget about using that critical reference.




    PHP and MySQL for Dynamic Web Sites. Visual QuickPro Guide
    PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition)
    ISBN: 0321336577
    EAN: 2147483647
    Year: 2005
    Pages: 166
    Authors: Larry Ullman

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net