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

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.

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.

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.