| Table of Contents | Previous Next |
18.5 Designing database applications with PHP (II)18.5.1 Deleting records from a tableFrom the SQL discussion earlier in section 18.2, you learned that the following SQL statement can be used to remove a record from a table: DELETE FROM ... WHERE ... To integrate this statement into a Web page, let's develop a PHP page to delete records from a MySQL table. This page will display all records and each record has an underlined text DELETE attached to it. To delete a record, all you have to do is to click on the underlined text. The new record will be updated automatically. The coding of the page is listed below:
Example: ex18-52.php - Deleting Records From A Table
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> Deleting Records From A Table - ex1852.php</title></head>
6: <body style="background:#000088;text-align:center;font-family:arial;
7: font-size:14pt;color:#ffff00">
8: <div style="font-size:16pt;color:#ffff00;font-weight:bold">
9: Deleting Records From A Table<br /><br /></div>
10: Click the DELETE keyword to delete a record<br/><br />
11: <img src="line1.gif" width="600"
12: height="6" alt="pic" /><br /><br />
13: <?php
14: $db = mysql_connect("www.pwt-ex.com", "johnsmith","johnsmith");
15: mysql_select_db("personnel",$db);
16:
17: if ($delete =="yes")
18: {
19: $sql = "DELETE FROM people WHERE id=$id";
20: $result = mysql_query($sql);
21: } else {
22: $delete = no;
23: }
24: $result = mysql_query("SELECT * FROM people",$db);
25: while ($myrow = mysql_fetch_array($result))
26: {
27: printf("%s, %s, %s, %s, %s --- ",$myrow["name"], $myrow["sex"],
28: $myrow["location"], $myrow["birth"], $myrow["salary"]);
29:
30: printf("<a href=\"%s?id=%s&delete=yes\" style=\"color:#00ff00\">
31: (DELETE)</a><br />",$PHP_SELF, $myrow["id"]);
32: }
33: ?><br /><br />
34: <img src="line1.gif" width="600"
35: height="6" alt="pic" /><br /><br />
36: </body>
37: </html>
The first two statements (lines 1415) inside the PHP scripting block are used to connect to MySQL and select test as the active database. If the DELETE underlined text is pressed, the statements in lines 1920 execute the SQL delete statement to remove the record according to its id. After the deletion, there is a while-loop in lines 2532 to display the updated records. The print functions printf() used in the page are C/C++ style. Then can print out a string according to the format setting. The first printf() in lines 2728 will print out five strings and each %s will be substituted by the corresponding string variable. For example, the first record will be printed out like this: Jenny, F, New York, 19721027, 27000 --- The second print function printf() will output the following string attached to the end of the text above:
<a href="$PHP_SELF?id=01&delete=yes"
style=\"color:#00ff00\">(DELETE)</a><br />
This is an anchor element with an underlined text (DELETE). If this text is clicked on, the same page is called with query string id=01&delete=yes. The id parameter is used to identify the record to be deleted. A screen shot of this example is shown in Fig. 18.49. Figure 18.49. ex18-52.php
18.5.2 A page to insert and update recordsNo matter what kind of database application you are designing, in addition to deletion, inserting and updating a record are also the most basic techniques you may need. As a demonstration, we are going to develop a PHP page which can perform delete, insert, and update operations on a table called test inside the company database. The test table is a copy of the table people so that it contains all the fields of people. The page will display all records in a select box so that it can hold a reasonably large number of records without destroying the view of the browser window. When one of the records is clicked twice (double click), the data of the record will be displayed in a series of text boxes so that manipulation can be made. We have three buttons to control the captured data:
The basic skills behind this page are the following two SQL statements:
UPDATE test SET name='$name',sex='$sex',location='$location',
birth='$birth', salary='$salary' WHERE id=$id;
INSERT INTO test (id,name,sex,location,birth,salary)
VALUES (NULL,'$name','$sex','$location','$birth','$salary');
Once you have the data $name, $sex, $location, $birth, and $salary specified, these two statements can be used to manipulate the records of the table. To hide the SQL statements from the users, you need to write a PHP page, the first part of which is listed as follows:
Example: ex18-53.php - A Page To Insert And Update Records (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>Manipulating Records - ex1853.php</title></head>
6: <style>
7: .txtSt{font-family:arial;font-weight:bold;font-size:14pt;color:#ffff00}
8: .butSt2{background-color:#aaffaa;font-family:arial;font-weight:bold;
9: font-size:14pt;color:#008800;width:550px}
10: .butSt{background-color:#aaffaa;font-family:arial;font-weight:bold;
11: font-size:14pt;color:#008800;width:150px}
12: </style>
13: <body style="background:#000088;text-align:center;font-family:arial;
14: font-size:14pt;color:#ffff00">
15: <div style="font-size:16pt;color:#ffff00;font-weight:bold">
16: Manipulating Database Records<br />
17: Select The Item By Double Clicking The Record Below <br /><br /></div>
18: <img src="line1.gif" width="600"
19: height="6" alt="pic" /><br /><br />
20: <?php
21: $db = mysql_connect("www.pwt-ex.com", "johnsmith","johnsmith");
22: mysql_select_db("personnel",$db);
23:
24: global $id,$delete,$update,$insert,$sel_id,
25: $name,$sex,$location,$birth,$salary;
26:
27: if ($sel_id)
28: {
29: $id = $sel_id;
30: $sel_id = false;
31: }
32:
33: if ($update)
34: {
35: $sql = "UPDATE people SET name='$name',sex='$sex',
36: location='$location',birth='$birth', salary='$salary' WHERE id=$id";
37: $result = mysql_query($sql);
38: $update = false;
39: }
40: if ($insert)
41: {
42: $sql = "INSERT INTO people (id,name,sex,location,birth,salary)
43: VALUES (NULL,'$name','$sex','$location','$birth','$salary')";
44: $result = mysql_query($sql);
45: $insert = false;
46: }
47:
48: if ($delete)
49: {
50: $sql = "DELETE FROM people WHERE id=$id";
51: $result = mysql_query($sql);
52: $delete = false;
53: }
This PHP page fragment is mainly for controlling the SQL operations. Lines 2122 are used to connect to the MySQL and select the database company. To control the SQL operations, we have used a series of if statements in this example. The first if statement (lines 2731) means the user has double clicked one of the records inside a select box. If one of the records is selected, the id field of the record is passed to the variable $id in line 29. This $id variable is used to extract the record data from the table and display them in a series of text boxes for editing. When the Update button is pressed, the if statement in lines 3339 is activated. In this case, the SQL update statement is executed by the statement in lines 3536 and consequently updates the record inside the table. When the Insert button is clicked, the SQL insert statement defined in line 42 is executed to insert the record at the end of the table. If the Delete button is pressed, the statements in lines 4953 are executed to delete the record from the table. When all the buttons are defined as illustrated in this page fragment, you need to display all records of the table in a select box waiting for a double click. The PHP coding for this task is listed in the page fragment below:
Listing: Continuation Of The PHP Script ex18-53.php (Part Two)
54:
55: $result = mysql_query("SELECT * FROM people ORDER BY id",$db);
56:
57: echo("<form name=\"myForm\" method=\"post\" action=\"$PHP_SELF\">
58: <select class=\"butSt2\" size=\"10\" name=\"sel_id\"
59: ondblclick=\"document.myForm.submit()\" >");
60:
61: while ($myrow = mysql_fetch_array($result))
62: {
63: if ($myrow["id"] == $id)
64: {
65: printf("<option selected value=\"%s\" />%s, %s, %s, %s, %s <br />",
66: $myrow["id"], $myrow["name"], $myrow["sex"], $myrow["location"],
67: $myrow["birth"], $myrow["salary"]);
68: } else {
69: printf("<option value=\"%s\" />%s, %s, %s, %s, %s <br />",
70: $myrow["id"], $myrow["name"], $myrow["sex"], $myrow["location"],
71: $myrow["birth"], $myrow["salary"]);
72: }
73: }
74: echo("</select></form>");
75: ?>
76: <img src="line1.gif" width="600"
77: height="6" alt="pic" /><br /><br />
To display all records of the table test in a select box, you may need to issue an SQL statement to get all the records as in line 55. Then a form and a select box are generated in lines 5774. We have used the variable $PHP_SELF in the form action so that this example will call itself when the form is submitted. Inside the select box, the following double click handler is used: ondblclick="document.myForm.submit()" If any item of the select box is double clicked, the form will be submitted. To populate the select box, we have used a while-loop on the rows ($myrow) of the table. Each row is displayed as an XHTML option element. When the row identity equals the selected or double-clicked record, the keyword selected is added to the option statement (line 65) so that the double-clicked record is kept active in the select box. When all records of the table have been generated, the statement in line 74 terminates the box and the form. A graphical line (lines 7677) is then generated to separate the browser window. If one of the records of the select box is double clicked, the record is extracted in a series of text boxes so that editing can be easily done. To demonstrate this part, the following page fragment is constructed:
Listing: Continuation Of The PHP Script ex1853.php (Part Three)
78: <form method="post" action="<?php echo $PHP_SELF?>">
79: <?php
80: if ($id)
81: {
82: $sql = "SELECT * FROM people WHERE id=$id";
83: $result = mysql_query($sql);
84: $myrow = mysql_fetch_array($result);
85: $name = $myrow["name"];
86: $sex = $myrow["sex"];
87: $location = $myrow["location"];
88: $birth = $myrow["birth"];
89: $salary = $myrow["salary"];
90:
91: }
92: ?>
93: <input type=hidden name="id" value="<?php echo $id ?>">
94: <table class="txtSt" cellspacing="10">
95: <tr><td>Name:</td><td><input class="butSt" type="Text"
96: name="name" value="<?php echo $name ?>"> </td>
97: <td>Sex:</td><td><input class="butSt" type="Text"
98: name="sex" value="<?php echo $sex ?>"></td></tr>
99: <tr><td>Location:</td><td><input class="butSt" type="Text"
100: name="location" value="<?php echo $location ?>"> </td>
101: <td>Birth:</td><td><input class="butSt" type="Text"
102: name="birth" value="<?php echo $birth ?>"></td></tr>
103: <tr><td>Salary:</td><td><input class="butSt" type="Text"
104: name="salary" value="<?php echo $salary ?>"></td>
105: <td>Current ID :</td><td><?php echo $id ?></td><tr>
106: </table>
107: <input class="butSt" type="submit" name="update" value="Update">
108:
109: <input class="butSt" type="submit" name="insert "value="Insert">
110:
111: <input class="butSt" type="submit" name="delete" value="Delete">
112: </form>
113: </body>
114: </html>
If one of the records is selected by a double click, the id field is stored in the variable $id. This data can be used to execute the SQL statement to extract the row from the table (line 82): SELECT * FROM test WHERE id=$id; The field data of this row are then stored in variables $name, $sex, $location, $birth, and $salary. When these variables are defined, an XHTML table (lines 94106) is constructed to display them in a series of text boxes. Since text boxes are editable, modifications can be made easily. Finally, three buttons are defined in lines 107111, namely, Update, Insert, and Delete buttons with submit type. Each button has a name so that the corresponding variable can be recognized when pressed. For example, when the Update button is pressed, the form is submitted to the page with variable $update defined. In this case, the SQL statements in lines 3537 update the record. Some screen shots of this example are shown in Figs 18.50 and 18.51. Figure 18.50. ex18-53.php
Figure 18.51. Insert and update records
18.5.3 A page to walk through records with navigation buttonsAnother interesting PHP function related to MySQL is the data seek function msql_data_seek(). This function has the following general calling format: int msql_data_seek (int query_identifier, int row_number) This function moves the internal row pointer of the query_identifier to point to the specified row number. Therefore, the next call to any fetch function such as msql_fetch_row() would return that row. Even in this simple form, the function can be called and used to fetch the rows in reverse order. Consider the following simple example:
Example: ex18-54.php - A Page To Fetch The Rows In Reverse Order
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>Fetch In Reverse Order - ex1854.php</title></head>
6: <body style="background:#000088;text-align:center;font-family:arial;
7: font-size:14pt;color:#ffff00">
8: <div style="font-size:16pt;color:#ffff00;font-weight:bold">
9: Displaying Database Records<br />
10: In Reverse Order Using PHP Functions<br /><br /></div>
11: <img src="line1.gif" width="600"
12: height="6" alt="pic" /><br /><br />
13: <?php
14: $db = mysql_connect("www.pwt-ex.com", "johnsmith","johnsmith");
15: mysql_select_db("personnel",$db);
16: $query = "SELECT * FROM people";
17: $result = mysql_query ($query)
18: or die ("SQL Query Error..");
19:
20: for ($ii = mysql_num_rows ($result) - 1; $ii >=0; $ii--)
21: {
22: if (!mysql_data_seek ($result, $ii))
23: {
24: echo "Error on Seeking Row $ii <br />";
25: continue;
26: }
27:
28: if(!($row = mysql_fetch_array ($result)))
29: continue;
30:
31: printf("%s, %s, %s, %s, %s <br />",
32: $row['name'], $row['sex'], $row['location'],
33: $row['birth'], $row['salary']);
34: }
35:
36: mysql_free_result ($result);
37: ?>
38:
39: <br /><img src="line1.gif" width="600"
40: height="6" alt="pic" /><br /><br />
41: </body>
42: </html>
This example displays all records in reverse order. The for-loop in lines 2034 begins from the last row of the table and the function: mysql_data_seek ($result, $ii) moves the internal pointer to the row represented by $ii. The fetch function mysql_fetch_array() in line 28 obtains the data of that row, which are displayed by the print function in lines 3133. As a practical example, let's consider a page that can display the database records one by one using navigation buttons. This technique is a popular choice for many online recruitment agencies or dating agencies to display their database records to clients. Usually, details of the record with a photo are displayed on the browser window one by one and controlled by a Next or Previous button (navigation buttons). For obvious reasons, our implementation can only be a simple one; no fancy searching interface is constructed. A simple SQL query is employed on a table called people. The records are displayed with navigation buttons. The details of the staff including photos are displayed on the browser window. Only the photos of the first four people are provided. You can use the Next or Previous buttons to walk through all returned records. Some screen shots of this example are shown in Figs 18.52 and 18.53. Figure 18.52. ex18-55.htm
Figure 18.53. Viewing records with navigation buttons
As you can see from Fig. 18.52, the interface part is a simple XHTML page. The page is listed as follows:
Example: ex18-55.htm - Walk Through Records With Navigation Buttons
1: <?xml version="1.0" encoding="iso-88591"?>
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>Using Navigation Buttons - ex1855.htm</title></head>
6: <style>
7: .txt{font-family:arial;font-size:14pt;color:#000088;font-weight:bold}
8: .butSt{background-color:#aaffaa;font-family:arial;font-weight:bold;
9: font-size:14pt;color:#008800;width:520px;height:30px}
10: .textareaSt{background-color:#aaffaa;font-family:arial;font-weight:bold;
11: font-size:14pt;color:#008800;width:580px;height:230px}
12: </style>
13: <body style="font-size:18pt;background:#bbbbff;
14: text-align:center" class="txt"><br />
15: A Page To Walk Through Returned Records<br />
16: On A MySQL Database Table: people <br />
17:
18: <form action = "ex18-55.php" method="post">
19: Enter Your SQL Query String<br /><br />
20: <textarea rows="8" cols="30" name = "querySt" class="textareaSt">
21: SELECT * FROM people</textarea><br /><br />
22: <input type = "submit" value = "Send Query" class="butSt"
23: style="width:180px;background:#bbbbbb">
24: </form>
25: </body>
26: </html>
The first part of this PHP page is to design some navigation functionalities to be used by some navigation buttons. To start, the page will display the first record (record 0). When you press the Next button, the current record will be incremented by 1. Similarly, the current record is decreased by 1 if you press the Previous button. If you press the New Query button, the PHP page will redirect you to the XHTML page ex18-55.htm so that a new query can be entered. The first part of this PHP program is listed below:
Example: ex18-55.php - The PHP Script For ex18-55.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: <head><title>Using Navigation Buttons - ex1855.php</title></head>
6: <style>
7: .txtSt{font-family:arial;font-size:14pt;color:#ffff00;font-weight:bold}
8: .butSt{background-color:#aaffaa;font-family:arial;font-weight:bold;
9: font-size:14pt;color:#008800;width:180px;height:30px}
10: </style>
11: <body style="background:#000088;text-align:center;font-family:arial;
12: font-size:14pt;color:#ffff00;font-weight:bold">
13: <div style="font-size:16pt;color:#ffff00;font-weight:bold">
14: Displaying Database Records<br />
15: One By One Using Navigation Buttons<br /><br /></div>
16: <img src="line1.gif" width="600"
17: height="6" alt="pic" /><br /><br />
18:
19: <form method="post" action="<?php echo $PHP_SELF?>">
20: <?php
21: global $query,$querySt,$next,$previous,$currentRow,$endRow,$result;
22: if ($querySt)
23: {
24: $currentRow = 0;
25: $query = $querySt;
26: $querySt = false;
27: }
28: if ($next)
29: {
30: $currentRow++;
31: if ($currentRow > ($endRow-1)) $currentRow = 0;
32: $next = false;
33: }
34: if ($previous)
35: {
36: $currentRow--;
37: if ( $currentRow < 0)
38: $currentRow = $endRow -1;
39: $previous = false;
40: }
41:
This page contains three if statements. When an SQL statement is submitted to this page, the variable $querySt has the true value (line 22). In this case, we set the current row $currentRow equals to 0 and assign the query statement to PHP variable $query. If you press the Next button, the if statement in lines 2833 is executed. The current row is incremented by 1 to reflect the current row situation. If the current row is beyond the last row, the first record (i.e., record 0) is set. Similarly, if the Previous button is clicked, the current row is decreased by 1. If the row number is a negative value, the end row is assigned in line 38. The second part of the PHP program is responsible for obtaining the detailed data of the current row. Based on the discussion in example ex18-54.php, the coding is listed below:
Listing: Continuation Of The PHP Script ex18-55.php (Part Two)
42: $db = mysql_connect("www.pwt-ex.com", "johnsmith","johnsmith");
43: mysql_select_db("personnel",$db);
44: $result = mysql_query ($query)
45: or die ("SQL Query Error..");
46: $endRow = mysql_num_rows ($result);
47:
48: echo("Total Number Of Rows = $endRow ----
49: Current Row = $currentRow<br />");
50:
51: if (!mysql_data_seek ($result, $currentRow))
52: {
53: echo "Error on Seeking Row $currentRow <br />";
54: }
55:
56: $myrow = mysql_fetch_array($result);
57: $name = $myrow["name"];
58: $sex = $myrow["sex"];
59: $location = $myrow["location"];
60: $birth = $myrow["birth"];
61: $salary = $myrow["salary"];
62: mysql_free_result ($result);
63: $photo = $name . ".jpg";
64: ?>
After connecting to the MySQL database and execution of the query in lines 4245, the total number of rows returned by the query is captured in the variable $endRow. This value together with the current row is displayed by the echo statement in lines 4849. In order to obtain the data of the current row, the function mysql_data_seek ($result, $currentRow) is used to move the internal pointer to the row represented by $currentRow. The mysql_fetch_array() function in line 56 obtains the data of the current row. The remaining coding of this page fragment is to store the data into variables:
Now we have everything necessary for us to develop the final part of the page to display and navigate the records. The coding of this page fragment is listed as follows: Listing: Continuation Of The PHP Script ex18-55.php (Part Three) 65: <form method="post" action="<?php echo $PHP_SELF?>"> 66: <input type=hidden name="query" value="<?php echo $query ?>"> 67: <input type=hidden name="currentRow" value="<?php echo $currentRow ?>"> 68: <input type=hidden name="endRow" value="<?php echo $endRow ?>"> <br /> 69: <table class="txtSt" cellspacing="5" border="1"> 70: <tr><td>Name:</td> 71: <td><input class="butSt" type="Text" readonly 72: name="name" value="<?php echo $name ?>"> </td> 73: <td rowspan="4"><img src="<?php echo($photo);?>" alt="pic" 74: width="150" height="200" /></td></tr> 75: <tr><td>Sex:</td> 76: <td><input class="butSt" type="Text" readonly 77: name="sex" value="<?php echo $sex ?>"></td></tr> 78: <tr><td>Location:</td> 79: <td><input class="butSt" type="Text" readonly 80: name="location" value="<?php echo $location ?>"> </td></tr> 81: <tr><td>Birth:</td> 82: <td><input class="butSt" type="Text" readonly 83: name="birth" value="<?php echo $birth ?>"></td></tr> 84: <tr><td>Salary:</td> 85: <td><input class="butSt" type="Text" readonly 86: name="salary" value="<?php echo $salary ?>"></td> 87: <td>Photo: <?php echo($photo);?></td></tr> 88: </table><br /> 89: <input class="butSt" type="submit" name="next" value="Next"> 90: <input class="butSt" type="submit" name="previous" value="Previous"> 91: 92: <input class="butSt" type="button" name="new_query" value="New Query" 93: onclick="document.location='ex18_259655.htm'"> 94: </form> 95: </body> 96: </html> Since this PHP page can call itself, we need to generate some hidden input elements as in lines 6668 to pass the $query, $endRow, and $currentRow parameters to the page. To display the detailed row information, a table with five rows is used in lines 6988. The name, sex, location, birth, and salary information are displayed here. Along with the name field, an image element is used in lines 7374 to display the photo. The size of the photo will cover four table rows. Finally, three buttons are declared in lines 8993. Each button is defined as submit type with a name. This name can be used to activate the corresponding PHP variable to perform actions. For example, if you press the Next button declared in line 89, the variable $next will have the true value and then the statements in lines 2833 in ex18-55.php will be executed. The Next and Previous buttons are known as the navigation buttons in databases. They allow you to walk through records. |
| Table of Contents | Previous Next |
