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.
Let's look at an example program that does database access with PHP. Suppose we have a database named "webdev" 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 PostgreSQL-specific 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.
1 <html>
2 <head>
3 <title>Roles and Actors</title>
4 </head>
5 <body>
6 <h2>Roles and Actors</h2>
7
8 <!-- Set up the table -->
9 <table>
10 <tr><th>Role</th><th>Actor</th></tr>
11
12 <!-- Retrieve records from database -->
13 <?php
14 $db=pg_connect("dbname=webdev");
15 $query = "select role, actor";
16 $query .= " from cast_t";
17 $query .= " order by role;";
18 $dbResult = pg_query($query);
19 if (!$dbResult) {
20 die("Database error...");
21 }
22 $num = pg_num_rows($dbResult);
23 if ($num == 0) {
24 echo '<tr><td colspan="2">';
25 echo 'Database Query Retrieved Nothing!</td></tr>';
26 }
27 $i = 0;
28 while ($i < $num) {
29 $role = pg_Result($dbResult, $i, 'role');
30 $actor = pg_Result($dbResult, $i, 'actor');
31 echo
"<tr><td><b>$role</b></td><td>$actor</td></tr>\n";
32 $i++;
33 }
34 ?>
35
36 <!-- Close out the table and end -->
37 </table>
38 </body>
39 <html>
Lines 1-13 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.In lines 15-17 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 18-21 pass the query to the database "back end" and abort the program if a database error occurs. The pg_query function returns a resource variable that holds the result of the SQL operation. If $dbresult is null, the database call has failed and we abort generating the Web page with a suitable message on line 20.
The PostgreSQL database API returns a two-dimensional array. The rows are rows retrieved from the database, and the columns are named by the database attributes selected.
Line 22 determines the number of rows the query returned, and lines 23-26 deal with the possibility that no rows were returned.
Lines 27-33 process the returned rows. If there weren't any, this code will not be executed because it's in a pretest loop. Line 34 ends the PHP part of the program. Lines 35-39 close the table and end the PHP part of the program. That's all there is to it! Lines 34-39 close out the HTML page.
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.
This example has been based on procedural programming and the PostgreSQL database functions. PHP defines PEAR, the PHP Extension and Application Repository. PEAR is PHP's version of foundation classes. The PEAR DB facility provides an object-oriented database interface that abstracts away the difference between the various database management systems and their interfaces. We haven't considered PEAR DB 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 PEAR and the database facilities it provides.