Database Access with PHP

College of Computing and Software Engineering
Kennesaw State University
Copyright © 2002, 2015 by Bob Brown

PHP and Database Management Systems

PHP is a general-purpose programming language of considerable power. Its huge number of special-purpose functions means you can do almost anything with PHP. The real power of PHP comes from using it as "middleware" to connect a database management system to the Web. This combination allows you to serve data from the database on the Web, and update the database with information obtained from Web users. The possibilities for electronic commerce are obvious, but there are many other things you can do with a combination such as this.

A Database Example

Let's look at an example program that does database access with PHP. Suppose we have a database named "weblab" with a table defined like this:

CREATE TABLE cast_t (
  role char(10),
  actor char(25));

Although this database is simplistic to the point of being trivial, it will allow us to demonstrate the power of database access with PHP. The example below uses MySQLi procedural functions. Read on to find out about a general-purpose database interface.

Let's examine a PHP program that reads the database described above and prints a table of roles and actors in roll order. The line numbers are used for reference in the discussion that follows the program listing. (If you copy this code to your own file as an exmaple, you will need to delete the line numbers.)

 1.  <!DOCTYPE html>
 2.  <html>
 3.  <head>
 4.  <meta charset="utf-8">
 5.  <title>Roles and Actors</title>
 6.  </head>
 7.  <body>
 8.  <h1>Roles and Actors</h1>
 9.  <!-- Set up the table -->
10.  <table>
11.  <tr><th>Role</th><th>Actor</th></tr>
12.  <!-- Retrieve records from database -->
13.  <?php
14.   $db=mysqli_connect(null,null,null,'weblab')
15.     or die("Can't connect to DB:" . mysqli_connect_error());
16.   $q = "select role, actor ";
17.   $q .= "from cast_t ";
18.   $q .= "order by role;";
19.   $dbResult = mysqli_query($db,$q)
20.     or die("Database query error" . mysqli_error($db));
21.   $num = mysqli_num_rows($dbResult);
22.   if ($num == 0) {
23.     echo '<tr><td colspan="2">';
24.     echo 'Database query retrieved zero rows.</td></tr>';
25.   }
26.   while ($row = mysqli_fetch_assoc($dbResult)) {
27.     $role = $row['role'];
28.     $actor = $row['actor'];
29.     echo "<tr><td><b>$role</b></td>";
30.     echo "<td>$actor</td></tr>\n";
31.   }
32.  ?>
33.  <!-- Close out the table and end -->
34.  </table>
35.  </body>
36.  </html>

Lines 1-12 are straight HTML; they start a document and open a table. In line 14 we connect to the database. In this case, the database is on the same machine as the Web server. See the PHP documentation for information about connecting to databases on other machines.

Lines 14-15 connect to the database. If connection fails, an error message is generated and the program ends.

In lines 16-18 we build an SQL query. Note that the assignment operator is combined with the string concatenation operator, period (.) to build up the query. Notice also that lines after the first start with a space to provide word separation in the final query.

Lines 19-20 pass the query to the database "back end" and abort the program if a database error occurs. The mysqli_query function returns a resource variable that holds the result of the SQL operation. If $dbResult is false, the database call has failed and we abort generating the Web page with a suitable message on line 20.

The mysqli fetch_assoc database API returns an associative array. The keys are named by the database attributes selected.

Line 21 determines the number of rows the query returned, and lines 22-25 deal with the possibility that no rows were returned.

Lines 26-31 process the returned rows. If there weren't any, this code will not be executed because it's in a pretest loop and mysqli_fetch_assoc returns false when there are no more rows to fetch. Line 32 ends the PHP part of the program. Lines 33-36 close the table and close the HTML element. That's all there is to it!

Updating Databases

Up to now, we've focused on retrieving data from databases. Notice, however, that what is sent to the database "back end" is an SQL query. This doesn't have to be a SELECT. It can just as easily ben an INSERT, DELETE or UPDATE query. To update a database, all you have to do is build a valid update query. If the result of sending the query to the database is null, then the query failed and you have to determine why and present a useful error message. If the result is non-null, then your database update succeeded.

PHP's PDO Interface

This example has been based on procedural programming and the mysqli database functions. PHP includes a lightweight multi-database interface called PHP Data Object, or PDO. PDO hides many of the differences between different database management systems. (It doesn't hide differences in SQL suntax.) We haven't considered PDO because we've been looking at PHP as a procedural language, not an object-oriented language. If you become seriously involved with PHP programming, you should look into PDO and the database facilities it provides because PDO will allow you to write much more portable code.

Last updated: 2015-07-06 17:14