Suppose we have created a database called Pdcl0bjc_prices; that a table of this database is called consum; and that this table has columns called ID, goods and price. The idea is that each row of this table contains the details about an item of consumables that is for sale; the columns headed goods and price contain the description and the price of the item, and the column headed ID contains a unique number for this item.
If we want to supply a WWW page that can be used to query this table, we need first to obtain from the person visiting the page the name of the item of consumables in which he/she is interested. We can do this by providing a WWW page that contains:
0561: <HTML><BODY> 0562: <FORM METHOD="POST" ACTION="pricesquery.php"> 0563: To search for an item of consumables, 0564: type in the name of the MySQL server: 0565: <INPUT TYPE="Text" NAME="server"> 0566: <P> 0567: and type in part of the name of the item (e.g., Series IV): 0568: <INPUT TYPE="Text" NAME="goods"> 0569: <P> 0570: <INPUT TYPE="Submit" VALUE="Run query"> 0571: </FORM> 0572: </BODY></HTML>Go to the WWW form at: http://www.dur.ac.uk/barry.cornelius/papers/phpintro/code/pricesquery.htm
When the visitor to the page clicks on the Run query button, the pricesquery.php script will be executed. The code of this PHP script appears below.
This script first uses mysql_connect to attempt to connect to the MySQL server. It then uses mysql_select_db to choose a database called Pdcl0bjc_prices. It then assigns to the SQLQuery variable a string containing the characters:
0573: SELECT * FROM consum WHERE goods LIKE '%$goods%'
i.e., a string containing the query in which the visitor is interested. It then calls the function mysql_query to send this SELECT statement to the database Pdcl0bjc_prices. If this is successful, the variable q_result now contains a pointer to a temporary table that has just been created; it contains only those rows satisfying the query.
The call of mysql_num_rows finds out how many rows are in this table. And if this is not zero, the script generates an HTML table containing the rows of data that are in this table. It does this by using a for command to wander through each row of the table. For each row, it generates HTML containing the contents of the ID, goods and price columns.
0574: <HTML><BODY>
0575: <?php
0576: $server = $_POST["server"];
0577: $goods = $_POST["goods"];
0578: $c_result = mysql_connect($server, "", "");
0579: $s_result = mysql_select_db("Pdcl0bjc_prices", $c_result);
0580: $SQLQuery = "SELECT * FROM consum WHERE goods LIKE '%$goods%'";
0581: $q_result = mysql_query($SQLQuery, $c_result);
0582: $numrows = mysql_num_rows($q_result);
0583: if ( $numrows == 0) {
0584: echo "<p>There are no consumables with a name like $goods</p>";
0585: }
0586: else {
0587: ?>
0588: <TABLE BORDER="1">
0589: <?php
0590: for ($rownum = 0; $rownum<$numrows; $rownum++) {
0591: ?>
0592: <TR>
0593: <TD ALIGN="right">
0594: <?php echo mysql_result($q_result, $rownum, "ID"); ?>
0595: </TD>
0596: <TD>
0597: <?php echo mysql_result($q_result, $rownum, "goods"); ?>
0598: </TD>
0599: <TD BGCOLOR="yellow" ALIGN="right">
0600: <?php printf("%01.2f", mysql_result($q_result, $rownum, "price")); ?>
0601: </TD>
0602: </TR>
0603: <?php
0604: }
0605: ?>
0606: </TABLE>
0607: <?php
0608: }
0609: ?>
0610: </BODY></HTML>