Viewing Records


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 19.3 shows the select-and-view script called selentry.php, that has two parts: the record selection form (lines 7 through 41) and the code to display the record contents (lines 43 through 155). 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 19.3. Script Called selentry.php for Selecting and Viewing a Record
  1: <?php  2: //connect to database  3: $conn = mysql_connect("localhost", "joeuser", "somepass")  4:    or die(mysql_error());  5: mysql_select_db("testDB",$conn) or die(mysql_error());  6:  7: if ($_POST[op] != "view") {  8:    //haven't seen the selection form, so show it  9:    $display_block = "<h1>Select an Entry</h1>"; 10: 11:   //get parts of records 12:   $get_list = "select id, concat_ws(', ', l_name, f_name) as display_name 13:       from master_name order by l_name, f_name"; 14:   $get_list_res = mysql_query($get_list) or die(mysql_error()); 15: 16:   if (mysql_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 = mysql_fetch_array($get_list_res)) { 29:           $id = $recs['id']; 30:           $display_name = stripslashes($recs['display_name']); 31: 32:           $display_block .= "<option value=\"$id\"> 33:                $display_name</option>"; 34:       } 35:       $display_block .= " 36:       </select> 37:       <input type=\"hidden\" name=\"op\" value=\"view\"> 38:       <p><input type=\"submit\" name=\"submit\" 39:           value=\"View Selected Entry\"></p> 40:       </FORM>"; 41:    } 42: 

As with the addentry.php script, the selentry.php script will perform 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 will be performed, the database still comes into play. Given that, we connect to it in lines 35.

The logic that determines the task begins at line 7, with a test for the value of $_POST[op]. If the value of $_POST[op] is not "view", the user is not coming from the selection form and therefore needs to see it. A string called $display_block is started in line 9, and this string will ultimately hold the HTML that makes up the record selection form.

In lines 1214, 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 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 a form something like that in Figure 19.4 (with different entries).

Figure 19.4. The record selection form.


However, we must finish the selentry.php script, so we continue Listing 19.3 at line 43, which begins the else portion of the if...else statement:

Listing 19.3.
 43:} else if ($_POST[op] == "view") { 44: 45:    //check for required fields 46:     if ($_POST[sel_id] == "") { 47:        header("Location: selentry.php"); 48:        exit; 49:    } 50: 51:   //get master_info 52:   $get_master = "select concat_ws(' ', f_name, l_name) as display_name 53:        from master_name where id = $_POST[sel_id]"; 54:   $get_master_res = mysql_query($get_master); 55:   $display_name = stripslashes(mysql_result($get_master_res, 56:        0,'display_name')); 57:   $display_block = "<h1>Showing Record for $display_name</h1>"; 58:   //get all addresses 59:   $get_addresses = "select address, city, state, zipcode, type 60:        from address where master_id = $_POST[sel_id]"; 61:   $get_addresses_res = mysql_query($get_addresses); 62: 63:   if (mysql_num_rows($get_addresses_res) > 0) { 64: 65:       $display_block .= "<P><strong>Addresses:</strong><br> 66:       <ul>"; 67: 68:       while ($add_info = mysql_fetch_array($get_addresses_res)) { 69:           $address = $add_info[address]; 70:           $city = $add_info[city]; 71:           $state = $add_info[state]; 72:           $zipcode = $add_info[zipcode]; 73:           $address_type = $add_info[type]; 74: 75:           $display_block .= "<li>$address $city $state $zipcode 76:               ($address_type)"; 77:       } 78: 79:       $display_block .= "</ul>"; 80:   } 81: 

Line 43 contains the else portion of the if...else statement, and is invoked if the value of $_POST[op] is "view", meaning the user has submitted the form and wants to see a specific record. We first check for a required field, in line 46, 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 a value was present for $_POST[sel_id], we issue a query in lines 5255 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 5980 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 6579.

Lines 82 through 152 of Listing 19.3 performs the same type of looping and writing to the $display_block variable, but the tables are different. For instance, lines 82 through 100 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 102 through 120 for information from the fax table, lines 122 through 140 for information from the email table, and lines 142 through 152 for any content present in the personal_notes table.

Listing 19.3.
  82:   //get all tel  83:   $get_tel = "select tel_number, type from telephone where  84:        master_id = $_POST[sel_id]";  85:   $get_tel_res = mysql_query($get_tel);  86:  87:   if (mysql_num_rows($get_tel_res) > 0) {  88:  89:       $display_block .= "<P><strong>Telephone:</strong><br>  90:       <ul>";  91:  92:       while ($tel_info = mysql_fetch_array($get_tel_res)) {  93:           $tel_number = $tel_info[tel_number];  94:           $tel_type = $tel_info[type];  95:  96:           $display_block .= "<li>$tel_number ($tel_type)";  97:       }  98:  99:       $display_block .= "</ul>"; 100:   } 101: 102:   //get all fax 103:   $get_fax = "select fax_number, type from fax where 104:        master_id = $_POST[sel_id]"; 105:   $get_fax_res = mysql_query($get_fax); 106: 107:   if (mysql_num_rows($get_fax_res) > 0) { 108: 109:       $display_block .= "<P><strong>Fax:</strong><br> 110:       <ul>"; 111: 112:       while ($fax_info = mysql_fetch_array($get_fax_res)) { 113:           $fax_number = $fax_info[fax_number]; 114:           $fax_type = $fax_info[type]; 115: 116:           $display_block .= "<li>$fax_number ($fax_type)"; 117:       } 118: 119:       $display_block .= "</ul>"; 120:   } 121: 122:   //get all email 123:   $get_email = "select email, type from email where 124:        master_id = $_POST[sel_id]"; 125:   $get_email_res = mysql_query($get_email); 126: 127:   if (mysql_num_rows($get_email_res) > 0) { 128: 129:       $display_block .= "<P><strong>Email:</strong><br> 130:       <ul>"; 131: 132:       while ($email_info = mysql_fetch_array($get_email_res)) { 133:           $email = $email_info[email]; 134:           $email_type = $email_info[type]; 135: 136:           $display_block .= "<li>$email ($email_type)"; 137:       } 138: 139:       $display_block .= "</ul>"; 140:   } 141: 142:   //get personal note 143:   $get_notes = "select note from personal_notes where 144:        master_id = $_POST[sel_id]"; 145:   $get_notes_res = mysql_query($get_notes); 146: 147:   if (mysql_num_rows($get_notes_res) == 1) { 148:       $note = nl2br(stripslashes(mysql_result($get_notes_res,0,'note'))); 149: 150:       $display_block .= "<P><strong>Personal Notes:</strong><br>$note"; 151:   } 152: 

We still have to do a little housekeeping and finish up the script, as shown in the last portion of Listing 19.3:

Listing 19.3.
 153:   $display_block .= "<br><br><P align=center> 154:       <a href=\"$_SERVER[PHP_SELF]\">select another</a></p>"; 155: } 156: ?> 157: <HTML> 158: <HEAD> 159: <TITLE>My Records</TITLE> 160: </HEAD> 161: <BODY> 162: <?php echo $display_block; ?> 163: </BODY> 164: </HTML> 

In lines 153154, we simply print a link back to the selection form before closing up the if...else statement in line 155 and the PHP block in the line following. Lines 157 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 19.4, you will see a result like that shown in Figure 19.5your data will vary, of course.

Figure 19.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.



Sams Teach Yourself PHP MySQL and Apache All in One
Sams Teach Yourself PHP, MySQL and Apache All in One (4th Edition)
ISBN: 067232976X
EAN: 2147483647
Year: 2003
Pages: 333
Authors: Julie Meloni

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net