PHP and MySQL Database Access

Section 0: Module Objectives or Competencies

Section 1: Overview

[These notes are derived, in part, from this chapter. Please refer to the chapter for more details.]

In order to access a database, the program must first establish a connection to the database.

Once a connection has been established, the database tables can be queried.

Note: the pubs database has been created on classweb for practice accessing a remote database.

You access it using the following:

$server = 'classweb.cob.isu.edu';
$database = 'pubs';
$userid = 'databaseStudent4407';
$password = 'notAhard1';
$port = '444';

You have select permissions only.

The structure of the database can be seen here.

Section 2: Connecting to a Database

Connection Function

The mysqli_connect function provided by PHP establishes a connection to a MySQL server.

• The "i" in "mysqli" stands for "improved".

Connect to the database

There are various ways of connecting to a database. The details below pertain to a MySQL database.

A connection string can be defined as consisting of several factors, e.g. server, port, database name, user id, and user password.

mysqli_connect accepts a variety of parameters, including…

• the server on which the database resides
  • "localhost" can be used for the server if the database resides on the same machine as the application
• the user id
• the user password
• the database name
• the port, if different than the default
  • MySQL defaults to port 3306. (Use quotes around the port number.)
  • Port 3306 is blocked by ISU, so the demo below uses port 444.

  The example below is not a great demonstration of maintainability and modifiability, but is satisfactory for testing purposes only: php file (text version)

  <?php
  $server = 'classweb.cob.isu.edu';
  $database = 'pubs';
  $userid = 'databaseStudent4407';
  $password = 'notAhard1';
  $port = '444';
  
  $connection = mysqli_connect($server, $userid, $password, $database, $port) 
  OR die ('Could not connect to MySQL: ' . mysql_connect_error() );
  echo 'Connection ' . ($connection ? 'succeeded!' : 'failed!');
  ?>
  

  The line

  $connection = mysqli_connect($server, $userid, $password, $database, $port)

  performs the actual connection from the program to the database.

  ---

  Maintainability and Modifiability

  Since there may be several programs that connect to that database, let's move the connection details to an include file called dbDetails.inc.php (text version):

  <?php
  $server = 'classweb.cob.isu.edu';
  $database = 'pubs';
  $userid = 'databaseStudent4407';
  $password = 'notAhard1';
  $port = '444';
  ?>
  

  And since connecting to an online database is such a common task, we'll set up a function to handle that task, and store it in an include file called  dbConnect.inc.php (text version).

  <?php
  function dbConnect($server, $userid, $password, $database, $port)
  {
  $link = mysqli_connect($server, $userid, $password, $database, $port) 
  OR die ('Could not connect to MySQL: ' . mysql_connect_error() );
  return $link;
  }
  ?>

  The following php file (text version) includes dbConnect.inc.php and dbDetails.inc.php.

  <?php
  include 'dbConnect.inc.php';
  include 'dbDetails.inc.php';
  
  $connection = dbConnect($server, $userid, $password, $database, $port);
  echo 'Connection ' . ($connection ? 'succeeded!' : 'failed!');
  ?>

Section 3: Simple Queries

We will demonstrate a SELECT query to extract data from one of the tables in the pubs database.

Writing the Query

Recall that the SELECT command lists the table contents, and can be followed by a set of attributes to be listed.

• The pubs database has a  variety of tables. We'll extract 'title' data from the 'titles' table.
• The syntax of the SELECT query is
  

  SELECT <attributeList> FROM <tableName>;

• Since we want a list of 'title' attributes from the 'titles' table the query will be
  

  SELECT TITLE FROM TITLES;

Issuing the Query

The mysqli_query function accepts a database connection and a query string, and performs a query on the database.

• The php code to issue the query will look like this:
  

  $sqlString = "select title from titles;";
  $result = mysqli_query($connection, $sqlString);

Accessing the Results

• For successful SELECT, SHOW, DESCRIBE, or EXPLAIN queries mysqli_query will return a mysqli_result object, which represents the result set obtained from a query against the database. For other queries it will return TRUE on success and FALSE on failure.
  • To access the result object to get the query results, functions like mysqli_fetch_row, mysqli_fetch_assoc, or mysqli_fetch_array (which is an extended version of mysqli_fetch_row and mysqli_fetch_assoc) can be used.
  • Here is an example that uses the mysqli_fetch_array:

    while ($row = mysqli_fetch_array($result))
    {
       echo $row['title'] . '<br/>';
    }
    

Here is the code used for this version.

Handling MYSQL Query Results In PHP provides an excellent explanation of dealing with results.

Alternative Approach

Here is an alternative approach that uses the Iterator interface:

foreach ($connection->query("select title from titles;") as $row)
{
   echo $row['title'] . '<br/>';
}

See this tutorial for an additional example.

Here is the code for the alternative version.

Demos

A demo page shows the results, and here is the code once again.

• Note the use of the error.html.php.
• The PDO_MySQL extension provides an alternative to using mysqli_result.
• HEre is a link to the 2220 Web Development notes if you wish to learn more about developing websites.
• Note also the use of a text version of the above PHP files so that the contents of the files can be viewed. For every PHP file that you submit as part of an assignment, a text version should also be provided.

Section 4: Basic Demo

Using PHP to dump a MySQL File

This demo program uses PHP to retrieve and dump the content of catalog data in a MySQL database.

Here is the code for this demo.

The include files, dbDetails.inc.php and dbConnect.inc.php, are identical to the previous example with the exception that the database being used is "productcatalog".

As in the previous example, the PHP code establishes a connection to the database, issues a SQL query, and then loops through the results.

The script to create and populate the table can be found here.

Section 5: Enhanced Demo

Using PHP, JavaScript, Ajax, JSON, and jQuery to dump a MySQL File

The previous example dumps the entire product catalog, but sites like Amazon allow the user to select a product and then displays details for that product only.

• That level of interactivity goes beyond what PHP can do, and requires Ajax.
• With Ajax, web applications can retrieve data from the server asynchronously in the background without interfering with the display and behavior of the existing page.
• This allows the user to select a product from a drop-down list, and then retrieves and displays the details for that product.
• JavaScript, jQuery, and JSON are also used to provide interactivity.

This demo program uses PHP to retrieve and dump the content of catalog data in a MySQL database.

Here is the PHP code for this demo.

The html file can be viewed by right clicking on the page and selecting View Page Source, and the JavaScript/jQuery code can be viewed by clicking on "enhancedCatalogDemo.js" near the bottom of the html code.

The code for this demo is much more complex due to the interactivity required.

• The JavaScript file calls a jQuery function upon page load.
  • This function asynchronously calls the PHP file, which connects to the database, issues a query, cycles through the results, and sends them to a JSON array for later processing.
  • The jQuery file then populates the drop-down box with the names of all products.
  • When the user selects a product from the list the Find button is activated.
  • When the user clicks Find, the findItem function is called to determine the index of the item selected, which is then used as an index to retrieve item details from the JSON array and display them.
  • The asynchronous feature was originally intended to be used only when the Find button was clicked, with the drop-down box populated by hardcoding the product list. However, it seemed more efficient to populate the list box via code, so the asynchronous benefits are not utilized. They were retained so that additional interactive features could be added later.
• All-in-all, this demo involves HTML, CSS, JavaScript, jQuery, JSON, Ajax, PHP, and MySQL.

Section 6: Resources

Old C# Notes on Database Access

Connect Script in PHP - Mysqli_connect

Secure Your PHP Connect Script

MySQL Select Query Using PHP

Display MySQL Query Results With PHP

Display MySQL Query Results in a HTML Table with PHP

Insert into MySQL Database Table With PHP & Debugging Tips

PHP and MySQL with mysqli methods

See PDO Tutorial for MySQL Developers.