Overview of PHP Database Programming

Overview of PHP Database Programming

There are various techniques for accessing a database through a programming language. We discussed some of the techniques in Chapter 13, in the overviews of how to access an SQL database using the C and Java programming languages. In particular, we discussed embedded SQL, JDBC, SQL/CLI (similar to ODBC), and SQLJ. In this section we give an overview of how to access the database using the script language PHP, which is quite suitable for creating Web interfaces for searching and updating databases, as well as dynamic Web pages.

There is a PHP database access function library that is part of PHP Extension and Application Repository (PEAR), which is a collection of several libraries of functions for enhancing PHP. The PEAR DB library provides functions for database access. Many database systems can be accessed from this library, including Oracle, MySQL, SQLite, and Microsoft SQLServer, among others.

We will discuss several functions that are part of PEAR DB in the context of some examples. Section 14.3.1 shows how to connect to a database using PHP. Section 14.3.2 discusses how data collected from HTML forms can be used to insert a new record in a database table (relation). Section 14.3.3 shows how retrieval queries can be executed and have their results displayed within a dynamic Web page.

1. Connecting to a Database

To use the database functions in a PHP program, the PEAR DB library module called DB.php must be loaded. In Figure 14.6, this is done in line 0 of the example. The DB library functions can now be accessed using DB::<function_name>. The function for connecting to a database is called DB::connect('string') where the

     require 'DB.php';

     $d = DB::connect('oci8://acct1:pass12@www.host.com/db1');

     if (DB::isError($d)) { die("cannot connect – " .  $d->getMessage());}

...

     $q = $d->query("CREATE TABLE EMPLOYEE

     (Emp_id INT,

     Name VARCHAR(15),

     Job VARCHAR(10),

     Dno INT)" );

     if (DB::isError($q)) { die("table creation not successful – " .

$q->getMessage()); }

...

     $d->setErrorHandling(PEAR_ERROR_DIE);

...

     $eid = $d->nextID('EMPLOYEE');

     $q = $d->query("INSERT INTO EMPLOYEE VALUES

     ($eid, $_POST['emp_name'], $_POST['emp_job'], $_POST['emp_dno'])" );

...

     $eid = $d->nextID('EMPLOYEE');

     $q = $d->query('INSERT INTO EMPLOYEE VALUES (?, ?, ?, ?)',

     array($eid, $_POST['emp_name'], $_POST['emp_job'], $_POST['emp_dno']) );

Figure 14.6

Connecting to a database, creating a table, and inserting a record.

string argument specifies the database information. The format for 'string' is:

<DBMS software>://<user account>:<password>@<database server>

In Figure 14.6, line 1 connects to the database that is stored using Oracle (specified via the string oci8). The <DBMS software> portion of the 'string' specifies the particular DBMS software package being connected to. Some of the DBMS software packages that are accessible through PEAR DB are:

        MySQL. Specified as mysql for earlier versions and mysqli for later versions starting with version 4.1.2.

        Oracle. Specified as oc8i for versions 7, 8, and 9. This is used in line 1 of Figure 14.6.

        SQLite. Specified as sqlite.

        Microsoft SQL Server. Specified as mssql.

        Mini SQL. Specified as msql.

        Informix. Specified as ifx.

        Sybase. Specified as sybase.

        Any ODBC-compliant system. Specified as odbc.

The above is not a comprehensive list.

Following the <DB software> in the string argument passed to DB::connect is the separator :// followed by the user account name <user account> followed by the separator : and the account password <password>. These are followed by the separator @ and the server name and directory <database server> where the database is stored.

In line 1 of Figure 14.6, the user is connecting to the server at www.host.com/db1 using the account name acct1 and password pass12 stored under the Oracle DBMS oci8. The whole string is passed using DB::connect. The connection infor-mation is kept in the database connection variable $d, which is used whenever an operation to this particular database is applied.

Line 2 in Figure 14.6 shows how to check whether the connection to the database was established successfully or not. PEAR DB has a function DB::isError, which can determine whether any database access operation was successful or not. The argument to this function is the database connection variable ($d in this example). In general, the PHP programmer can check after every database call to determine whether the last database operation was successful or not, and terminate the pro-gram (using the die function) if it was not successful. An error message is also returned from the database via the operation $d->get_message(). This can also be displayed as shown in line 2 of Figure 14.6.

In general, most SQL commands can be sent to the database once a connection is established via the query function. The function $d->query takes an SQL com-mand as its string argument and sends it to the database server for execution. In Figure 14.6, lines 3 to 7 send a CREATE TABLE command to create a table called EMPLOYEE with four attributes. Whenever a query is executed, the result of the query is assigned to a query variable, which is called $q in our example. Line 8 checks whether the query was executed successfully or not.

The PHP PEAR DB library offers an alternative to having to check for errors after every database command. The function

$d–>setErrorHandling(PEAR_ERROR_DIE)

will terminate the program and print the default error messages if any subsequent errors occur when accessing the database through connection $d (see line 9 in Figure 14.6).

2. Collecting Data from Forms and Inserting Records

It is common in database applications to collect information through HTML or other types of Web forms. For example, when purchasing an airline ticket or apply-ing for a credit card, the user has to enter personal information such as name, address, and phone number. This information is typically collected and stored in a database record on a database server.

Lines 10 through 12 in Figure 14.6 illustrate how this may be done. In this example, we omitted the code for creating the form and collecting the data, which can be a variation of the example in Figure 14.1. We assume that the user entered valid values in the input parameters called emp_name, emp_job, and emp_dno. These would be accessible via the PHP auto-global array $_POST as discussed at the end of Section 14.2.4.

In the SQL INSERT command shown on lines 11 and 12 in Figure 14.6, the array entries $POST['emp_name'], $POST['emp_job'], and $POST['emp_dno'] will hold the values collected from the user through the input form of HTML. These are then inserted as a new employee record in the EMPLOYEE table.

This example also illustrates another feature of PEAR DB. It is common in some applications to create a unique record identifier for each new record inserted into the database.

PHP has a function $d–>nextID to create a sequence of unique values for a particular table. In our example, the field Emp_id of the EMPLOYEE table (see Figure 14.6, line 4) is created for this purpose. Line 10 shows how to retrieve the next unique value in the sequence for the EMPLOYEE table and insert it as part of the new record in lines 11 and 12.

The code for insert in lines 10 to 12 in Figure 14.6 may allow malicious strings to be entered that can alter the INSERT command. A safer way to do inserts and other queries is through the use of placeholders (specified by the ? symbol). An example is illustrated in lines 13 to 15, where another record is to be inserted. In this form of the $d->query() function, there are two arguments. The first argument is the SQL statement, with one or more ? symbols (placeholders). The second argument is an array, whose element values will be used to replace the placeholders in the order they are specified.

3. Retrieval Queries from Database Tables

We now give three examples of retrieval queries through PHP, shown in Figure 14.7. The first few lines 0 to 3 establish a database connection $d and set the error handling to the default, as we discussed in the previous section. The first query (lines 4 to 7) retrieves the name and department number of all employee records. The query variable $q is used to refer to the query result. A while-loop to go over each row in the result is shown in lines 5 to 7. The function $q->fetchRow() in line 5 serves to retrieve the next record in the query result and to control the loop. The looping starts at the first record.

The second query example is shown in lines 8 to 13 and illustrates a dynamic query. In this query, the conditions for selection of rows are based on values input by the user. Here we want to retrieve the names of employees who have a specific job and work for a particular department. The particular job and department number are entered through a form in the array variables and

     require 'DB.php';

     $d = DB::connect('oci8://acct1:pass12@www.host.com/dbname');

     if (DB::isError($d)) { die("cannot connect – " .  $d->getMessage()); }

     $d->setErrorHandling(PEAR_ERROR_DIE);

...

     $q = $d->query('SELECT Name, Dno FROM EMPLOYEE');

     while ($r = $q->fetchRow()) {

     print "employee $r[0] works for department $r[1] \n" ;

     }

...

     $q = $d->query('SELECT Name FROM EMPLOYEE WHERE Job = ? AND Dno = ?',

     array($_POST['emp_job'], $_POST['emp_dno']) );

     print "employees in dept $_POST['emp_dno'] whose job is

$_POST['emp_job']: \n"

     while ($r = $q->fetchRow()) {

     print "employee $r[0] \n" ;

     }

...

     $allresult = $d->getAll('SELECT Name, Job, Dno FROM EMPLOYEE');

     foreach ($allresult as $r) {

     print "employee $r[0] has job $r[1] and works for department $r[2] \n" ;

     }

...

Figure 14.7

Illustrating database retrieval queries.

$POST['emp_dno']. If the user had entered ‘Engineer’ for the job and 5 for the department number, the query would select the names of all engineers who worked in department 5. As we can see, this is a dynamic query whose results differ depend-ing on the choices that the user enters as input. We used two ? placeholders in this example, as discussed at the end of Section 14.3.2.

The last query (lines 14 to 17) shows an alternative way of specifying a query and looping over its rows. In this example, the function $d=>getAll holds all the records in a query result in a single variable, called $allresult. To loop over the individual records, a foreach loop can be used, with the row variable $r iterating over each row in $allresult.

As we can see, PHP is suited for both database access and creating dynamic Web pages.