| Table of Contents | Previous Next |
18.4 Designing database applications with PHP (I)18.4.1 Using PHP on databasesStarting out as a Personal Home Page tool in 1995, PHP is now a widely used scripting language on the Web. It is believed that more than 5 million sites use PHP as their scripting language on the Internet (see www.php.net). As a CGI scripting language, PHP can do everything that other CGI programs can do. Also, like ASP, PHP is an HTML/XHTML embedding language so that PHP statements can be integrated within XHTML pages. Perhaps the most important reason for its success is the support of a wide range of databases. PHP has built-in functions directly supporting the databases listed in Table 18.4.
Supporting databases has become the most significant feature of PHP. For each of the database types in the table, there are built-in PHP functions dedicated to controlling and accessing them. For example, there are more than 40 functions directly related to MySQL. All MySQL-supported functions have "mysql" as a prefix. Some frequently used PHP functions for MySQL are listed in Table 18.5.
For a complete listing of the database-supported functions and others, see the documentation that comes with your PHP package or visit the official PHP site on www.php.net. With PHP, you don't need to install add-on packages. In a proper PHP installation, you can expect that everything will be ready for you to perform database actions. For example, you can use the following PHP program fragment to connect to the MySQL in www.pwt-ex.com:
Listing: ex18-03.txt - PHP Page Fragment To Connect MySQL
1: <?php
2: $host="www.pwt-ex.com";
3: $user="johnsmith";
4: $password="johnsmith";
5: mysql_connect($host,$user,$password);
6: mysql_select_db("personnel");
7: $result = mysql_query("SELECT * FROM people");
8: ?>
In this PHP fragment, we have used three MySQL-related functions:
Based on this program fragment, you can develop an SQL testing page using PHP. 18.4.2 An SQL test page using PHPA general SQL test page is important for almost any database programming on the Web. It is a fundamental tool to see whether you have a healthy database connection and have used the functions or technologies correctly. In many cases, it helps to spot errors and perform evaluations of quality with different database types. If you can read and understand a good SQL test program for a particular technology, you should be able to design database applications with the style, syntax, and layout of that particular technology. To develop an SQL test program using PHP, consider a new example ex18-50.htm which is the same as ex18-01.htm but with line 15 replaced by 15: Accessing MySQL Database(s) <br />Using PHP Built-in Functions<br /> This line indicates that you are using the PHP server page. Another line you need to change is line 30: 30: <form action = "ex1850.php" method="post"> This form element will activate another the PHP program ex18-50.php to perform the SQL query. The first part of the PHP page is listed below:
Example: ex18-50.php - The PHP Script For ex18-50.htm (Part One)
1: <?PHP echo"<?";?>xml version="1.0" encoding="iso-88591"<?PHP echo"?>";?>
2: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
3: "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4: <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
5: <style>
6: .txtSt{font-family:arial;font-weight:bold;font-size:13pt;color:#ffff00}
7: </style>
8: <body class="txtSt" style="background:#000088;font-size:18pt;
9: text-align:center" >
10: Based On Your SQL Query String:<br />
11: <div style="font-size:14pt" align="center">
12: <table><tr>
13: <td class="txtSt" style="color:#ffffff;width:500px;
14: text-align:center;font-size:14pt"><br />
15: <?php echo($querySt); ?> </br><br /></td></tr>
16: </table>
17: The Returned Results Are As Follows:<br /><br />
18: </div>
19: <img src="line1.gif" width="600"
20: height="6" alt="pic" /><br />
21: <?php
22: $host="www.pwt-ex.com";
23: $user="johnsmith";
24: $password="johnsmith";
25:
26: mysql_connect($host,$user,$password);
27: mysql_select_db("personnel");
28: $querySt = stripSlashes($querySt) ;
29: $result = mysql_query($querySt);
This PHP program fragment is easy to read. The PHP statements in lines 2124 obtain the necessary parameters for connecting to a database. The MySQL functions in lines 2629 perform connection to the database and store the results. In order to display the results on the browser window, we need the second half of the PHP program below:
Listing: Continuation Of The PHP Script ex1850.php (Part Two)
30:
31: if ($result == 0):
32: echo("SQL Query Error..");
33: elseif (mysql_num_rows($result) == 0):
34: echo("SQL Query executed successfully!<br />");
35: else :
36: echo('<table cellspacing="5" class="txtSt" align="center" width="570">');
37: echo('<tr>');
38: $num_field = mysql_num_fields($result);
39: for ($ii = 0; $ii < $num_field; $ii++)
40: {
41: echo("<td style='color:#00ff00'>".mysql_field_name($result,$ii)."</td>");
42: }
43: echo('</tr>');
44:
45: $count = mysql_num_rows($result);
46: for ($ii = 0; $ii < $count; $ii++)
47: {
48: echo("<tr>");
49: $row_array = mysql_fetch_row($result);
50: for ($jj = 0; $jj < $num_field; $jj++)
51: {
52: echo("<td>" . $row_array[$jj] . "</td>");
53: }
54: echo("</tr>");
55: }
56: echo('</table>');
57: endif
58: ?>
59:
60: <img src="line1.gif" width="600"
61: height="6" alt="pic" /><br /><br />
62: Total Entries = <?php echo($count); ?><br />
63: </body>
64: </html>
If the query result $result is zero, you will have an SQL query error. You may need to double-check your SQL query statement and/or connection to the database. If the query result is not zero and the number of returned rows is zero (lines 3334), that means the SQL query has been executed successfully but not returned anything. For example, if you insert some data into the database, the database may not return anything to you. If the database returns some rows to you, the coding in lines 3656 will generate a table to display all the rows. Since PHP has a built-in function called mysql_field_name() to control the fields, a simple for-loop with this function (see lines 3942) is used to display all the field names. A double for-loop as illustrated in lines 4655 outputs all the rows and field data to the browser window. A screen shot of this example is shown in Fig. 18.46. Figure 18.46. An SQL testing program with PHP
Now it is time to design some database applications on the Web using PHP. 18.4.3 A page to explore all MySQL databases and tablesIn this section, we are going to develop a PHP page that can walk through any MySQL installation and databases on the Internet with their associated tables. This page will display three text fields, Host, User, and Password. These fields are used to connect to the MySQL on the remote site. All the available databases will be displayed in a select box. Once you have picked a particular database and clicked the button, all the tables associated with the database will be displayed in another select box. For an application like this, we use a single page with PHP built-in functions to hide all the details from the end users. The first part of the PHP page is listed below:
Example: ex18-51.php - Exploring All MySQL Databases And Tables (Part One)
1: <?PHP echo"<?";?>xml version="1.0" encoding="iso-88591"<?PHP echo"?>";?>
2: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
3: "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4: <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
5: <head><title> Exploring All MySQL Databases - ex1851.php</title></head>
6: <style>
7: .butSt{background-color:#aaffaa;font-family:arial;font-weight:bold;
8: font-size:14pt;color:#008800;width:240px;height:30px}
9: .butSt2{background-color:#aaffaa;font-family:arial;font-weight:bold;
10: font-size:14pt;color:#008800;width:250px;height:120px}
11: </style>
12: <body style="background:#000088;text-align:center;font-family:arial;
13: font-size:16pt;color:#ffff00">
14: <div style="font-size:16pt;color:#ffff00">
15: Exploring MySQL Databases and Tables<br /><br />
16:
17: <?php
18: global $database,$host,$user,$password;
19: ?>
20:
21: <form action="ex18-51.php" method="post">
22: <table style="font-size:14pt;font-weight:bold">
23: <tr><td>Host Name: </td><td><input type="text" name="host"
24: value="<?PHP echo($host)?>" class="butSt" /></td></tr>
25: <tr><td>User Name: </td><td><input type="text" name="user"
26: value="<?PHP echo($user)?>" class="butSt" /></td></tr>
27: <tr><td>Password: </td><td><input type="password" name="password"
28: value="<?PHP echo($password)?>" class="butSt" /></td></tr>
29: </table>
30:
After the global variable declarations in line 18, this page fragment contains a form with three fields. They are used to get the user input on host name, user name, and password so that a proper connection to the specified MySQL can be made. We have used a PHP echo() function on the value of each field to capture the data whenever the program is activated. The next task of the page is to make the connection, search for all available databases, and store them in a select box. Once you have picked a particular database and clicked the Find Databases/Tables button (see Fig. 18.47), all the tables associated with the selected database will be displayed in another select box. The second part of the page is listed as follows:
Listing: Continuation Of The PHP Script ex18-51.php (Part Two)
31: <table cellspacing="20" style="font-size:12pt;font-weight:bold"><tr>
32: <td>
33: Select Your MySQL Databases Here And Click
34: The Button Below To Find All Tables<br /><br />
35: <input type="submit" value="Find Databases/Tables" class="butST"
36: style="background:#bbbbbb" /><br /><br />
37:
38: <select name="database" size="1" class="butSt" >
39: <?php
40: mysql_connect($host, $user, $password);
41: $db_table = mysql_list_dbs();
42:
43: for ($i = 0; $i < mysql_num_rows($db_table); $i++)
44: {
45: $dbs = mysql_tablename($db_table, $i);
46: if ($database == $dbs)
47: echo("<option selected>" . $dbs);
48: else
49: echo("<option>" . $dbs);
50: }
51: ?>
52: </select>
53: </td><td>
54: All The Tables Of Your Selected Database Are Here:<br /><br />
55: <select name="tables" size="4" class="butSt2" >
56: <?php
57: $db_table = mysql_list_tables($database);
58: for ($i = 0; $i < mysql_num_rows($db_table); $i++)
59: {
60: echo("<option>" . mysql_tablename($db_table, $i));
61: }
62: ?>
63: </select>
64: </td></tr>
65: </table>
66: </form>
67: </body>
68: </html>
Figure 18.47. ex18-51.php
The page fragment contains an execution button in lines 3536. Also, we have two select boxes. Inside the first box (lines 3852), some PHP functions are called to make the database connection (line 40), get the database listing (line 41), and populate the select box with the names of the databases using a for-loop. The selected keyword used in line 47 is to specify the selected database so that it will not disappear or scroll away from the select box. Once you have selected your database and clicked the Find Databases/Tables button, the statement in line 57 will get all the tables from that database and display them in another select box using a for-loop in lines 5861. A page like this is handy to locate any MySQL databases and tables anywhere on the Internet. Some screen shots are shown in Figs 18.47 and 18.48. Figure 18.48. Exploring MySQL databases and tables
|
| Table of Contents | Previous Next |
