| If you verified your work in the preceding section by issuing queries through the MySQL monitor or other interface, you probably became tired of typing SELECT * FROM... for every table. In this section, you'll create the two-part script that shows you how to select and view records in your database. Listing 20.3 shows the select-and-view script called selentry.php, that has two parts: the record selection form (lines 640) and the code to display the record contents (lines 51163). Because this code is longer than the other code you've seen so far, we'll break it up into smaller chunks for discussion. Listing 20.3. Script Called selentry.php for Selecting and Viewing a Record 1: <?php 2: //connect to database 3: $mysqli = mysqli_connect("localhost", "joeuser", "somepass", "testDB"); 4: 5: if (!$_POST) { 6: //haven't seen the selection form, so show it 7: $display_block = "<h1>Select an Entry</h1>"; 8: 9: //get parts of records 10: $get_list_sql = "SELECT id, 11: CONCAT_WS(', ', l_name, f_name) AS display_name 12: FROM master_name ORDER BY l_name, f_name"; 13: $get_list_res = mysqli_query($mysqli, $get_list_sql) 14: or die(mysqli_error($mysqli)); 15: 16: if (mysqli_num_rows($get_list_res) < 1) { 17: //no records 18: $display_block .= "<p><em>Sorry, no records to select!</em></p>"; 19: 20: } else { 21: //has records, so get results and print in a form 22: $display_block .= " 23: <form method=\"post\" action=\"".$_SERVER["PHP_SELF"]."\"> 24: <p><strong>Select a Record to View:</strong><br/> 25: <select name=\"sel_id\"> 26: <option value=\"\">-- Select One --</option>"; 27: 28: while ($recs = mysqli_fetch_array($get_list_res)) { 29: $id = $recs['id']; 30: $display_name = stripslashes($recs["display_name"]); 31: $display_block .= "<option value=\"".$id."\">". 32: $display_name."</option>"; 33: } 34: 35: $display_block .= " 36: </select> 37: <p><input type=\"submit\" name=\"submit\" 38: value=\"View Selected Entry\"></p> 39: </form>"; 40: } 41: //free result 42: mysqli_free_result($get_list_res); 43: } else if ($_POST) { 44: //check for required fields 45: if ($_POST["sel_id"] == "") { 46: header("Location: selentry.php"); 47: exit; 48: } 49: 50: //get master_info 51: $get_master_sql = "SELECT concat_ws(' ',f_name,l_name) as display_name 52: FROM master_name WHERE id = '".$_POST["sel_id"]."'"; 53: $get_master_res = mysqli_query($mysqli, $get_master_sql) 54: or die(mysqli_error($mysqli)); 55: 56; while ($name_info = mysqli_fetch_array($get_master_res)) { 57: $display_name = stripslashes($name_info['display_name']); 58: } 59: 60: $display_block = "<h1>Showing Record for ".$display_name."</h1>"; 61: 62: //free result 63: mysqli_free_result($get_master_res); 64: 65: //get all addresses 66: $get_addresses_sql = "SELECT address, city, state, zipcode, type FROM 67: address WHERE master_id = '".$_POST["sel_id"]."'"; 68: $get_addresses_res = mysqli_query($mysqli, $get_addresses_sql) 69: or die(mysqli_error($mysqli)); 70: 71: if (mysqli_num_rows($get_addresses_res) > 0) { 72: $display_block .= "<p><strong>Addresses:</strong><br/> 73: <ul>"; 74: 75: while ($add_info = mysqli_fetch_array($get_addresses_res)) { 76: address = stripslashes($add_info['address']); 77: $city = stripslashes($add_info['city']); 78: $state = stripslashes($add_info['state']); 79: $zipcode = stripslashes($add_info['zipcode']); 80: $address_type = $add_info['type']; 81: 82: $display_block .= "<li>$address $city $state $zipcode 83: ($address_type)</li>"; 84: } 85: $display_block .= "</ul>"; 86: } 87: //free result 88: mysqli_free_result($get_addresses_res); 89: //get all tel 90: $get_tel_sql = "SELECT tel_number, type FROM telephone WHERE 91: master_id = '".$_POST["sel_id"]."'"; 92: $get_tel_res = mysqli_query($mysqli, $get_tel_sql) 93: or die(mysqli_error($mysqli)); 94: 95: if (mysqli_num_rows($get_tel_res) > 0) { 96: $display_block .= "<p><strong>Telephone:</strong><br/> 97: <ul>"; 98: 99: while ($tel_info = mysqli_fetch_array($get_tel_res)) { 100: $tel_number = stripslashes($tel_info['tel_number']); 101: $tel_type = $tel_info['type']; 102: 103: $display_block .= "<li>$tel_number ($tel_type)</li>"; 104: } 105: $display_block .= "</ul>"; 106: } 107: //free result 108: mysqli_free_result($get_tel_res); 109: 110: //get all fax 111: $get_fax_sql = "SELECT fax_number, type FROM fax WHERE 112: master_id = '".$_POST["sel_id"]."'"; 113: $get_fax_res = mysqli_query($mysqli, $get_fax_sql) 114: or die(mysqli_error($mysqli)); 115: 116: if (mysqli_num_rows($get_fax_res) > 0) { 117: $display_block .= "<p><strong>Fax:</strong><br/> 118: <ul>"; 119: 120: while ($fax_info = mysqli_fetch_array($get_fax_res)) { 121: $fax_number = stripslashes($fax_info['fax_number']); 122: $fax_type = $fax_info['type']; 123: 124: $display_block .= "<li>$fax_number ($fax_type)</li>"; 125: } 126: $display_block .= "</ul>"; 127: } 128: //free result 129: mysqli_free_result($get_fax_res); 130: 131: //get all email 132: $get_email_sql = "SELECT email, type FROM email WHERE 133: master_id = '".$_POST["sel_id"]."'"; 134: $get_email_res = mysqli_query($mysqli, $get_email_sql) 135: or die(mysqli_error($mysqli)); 136: if (mysqli_num_rows($get_email_res) > 0) { 137: $display_block .= "<p><strong>Email:</strong><br/> 138: <ul>"; 139: 140: while ($email_info = mysqli_fetch_array($get_email_res)) { 141: $email = stripslashes($email_info['email']); 142: $email_type = $email_info['type']; 143: 144: $display_block .= "<li>$email ($email_type)</li>"; 145: } 146: $display_block .= "</ul>"; 147: } 148: //free result 149: mysqli_free_result($get_email_res); 150: 151: //get personal note 152: $get_notes_sql = "SELECT note FROM personal_notes WHERE 153: master_id = '".$_POST["sel_id"]."'"; 154: $get_notes_res = mysqli_query($mysqli, $get_notes_sql) 155: or die(mysqli_error($mysqli)); 156: 157: if (mysqli_num_rows($get_notes_res) == 1) { 158: while ($note_info = mysqli_fetch_array($get_notes_res)) { 159: $note = nl2br(stripslashes($note_info['note'])); 160: } 161: $display_block .= "<p><strong>Personal Notes:</strong><br/> 162: $note</p>"; 163: } 164: //free result 165: mysqli_free_result($get_notes_res); 166: $display_block .= "<br/> 167: <p align=\"center\"> 168: <a href=\"".$_SERVER["PHP_SELF"]."\">select another</a></p>"; 169: } 170: //close connection to MySQL 171: mysqli_close($mysqli); 172: ?> 173: <html> 174: <head> 175: <title>My Records</title> 176: </head> 177: <body> 178: <?php echo $display_block; ?> 179: </body> 180: </html>
|
As with the addentry.php script, the selentry.php script performs one of two tasks at any given time: It either shows the selection form, or it performs all the SQL queries related to viewing the record. No matter which of the two tasks is performed, the database still comes into play. Given that, we connect to it in line 3. The logic that determines the task begins at line 5, with a test for the value of the $_POST superglobal. If $_POST has no value, the user is not coming from the selection form and therefore needs to see it. A string called $display_block is started in line 7, and this string will ultimately hold the HTML that makes up the record selection form. In lines 1012, we select specific fields from the records in the master_name table to build the selection drop-down options in the form. For this step, you need only the name and ID of the person whose record you want to select. Line 16 tests for results of the query; if the query has no results, you can't build a form. If this were the case, the value of $display_block would be filled with an error message and the script would end, printing the resulting HTML to the screen. However, let's assume that you have a few records in the master_name table. In this case, you have to extract the information from the query results to be able to build the form. This is done in lines 2833, with form elements written to the $display_block string both above and below it. We've stopped this listing at line 42, but you'll soon see lines 43 through the end of the script. If we were to close up the if statement and the PHP block and print the value of $display_block to the screen at this point, you would see a form something like that shown in Figure 20.4 (with different entries). Figure 20.4. The record selection form. 
However, we must finish the selentry.php script, so we continue Listing 20.3 at line 43, which begins the else portion of the if...else statement: Line 43 contains the else portion of the if...else statement and is invoked if the form wants to see a specific record. We first check for a required field, in line 45, in this case the value of $_POST["sel_id"]. This value matches the ID from the master_name table to that of the selection made in the record selection form. If that value does not exist, the user is redirected back to the selection formyou can't very well gather information from a set of tables when the primary key isn't present! Assuming that a value was present for $_POST["sel_id"], we issue a query in lines 5154 that obtains the name of the user whose record you want to view. This information is placed in the now-familiar $display_block string, which will continue to be built as the script continues. Lines 6685 represent the query against the address table, and the resulting display that is built. If the selected individual has no records in the address table, nothing is added to the $display_block string. However, if there are one or more entries, the addresses for this person are added to the $display_block string as one or more unordered list elements, as shown in lines 7385. Lines 89165 of Listing 20.3 perform the same type of looping and writing to the $display_block variable, but the tables are different. For instance, lines 90105 look for information in the telephone table and create an appropriate string to be added to $display_block, if any information is present. The same structure is repeated in lines 111126 for information from the fax table, lines 132146 for information from the email table, and lines 152162 for any content present in the personal_notes table. We still have to do a little housekeeping and finish up the script, as shown in the last portion of Listing 20.3. In lines 166168, we simply print a link back to the selection form before closing up the if...else statement in line 169 and the PHP block in the line following. Lines 172 through the end of the script are the generic HTML template that we use to surround the contents of the $display_block string. After selecting a record from the form shown in Figure 20.4, you will see a result like that shown in Figure 20.5your data will vary, of course. Figure 20.5. An individual's record. 
When you try this script for yourself, against your own records, you should see information only for those individuals who have additional data associated with them. For example, if you have an entry for a friend, and all you have is an email address entered in the email table, you shouldn't see any text relating to address, telephone, fax, or personal notesno associated records were entered in those tables. |
