Using PHP with MySQL 3

Before class begins be ready with all of the following.

1. Fire up Winscp (or whatever you use to connect to the server) AND
   1. connect to math.mercyhurst.edu.
   2. on the server (RHS) change into your:
      public_html/courses/MIS_380/construction/Chap9/ directory.
   3. on your local machine (LHS) change into the equivalent folder for your local workspace.
   4. Make sure your local workspace and the files on the server are synchronized .
2. Fire-up a browser, and navigate to:
   1. this page;   and then in a second tab:
   2. http://math.mercyhurst.edu/~USERNAME/courses/MIS_380/construction/Chap9/
3. Open the following files in your text editor:
   1. MIS_380/construction/Chap9/view_users.php
   2. MIS_380/construction/Chap9/register.php
   3. MIS_380/construction/Chap9/includes/header.inc.php
   4. MIS_380/construction/Chap9/password.php

In This Chapter

• Modifying the Template
• Connecting to MySQL
• Executing Simple Queries
• Retrieving Query Results
• Ensuring Secure SQL
• Counting Returned Records
• Updating Records with PHP     (today)

Day Three PHP with MySQL

Finishing up our earlier Scripts

We will add a few finishing touches to our view_users.php and register.php scripts.

Below we have links to the PHP manual, we will add mysqli_free_result(), mysqli_close(), and mysqli_affected_rows().

In our register.php script we want to ad a logic block to prevent someone from registering with the same email address multiple times. Although the UNIQUE index on that column in the database will prevent that from happening, such attempts will create a MySQL error, like:

     You could not be registered due to a system error.
       We apologize for any inconvenience.

   Duplicate entry 'rgriffiths@mercyhurst.edu' for key 'email'

To avoid this using PHP, run a SELECT query to confirm that the email address is not currently registered. That query would be simply

  SELECT user_id FROM users WHERE email='$e'

Then call mysqli_num_rows(). If mysqli_num_rows() returns 0, you know that the email address hasn’t already been registered and it is safe to run the INSERT.

The logic block for this protection

       if (empty($errors)) { // If everything's OK.
          $q = "SELECT user_id FROM users WHERE email='$e'";
   $r = @mysqli_query ($dbc, $q); // Run the query.
          $num = @mysqli_num_rows($r);

          if ($num == 0) { // email is NOT already used.
                  // Register the user in the database ...
                  // Make the query:
       

       
    } else { // email is already in the db  (the $num != 0  case )
       echo '<h2 class="error">Error!</h2>
                   <p class="error">The email address you entered is already in our system.
       Please enter a different email, or login using that email address.</p>';
     

    } // END of if ($num == 0)  IF

       }  else { // Report the errors.    
 

Notice the difference from last class,
the mysqli_num_rows( ) function returns the number of rows generated by a SELECT query, whereas, 
the mysqli_affected_rows( ) function returns the number of rows affected by an INSERT, UPDATE, or DELETE query. It's used like so:

  $num = mysqli_affected_rows($dbc);

Unlike mysqli_num_rows( ), the one argument the function takes is the database connection ($dbc), not the results of the previous query ($r).

Now we will create a script to allow users to change their password (they must remember their current password). 

We will use this same logic (above), as well as the mysqli_num_rows() to make sure an email/password pair exists in the db. If mysqli_num_rows() does not return a value of 1, then the submitted email address and password do not match those in the database and this error is printed. 
In this case, the form will be displayed again so that the user can enter the correct information.

Adding in the error checking

Let's intentionally add some errors to our register.php script, and tweak the define('LIVE', ****);constants.

Updating Records with PHP

The last example from this chapter shows how to update database records through a PHP script. Doing so requires an UPDATE query, its successful execution can be verified with the mysqli_affected_rows( ) function -- our only new PHP function today -- demonstrated with the register.php script.

Our in-class example will be a script that allows registered users to change their password (the password.phpscript). It demonstrates two important ideas:

1. Checking a submitted username and password against registered values (the key to a login system as well)
2. Updating database records using the primary key as a reference

Normally, our site would have two pages for managing user passwords. One would be used to change an existing password, and the other to recover a forgotten password.
Both pages are simple forms. Once we get closer to a real working site, users wouldn't be logged in when recovering a forgotten password, but they must be logged in to change their password. 
We will only do the first one, change an existing password.

This will be another example of a self-contained script.
We will follow the book fairly closely (page 296).

Changing a password is kind of like a combination of a login and our registration process. The user should enter their current password as an extra precaution, then their new password, AND a confirmation of the new password. On a real working site, the user would be logged in to perform this task.

  
      CLASS EXAMPLE

A few code blocks

Save some time and typing.

    // Check if user entered a correct  email address/password combination:
    $q = "SELECT user_id FROM users WHERE (email='$e' AND pass=SHA1('$p') )";
    $r = @mysqli_query($dbc, $q);
    $num = @mysqli_num_rows($r);
    if ($num == 1) { // A match was made.

Now we know we have a valid user, so we can run the UPDATE query.

  // Get the user_id:
  $row = mysqli_fetch_array($r, MYSQLI_NUM);
  
  // Make the UPDATE query:  IN the future the user_id will be in the session
  $q = "UPDATE users SET pass=SHA1('$np') WHERE user_id=$row[0]";  
  $r = @mysqli_query($dbc, $q);

After running the query, we would like to know it correctly updated the db.

    if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.

    // Print a message.
      echo '<h2>Thank you!</h2>
      <p>Your password has been updated. In Chapter 12 you will actually be
 able to log in!</p>';  

      } else { // If it did not run OK.

      // Public message:
      echo '<h2 class="error">System Error</h2>
    <p class="error">Your password could not be changed due to a system
      error. We apologize for any inconvenience.</p>';  
    
    // Debugging message:
    echo '<p>' . mysqli_error($dbc) . '<br><br>Query: ' . $q . '</p>';
    }  // END if (mysqli_affected_rows($dbc) == 1)  IF

At this point, provided there were no form errors we changed the password and are done here -- don't need the form or the db connection.

     mysqli_close($dbc); // Close the database connection.
     
     // Include the footer and quit the script (to not show the form).
     include 'includes/footer.inc.php'; 
     exit();

Most of the time (when ever we use a SELECT query, or any queries which return a mysqli_result object.) we would also free up the memory associated with a result using;

     mysqli_free_result ($r); // Free up the resources.

however, INSERT, DELETE, and UPDATE queries simply return TRUE (if successful) or FALSE (if failed), so we can not use mysqli_free_result() here (the last result is an UPDATE).

We are still inside the if ($num == 1) -- that is an invalid -- -- email/password combination.

     } else { // Invalid email address/password combination.
        echo '<h2 class="error">Error!</h2>
           <p class="error">The email address and password do not match those on file.</p>';
     }  // END  if ($num == 1)  IF

<Repeated from last class>

Accessing a database from a PHP page

1. A user makes an HTTP request with the browser for a file, say view_users.php.
2. Web server receives the request for the file, retrieves the file, and passes it to the PHP module (engine).
3. PHP engine parses the script - including opening a connection to a database.
4. MySQL server receives the database query, processes the request, passes result back to the PHP engine.
5. The PHP engine finishes parsing the script - transforms the result set returned from MySQL into an array and then further into a set of HTML table rows (for example).
6. The web server passes the HTML back to the browser, which displays the file.

Basic steps in querying a database from a PHP page

1. Check and filter any data inputted from the user - called validating the data.
2. Set up a connection to the appropriate database.
3. Query the database.
4. Retrieve the results of the query.
5. Display these results to the user.
6. Disconnect from the database (close the connection when finished)

Connecting to MySQL

The first step for interacting with MySQL, connecting to the server, requires the mysqli_connect( ) function:

$dbc = mysqli_connect (hostname, username, password, db_name);

The first three arguments sent to the function (hostname, username, and password) are based upon the users and privileges established within MySQL.
Commonly (but not always), the host value will be localhost. 
The fourth argument is the name of the database to use. This is the equivalent of saying USE databasename within the mysql client. 
If the connection was made, the $dbc variable, short for database connection (but you can use any name you want, of course), will become a reference point for all of your subsequent database interactions. Most of the PHP functions for working with MySQL will take this variable as its first argument.

If a connection problem occurred, you can call mysqli_connect_error( ), which returns the connection error message. It takes no arguments, so would be called using just:

 mysqli_connect_error( );

We will use a file that contains all of this information, you will never actually see this file, it is buried deep on the server (Never create a connection file like this); but it has a general format:

 <?php #
    Chapter 9 - siteName_connect_i.php

// This file contains the database access information.
// This file also establishes a connection to MySQL,
// selects the database, and sets the encoding.

// Set the database access information as constants:
   DEFINE ('DB_USER', '');
   DEFINE ('DB_PASSWORD', '');
   DEFINE ('DB_HOST', 'localhost');
   DEFINE ('DB_NAME', '');

// Make the connection:
      $dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
            OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );


  // Set the encoding...
  mysqli_set_charset($dbc, 'utf8');
?>

Once you have the connection information in a file the connection to the database is as simple as adding a line such as this in your script:

  require 'siteName_connect_i.php';

Cautions - Secure SQL

Database security with respect to PHP comes down to three broad issues:

1. Protecting the MySQL access information
   We will accomplish this by ensuring that the MySQL connection script is outside of any web directory, so that it is never viewable through a browser
2. Not revealing too much about the database - 
   We have discussed this extensively - this objective is attained by not letting the user see PHP's error messages or your queries. In our "construction" directory your scripts display that information for your debugging purposes; BUT you would never want to do that on a live site!.
3. Being cautious when running queries, particularly those involving user-submitted data - 
   there are many steps you must take, all based on the premise of never trusting user-supplied data.
   1. validate that some value has been submitted or that it is of the proper type (number, string, etc.).
   2. use the Filter extension (we will see in Chapter 13 -- "Security Methods") or regular expressions (we will see in Chapter 14 -- "Perl-Compatible Regular Expressions") to make sure that submitted data matches what you would expect it to be.
   3. you can typecast some values to guarantee that they are numbers.
   4. run user-submitted data through the mysqli_real_escape_string() function. This function makes data safe to use in a query by escaping what could be problematic characters.

       $safe = mysqli_real_escape_string ($dbc, data);

      We will see a even safer method in Chapter 13, using prepared statements, which can be more secure than running queries in the old-fashioned way and they may also be faster.

Don't forget when you are all done to close the database connection:

  mysqli_close($dbc);

And to free your result when your result object ($result) is not needed anymore (this frees the memory associated with a result),

  mysqli_free_result($result);

Executing Simple Queries

Once you have successfully connected to and selected a database, you can start executing queries.
The queries can be as basic as inserts, updates, and deletions or as involved as complex joins returning numerous rows. Regardless of the SQL command type, the PHP function for executing a query is mysqli_query( ):

  result = mysqli_query(dbc, query);

The function takes the database connection as its first argument and the query itself as the second. Within the context of a complete PHP script, Our author (our text book) normally assigns the query to another variable, called $query or just $q, so running a query might look like:

 $r = mysqli_query($dbc, $q);

For simple queries like INSERT, UPDATE, DELETE, etc. (which do not return records), the $r variable, short for result, will be either TRUE or FALSE, depending upon whether the query executed successfully. 
Keep in mind that "executed successfully" means that it ran without error; it doesn't mean that the query's execution necessarily had the desired result; you'll need to test for that.

Remark:
You should not end your queries with a semicolon in PHP, as you do when using the mysql client. When working with MySQL, this is a common, albeit harmless, mistake to make. When working with other database applications (Oracle, for one), doing so will make your queries unusable.

(PHP manual)

• mysqli_real_escape_string   -   Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection
• trim   -   Strip whitespace (or other characters) from the beginning and end of a string
• mysqli_query   -   Performs a query on the database
• mysqli_fetch_array   -   Fetch a result row as an associative, a numeric array, or both mysqli_fetch_assoc  -   Fetch a result row as an associative array
• mysqli_num_rows   -   Gets the number of rows in a result
• mysqli_free_result   -   Frees the memory associated with a result
• mysqli_affected_rows   -   Gets the number of affected rows in a previous MySQL operation
• Over view of these functions

Class Example

Most of class today will be a simple example to illustrate using MySQL (MariaDB) with PHP. We will use the sitename database form our text book, covered in chapters 4 and 5 (schema below).

To insert a record into the database (or update a record) requires careful validation/filtering, so to focus on just the connection to the database, as a first example, we will simply retrieve records from the database.

Our connection to the MySQL database contains a function to both trim and use the mysqli_real_escape_stringfunction (escapes special characters in a string for use in an SQL statement). This requires that the database connection script must be required earlier in the current script.

 // Function for escaping and trimming form data.
 // Takes two arguments: the data to be treated (string) and the MySQLi connection.
     // Returns the treated data (string).
  function escape_data ($data, $dbc) {
          // Apply trim() and mysqli_real_escape_string():
 
        return mysqli_real_escape_string ($dbc, trim ($data));
     } // End of the escape_data() function.

Later in the semester we will no longer need this function (or the mysqli_real_escape_string()), as we start using prepared statements to isolate the SQL command from the data (more secure and may be more efficient).