Integrating MySQL with PHP

PHP has excellent MySQL support, having integrated the API since PHP version 2! In this course we will be using the Improved MySQL extension for PHP, mysqli, which allows for an object oriented approach using prepared statements, all is a relatively simple interface.

Connecting to the database

The code to connect to the database is rather straight forward:

$db = new mysqli('hostname',
                 'username',
                 'password',
                 'database_name');
To disconnect, when all queries are completed, usually at the end of your PHP script.

$db->close();
Please understand that this method is not what is normally used, please review the next section!

Connecting (in practice)

Since your username and password (for the database) is needed for the connection, standard convention is to create a small .php script which handles the connection, and is readable by the webserver. We could just embed the $db line above, but a better way is to extend the class, the example below is in a file called mysql.inc.php:

<?php
  // mysql.inc.php - This file will be used to establish the database connection.
  class myConnectDB extends mysqli{
    public function __construct($hostname="localhost",
        $user="m23xxxx",
        $password="MYPASSWORD",
        $dbname="m23xxxx"){
      parent::__construct($hostname, $user, $password, $dbname);
    }
  }
?>
and then connect to the database via:

require_once('mysql.inc.php');    # MySQL Connection Library
$db = new myConnectDB();          # Connect to MySQL

We can check to see if we connected successfully via

if (mysqli_connect_errno()) {
  echo "<h5>ERROR: " . mysqli_connect_errno() . ": " . mysqli_connect_error() . " </h5><br>";
}

Performing a basic query

There are a few basic steps needed when performing SQL queries via PHP and MySQL, in the examples below simple queries will be presented on the left, while dynamic queries are shown on the right.

  1. Create the query, create a variable that will store the SQL query, to support dynamic queries that require prepared statements, place a ? in spaces that will be filled in by variables later.
  2. Simple Query
    $course=$_GET["course"];
    $query = "SELECT alpha, Course
                FROM grades
                HERE Course='".$course."'";
    Dynamic Query
    $course=$_GET["course"];
    $query = "SELECT alpha, Course
                FROM grades
                WHERE Course=?";
  3. Build the prepared statement. To perform a prepared SQL statement we first initialize it with stmt_init() and then prepare() to set up the query in PHP.
  4. $stmt = $db->stmt_init();
    $stmt->prepare($query);
  5. Bind any variables need for the dynamic query. In the case where we will be filling in variables based on input from the user, or dynamically via the script, we need to bind the variables to the query, this is done via bind_param(). This function requires a list of variables that will be provided to the query that is preceded by a string that annotates the types of the variables with one character for each variable needed. Using 's' will normally work and should be considered by default, the actual options are:

     
    Type Specification Characters
    CharacterDescription
    iCorresponding variable has type integer
    dCorresponding variable has type double
    sCorresponding variable has type string
    bCorresponding variable is a blob and will be sent in packets
  6. // This step not required for simple query!
    $stmt->bind_param('s', $course);
    
    // Multiple variables? List data types in order up front!
    $stmt->bind_param('ssi', $course, $name, $alpha);
        
  7. Execute the query. Send the query to the database, the $success will contain information regarding the success or failure of the query.
  8. $success = $stmt->execute();
  9. Check for any errors, and provide them to the user if desired.
  10. if (!$success || $db->affected_rows == 0) {
      echo "<h5>ERROR: " . $db->error . " for query *$query*</h5><hr>";
    }
  11. Optionally retrieve any basic / interesting metadata (# rows returned, AUTO_INCREMENTED values, etc.) Use these options when needed, such as when you insert a row into a table and the primary key is auto incremented and you need that key for future queries.
  12. $stmt->store_result();         // Needed if we want to be able to retrieve specific data
                                   // or it we want to run new queries while walking through
                                   // the results of this one.
    $num_rows = $stmt->num_rows;   // Retrieve the number of rows returned by the query
    $insert_id = $stmt->insert_id; // Retrieve the auto_increment value
    Another optional capability is to retrieve the column names and store them into an array:
    $meta = $stmt->result_metadata();
    $columns = array();
    while ($field = $meta->fetch_field()) {
      $columns[] = $field->name;   // Append the column name to the array
    }
    
  13. We need to bind the results for each column to a variable before we can access the rows.
  14. $stmt->bind_result($alpha, $course);
  15. Walk through each row returned by as a result of your query.
  16. while ($stmt->fetch()) {
      echo "$alpha $course <br>";
    }
  17. Close the query.
  18. $stmt->close();

Problems

  1. Create your mysql.inc.php file based on the "Connecting (in practice)" section above
  2. Build a PHP script that will connect to the database and provide a table of alpha, first, last, Course, and CourseGrade
  3. Modify the previous script so that it will take an alpha from the URL ($_GET) and only return those results if the alpha is provided. A completed example is here.