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.

Figure 19.4. The record selection form.

graphics/19fig04.gif

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.

Figure 19.5. An individual's record.

graphics/19fig05.gif

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.



Sams Teach Yourself PHP, MySQL and Apache in 24 Hours
Sams Teach Yourself PHP, MySQL and Apache in 24 Hours
ISBN: 067232489X
EAN: 2147483647
Year: 2005
Pages: 263

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