MySQL Basics with PHP: Connecting, SELECT

PHP provides the capability of connecting with a MySQL database. Once this framework is established, a PHP program can construct SQL queries and then execute the queries on the database. Of course, the interaction of PHP with a database also opens the possibility of embedding database queries within a Web page.

This lab is the first in a series that gradually provides experience with PHP with MySQL.

• This lab explores establishing a MySQL connection within PHP and working with SELECT queries.

• The next lab provides experience with INSERT and UPDATE statements.

• Two more labs consider the inclusion of such MySQL/PHP interactions within the context of Web pages.

Making a PHP Connection to a Database

The textbook describes three different approaches that allow PHP to work with MySQL: MySQL, MySQLi (MySQL "improved"), and PDO (PHP Data Objects). Although the first of these is outdated, both of the other approaches are in widespread, active use.

• MySQLi is reported to come with PHP for both Macs and Windows, but it works only with MySQL databases (not other database packages), and reports suggest this software may not be available in later releases of PHP.

• PDO works works, largely unchanged, with numerous databases (not just MySQL), so this course explores PDO—paralleling the discussion in the textbook.

The basic steps for establishing a PDO connection to a database utilizes the following PHP code:

<?php
   // identify details and credentials for accessing the database
   $server   = 'localhost';
   $database = ... ;
   $username = ... ;
   $password = ... ;

   // Establish an environment to report trouble, if a problem occurs
   try {
       $pdo = new PDO("mysql:host=localhost;dbname=$database", $username, $password);    

       // set the PDO error mode to throw exception when run-time error encountered
       $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

       // provide as much protection as possible to counteract possible injection attacks
       $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
   }
   catch (Exception $e) {
       echo "error connecting to $database \n";
       echo "full error message " . $e->getMessage() . "\n";
       echo "exiting\n";
       exit;
   }

   echo "connected to database '$database'\n";

The following notes explain the various steps in this code:

• PHP must know where the database is located; localhost indicates the program will be using the database established on the current Web server.

• Just as when using MySQL in a terminal window, a PHP program must log on with a username and password, identifying which database will be used.

• When trying to connect with a database, many details may go wrong. For example, the username and/or password may be incorrect, the database name may be wrong, or the user may not have permission to use the specified database.

  With the possibility of error in mind, a try statement specifies a block of code that will be monitored for potential errors. Within the try block:

  • If all goes smoothly, the statements will be executed, and the program will go forward—in this case, to the statement
    echo "connected to database '$database \n'";
  • If something goes wrong with the new PDO statement (e.g., a wrong password), then work jumps outside the try block and goes immediately to the catch section that follows. In computing jargon, the difficulty generates an exception, a designated variable ($e in this case) is provided information about the error, and the actions in the catch (...) {...} section indicate how to respond to the error. In this case, an error message is reported, and the expression $e->message() provides some information about the details of the trouble.

• Also, to enhance error reporting, the try block contains two statements (both involving $pdf->setAttribute) to enhance error detection and reporting throughout the processing of this page.

Once a connection is established, one can use SQL queries within the PHP program to work with the database, just as one would in working with a terminal window.

When work with the database is complete (usually at the end of the PHP/HTML page), the connection to the database is closed with the line:

  $pdo = null;

For reference, the first part mysql-pdo-select-1.php shows the code for connection to a database within a complete program.

Initial Steps for the Lab

1. Copy the above code to your own file. Edit the variable names to indicate your own database and an appropriate username and password. Then save the file on the Web/PHP/MySQL server, and execute it within a Terminal window.

   • If all goes well, the "connected to database" text will appear, and you can celebrate.
   • If troubles arise, check the username, password, etc., as well as the syntax of your statements. If you still have trouble, talk to the instructor.

2. Once your code allows you to make a MySQL connection:

   1. Make an error in the database name, and observe what happens.
   2. Make an error in the username, and observe what happens.
   3. Make an error in the password, and observe what happens.

Often, placing a username and password in every PHP/MySQL file is awkward:

• This private information is visible to anyone looking at your code.

• When writing repeated lines of code in every program, it is easy to make a typographical error from time to time.

• If you change your username or password information, then you would need to edit every PHP/MySQL program you have ever written.

To resolve such troubles, it is common to create a separate file that sets your username and password. For example, a credentials.php file might have the form:

    <?php
      $username = '...';
      $password = '...';

    ?>
  

With this file created, the main PHP/MySQL program can be modified by removing the assignments for $username and $password, and inserting a directive to include the credentials file:

  <?php  
     require "credentials.php";
  ?>

3. Create your own credentials.php file, insert the appropriate username and password information, replace the username/password lines in the main PHP/MySQL program with a require statement, and check that your program still connects to the database appropriately.

Creating and Executing SELECT Statements

Once a connection is established within a PHP program to a MySQL database, the program can issue and process SQL statements, just as when one is working within a Terminal window. This lab explores two common approaches involving SELECT statements.

Using $pdo->query to Execute the Query

Assuming that a PDO database connection is established, perhaps the simplest way to extract data involves these steps:

• Formulate the query as a string
• Execute the query
• Process the results

Extracting and Displaying a faculty Table

As an example, consider the faculty table from the lab on Getting Starting with a MySQL Database. The first part of that table follows:

  +-----------+---------+------------+------------------+-------------------------+----------+--------+
| facultyID | first   | last       | dept             | email                   | phone    | office |
+-----------+---------+------------+------------------+-------------------------+----------+--------+
|         1 | Haiyan  | Cheng      | Computer Science | hcheng@willamette.edu   | 375-5339 |    207 |
|         2 | Jim     | Levenick   | Computer Science | levenick@willamette.edu | 370-6486 |    206 |
|         3 | Fritz   | Ruehr      | Computer Science | fruehr@willamette.edu   | 370-6165 |    208 |
|         4 | Henry   | Walker     | Computer Science | walker@cs.grinnell.edu  | 375-5314 |    210 |
|         5 | Peter   | Otto       | Mathematics      | potto@willamette.edu    | 370-6487 |    214 |

Within this context, the following code prints the first and last names of each faculty member in the table.

   /* query the database */
   try {
       $sqlQuery = 'SELECT * FROM faculty';
       echo "query:  $sqlQuery \n";
       $result = $pdo->query($sqlQuery);
   }
   catch (Exception $e) {
       echo "error on query:  $sqlQuery \n";
       echo "error message:  $e->getMessage \n";
       echo "exiting \n";
       exit;
   }

   /* report results */
   while ($row = $result->fetch()) {
      echo $row['first'] . " " . $row['last'] . "\n";
   }

Commentary on each part of this code follows:

• Since an error might arise in processing a SELECT query, work with the query is placed within a try block, as described above.

• The SELECT statement given identifies all faculty in the table, following the same syntax used in earlier labs.

• Although there is no requirement that a query be printed, such output can be helpful as a program is being developed, so one can check that the query is formulated as planned. (Once the program has been carefully tested, this code may be commented out, so it does not appear in any final output.)

• In PHP, the $pdo->query statement executes the SQL command, just as it would be performed when the statement was typed in a Terminal window.

• Rather than printing the results directory, the $pdo->query statement returns all results, so they can be stored (in this case in variable $results) for further processing.

• Once all results are stored, PHP allows the rows to be obtained row-by-row, where they can be extracted by field name.

  Here, the while loop extracts the rows, one at a time, until all rows are processed:

        while ($row = $result->fetch()) {
      

  Once obtained, the variable ($row in this case) allows the retrieval of each field, as desired.

For reference, the second part mysql-pdo-select-1.php shows the code for this approach to SELECT from a database within a complete program.

More Steps for this Lab

Use the $pdo->query approach in the following steps.

4. Connect to the faculty database, and print a table of first names, last names, and email addresses for all faculty in the table.

5. Modify the previous step to print only Mathematics faculty.

6. Modify the previous step to print only Computer Science faculty whose last names come in the first half of the alphabet.

Using $pdo->prepare and $pd->execute to Execute the Query

When the programmer has full control over the details of an SQL query, the $pdo->query approach can work well. However, consider the following scenario.

An Injection Attack

Suppose a PHP script asks a user to identify a room number, with the intention of retrieving that faculty member from the table. For example, the room number might come from an HTML form. Possible code might have the following form:

  $room = ...
  $query = 'SELECT * FROM faculty where office = ' . $room;

That is, the $query involves the standard SELECT text, and the room number is appended at the end. This approach works fine, if the user types 210 or similar room number. But what if the user enters:

  210 ; DROP TABLE faculty;

Combining these two elements gives the compound SQL statements:

  'SELECT * FROM faculty where office = 210 ; DROP TABLE faculty;

The first part of this is a reasonable SQL query, but the second part erases the entire table! Such a scenario is called an Injection Attack, and such circumstances can undermine much database processing. Of course, if a programmer is writing complete SQL statements, such issues may not arise. However, in many applications, precautions are essential. This type of problem yields an approach to database processing, in which statements are "prepared" before they are executed. The intention is to handle many injection attacks as part of the preparation.

The following code illustrates this cautious approach to SQL queries:

   /* query the database */
   try {
       $query = 'SELECT * FROM faculty where office <= 210';
       echo "query:  $query \n";
       $preparedQuery = $pdo->prepare($query);
       $preparedQuery->execute();
       $result = $preparedQuery->fetchall();
   }
   catch (Exception $e) {
       echo "error on query:  $query \n";
       echo "exiting \n";
       exit;
   }

   /* report results */
   foreach ($result as $row) {
      echo $row['first'] . " " . $row['last'] . "\n";
   }

Again, commentary is needed for the various elements of this code.

• The initial specification and echoing of the SQL query proceeds as before.

• Once created, the standard SQL query is "prepared" and executed:

         $preparedQuery = $pdo->prepare($query);
         $preparedQuery->execute();
      

• Note: The prepare function/method helps in protecting against various database attacks. However, challenges from the use of variables may remain. Such potential troubles are addressed in another few labs.

• After query execution, all results are gathered into a $result variable:

         $result = $preparedQuery->fetchall();
      

  Here, $result is an array containing all relevant rows of the table.

• Since result$ is an array (of rows), a foreach statement allows processing to proceed row-by-row.

For reference, mysql-pdo-select-2.php shows the code for this prepare/execture approach SELECT to a database within a complete program.

Still More Steps: Fun in the Lab Continues

7. Repeat steps 4, 5, and 6 with this alternative approach for constructing and executing SELECT statements within a PHP program.

8. Turning to the course table, described in the lab on Changing MySQL Tables,

   1. Write a PHP/MySQL program that retrieves the program, course number, and title of all courses in the table.
   2. Write a PHP/MySQL program that prints all information about the writing courses identified in the table.
   3. Write a PHP/MySQL program that prints the program, course number, and title of all 100-level courses in the table.

created 13 February 2019 revised 15 February 2019