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.

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 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: 
 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: 
 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: 
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: <? print $display_block; ?>
163: </BODY>
164: </HTML>

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 3?5.

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 form and therefore needs to see the selection form. A string called $display_block is started in line 9, and this string will be added to throughout this task. We hope that it will ultimately hold a selection form.

In lines 12?14, we select part of the master_name records to build the selection option 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. In this 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, 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 28?33, with form elements written to the $display_block string both above and below it. The script then breaks out of the if...else construct and jumps down to line 110, which outputs the HTML and prints the value of $display_block, in this case the form. This outcome is shown in Figure 19.4.

Line 43 begins the second condition if the value of $_POST[op] is "view", meaning the user has submitted the form and wants to see a specific record. The required field in this section of the script is $_POST[sel_id], holding the ID from the master_name table of the user selected in the form. If that value does not exist, the user is redirected to the selection form. In lines 52?55, a query 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 59?80 represent the query against the address table. 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, they are placed in $display_block as unordered list elements, as shown in lines 65?79.

The same principle is followed for records in the telephone (lines 83?100), fax (lines 103?120), and email (lines 123?140) tables. If there are one or more entries, place the results in $display_block. Otherwise, the script moves on. Because there can be only one entry per individual in the personal_notes table, the script checks for the entry beginning in line 143, and moves on if it doesn't exist. If a note exists, it's written in $display_block in lines 147?151.

The final action in this part of the script is to print a link in lines 153?154, in case the user wants to return to the selection screen. After this point, the script exits from the if...else construct and prints the HTML to the screen. Figure 19.5 shows a record from the record selection script, with one entry in each table.

Try this script yourself. You should see data only for individuals who have data associated with them. For example, if you have an entry for a friend, and all you have is an email address for that person, you shouldn't see any text relating to address, telephone, fax, or personal notes.