Querying a Database Using ODBC and PHP

I l @ ve RuBoard

Querying a database with PHP using ODBC is very straightforward, but you do need to follow the steps outlined next .

Setting Up a Database

This chapter uses a sample Microsoft Access database called php4win. (Be warned that Microsoft Access is not a suitable DBMS for use in a situation in which multiple clients access it almost simultaneously . In these circumstances, you should use a product such as Microsoft SQL Server or Oracle.) I have created a simple table called names in the database. Its fields are shown in Table 6.1.

Table 6.1. A Table in the php4win Database

Field Name	Field Type	Field Size
ID	AutoNumber
Name	Text	50

Setting Up a DSN

Appendix A shows you how to set up a DSN for the php4win database. I have created the DSN as a System DSN, but if you are running the Access database and PHP on the same computer, you can use a User DSN.

Setting a Connection

If you obtained PHP as a binary distribution, ODBC is compiled straight into PHP, so you don't need to use a PHP extension for ODBC. (Due to the changing development between PHP versions, I recommend that you make sure that the binary distribution has ODBC support.) To begin using ODBC, you first connect to the database:

 <?php  //connect to the database  $connectionstring = odbc_connect("phpwinaccess", "", "");  ?> 

The odbc_connect function has three parameters: the DSN name (in this case, phpwinaccess), a username, and a password. The username and password are always required. However, if your DBMS does not need a username and password, set them to empty strings.

Although our script will connect to the database, it needs to disconnect from the database after it has finished working with it:

 <?php  //connect to the database  $connectionstring = odbc_connect("phpwinaccess", "", "");  //disconnect from database  odbc_close($connectionstring);  ?> 

Most databases keep connections open but impose a limit on how many are open in order to preserve database resources. When a database reaches its limit for open connections, it prevents other connections from being opened. Thankfully, PHP closes all database connections for you (with or without a close function such as odbc_close ) unless they have been opened with a persistent function.

One such type of persistent connection is the odbc_pconnect function:

 <?php  //connect to the database  $connectionstring = odbc_pconnect("phpwinaccess", "", "");  ?> 

odbc_pconnect differs from odbc_connect in that it preserves the connection for the life of the web server process. You might think this is a bad idea, but in fact it's a good one. As soon as a connection using odbc_pconnect is opened, all PHP scripts that use the same odbc_pconnect function use that one connection (as opposed to the individual connections odbc_connect makes). Using a persistent database connection means that you are not constantly connecting to the database every time a connection is needed. Instead, one remains open for you. When using either a persistent or nonpersistent database connection, it's often best to benchmark both methods of connection and review your DBMS documentation to decide when to use either method.

Querying a Database

The next stage is to query the database to obtain the information you need:

 <?php  //connect to the database  $connectionstring = odbc_connect("phpwinaccess", "", "");  //SQL query  $query = "SELECT Name FROM Names";  //execute query  $result = odbc_do($connectionstring, $query);  //disconnect from database  odbc_close($connectionstring);  ?> 

This script builds on previous scripts by adding the capability to query the database. To do this, you must first set the SQL query that you need to query the database. You store it in a variable ( $query ). This lets you reuse the same SQL query in any later queries. (Although you don't need to do this in this example, it's always a good practice, especially if you are using advanced SQL with long syntax.) It's also useful for error checking. If you develop an error in your SQL query string, you can simply print the variable to the screen to see where you went wrong.

 $query = "SELECT Name FROM Names"; 

You then pass the query to the database and store its result in $result :

 $result = odbc_do($connectionstring, $query); 

Query Errors

It is important when you set the SQL query to spell database and table names correctly. For example, this query:

 $query = "SELECT Name FROM Namess"; 

returns the error shown in Figure 6.5.

Figure 6.5. PHP returning a database query error.

Query Results

If you run the preceding script, you won't see anything displayed. To see things displayed, use the following script:

 <?php  //connect to the database  $connectionstring = odbc_connect("phpwinaccess", "", "");  //SQL query  $query = "SELECT Name FROM Names";  //execute query  $result = odbc_do($connectionstring, $query);  //output results to standard output  odbc_result_all($result, "BORDER=1");  //disconnect from database  odbc_close($connectionstring);  ?> 

This script differs only slightly from the preceding one. It adds the odbc_result_all function:

 odbc_result_all($queryexe, "BORDER=1"); 

This function takes the result from the query you passed to the database in the odbc_do function and displays it in an HTML table, as shown in Figure 6.6. The second argument ( BORDER=1 ) lets you pass some values that you can use to format the HTML table.

Figure 6.6. Database query results from the Access database.

Formatting Results

The problem with using the odbc_result function is that you have no control over how the data is presented. To do this, you must use a few other PHP ODBC functions:

 <html>  <head></head>  <body bgcolor="#FFFFFF">  <table width="75%" border="1" cellspacing="1" cellpadding="1"  bgcolor="#FFFFFF">    <tr bgcolor="#CCFFFF">      <td height="22"><b>Names</b></td>    </tr>       <?php       //connect to the database       $connectionstring = odbc_connect("phpwinaccess", "", "");       //SQL query       $query = "SELECT Name FROM Names";       //execute query       $result = odbc_do($connectionstring, $query);           //query database           while(odbc_fetch_row($result))           {               $name = odbc_result($result, 1);                //format results                print ("<tr><td>$name</td></tr>");           }           //disconnect from database           odbc_close($connectionstring);      ?>  </table>  </body>  </html> 

Notice that you are embedding your PHP script in HTML. You need to do this in order to use HTML to improve the display of your data. First notice the use of the odbc_fetch_row function. This function cycles through all the rows in the database. As it cycles through each row, it will make sure that you don't run into any EOF (End Of File) or other such errors (by trying to read records when he reached the last record in the database).

 while(odbc_fetch_row($queryexe)) 

You can then access the value of each record's columns. These are identified by number, starting at 0. Your table has the columns ID and Name, so ID would be column 0, and Name would be column 1.

 $name = odbc_result($queryexe, 1); 

Note that you store the value of the record in the variable $name . (Again, you don't have to do this, but if you need this information later in the script, it's always a good practice to store values. It also makes reading scripts a little easier.)

Now that you have the values you require, you can display them using HTML for formatting:

 print ("<tr><td>$name</td></tr>"); 

If you run the script, you should see a result similar to the last script (refer to Figure 6.6), but with additional formatting (see Figure 6.7).

Figure 6.7. HTML-formatted database query results from the Access database.

I l @ ve RuBoard