A Sample PHP Web Site | Advantage Database Server: The Official Guide

In order to demonstrate the basic use of ADS using PHP, we have provided you with a simple PHP-based Web site on this book’s CD-ROM . The main page of this site is named index.htm, and it produces the page shown in Figure 16-2 when rendered in a Web browser.

click to expand
Figure 16-2: The home page for the PHP Web site

ON THE CD

The examples in this chapter can be found in the PHP directory on this book’s CD-ROM (see Appendix B).

This Web page contains five different HTML forms, each of which submits an HTTP (Hypertext Transfer Protocol) GET request to an associated PHP file. The following is the contents of this HTML file:

<html> <head> <title>Advantage Database Server</title> </head> <body> <form method="GET" action="getcustomer.php" name="getcustomer">   <h1 align="center">   Advantage Database Server: The Official Guide</h1>   <h2 align="center">PHP Demonstration</h2>   <p align="center">   <img border="0" src="/books/1/373/1/html/2/advantage_logo.gif"></p>   Get a customer record<br>   Customer Number    <input type="text" name="custnumber" size="7"><br>   <input type="submit" value="Submit" name="B1">   <input type="reset" value="Reset" name="B2"><br> </form> <form method="GET" action="storedproc.php" name="storedproc">   <hr>   Execute a stored procedure<br>   Return 10% of invoices for Customer Number    <input type="text" name="custnumber" size="7"><br>   <input type="submit" value="Submit" name="B1">   <input type="reset" value="Reset" name="B2"><br> </form> <form method="GET" action="changeaddress.php" name="changeaddress">   <hr>   Change an address in the customer table <br>   Customer Number    <input type="text" name="custnumber" size="7"><br>   New Address <input type="text" name="newaddress" size="50"><br>   <input type="submit" value="Submit" name="UpdateAddress">   <input type="reset" value="Reset" name="B1"><br> </form> <form method="GET" action="showproducts.php">   <hr>   Build product selection page<br>   <input type="submit" value="Submit" name="B1"><br> </form> <form method="GET" action="showtables.php" name="showtables">   <hr>   Show table names<br>   <input type="submit" value="Submit" name="B1"><br> </form> </body> </html>

Note

These forms were submitted using the HTTP GET method so that you can see any submitted data in the URL displayed in your Web browser. Many Web developers prefer to submit forms using the POST method.

There is another characteristic of these examples that is worth noting. Several of these example PHP files expect user input, which is then incorporated into SQL queries. Parameterized queries are used when user input is incorporated into the SQL queries in these examples. As you learned in Chapter 9, since ADS version 7.0 you can execute SQL scripts that contain two or more SQL statements, separated by semicolons. While this is convenient, it exposes a potential security risk if you do not use parameterized queries. Because all user input is incorporated into queries using parameters, this security risk is eliminated.

Connecting to ADS Using PHP

You connect to ADS from PHP by calling the function ads_connect. This function takes either three or four arguments and returns a connection resource. The first argument is an ODBC connection string. The PHP driver connects through the ODBC API by invoking SQLDriverConnect, to which it passes this connection string. See Table 16-1 for the required and valid connection string parameters.

The second and third parameters are the user name and password to use for the connection, and the optional fourth parameter is used to define what type of cursor you want returned. The valid values for this fourth parameter are 0 (SQL_CURSOR_FORWARD_ONLY), 1 (SQL_CURSOR_DYNAMIC), and 2 (SQL_CURSOR_KEYSET_DRIVEN). If you omit this parameter, a live (dynamic) cursor is returned.

The following is an example of a call to ads_connect:

$rConn = ads_connect("DataDirectory=\\\\server\\share\\".   "program files\\extended systems\\advantage\\adsbook\\".   "DemoDictionary.add;ServerTypes=2;", "adsuser", "password");

The connection string in this command attempts to connect to the DemoDictionary data dictionary located on a share named share on a server named server. In addition, this connection string specifies that it wants to connect to ADS (the license for ALS does not permit you to connect to ALS from a Web server). As is the case with ODBC connections, all parameters not included in the connection string will be expanded using the default parameter values.

Once you are through with the connection, you must close it. You do this by invoking ads_close, passing the connection resource obtained from the call to ads_connect. The following is a simple example of this function call:

ads_close( $rConn );

Using Parameterized Queries in PHP

Once you have established a connection, you invoke the ads_prepare function to prepare a parameterized query. This function takes two parameters. The first parameter is the connection resource obtained by calling ads_connect, and the second is a string containing the parameterized query.

Once you have prepared your parameterized query, you bind the parameters and execute the query by calling ads_execute. This function takes two parameters: the handle of the prepared statement returned from the ads_prepare function, and an array of values to bind to the parameters, based on their ordinal position within the query.

The execution of a parameterized query is demonstrated in the following listing, which contains the PHP statements from the getcustomer.php file:

<? $rConn = ads_connect( "DataDirectory=\\\\server\\share\\".   "program files\\extended systems\\advantage\\adsbook\\".   "DemoDictionary.add;ServerTypes=2;", "adsuser", "password" ); $rStmt = ads_prepare( $rConn, "SELECT * FROM customer ".   "WHERE [Customer ID] = ?" ); $aParams = array( 1 => $_GET[ "custnumber" ] ); $rResult = ads_execute( $rStmt, $aParams ); if ( ads_fetch_row( $rStmt ) )   {   $strFirstName = ads_result( $rStmt, "First Name" );   $strLastName = ads_result( $rStmt, "Last Name" );   $strAddress = ads_result( $rStmt, "Address" );   echo "Name: " . $strFirstName . " " . $strLastName . "<br>";   echo "Address: " . $strAddress . "<br>";   } else   {   echo "Invalid Customer Number! <br>";   } ads_close( $rConn ); ?>

As you can see, once the connection is established, and the parameterized query is prepared, a single element array is constructed from the custnumber value passed in the query string of this HTTP GET request (if you used a POST action, you would have read this value using the $_POST PHP function). The query is then executed.

Following the execution of the query, ads_fetch_row is called to retrieve one record from the result set. Individual fields of this record are read using the ads_result function.

If you enter customer number 12037 in the Customer Number field of the Get a customer record HTML form of index.htm and click Submit, your browser will display the following page:

click to expand

Getting Tables from Result Sets Using PHP

After executing a query, you can easily display the entire result set by calling ads_result_all. This function takes the handle returned from a call to ads_execute in its first parameter, and an optional string containing HTML table element attributes in the second, and returns a string containing a complete HTML <TABLE> definition that includes one row for each record in the result set. This function is only used when you execute a query that returns one or more records.

The use of ads_result_all is demonstrated in the showtables.php script. The PHP statements from this file are shown in the following listing. When this script is rendered, it produces the Web page shown in Figure 16-3:

<? $rConn = ads_connect( "DataDirectory=\\\\server\\share\\".   "program files\\extended systems\\advantage\\adsbook\\".   "DemoDictionary.add;ServerTypes=2;", "adsuser", "password" ); $rStmt = ads_prepare( $rConn, "SELECT Name FROM system.tables" ); $rResult = ads_execute( $rStmt ); ads_result_all( $rStmt ); ads_close( $rConn ); ?>

click to expand
Figure 16-3: The Web page returned by showtables.php

As mentioned earlier, the ads_result_all function can accept a second, optional parameter. You use this parameter to pass a string that will be incorporated into the <TABLE> element. Normally you use this parameter to pass one or more table attributes that will be used to control the table’s format and behavior, such as bgcolor, border, onclick, style, id, and width, to name a few.

Editing Data

Since PHP uses ODBC, and ODBC uses SQL to edit data, you change data from a PHP script by executing a SQL UPDATE statement. This is demonstrated in the following PHP statements from the script named changeaddress.php. This script expects two values, a customer ID and a string containing a new address, to be passed in the HTTP GET query string. These values are used to execute a parameterized UPDATE query statement. Once the update has been executed, this script performs a SQL SELECT to read the newly updated address from the CUSTOMER table:

<? $rConn = ads_connect( "DataDirectory=\\\\server\\share\\".   "program files\\extended systems\\advantage\\adsbook\\".   "DemoDictionary.add;ServerTypes=2;", "adsuser", "password" ); $rStmt = ads_prepare( $rConn, "UPDATE customer ".   "SET Address = ? WHERE [Customer ID] = ?" ); $aUpdateParams = array( 1 => $_GET[ "newaddress" ],   2 => $_GET[ "custnumber" ] ); $rResult = ads_execute( $rStmt, $aUpdateParams ); $iRowsAffected = ads_num_rows( $rStmt ); if ( $iRowsAffected == 0 )    {   echo "Invalid customer ID!<br><br>\n";   } $rStmt = ads_prepare( $rConn, "SELECT * FROM customer ".   "WHERE [Customer ID] = ?" ); $aSelectParams = array( 1 => $_GET[ "custnumber" ] ); $rResult = ads_execute( $rStmt, $aSelectParams ); if ( ads_fetch_row( $rStmt ) )   {   $strFirstName = ads_result( $rStmt, "First Name" );   $strLastName = ads_result( $rStmt, "Last Name" );   $strAddress = ads_result( $rStmt, "Address" );   echo "Address successfully changed!<br><br>";   echo "The new address is: " . $strAddress . "<br>";   } ads_close( $rConn ); ?>

Scanning Result Sets

Scanning involves the sequential navigation of the records in a result set. Scanning is often done when you want to manually insert HTML based on your result set into the stream that is inserted in place of the PHP commands.

The following PHP commands are located in the showproducts.php script. These commands produce an HTML form that includes one radio button for each product found in the PRODUCT table. Because of the action attribute of the HTML form, the product code of the selected product name will be appended to the query string part of the HTTP GET command that will be submitted to the showselection.php script.

<? $rConn = ads_connect( "DataDirectory=\\\\server\\share\\".   "program files\\extended systems\\advantage\\adsbook\\".   "DemoDictionary.add;ServerTypes=2;", "adsuser", "password" ); $rStmt = ads_prepare( $rConn, "SELECT [Product Name], ".   "[Product Code] FROM Products" ); $rResult = ads_execute( $rStmt ); while ( ads_fetch_row( $rStmt ) )   {   $strProductName = ads_result( $rStmt, "Product Name" );   $strProductCode = ads_result( $rStmt, "Product Code" );   echo "<INPUT Type = \"radio\" Name = \"rb\" Value = \"" .    trim( $strProductCode ) .  "\" > " .    $strProductName . "<br>\n";    } ads_close( $rConn ); ?>

When this PHP file is processed, it produces the Web page shown in Figure 16-4.

click to expand
Figure 16-4: The output from showproducts.php

Calling a Stored Procedure

This final example demonstrates the execution of a stored procedure using PHP. Actually, as you can see from these statements, executing a stored procedure is no different than any other type of parameterized query. After a call to ads_prepare, an array is created to hold the parameter values, after which it is passed as an argument to ads_execute. Once again, the ads_result_all function is used to render an HTML table from the query result set.

<? $rConn = ads_connect( "DataDirectory=\\\\server\\share\\".   "program files\\extended systems\\advantage\\adsbook\\".   "DemoDictionary.add;ServerTypes=2;", "adsuser", "password" ); $rStmt = ads_prepare( $rConn,    "EXECUTE PROCEDURE DelphiAEP ( ? )" ); $aParams = array( 1 => $_GET[ "custnumber" ] ); $rResult = ads_execute( $rStmt, $aParams ); if ( $rResult == FALSE )    {   echo ads_errormsg( $rConn ) . "<br>\n";   } else   {   ads_result_all( $rStmt );   } ads_close( $rConn ); ?>