PHP and Informix: Web Development, Dynamic Content and More!
by Mario Estrada
Overview
This article contains the following information:
Many of us at some point have had to create database-driven Web pages, ranging from simple registration forms to sales reports with graphics and E-commerce solutions. Coming from the UNIX world, I am surprised how third party technologies such as ASP from Microsoft interact with their database servers (MS Access/SQL Server), using vbscript with ADO/ODBC in a so-called "easy way." Examples of ASP are available in many magazines around the world; while the look and feel is fantastic, problems arise when the application is actually deployed in a real-world environment with many hits made to the Web server and many operations executed against the database server.
Since Microsoft ASP technology does not work with UNIX Web servers unless you use third party ASP implementations, UNIX users need an NT Web Server and have to deal with ADO/ODBC connections in order to interact with their Informix database server, which can reside in either another NT machine or even in another UNIX machine. Either way, the configuration is not optimal due to the overhead associated with the ADO/ODBC connections.
Personal Home Page (PHP), a server-side HTML-embedded scripting language freely available at www.php3.org, on the other hand, offers an easy way to make scripts that interact with your Informix database server. Like ASP, PHP is embedded within the HTML code; however, it has the added advantage of being able to talk to your database server without the need of ODBC connections. PHP itself is written in C, which is why the extended database API that now supports Informix is actually an ESQL/C program (ifx.ec) dynamically linked to the ESQL/C libraries. This allows native communication support for Informix Dynamic Server™ (IDS), Extended Parallel Option™ for Informix Dynamic Server, Informix Universal Server, and Informix SE database servers. As a result, PHP's performance is incredible.
If you are coming from the C or PERL world, you will feel at home with PHP since it has borrowed its syntax from these languages. PHP can be integrated into the Apache Web server, making it even more powerful since it will not force the Web server to fork a new process each time a script is executed. PHP was created by Rasmus Lerdorf but many developers have joined in to extend the language; among these developers are Danny Heijl, Christian Cartus and Jouni Ahto, who are responsible for the Informix interface. PHP can be integrated with Apache, Microsoft IIS and Netscape Web servers running on Linux, UNIX and Microsoft Win95 and NT operating systems. PHP interacts with Informix database servers natively regardless of the operating system or Web server you choose.
This article will cover the following topics, presenting a sample application to give you an idea of how powerful PHP can be when serving as the front-end to an Informix database server:
- PHP and Informix Architecture
- Installing PHP and Apache Web Server
- Escaping from HTML
- PHP Informix API
- An Example Application
- The Registration Form
- Displaying Images Stored in the Informix Database
- Drilling Down from Orders to Items
- Deleting Cookies Stored at the Client Machine
Installing PHP is simply a matter of following the installation instructions in the manual available on the PHP Web site. I have installed RedHat Linux Distribution, IDS 7.3.x, and Informix Client SDK™ available for Linux. If you want the database to reside in another machine, you still need Informix Client SDK where the Apache Web server and PHP will be installed.
Make sure to install the Informix database server and Informix Client SDK prior to installing the Apache Web server and PHP. Also, have the following Informix environment variables available (shown in Table 1), so that when you configure PHP it will know the Informix version and the location of the ESQL/C libraries.
Table 1: Informix environment variables.
Below is a step-by-step guide to installing the Apache Web server with PHP as a module. If you have already installed Apache with Dynamic Shared Object (DSO) support, then refer to the PHP manual on how to add the PHP module as a shared object, without the need to recompile and re-install the Apache Web server.
Step 1: Extract the Packages
It is recommended that you install the latest versions of Apache and PHP. Make sure to have both distributions in the same directory. In my configuration, I have these two packages located at /usr/src/redhat/SOURCES. From this directory, run the following two commands to extract the source code.
$ gzip -d -c apache_1.3.x.tar.gz | tar xvf -
$ gzip -d -c php-3.0.x.tar.gz | tar xvf -
Step 2: Pre-configure Apache
At this point, you need to know where you want the Apache Web server to be installed—for example, /usr/local/apache. Run the following commands to pre-configure Apache so that PHP knows exactly where the Apache Web server will be installed.
$ cd apache_1.3.x
$ ./configure \
--prefix=/usr/local/apache
$ cd ..
Step 3: Configure PHP
Now you will need to configure, compile, and install PHP as a module and apply it to the Apache source tree.
$ cd ../php-3.0.x
$ ./configure \
--with-apache=../apache_1.3.x \
--with-informix=$INFORMIXDIR
$ make
$ make install
$ cd ..
Step 4: Build and Install Apache with mod_php
$ cd apache_1.3.x
$ ./configure \
--prefix=/usr/local/apache \
--activate-module=src/modules/php3/libphp3.a \
--enable-module=php3
$ make
$ make install
$ cd ..
Step 5: Delete the source trees (optional)
$ rm --fr php-3.0.x
$ rm --fr apache_1.3.x
At this point, you have just compiled and installed Apache with PHP as a module. Follow the instructions in the PHP manual from here for your particular platform.
Now you need to add the LD_LIBRARY_PATH environment variable to the script apachectl located in /usr/local/apache/bin (if you are using Apache1.3.6) or /usr/local/apache/sbin (if you are using a prior version of Apache). You must use the directory where you installed your Apache Web server. Keep in mind that I installed my Apache distribution at /usr/local/apache.
Example:
---file apachectl--
LD_LIBRARY_PATH=/opt/informix/lib:/opt/informix/lib/esql;export
LD_LIBRARY_PATH
If you are using Informix SE, then you can set in this file the DBPATH environment variable so that PHP knows the location of your databases.
Example:
---file apachectl--
DBPATH=/your/path_to_your_databases;export DBPATH
LD_LIBRARY_PATH=/opt/informix/lib:/opt/informix/lib/esql;export
LD_LIBRARY_PATH
If you do not set the LD_LIBRARY_PATH in the apachectl script file, whenever you try to start your Apache Web server it will complain about the location of the Informix ESQL/C libraries.
Note: For large scale Web applications that need to be optimized and compiled into platform-independent library files, a tool is currently under development. This tool will also be freely available and will replace a future version of PHP, but not PHP as a whole. For more information, visit their Web site at www.zend.com.
In this section, we will discuss the features of the Registration Form—the HTML form displayed in the client browser as shown in Figure 2. The register.php3 script shown below in Listing 1 actually creates this form. Listing 1 will create two <SELECT> options based on information in the tables "country" and "state." Take a look at the sequence of the Informix API calls that achieves this.
- ifx_connect—connects to Informix Dynamic Server
- ifx_prepare—prepares the SQL statement
- ifx_do—executes the statement, generating an identifier (actually a cursor)
- while (is_array(?))—will make a loop until no more information is returned from the cursor, in the form of an array
- ifx_fetch_row—returns an array structure based on columns specified in the SQL select statement; in this case, we pass the word "next," which means fetch the next row.
After entering their information, the user needs to press the "Register!" button, which in turn calls the script registerinsert.php3 detailed in Listing 2.
Listing 1
<SCRIPT LANGUAGE = PHP>
/*
Program : register.php3
Author : Mario Estrada
Date : 05/01/1999
*/
require("../incl/headerfooter.inc");
nocache;
common_header();
</SCRIPT>
<TABLE VALIGN=TOP ALIGN=CENTER BORDER=0>
<CAPTION>
<H2 CLASS="subtitle">Welcome! to the Registration Form</H2>
</CAPTION>
<FORM ACTION="registerinsert.php3" METHOD="POST">
<TR><TD WIDTH=10%>First Name :</TD>
<TD WIDTH=40%>
<INPUT TYPE="text" NAME="fname" SIZE="15" MAXLENGTH="15"></TD></TR>
<TR><TD WIDTH=10%>Last Name:</TD>
<TD WIDTH=40%>
<INPUT TYPE="text" NAME="lname" SIZE="15" MAXLENGTH="15"></TD></TR>
<TR><TD WIDTH=10%> Company :</TD>
<TD WIDTH=40%>
<INPUT TYPE="text" NAME="company" SIZE="20" MAXLENGTH="20"></TD></TR>
<TR><TD WIDTH=10%> Address: </TD>
<TD WIDTH=40%>
<INPUT TYPE="text" NAME="address1" SIZE="20" MAXLENGTH="20">
<INPUT TYPE="text" NAME="address2" SIZE="20" MAXLENGTH="20"></TD></TR>
<TR><TD WIDTH=10%> City:</TD>
<TD WIDTH=40%>
<INPUT TYPE="text" NAME="city" SIZE="15" MAXLENGTH="15"></TD></TR>
<TR><TD WIDTH=10%> Country:</TD>
<TD WIDTH=40%>
<?php
/*
We are going to use PHP to generate a list of valid countries
and states from the tables country and state.
*/
$res = ifx_connect("stores7@ds_mario","informix","informix");
if (!$res)
{
printf("can not connect to server\n");
exit;
}
/*----------Generate the list of Countries----------*/
$querycountries = "select * from country order by cname";
$countryCR = ifx_prepare($querycountries,$res);
ifx_do($countryCR);
$arrayofcountries = ifx_fetch_row($countryCR,"next");
printf("<SELECT NAME='country'>\n");
$selected = "";
while (is_array($arrayofcountries))
{
if ($arrayofcountries["code"] == "GT")
$selected = "SELECTED";
printf("<OPTION VALUE='%s' %s> %s\n",
$arrayofcountries["code"],$selected,$arrayofcountries["cname"]);
$arrayofcountries = ifx_fetch_row($countryCR,"next");
}
printf("</SELECT>\n");
/*Free the memory associated with the cursor*/
ifx_free_result($countryCR);
/*----------Generate the list of States----------*/
$querystates = "select * from state order by sname";
$stateCR = ifx_prepare($querystates,$res);
ifx_do($stateCR);
$arrayofstates = ifx_fetch_row($stateCR,"next");
printf(" State:<SELECT NAME='state'>\n");
/*This is to allow a NULL value where the state name is not applicable*/
printf("<OPTION VALUE=''>\n");
while (is_array($arrayofstates))
{
printf("<OPTION VALUE='%s'>%s\n",
$arrayofstates["code"],$arrayofstates["sname"]);
$arrayofstates = ifx_fetch_row($stateCR,"next");
}
printf("</SELECT>\n");
/*Free the memory associated with the cursor*/
ifx_free_result($stateCR);
?>
<TR><TD WIDTH=10%> Zip Code: </TD>
<TD WIDTH=40%>
<INPUT TYPE="text" NAME="zipcode" SIZE="05" MAXLENGTH="05"></TD></TR>
<TR><TD WIDTH=10%> Phone Number:</TD>
<TD WIDTH=40%>
<INPUT TYPE="text" NAME="phone" SIZE="18" MAXLENGTH="18"></TD></TR>
<TR><TD WIDTH=10%>Login:</TD>
<TD WIDTH=40%>
<INPUT TYPE="text" NAME="login" SIZE=20 MAXLENGTH="20"></TD></TR>
<TR><TD WIDTH=10%> Password: </TD>
<TD WIDTH=40%>
<INPUT TYPE="password" NAME="password" SIZE=20 MAXLENGTH="20"></TD</TR>
<TR><TD ALIGN=CENTER COLSPAN=2><TD>
<FONT COLOR= #7384bd> <INPUT TYPE="submit" VALUE="Register!" > </FONT>
</TD>
<TD>
</TD></TR>
</FORM>
</TABLE>
<SCRIPT LANGUAGE = PHP>
common_footer(8);
</SCRIPT>
Figure 2: The Registration Form.
Listing 2
<SCRIPT LANGUAGE = PHP>
/*
Program : registerinsert.php3
Author : Mario Estrada
Date : 05/01/1999
*/
/*Connect to Informix, notice that you can use the ifx_pconnect function
as well to provide persistent connections*/
$res = ifx_connect("stores7@ds_mario","informix","informix");
/*Create the string to hold the insert statement*/
$insstmt= sprintf("insert into customer(customer_num,fname,lname,");
$insstmt= sprintf("%s %s",$insstmt,
"company,address1,address2,country,city,state,zipcode,");
$insstmt= sprintf("%s %s",$insstmt,"phone,login,password) values (0,");
$insstmt= sprintf("%s %s",$insstmt,"\"$fname\",");
$insstmt= sprintf("%s %s",$insstmt,"\"$lname\",");
$insstmt= sprintf("%s %s",$insstmt,"\"$company\",");
$insstmt= sprintf("%s %s",$insstmt,"\"$address1\",");
$insstmt= sprintf("%s %s",$insstmt,"\"$address2\",");
$insstmt= sprintf("%s %s",$insstmt,"\"$country\",");
$insstmt= sprintf("%s %s",$insstmt,"\"$city\",");
$insstmt= sprintf("%s %s",$insstmt,"\"$state\",");
$insstmt= sprintf("%s %s",$insstmt,"\"$zipcode\",");
$insstmt= sprintf("%s %s",$insstmt,"\"$phone\",");
$insstmt= sprintf("%s %s",$insstmt,"\"$login\",");
/*In this case we are using the salt I9 with the PHP function crypt to
make sure nobody can read the customer's password
*/
$password = crypt($password,"I9");
$insstmt= sprintf("%s %s",$insstmt,"\"$password\"");
$insstmt= sprintf("%s %s",$insstmt,")");
$statement_id = ifx_prepare($insstmt,$res);
/*Create a cookie at the client.
The third parameter indicates it will expire in approximately 30 days!
TIP: For shopping carts, the time expiration field could be 0; that is,
it will be available until you close the browser program or your script
directly calls the SetCookie function without parameters (just specifying
the cookie name).
*/
SetCookie("CfirstName",$fname,time()+2592000,"/articulo/bin",".lesco.com.gt",0);
SetCookie("ClastName",$lname,time()+2592000,"/articulo/bin",".lesco.com.gt",0);
require("../incl/headerfooter.inc");
common_header();
if (ifx_do($statement_id))
{
?>
<CENTER>
<H1> Thank you for Registering! </H1>
<A HREF='http://mario.lesco.com.gt/articulo/bin/index.php3'>
Continue Shopping!
</A>
</CENTER>
<?php
}
ifx_free_result($statement_id);
ifx_close($res);
common_footer(10);
</SCRIPT>
The registerinsert.php3 script makes the user information available in the form of variables; it then inserts this information into the customer table. You will note that the password field in the customer table is encrypted. This is achieved by calling the crypt() PHP function, allowing us to keep the user's password a secret...even from us!
After inserting the information, the registerinsert.php3 script calls the SetCookie() PHP function in order to create two variables in the client machine: CfirstName and ClastName. These two variables expire in 30 days, indicated by the third parameter of the SetCookie() PHP function. By registering the two variables at the client machine, we will be able to recognize the user the next time he/she comes to this site. This is possible because PHP automatically asks the client browser for cookies registered by our application and then makes them available through the GLOBAL information array in the form of normal variables.
Some of the shopping cart routines you see whenever you buy merchandise over the Internet make use of cookies, but instead of variables, an array is used for every column of information you want in the shopping cart. If you use PHP for shopping carts, you should use the number zero (0) in the third parameter of the SetCookie() PHP function. This will tell the client browser that the information will be available just during the user session.
Informix Dynamic Server supports both TEXT and BYTE datatypes for storing almost anything, including text and images of any type. In this example, we use the BLOB column cat_picture from the catalog table to display the item's picture. We also use two separate scripts, one to display the item's information and the other to display its picture. This is necessary since a script can only send one type of information to the client browser at a time—specified in the header() PHP function. Thus, it is impossible to have the script that sends the item's description also display its picture. This technique is illustrated in Figure 3.
Figure 3: Technique for using two PHP scripts to display both text and images.
Once the user enters their query information in the text box located in the left menu—such as the word "jersey" or "baseball"—he/she can click on the "Go" button located just after the text box to start the search. The text box and the "Go" button are actually components of an HTML form that calls the catsearchscript.php3 script (Listing 3) along with the "catdescription" variable. This variable is then used to query the catalog and stock tables in order to retrieve the Catalog Number, Stock Number, Manufacturer Code, Advertising Text, Description and Unit Price for every item that matches the description.
Take a close look at the following two scripts (Listings 3 and 4) as they form the basis for electronic catalogs.
Listing 3
<?php
/*
Program : catsearchscript.php3
Author : Mario Estrada
Date : 05/01/1999
*/
require("../incl/headerfooter.inc");
require("../incl/formatoutput.inc");
nocache();
common_header();
/*The following if statement ensures that this php script is
invoked along with information in the $catdescription variable
*/
if (!$catdescription)
{
echo("<CENTER><H1> You must supply a Catalog Number ");
echo("! </CENTER></H1>\n");
die;
}
/*Connect to Informix; in this case, we use the login and password informix*/
$res = ifx_connect("stores7@ds_mario","informix","informix");
if (!$res)
{
printf("can not connect to server\n");
exit;
}
$querycatalog = sprintf("select a.catalog_num,a.stock_num,".
"a.manu_code,a.cat_advert,b.description,b.unit_price ".
" from catalog a,stock b ".
" where a.stock_num = b.stock_num".
" and a.manu_code = b.manu_code".
" and b.description matches '*%s*'" ,$catdescription);
$catalogCR = ifx_prepare($querycatalog,$res);
ifx_do($catalogCR);
$catarray = ifx_fetch_row($catalogCR,"next");
while (is_array($catarray))
{
?>
<DD>
<TABLE bgColor=#ffffff border=0 cellPadding=0 cellSpacing=0 width="100%">
<TBODY>
<TR>
<TD bgColor=#ff0000 vAlign=top>
<IMG align=top alt="" height=16 src="../images/slc.gif" width=12>
<FONT color=#ffffff face=arial,helvetica size=2><B>
<?php
echo($catarray["description"]);
?>
</B> </FONT>
</TD>
<TD vAlign=bottom width=4>
<IMG align=bottom alt="" height=18 src="../images/srs.gif" width=4>
</TD>
</TR>
</TBODY>
</TABLE>
<IMG align=right alt=Technology border=0 height=80 hspace=20
<?php
/* IMPORTANT: A script can only send back information of a certain type;
in this case, we use a separate script to show the image, thus,
the image/gif header information has to be sent back from
showpiccatalog
*/
printf("<IMG ALIGN=RIGHT border=0 height=80 hspace=20 SRC =
\"showpiccatalog.php3?catalognum=%s\" vspace=10 width=80>\n",
$catarray["catalog_num"]);
echo("<BR><DD>");
printf("<B>Catalog Number:</B><font Size=2> %s </font>\n",
$catarray["catalog_num"]);
echo("<BR><DD>\n");
printf("<B> Advertising :</B><font size=2><DD>%s</DD></font> \n"
,$catarray["cat_advert"]);
echo("<BR><DD>\n");
printf("<B>Unit Price $:</B><font size=2> %s</font>\n",$catarray["unit_price"]);
echo("<BR>\n");
/* ---------->Important<----------
The addcart.php3 script handles the cookies that will be
registered in the client side. For shopping carts, you must
use an array to hold the quantity and the catalog number
*/
echo("<BR><DD>\n");
printf("<FORM ACTION = './addcart.php3'>\n");
printf("<INPUT TYPE='TEXT' NAME= 'quantity' VALUE=1 size=03 maxlength=03>\n");
printf("<INPUT TYPE='hidden' NAME='catalognum' VALUE=%s>\n",
$catarray["catalog_num"]);
printf("<INPUT TYPE='submit' VALUE='Buy Now'>\n");
printf("</FORM>\n");
echo("<BR>\n");
echo("<BR>\n");
echo("</DD>\n");
$catarray = ifx_fetch_row($catalogCR,"next");
}
ifx_free_result($catalogCR);
common_footer(8);
?>
The catsearchscript.php3 script creates an <IMG> HTML tag. As a source for that image, it calls the showpiccatalog.php3 script (Listing 4) along with the "catalognum" variable. You can see that the showpiccatalog.php3 script only retrieves the cat_picture from the catalog table for the item whose Catalog Number matches the content of the "catalognum" variable and then displays this image back to the calling script with a simple echo PHP function. Notice that the header information for the showpiccatalog.php3 script differs from the rest of the scripts shown in this article in that it uses the IMAGE/GIF string in the header() PHP function. This is a directive to the client browser indicating that the content must be interpreted as an image and not as a text.
Listing 4
<SCRIPT LANGUAGE=PHP>
/*
Program : showpiccatalog.php3
Author : Mario Estrada
Date : 05/01/1999
Purpose : Reads the image from a BLOB column and sends it
back to the Web browser with the appropriate header.
*/
/*The following statement ensures that the Web browser will
recognize the following content as a GIF image */
header("Content-type: image/gif");
$res = ifx_connect("stores7@ds_mario","informix","informix");
ifx_blobinfile_mode(0); //0=in memory, 1=in file
ifx_nullformat(1);
$query = sprintf("select cat_picture from catalog where catalog_num = %s",
$catalognum);
$rid=ifx_prepare($query,$res);
ifx_do($rid);
$array=ifx_fetch_row($rid);
$tmp=ifx_get_blob($array["cat_picture"]);
/*The following statement sends the content of the image back to
the Web browser
*/
echo $tmp;
ifx_free_blob($array["cat_picture"]);
ifx_free_result($rid);
ifx_close($res);
</SCRIPT>
Assuming the user entered the word "baseball", you will see the output shown in Figure 4.
Figure 4: Search results displaying both item text and image.
This section of the example application shows how a PHP script can drill down from an orders result set to an item result set. We begin by creating the HTML form that asks the user for a customer number. The ordsearch.php3 script shown in Listing 5 below creates this form.
Listing 5
<SCRIPT LANGUAGE = PHP>
/*
Program : ordsearch.php3
Author : Mario Estrada
Date : 05/01/1999
*/
require("../incl/headerfooter.inc");
common_header();
</SCRIPT>
<!-The following is the HTML code to ask for a Customer Number->
<CENTER>
<HR>
<I>
Try numbers between 100 and 110; in the real application, the
browser should already know your customer number. Remember this is
just a simple demonstration program!
<I>
<FORM ACTION="ordsearchscript.php3" METHOD="GET">
Enter the Customer Number:
<INPUT TYPE="text" NAME="customernum" SIZE="10" MAXLENGTH="10">
<INPUT TYPE="submit" VALUE="Search Now!" >
</FORM>
</CENTER>
<SCRIPT LANGUAGE = PHP>
common_footer(12);
</SCRIPT>
Notice the ordsearch.php3 script calls a second script, ordsearchscript.php3 (see Listing 6), that shows all the orders owned by the customer number specified by the user and available to this script via the "customernum" variable. The resulting HTML page is shown in Figure 5.
Note: If you don't want your users to see the query string from their browsers, then just change the METHOD parameter of any HTML form from "GET" to "POST." PHP handles the parsing automatically, regardless of the method option you choose.
Listing 6
<SCRIPT LANGUAGE=PHP>
/*
Program : ordsearchscript.php3
Author : Mario Estrada
Date : 05/01/1999
*/
require("../incl/headerfooter.inc");
require("../incl/formatoutput.inc");
nocache();
common_header();
if (!$customernum)
{
echo("<CENTER><H1> You must supply a Customer Number ");
echo("</CENTER></H1>\n");
die;
}
/*Connect to Informix; in this case, we use the login and password
informix*/
$res = ifx_connect("stores7@ds_mario","informix","informix");
if (!$res)
{
printf("can not connect to server\n");
exit;
}
/* Select the information about the customer and display it in a table just prior to
creating the table that will hold the orders
*/
$querycustomer = sprintf("select fname,lname,company,phone ".
" from customer where customer_num = %s"
,$customernum);
$customerCR = ifx_prepare($querycustomer,$res);
ifx_do($customerCR);
$custarray = ifx_fetch_row($customerCR);
ifx_free_result($customerCR);
echo("<TABLE BORDER = 0 ALIGN = CENTER BGCOLOR = #FFFFCC> \n");
echo("<CAPTION> <B> CUSTOMER INFORMATION </B> </CAPTION>\n");
echo("<TR><TD>\n");
printf("<B>Name</B> %s\n",$custarray["fname"]);
echo("<TD>\n");
printf("%s\n",$custarray["lname"]);
echo("<TD> \n");
printf("<B>Company</B> %s\n",$custarray["company"]);
echo("<TD>\n");
printf("<B>Phone #</B> %s\n",$custarray["phone"]);
echo("</TABLE>\n");
/*If we passed the $ordernum to this script, it will know that we
are drilling down from orders to items; thus, it will need to
create the query string for both the orders and items. If we
do not pass this variable, then it only needs to create a query
string for the orders.
*/
if (!$ordernum)
/*No ordernum was supplied, the user wants to see the list of orders*/
{
$queryorders = sprintf("select orders.*,sum(items.total_price)".
" total_price from orders,items ".
"where orders.customer_num = %s".
" and orders.order_num = items.order_num".
" group by 1,2,3,4,5,6,7,8,9,10 ",$customernum);
}
else
/*The user clicked on an order and wants to see its details*/
{
$queryorders = sprintf("select orders.*,sum(items.total_price)".
" total_price from orders,items ".
"where orders.order_num = %s and orders.customer_num".
" = %s".
" and orders.order_num = items.order_num".
" group by 1,2,3,4,5,6,7,8,9,10 "
,$ordernum,$customernum);
$queryitems = sprintf("select items.item_num,items.order_num,".
" items.stock_num".
" ,items.manu_code,stock.description,items.quantity".
" ,items.total_price".
" from items,stock ".
" where items.stock_num = stock.stock_num".
" and items.manu_code = stock.manu_code".
" and order_num = %s",$ordernum);
}
$resultid = ifx_query($queryorders, $res);
if (!check_ifx($resultid))
{
printf("ifx_query : %s:%s \n", ifx_error($res), ifx_errormsg());
die;
}
if (! $resultid)
{
printf("ifx_query : bailing out \n");
die;
}
ShowOrders($resultid,"BORDER=0 ALIGN=CENTER");
/*If we have items to show*/
if ($queryitems)
{
$resultitemid = ifx_query($queryitems,$res);
/*In this case, we are calling the generic function to
format the rows*/
ShowResults($resultitemid,"ITEMS INFORMATION",
"BORDER=0 ALIGN=CENTER");
}
common_footer(5);
</SCRIPT>
Figure 5: Order result set from the Customer Number.
From here, if the user clicks on any order row, it will call the ordsearchscript.php3 script again (Listing 6); but this time, it will display the order clicked by the user along with its item information. The resulting HTML page is shown in Figure 6 below.
Figure 6: Item information from the order result set.
When we create the information about the orders, we call the function ShowOrders() located in the file formatoutput.inc (see Listing 7). This function generates anchor HTML tags for every order row so that the same script (ordsearchscript.php3) is called but with different parameters. The item information, on the other hand, is displayed using a generic function, ShowResults(), which creates the HTML table but without anchor HTML tags. You can use this example as the basis for any kind of Web-based report using Informix as the database server.
Listing 7
<SCRIPT LANGUAGE = PHP>
/*
Program : formatoutput.inc
Author : Mario Estrada
Date : 05/01/1999
Purpose : To hold the functions to provide a way to format the output
to the browser and functions to check for sql errors.
Note : The ShowResults() function is a generic function to show
the result of a database query within a colored table.
The ShowOrders has been implemented using the same
technique, except that it uses the HREF html tag and uses
more appropriate header names.
*/
/*********************************************************************/
function ShowResults($resultid,$caption,$attributes)
/*********************************************************************/
{
$rows = ifx_affected_rows($resultid);
$cols = ifx_num_fields($resultid);
echo( "<TABLE ");
echo("$attributes>\n");
echo("<CAPTION> <B>$caption</B> </CAPTION>\n");
/* Create the headers */
unset($types);
$types= ifx_fieldtypes($resultid);
echo("<BR>\n");
echo("<BR>\n");
echo( "<TR>\n");
for($i = 0; $i < count($types); $i++)
{
$fname = key($types);
//Print the Headers
printf("<TH align=center bgcolor=#ff0000>");
printf(" <FONT color=#FFFFFF>%s</FONT></TH>\n"
,$fname);
next($types);
}
echo( "</TR>");
$row = ifx_fetch_row($resultid,"first");
$back_color = "#FFFFCC";
$for_color = "#000066";
while (is_array($row))
{
echo("<TR>\n");
for(reset($row); $fname = key($row);next($row))
{
printf("<TD bgcolor=%s> <FONT color=%s>%s</FONT></TD>\n",
$back_color,$for_color,$row[$fname]);
}
echo("</TR>");
$row = ifx_fetch_row($resultid,"next");
if ($back_color == "#FFFFCC")
{
$back_color = "#7384bd";
}
else
{
$back_color = "#FFFFCC";
}
if ($for_color == "#000066")
{
$for_color = "#000000";
}
else
{
$for_color = "#000066";
}
}
echo( "</TABLE>\n");
}
/*********************************************************************/
function ShowOrders($resultid,$atttributes)
/*********************************************************************/
{
$rows = ifx_affected_rows($resultid);
$cols = ifx_num_fields($resultid);
echo( "<TABLE ");
echo("$attributes>\n");
echo("<CAPTION> <B>ORDER INFORMATION</B> </CAPTION>\n");
/* Create the headers */
unset($types);
$types= ifx_fieldtypes($resultid);
make_spaces(1,0);
/*Display the headers for the table orders*/
echo( "<TR>\n");
echo("<TH align=center bgcolor=#ff0000><FONT color=#FFFFFF>Order No.</FONT>
</TH>\n");
echo("<TH align=center bgcolor=#ff0000><FONT color=#FFFFFF>Order Date</FONT>
</TH>\n");
echo("<TH align=center bgcolor=#ff0000><FONT color=#FFFFFF>Customer No.</FONT>
</TH>\n");
echo("<TH align=center bgcolor=#ff0000><FONT color=#FFFFFF>Ship Instructions
</FONT></TH>\n");
echo("<TH align=center bgcolor=#ff0000><FONT color=#FFFFFF>Backlog</FONT>
</TH>\n");
echo("<TH align=center bgcolor=#ff0000><FONT color=#FFFFFF>PoBox No.</FONT>
</TH>\n");
echo("<TH align=center bgcolor=#ff0000><FONT color=#FFFFFF>Ship Date</FONT>
</TH>\n");
echo("<TH align=center bgcolor=#ff0000><FONT color=#FFFFFF>Ship Weight</FONT>
</TH>\n");
echo("<TH align=center bgcolor=#ff0000><FONT color=#FFFFFF>Ship Charge</FONT>
</TH>\n");
echo("<TH align=center bgcolor=#ff0000><FONT color=#FFFFFF>Paid Date</FONT>
</TH>\n");
echo("<TH align=center bgcolor=#ff0000><FONT color=#FFFFFF>Total $</FONT>
</TH>\n");
echo( "</TR>");
$row = ifx_fetch_row($resultid,"first");
$back_color = "#FFFFCC";
$for_color = "#000066";
while (is_array($row))
{
echo("<TR>\n");
for(reset($row); $fname = key($row);next($row))
{
printf("<TD BGCOLOR=%s>\n",$back_color);
printf("<A HREF='ordsearchscript.php3?customernum=%s&ordernum=%s'>"
,$row["customer_num"],$row["order_num"]);
printf("<FONT COLOR=%s> %s </A></FONT></TD>\n"
,$for_color,$row[$fname]);
}
echo("</TR>");
$row = ifx_fetch_row($resultid,"next");
if ($back_color == "#FFFFCC")
{
$back_color = "#7384bd";
}
else
{
$back_color = "#FFFFCC";
}
if ($for_color == "#000066")
{
$for_color = "#000000";
}
else
{
$for_color = "#000066";
}
}
echo( "</TABLE>\n");
}
/*********************************************************************/
function check_ifx($res)
/*********************************************************************/
{
$msg = ifx_error($res);
if ($msg[0] == ' ')
return 1;
else
return 0;
}
</SCRIPT>
Listing 8 shows the source code for the headerfooter.inc file.
Listing 8
<?php
/*
Program : headerfooter.inc
Author : Mario Estrada
Date : 05/01/1999
Purpose :
*/
/*********************************************************************/
function common_header()
/*********************************************************************/
{
?>
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html">
<META NAME = "Generator" CONTENT = "PHP & INFORMIX">
<META NAME = "Author" CONTENT = "Mario Estrada">
<META NAME = "Company" CONTENT = "LESCO-Division de Sistemas">
<STYLE TYPE="text/css">
<!-
BODY { BACKGROUND-COLOR: WHITE; }
.copyright { FONT-FAMILY: arial, helvetica, sans-serif;
TEXT_ALIGN: center;
FONT_SIZE: 8pt; }
.note { FONT-FAMILY: arial, helvetica, sans-serif;
TEXT-ALIGN: center; FONT-SIZE: 75%; color: #336699; }
H1, H2, H3, H4, H5, H6 { FONT-FAMILY: arial, helvetica,
sans-serif;
color: #003399; }
H2.subtitle
{ COLOR: #003399;
FONT-FAMIlY: "times new roman", serif;
FONT-STYLE: italic; }
A:link { color: #0066CC; }
A:visited { color: #660033; }
A:active { color: #FFCC00; }
->
</STYLE>
<TITLE>INFORMIX & PHP INTEGRATION</TITLE>
<?php
/* The following declares an array of 3 elements containing the
names of the buttons */
$buttons[] = "index";
$buttons[] = "register";
$buttons[] = "ordsearch";
/* Now generate the Java script code that will pre-load the
images to the local machine */
echo ("<SCRIPT LANGUAGE = 'JAVASCRIPT'>\n");
echo ("<!--\n");
echo ("if (document.images) {\n");
for ($i=0; $i<count($buttons);$i++)
{
printf("<!-- Preload images for %s -->\n",$buttons[$i]);
/* The ON button */
printf("%son = new Image(135,24);\n",$buttons[$i]);
printf("%son.src = '../images/nav_%s_on.gif';\n"
,$buttons[$i],$buttons[$i]);
/* The OFF button */
printf("%soff = new Image(135,24);\n",$buttons[$i]);
printf("%soff.src = '../images/nav_%s_off.gif';\n"
,$buttons[$i],$buttons[$i]);
}
echo ("}\n");
echo ("function rollover(imgDocID,imgObjName)\n");
echo (" {\n");
echo (" if (document.images) {\n");
echo (" document.images[imgDocID].src = eval(imgObjName + '.src');\n");
echo (" }\n");
echo (" }\n");
echo ("// -->\n");
echo ("</SCRIPT> \n");
echo ("</HEAD>\n");
?>
</HEAD>
<BODY>
<!-- The following table is for the main menu -->
<TABLE WIDTH=100% BORDER=0 CELLSPACING=0 CELLPADDING=0 ALIGN=center>
<TR>
<TD VALIGN=TOP ALIGN=LEFT BGCOLOR=#ffffff>
<A href="http://mario.lesco.com.gt">
<IMG BORDER=0 SRC="../images/lescologo.jpg" ALT="LESCO">
</A>
<TD VALIGN=MIDDLE ALIGN=CENTER BGCOLOR=#ffffff>
<A HREF="http://www.informix.com/linux">
<IMG BORDER=0 SRC="../images/presidentgif.gif"
ALT="Linux & Informix">
</A
</TD>
</TR>
<TR>
<TD VALIGN=TOP>
<FORM ACTION="catsearchscript.php3" METHOD="GET">
<INPUT TYPE="text" NAME="catdescription" SIZE="15"
MAXLENGTH="15">
<INPUT TYPE="IMAGE" NAME="submit" ALIGN=ABSMIDDLE
SRC="../images/searchgo.gif"
WIDTH="30" HEIGHT="16" ALGT="Go" BORDER="0"><BR>
<P CLASS="copyright">
Search Now!, try the words <BR>
glove, basketball,jersey,<BR> baseball
</P>
</FORM>
</TD>
<TD VALIGN=TOP BGCOLOR=#FFFFFF>
<P CLASS="note">
In this space you can Advertise other Companies!
</P>
</TD>
</TR>
<!-- Now Generate The Menu on the left -->
<TR>
<TD VALIGN=TOP>
<?php
/*We are going to use the array declared above to generate the
menu on the left */
for ($i=0; $i<count($buttons);$i++)
{
echo("<BR>\n");
printf("<!-- Row to hold the %s button-->\n",$buttons[$i]);
printf("<A HREF = '%s.php3'\n", $buttons[$i]);
printf(" onMouseover=\"rollover('%s','%son')\"\n"
,$buttons[$i],$buttons[$i]);
printf(" onMouseout =\"rollover('%s','%soff')\">\n"
,$buttons[$i],$buttons[$i]);
printf(" <IMG SRC='../images/nav_%s_off.gif'\n",$buttons[$i]);
printf(" width=135 height=24 alt = '%s' Border = 0 \n"
,$buttons[$i]);
printf(" name ='%s' > </A>\n",$buttons[$i]);
}
/* Insert TWO ROWS */
echo(" <!--In this row, display the Powered by Informix Logo-->\n");
echo("<BR><BR><BR><BR><BR>\n");
echo("<P ALIGN=CENTER>\n");
echo("<A HREF='http://www.informix.com'>\n");
echo("<IMG SRC='../images/pwdinfx.gif' Border = 0> \n");
echo("</A>\n");
echo("</P>\n");
echo(" <!--In this row, display the Powered by PHP Logo-->\n");
echo("<BR>\n");
echo("<P ALIGN=CENTER>\n");
echo("<A HREF='http://www.php3.org'>\n");
echo("<IMG SRC='../images/logophp.gif' Border = 0> \n");
echo("</A>\n");
echo("</P>\n");
echo("</TD>\n");
echo("<!-- Start the Main Content Here -->\n");
echo(" <TD valign=top bgcolor=#ffffff>\n");
}
/*********************************************************************/
function common_footer($maxlines)
/********************************************************************/
{
?>
</TD><TR><TD> </TD><TD>
<!-- Start the Common Footer -->
<BR><BR>
<CENTER>
<A HREF= './index.php3'>Home</A>|
<A HREF= './register.php3'>Register</A>|
<A HREF= './ordsearch.php3'>Search Orders</A>
<BR>
</CENTER>
<P CLASS = "copyright">
Copyright(C) 1999, LESCO - Division de Sistemas , All rights reserved
<BR>
7av.7-78, Of.208 Edf.Centroamericano Z.4 Guatemala, C.A., <BR>
Tels. (502) 3340868 - 3340905
</P>
</TD></TR>
</TABLE>
<!-- End Common Footer -->
</BODY>
</HTML>
<?php
}
/*********************************************************************/
function make_spaces($maxlines,$intable)
/*********************************************************************/
{
for ($i = 0; $i < $maxlines; $i++)
{
if ($intable == 0)
{
echo("<BR> \n");
}
else
echo("<TR><BR> \n");
}
}
/*********************************************************************/
function nocache()
/*********************************************************************/
{
/*This section of code was taken from the PHP manual*/
header("Expires: Mon, 01 Jan 1900 05:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . "GMT");
header("Cache-Control: no-cache, must-revalidate"); //HTTP/1.1
header("Pragma: no-cache"); //HTTP/1.0
}
/*******************************************************************/
function welcome_banner()
/*******************************************************************/
{
global $CfirstName;
global $ClastName;
/*Notice how easy it is to reference the cookie variables again; by
defining them using the keyword global, PHP will make them
available to this function*/
if ($CfirstName!="")
{
printf("<CENTER>\n");
printf("<Font Face = 'Arial' Color = '#339966'>\n");
printf("<B>Hello <I>%s</I><B>\n"
,$CfirstName);
printf(",thanks for visiting!<BR>\n");
printf(",if you are not %s %s "
,$CfirstName
,$ClastName);
printf("<A HREF='./delcookie.php3'> <I>Click here.</I></A></BR>\n");
printf("</Font>\n");
printf("</CENTER>\n");
}
else
{
printf("<CENTER>\n");
printf("<Font Face = 'Arial' Color = '#339966'\n");
printf("<BR>You are not registered!, ");
printf("<BR><A href='./register.php3'> Register Now! </A>");
printf("and see how the application recognizes your information");
printf(" the next time!\n");
printf("</Font>\n");
printf("</CENTER>\n");
}
}
</SCRIPT>
Once a user registers on our site their information is stored in the customer table in the stores7 database and two variables are created in the client machine. If they return to the application again, the browser will display their first and last name and will create an anchor HTML tag, where you can click to delete these two variables. This is important because, in many situations, the user that was our customer may not be the owner of the computer anymore. In Listing 9, you can take a look at the delcookie.php3 script, whose only function is to delete the information we stored at the client machine. To delete a cookie, just call the SetCookie() PHP function specifying the cookie name but without any value.
Listing 9
<SCRIPT LANGUAGE = PHP>
/*
Program : delcookie.php3
Author : Mario Estrada
Date : 05/01/1999
*/
SetCookie("CfirstName","",0,"/articulo/bin",".lesco.com.gt",0);
SetCookie("ClastName","",0,"/articulo/bin",".lesco.com.gt",0);
/*The following PHP function will redirect the browser to the specified
location after removing the cookies from the client computer*/
header("Location: http://mario.lesco.com.gt/articulo/bin/index.php3");
</SCRIPT>
As you can see, you can create powerful, interactive Web pages using PHP and Informix. The PHP language itself is not hard to learn since its syntax is similar to C. What's more, since PHP accesses Informix natively, it performs better that those scripting languages that can only access Informix using ODBC connections.
About the Author
|
