Creating an Online Address Book

In this hands-on lesson, your project will be to create a manageable, online address book. You will learn the methods for creating the relevant database tables, as well as the forms and scripts for adding, deleting, and viewing database records.

In this chapter, you will learn

How to create relational tables for an online address book

How to create the forms and scripts for adding and deleting records in the address book

How to create the forms and scripts for viewing records

ارایه درگاه پرداخت برای دریافت وجه خرید اینترنتی از خریداران محصولات سایت

خرید بسته آموزش برنامه نویسی سایت در آدرس امکان پذیر می باشد.

تبلیغات اینترنتی خدمات و محصولاتتان را میتوانید در سایت نیازمندی درج آگهی کنید.

Planning and Creating the Database Tables
When you think of an address book, the obvious fields come to mind: name, address, telephone number, email address. However, if you look at your own paper-based address book, you may note that you have several entries for one person. Maybe that person has three telephone numbers, or two email addresses, and so forth. In your online address book, a set of related tables will help alleviate the redundancy and repetition of information.

Table 20.1 shows sample table and field names to use for your online address book. In a minute, you’ll use actual SQL statements to create the tables, but first you should look at this information and try to see the relationships appear. Ask yourself which of the fields should be primary or unique keys.

Table 20.1. Address Book Table and Field Names Table Name
Field Names

id, date_added, date_modified, f_name, l_name

id, master_id, date_added, date_modified, address, city, state, zipcode, type

id, master_id, date_added, date_modified, tel_number, type

id, master_id, date_added, date_modified, fax_number, type

id, master_id, date_added, date_modified, email, type

id, master_id, date_added, date_modified, note

Notice the use of date-related fields; each table has a date_added and date_modified field in it. The fields will help maintain your data; you may at some point want to issue a query that removes all records that are older than a certain number of months or years, or that removes all records that haven’t been updated within a certain period of time.

As you can see in the following SQL statements, the master_name table has two fields besides the ID and date-related fields: f_name and l_name, for first name and last name. The id field is the primary key. No other keys need to be primary or unique, unless you really want to limit your address book to one John Smith, one Mary Jones, and so forth.

By the Way

The field lengths for the text fields in the following statements are arbitrary; you can make them as long or as short as you want, within the allowable definition of the field type.

The following SQL statement creates the master_name table:

mysql> CREATE TABLE master_name (
-> date_added DATETIME,
-> date_modified DATETIME,
-> f_name VARCHAR (75),
-> l_name VARCHAR (75)
-> );
Query OK, 0 rows affected (0.01 sec)

Next, you’ll create the supplementary tables, which all relate back to the master_name table. For instance, the address table has the basic primary key id field and the date_added and date_modified fields, plus the field through which the relationship will be madethe master_id field.

The master_id will be equal to the id field in the master_name table, matching the person whose address this is. The master_id field is not a unique key because it is a perfectly valid assumption that one person may have several address entries. We see this in the type field, which is defined as an enumerated list containing three options: home, work, or other. A person may have one or more of all three types, so no other keys are present in this table besides the primary key id. Assuming that this particular address book contains only United States addresses, we round out the table with address, city, state, and zipcode fields:

mysql> CREATE TABLE address (
-> master_id INT NOT NULL,
-> date_added DATETIME,
-> date_modified DATETIME,
-> address VARCHAR (255),
-> city VARCHAR (30),
-> state CHAR (2),
-> zipcode VARCHAR (10),
-> type ENUM (‘home’, ‘work’, ‘other’)
-> );
Query OK, 0 rows affected (0.01 sec)

The telephone, fax, and email tables are all variations on the same theme:

mysql> CREATE TABLE telephone (
-> master_id INT NOT NULL,
-> date_added DATETIME,
-> date_modified DATETIME,
-> tel_number VARCHAR (25),
-> type ENUM (‘home’, ‘work’, ‘other’)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE fax (
-> master_id INT NOT NULL,
-> date_added DATETIME,
-> date_modified DATETIME,
-> fax_number VARCHAR (25),
-> type ENUM (‘home’, ‘work’, ‘other’)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE email (
-> master_id INT NOT NULL,
-> date_added DATETIME,
-> date_modified DATETIME,
-> email VARCHAR (150),
-> type ENUM (‘home’, ‘work’, ‘other’)
-> );
Query OK, 0 rows affected (0.00 sec)

The personal_notes table also follows the same sort of pattern, except that master_id is a unique key and allows only one notes record per person:

mysql> CREATE TABLE personal_notes (
-> master_id INT NOT NULL UNIQUE,
-> date_added DATETIME,
-> date_modified DATETIME,
-> note TEXT
-> );
Query OK, 0 rows affected (0.00 sec)

Now that your tables are created, you can work through the forms and scripts for managing and viewing your records.

Creating a Menu
Your online address book will contain several actions, so it makes sense to create a menu for your links. Listing 20.1 creates a simple menu for all the scripts you will create in this chapter, called mymenu.html.

Listing 20.1. Address Book Menu
1: <html>
2: <head>
3: <title>My Address Book</title>
4: </head>
5: <body>
6: <h1>My Address Book</h1>
8: <p><strong>Management</strong></p>
9: <ul>
10: <li><a href=”addentry.php”>Add an Entry</a></li>
11: <li><a href=”delentry.php”>Delete an Entry</a></li>
12: </ul>
14: <p><strong>Viewing</strong></p>
15: <ul>
16: <li><a href=”selentry.php”>Select a Record</a></li>
17: </ul>
18: </body>
19: </html>

Creating the Record Addition Mechanism
Just because you’ll potentially be adding information to six different tables doesn’t mean your form or script will be monstrous. In fact, your scripts won’t look much different from any of the ones you created in previous lessons, and with practice, you will be able to make these verbose scripts much more streamlined and efficient.

In Listing 20.2, you can see a basic record addition script, called addentry.php, that has two parts: what to do if the form should be displayed (lines 246) and what actions to take if the form is being submitted (lines 48133). Lines 246 simply place the contents of the HTML form into a string called $display_block.

Listing 20.2. Basic Record Addition Script Called addentry.php
1: <?php
2: if (!$_POST) {
3: //haven’t seen the form, so show it
4: $display_block = ”
5: <form method=\”post\” action=\””.$_SERVER[“PHP_SELF”].”\”>
6: <p><strong>First/Last Names:</strong><br/>
7: <input type=\”text\” name=\”f_name\” size=\”30\” maxlength=\”75\”>
8: <input type=\”text\” name=\”l_name\” size=\”30\” maxlength=\”75\”></p>
10: <p><strong>Address:</strong><br/>
11: <input type=\”text\” name=\”address\” size=\”30\”></p>
13: <p><strong>City/State/Zip:</strong><br/>
14: <input type=\”text\” name=\”city\” size=\”30\” maxlength=\”50\”>
15: <input type=\”text\” name=\”state\” size=\”5\” maxlength=\”2\”>
16: <input type=\”text\” name=\”zipcode\” size=\”10\” maxlength=\”10\”></p>
18: <p><strong>Address Type:</strong><br/>
19: <input type=\”radio\” name=\”add_type\” value=\”home\” checked> home
20: <input type=\”radio\” name=\”add_type\” value=\”work\”> work
21: <input type=\”radio\” name=\”add_type\” value=\”other\”> other</p>
23: <p><strong>Telephone Number:</strong><br/>
24: <input type=\”text\” name=\”tel_number\” size=\”30\” maxlength=\”25\”>
25: <input type=\”radio\” name=\”tel_type\” value=\”home\” checked> home
26: <input type=\”radio\” name=\”tel_type\” value=\”work\”> work
27: <input type=\”radio\” name=\”tel_type\” value=\”other\”> other</p>
29: <p><strong>Fax Number:</strong><br/>
30: <input type=\”text\” name=\”fax_number\” size=\”30\” maxlength=\”25\”>
31: <input type=\”radio\” name=\”fax_type\” value=\”home\” checked> home
32: <input type=\”radio\” name=\”fax_type\” value=\”work\”> work
33: <input type=\”radio\” name=\”fax_type\” value=\”other\”> other</p>
35: <p><strong>Email Address:</strong><br/>
36: <input type=\”text\” name=\”email\” size=\”30\” maxlength=\”150\”>
37: <input type=\”radio\” name=\”email_type\” value=\”home\” checked> home
38: <input type=\”radio\” name=\”email_type\” value=\”work\”> work
39: <input type=\”radio\” name=\”email_type\” value=\”other\”> other</p>
41: <p><strong>Personal Note:</strong><br/>
42: <textarea name=\”note\” cols=\”35\” rows=\”3\”
43: wrap=\”virtual\”></textarea></p>
45: <p><input type=\”submit\” name=\”submit\” value=\”Add Entry\”></p>
46: </form>”;
48: } else if ($_POST) {
49: //time to add to tables, so check for required fields
59: if (($_POST[“f_name”] == “”) || ($_POST[“l_name”] == “”)) {
60: header(“Location: addentry.php”);
61: exit;
62: }
64: //connect to database
65: $mysqli = mysqli_connect(“localhost”,”joeuser”,”somepass”,”testDB”);
67: //add to master_name table
68: $add_master_sql = “INSERT INTO master_name (date_added, date_modified,
69: f_name, l_name) VALUES (now(), now(),
70: ‘”.$_POST[“f_name”].”‘, ‘”.$_POST[“l_name”].”‘)”;
71: $add_master_res = mysqli_query($mysqli, $add_master_sql)
72: or die(mysqli_error($mysqli));
74: //get master_id for use with other tables
75: $master_id = mysqli_insert_id($mysqli);
77: if (($_POST[“address”]) || ($_POST[“city”]) || ($_POST[“state”])
78: || ($_POST[“zipcode”])) {
79: //something relevant, so add to address table
80: $add_address_sql = “INSERT INTO address (master_id, date_added,
81: date_modified, address, city, state, zipcode,
82: type) VALUES (‘”.$master_id.”‘, now(), now(),
83: ‘”.$_POST[“address”].”‘, ‘”.$_POST[“city”].”‘,
84: ‘”.$_POST[“state”].”‘, ‘”.$_POST[“zipcode”].”‘,
85: ‘”.$_POST[“add_type”].”‘)”;
86: $add_address_res = mysqli_query($mysqli, $add_address_sql)
87: or die(mysqli_error($mysqli));
88: }
90: if ($_POST[“tel_number”]) {
91: //something relevant, so add to telephone table
92: $add_tel_sql = “INSERT INTO telephone (master_id, date_added,
93: date_modified, tel_number, type) VALUES
94: (‘”.$master_id.”‘, now(), now(),
95: ‘”.$_POST[“tel_number”].”‘,
96: ‘”.$_POST[“tel_type”].”‘)”;
97: $add_tel_res = mysqli_query($mysqli, $add_tel_sql)
98: or die(mysqli_error($mysqli));
99: }
101: if ($_POST[“fax_number”]) {
102: //something relevant, so add to fax table
103: $add_fax_sql = “INSERT INTO fax (master_id, date_added,
104: date_modified, fax_number, type) VALUES
105: (‘”.$master_id.”‘, now(), now(),
106: ‘”.$_POST[“fax_number”].”‘,
107: ‘”.$_POST[“fax_type”].”‘)”;
108: $add_fax_res = mysqli_query($mysqli, $add_fax_sql)
109: or die(mysqli_error($mysqli));
110: }
112: if ($_POST[“email”]) {
113: //something relevant, so add to email table
114: $add_email_sql = “INSERT INTO email (master_id, date_added,
115: date_modified, email, type) VALUES
116: (‘”.$master_id.”‘, now(), now(),
117: ‘”.$_POST[“email”].”‘,
118: ‘”.$_POST[“email_type”].”‘)”;
119: $add_email_res = mysqli_query($mysqli, $add_email_sql)
120: or die(mysqli_error($mysqli));
121: }
123: if ($_POST[“note”]) {
124: //something relevant, so add to notes table
125: $add_notes_sql = “INSERT INTO personal_notes (master_id, date_added,
126: date_modified, note) VALUES (‘”.$master_id.”‘,
127: now(), now(), ‘”.$_POST[“note”].”‘)”;
128: $add_notes_res = mysqli_query($mysqli, $add_notes_sql)
129: or die(mysqli_error($mysqli));
130: }
131: mysqli_close($mysqli);
132: $display_block = “<p>Your entry has been added.
133: Would you like to <a href=\”addentry.php\”>add another</a>?</p>”;
134: }
135: ?>
136: <html>
137: <head>
138: <title>Add an Entry</title>
139: </head>
140: <body>
141: <h1>Add an Entry</h1>
142: <?php echo $display_block; ?>
143: </body>
144: </html>

As already noted, this script performs one of two tasks at any given time: It either shows the record addition form, or it performs the SQL queries related to adding a new record. The logic that determines the task begins at line 2, with a test for the value of $_POST. If there is no value in the $_POST superglobal, the user has not submitted the form and therefore needs to see the form. The HTML for the form is placed in a string called $display_block, from lines 446. The script then breaks out of the if…else construct and jumps down to line 136, which outputs the HTML and prints the value of $display_block, in this case the form. Figure 20.2 displays the outcome.

Figure 20.2. The record addition form.

[View full size image]

The else condition on Line 48 is invoked if there is a value in $_POST, meaning that the user has submitted the form. In this simple example, two fields have been designated as required fields: the first name and last name of the person. So, lines 5962 check for values in $_POST[“f_name”] and $_POST[“l_name”] and redirect the user back to the form if either value is missing.

After making it through the check for required fields, we connect to the database in line 65. Next comes the multitude of insertion statements, only one of which is requiredthe insertion of a record into the master_name table. This occurs on lines 6872. After the insertion is made, the id of this record is extracted using mysqli_insert_id() on line 75. We use this value, now referred to as $master_id, in our remaining SQL queries.

The SQL queries for inserting records into the remaining tables are all conditional, meaning they will occur only if some condition is true. In lines 7778, we see that the condition that must be met is that a value exists for any of the following variables:

$_POST[“address”], $_POST[“city”], $_POST[“state”], $_POST[“zipcode”].

Lines 8087 create and issue the query if this condition is met.

The same principle holds true for adding to the telephone table (lines 9099), the fax table (lines 101110), the email table (lines 112121), and the personal_notes table (lines 123130). If the conditions are met, records are inserted into those tables.

Once through this set of conditions, the message for the user is placed in the $display_block variable, and the script exits this if…else construct and prints HTML from lines 136145.

Figure 20.3 shows an output of the record addition script.

Figure 20.3. A record has been added.

Add a few records using this form so that you have some values to play with in the following sections. On your own, try to modify this script in such a way that the values entered in the form are printed to the screen after successful record insertion.

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 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”);
5: if (!$_POST) {
6: //haven’t seen the selection form, so show it
7: $display_block = “<h1>Select an Entry</h1>”;
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));
16: if (mysqli_num_rows($get_list_res) < 1) {
17: //no records
18: $display_block .= “<p><em>Sorry, no records to select!</em></p>”;
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>”;
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: }
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: }
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));
56; while ($name_info = mysqli_fetch_array($get_master_res)) {
57: $display_name = stripslashes($name_info[‘display_name’]);
58: }
60: $display_block = “<h1>Showing Record for “.$display_name.”</h1>”;
62: //free result
63: mysqli_free_result($get_master_res);
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));
71: if (mysqli_num_rows($get_addresses_res) > 0) {
72: $display_block .= “<p><strong>Addresses:</strong><br/>
73: <ul>”;
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’];
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));
95: if (mysqli_num_rows($get_tel_res) > 0) {
96: $display_block .= “<p><strong>Telephone:</strong><br/>
97: <ul>”;
99: while ($tel_info = mysqli_fetch_array($get_tel_res)) {
100: $tel_number = stripslashes($tel_info[‘tel_number’]);
101: $tel_type = $tel_info[‘type’];
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);
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));
116: if (mysqli_num_rows($get_fax_res) > 0) {
117: $display_block .= “<p><strong>Fax:</strong><br/>
118: <ul>”;
120: while ($fax_info = mysqli_fetch_array($get_fax_res)) {
121: $fax_number = stripslashes($fax_info[‘fax_number’]);
122: $fax_type = $fax_info[‘type’];
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);
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>”;
140: while ($email_info = mysqli_fetch_array($get_email_res)) {
141: $email = stripslashes($email_info[’email’]);
142: $email_type = $email_info[‘type’];
144: $display_block .= “<li>$email ($email_type)</li>”;
145: }
146: $display_block .= “</ul>”;
147: }
148: //free result
149: mysqli_free_result($get_email_res);
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));
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.

Creating the Record Deletion Mechanism
The record deletion mechanism is virtually identical to the script used to view a record. In fact, you can just take the first 42 lines of Listing 20.3, paste them into a new file called delentry.php, and change “View” to “Delete” in lines 24 and 38.

Starting with a new line 43, the remainder of the code for delentry.php is shown in Listing 20.4.

Listing 20.4. Script Called delentry.php for Selecting and Deleting a Record
43: } else if ($_POST) {
44: //check for required fields
45: if ($_POST[“sel_id”] == “”) {
46: header(“Location: delentry.php”);
47: exit;
48: }
50: //issue queries
51: $del_master_sql = “DELETE FROM master_name WHERE
52: id = ‘”.$_POST[“sel_id”].”‘”;
53: $del_master_res = mysqli_query($mysqli, $del_master_sql)
54: or die(mysqli_error($mysqli));
56: $del_address_sql = “DELETE FROM address WHERE
57: id = ‘”.$_POST[“sel_id”].”‘”;
58: $del_address_res = mysqli_query($mysqli, $del_address_sql)
59: or die(mysqli_error($mysqli));
61: $del_tel_sql = “DELETE FROM telephone WHERE id = ‘”.$_POST[“sel_id”].”‘”;
62: $del_tel_res = mysqli_query($mysqli, $del_tel_sql)
63: or die(mysqli_error($mysqli));
66: $del_fax_sql = “DELETE FROM fax WHERE id = ‘”.$_POST[“sel_id”].”‘”;
67: $del_fax_res = mysqli_query($mysqli, $del_fax_sql)
68; or die(mysqli_error($mysqli));
70: $del_email_sql = “DELETE FROM email WHERE id = ‘”.$_POST[“sel_id”].”‘”;
71: $del_email_res = mysqli_query($mysqli, $del_email_sql)
72: or die(mysqli_error($mysqli));
74: $del_note_sql = “DELETE FROM personal_notes WHERE
75: id = ‘”.$_POST[“sel_id”].”‘”;
76: $del_note_res = mysqli_query($mysqli, $del_note_sql)
77: or die(mysqli_error($mysqli));
79: $display_block = “<h1>Record(s) Deleted</h1>
80: <p>Would you like to
81: <a href=\””.$_SERVER[“PHP_SELF”].”\”>delete another</a>?</p>”;
82: }
83: ?>
84: <html>
85: <head>
86: <title>My Records</title>
87: </head>
88: <body>
89: <?php echo $display_block; ?>
90: </body>
91: </html>

Picking up with line 45, the script looks for the required field, $_POST[“sel_id”], just as it did in the selentry.php script. If that required value does not exist, the user is redirected to the selection form. In lines 5177, queries delete all information related to the selected individual, from all tables. Lines 7981 place a nice message in $display_block, and the script exits and prints the HTML to the screen. Figure 20.6 shows an output of the record deletion script.

Figure 20.6. Deleting a record.

When you go back to the record selection form after deleting a record, you’ll note that the individual you deleted is no longer in the selection menu as it should be!

Adding Subentries to a Record
At this point in the chapter, you’ve learned how to add, remove, and view records. What’s missing is adding additional entries to the related tables after you’ve already entered a master recordentries for home versus work telephone number, for example. All you need to do is make a few changes to existing scripts.

In the selentry.php script in Listing 20.3, change lines 166167 to read

$display_block .= “<p align=\”center\”>
<a href=\”addentry.php?master_id=”.$_POST[“sel_id”].”\”>add info</a> …
<a href=\””.$_SERVER[“PHP_SELF”].”\”>select another</a></p>”;

This change simply adds a link to the addentry.php script and also passes it a variable accessible via $_GET[“master_id”].

Now we need to modify the addentry.php script from Listing 20.2 to account for its dual purposes. Here is a summary of the changes to the original script.

Replace the first 10 lines of the original addentry.php script with the following snippet:

if ((!$_POST) || ($_GET[“master_id”] != “”)) {
//haven’t seen the form, so show it
$display_block = ”
<form method=\”post\” action=\””.$_SERVER[“PHP_SELF”].”\”>”;

if (isset($_GET[“master_id”])) {
//connect to database
$mysqli = mysqli_connect(“localhost”, “joeuser”, “somepass”, “testDB”);

//get first, last names for display/tests validity
$get_names_sql = “SELECT concat_ws(‘ ‘, f_name, l_name) AS display_name
FROM master_name WHERE id = ‘”.$_GET[“master_id”].”‘”;
$get_names_res = mysqli_query($mysqli, $get_names_sql)
or die(mysqli_error($mysqli));

if (mysqli_num_rows($get_names_res) == 1) {
while ($name_info = mysqli_fetch_array($get_names_res)) {
$display_name = stripslashes($name_info[‘display_name’]);
if (isset($display_name)) {
$display_block .= “<p>Adding information for
} else {
$display_block .= ”
<p><strong>First/Last Names:</strong><br/>
<input type=\”text\” name=\”f_name\” size=\”30\” maxlength=\”75\”>
<input type=\”text\” name=\”l_name\” size=\”30\” maxlength=\”75\”>”;
$display_block .= “<p><strong>Address:</strong><br/>

This snippet simply moves around the form elements, printing the first and last name fields only if they contain a new record. If they contain an addition to a record, the individual’s name is extracted from the database for aesthetic purposes as well as for a validity check of the ID.

Next, find this line in the original addentry.php script:

<p><input type=\”submit\” name=\”submit\” value=\”Add Entry\”></p>

Directly above it, add the following:

if ($_GET) {
$display_block .= “<input type=\”hidden\” name=\”master_id\”

This modification ensures that the known value of master_id is passed along to the next task, if it is present. Be sure to close the $display_block string in the line above this and restart the $display_block string in the line beneath this if statement.

Identify what were lines 4975 of the original script, beginning with the comment time to add to tables and ending with obtaining the value of $master_id. These lines should be replaced with the following:

//time to add to tables, so check for required fields
if ((($_POST[“f_name”] == “”) || ($_POST[“l_name”] == “”)) &&
(!isset($_POST[“master_id”]))) {
header(“Location: addentry.php”);
//connect to database
$mysqli = mysqli_connect(“localhost”, “joeuser”, “somepass”, “testDB”);

if (!$_POST[“master_id”]) {
//add to master_name table
$add_master_sql = “INSERT INTO master_name (date_added, date_modified,
f_name, l_name) VALUES (now(), now(),
‘”.$_POST[“f_name”].”‘, ‘”.$_POST[“l_name”].”‘)”;
$add_master_res = mysqli_query($mysqli, $add_master_sql)
or die(mysqli_error($mysqli));

//get master_id for use with other tables
$master_id = mysqli_insert_id($mysqli);
} else {
$master_id = $_POST[“master_id”];

These lines modify the check for required fields, allowing the script to continue without values for first and last names, but only if it has a $_POST[“master_id”] value. Then the script connects to the database to perform all the additions we want it to, but it skips the addition to the master_name table if a value for $_POST[“master_id”] exists.

Finally, in the section of the script that handles the insertion into the personal_notes table, change INSERT into to UPDATE to handle an update of the notes field:

$add_notes_sql = “UPDATE personal_notes set note = ‘”.$_POST[“note”].”‘ WHERE
master_id = ‘”.$master_id.”‘”;

The new script should look like Listing 20.5.

Listing 20.5. New addentry.php Script
1: <?php
2: if ((!$_POST) || (isset($_GET[“master_id”]))) {
3: //haven’t seen the form, so show it
4: $display_block = ”
5: <form method=\”post\” action=\””.$_SERVER[“PHP_SELF”].”\”>”;
7: if (isset($_GET[“master_id”])) {
8: //connect to database
9: $mysqli = mysqli_connect(“localhost”, “joeuser”, “somepass”,
10: “testDB”);
12: //get first, last names for display/tests validity
13: $get_names_sql = “SELECT concat_ws(‘ ‘, f_name, l_name) AS
14: display_name FROM master_name WHERE id =
15: ‘”.$_GET[“master_id”].”‘”;
16: $get_names_res = mysqli_query($mysqli, $get_names_sql)
17: or die(mysqli_error($mysqli));
19: if (mysqli_num_rows($get_names_res) == 1) {
20: while ($name_info = mysqli_fetch_array($get_names_res)) {
21: $display_name = stripslashes($name_info[‘display_name’]);
22: }
23: }
24: }
26: if (isset($display_name)) {
27: $display_block .= “<p>Adding information for
28: <strong>$display_name</strong>:</p>”;
29: } else {
30: $display_block .= ”
31: <p><strong>First/Last Names:</strong><br/>
32: <input type=\”text\” name=\”f_name\” size=\”30\” maxlength=\”75\”>
33: <input type=\”text\” name=\”l_name\” size=\”30\” maxlength=\”75\”>”;
34: }
35: $display_block .= “<p><strong>Address:</strong><br/>
36: <input type=\”text\” name=\”address\” size=\”30\”></p>
38: <p><strong>City/State/Zip:</strong><br/>
39: <input type=\”text\” name=\”city\” size=\”30\” maxlength=\”50\”>
40: <input type=\”text\” name=\”state\” size=\”5\” maxlength=\”2\”>
41 <input type=\”text\” name=\”zipcode\” size=\”10\” maxlength=\”10\”></p>
43: <p><strong>Address Type:</strong><br/>
44: <input type=\”radio\” name=\”add_type\” value=\”home\” checked> home
45: <input type=\”radio\” name=\”add_type\” value=\”work\”> work
46: <input type=\”radio\” name=\”add_type\” value=\”other\”> other</p>
48: <p><strong>Telephone Number:</strong><br/>
49: <input type=\”text\” name=\”tel_number\” size=\”30\” maxlength=\”25\”>
50: <input type=\”radio\” name=\”tel_type\” value=\”home\” checked> home
51: <input type=\”radio\” name=\”tel_type\” value=\”work\”> work
52: <input type=\”radio\” name=\”tel_type\” value=\”other\”> other</p>
54: <p><strong>Fax Number:</strong><br/>
55: <input type=\”text\” name=\”fax_number\” size=\”30\” maxlength=\”25\”>
56: <input type=\”radio\” name=\”fax_type\” value=\”home\” checked> home
57: <input type=\”radio\” name=\”fax_type\” value=\”work\”> work
58: <input type=\”radio\” name=\”fax_type\” value=\”other\”> other</p>
60: <p><strong>Email Address:</strong><br/>
61: <input type=\”text\” name=\”email\” size=\”30\” maxlength=\”150\”>
62: <input type=\”radio\” name=\”email_type\” value=\”home\” checked> home
63: <input type=\”radio\” name=\”email_type\” value=\”work\”> work
64: <input type=\”radio\” name=\”email_type\” value=\”other\”> other</p>
66: <p><strong>Personal Note:</strong><br/>
67: <textarea name=\”note\” cols=\”35\” rows=\”3\”
68: wrap=\”virtual\”></textarea></p>
70: <input type=\”hidden\” name=\”master_id\” value=\””.$_GET[“master_id”].”\”>
71: <p><input type=\”submit\” name=\”submit\” value=\”Add Entry\”></p>
72: </form>”;
74: } else if ($_POST) {
75: //time to add to tables, so check for required fields
76: if ((($_POST[“f_name”] == “”) || ($_POST[“l_name”] == “”)) &&
77: (!isset($_POST[“master_id”]))) {
78: header(“Location: addentry.php”);
79: exit;
80: }
82: //connect to database
83: $mysqli = mysqli_connect(“localhost”, “joeuser”, “somepass”, “testDB”);
85: if (!$_POST[“master_id”]) {
86: //add to master_name table
87: $add_master_sql = “INSERT INTO master_name (date_added,
88: date_modified, f_name, l_name) VALUES
89: (now(), now(), ‘”.$_POST[“f_name”].”‘,
90: ‘”.$_POST[“l_name”].”‘)”;
91: $add_master_res = mysqli_query($mysqli, $add_master_sql)
92: or die(mysqli_error($mysqli));
94: //get master_id for use with other tables
95: $master_id = mysqli_insert_id($mysqli);
96: } else {
97: $master_id = $_POST[“master_id”];
98: }
100: if (($_POST[“address”]) || ($_POST[“city”]) || ($_POST[“state”])
101: || ($_POST[“zipcode”])) {
102: //something relevant, so add to address table
103: $add_address_sql = “INSERT INTO address (master_id, date_added,
104: date_modified, address, city, state, zipcode,
105: type) VALUES (‘”.$master_id.”‘, now(), now(),
106: ‘”.$_POST[“address”].”‘, ‘”.$_POST[“city”].”‘,
107: ‘”.$_POST[“state”].”‘, ‘”.$_POST[“zipcode”].”‘,
108: ‘”.$_POST[“add_type”].”‘)”;
109: $add_address_res = mysqli_query($mysqli, $add_address_sql)
110: or die(mysqli_error($mysqli));
111: }
113: if ($_POST[“tel_number”]) {
114: //something relevant, so add to telephone table
115: $add_tel_sql = “INSERT INTO telephone (master_id, date_added,
116: date_modified, tel_number, type) VALUES
117: (‘”.$master_id.”‘, now(), now(),
118: ‘”.$_POST[“tel_number”].”‘,
119: ‘”.$_POST[“tel_type”].”‘)”;
120: $add_tel_res = mysqli_query($mysqli, $add_tel_sql)
121: or die(mysqli_error($mysqli));
122: }
124: if ($_POST[“fax_number”]) {
125: //something relevant, so add to fax table
126: $add_fax_sql = “INSERT INTO fax (master_id, date_added,
127: date_modified, fax_number, type) VALUES
128: (‘”.$master_id.”‘, now(), now(),
129: ‘”.$_POST[“fax_number”].”‘,
130: ‘”.$_POST[“fax_type”].”‘)”;
131: $add_fax_res = mysqli_query($mysqli, $add_fax_sql)
132: or die(mysqli_error($mysqli));
133: }
135: if ($_POST[“email”]) {
136: //something relevant, so add to email table
137: $add_email_sql = “INSERT INTO email (master_id, date_added,
138: date_modified, email, type) VALUES
139: (‘”.$master_id.”‘, now(), now(),
140: ‘”.$_POST[“email”].”‘,
141: ‘”.$_POST[“email_type”].”‘)”;
142: $add_email_res = mysqli_query($mysqli, $add_email_sql)
143: or die(mysqli_error($mysqli));
144: }
146: if ($_POST[“note”]) {
147: //something relevant, so add to notes table
148: $add_notes_sql = “UPDATE personal_notes set note =
149: ‘”.$_POST[“note”].”‘ WHERE
150: master_id = ‘”.$master_id.”‘”;
151: $add_notes_res = mysqli_query($mysqli, $add_notes_sql)
152: or die(mysqli_error($mysqli));
153: }
154: mysqli_close($mysqli);
155: $display_block = “<p>Your entry has been added.
156: Would you like to <a href=\”addentry.php\”>add another</a>?</p>”;
157: }
158: ?>
159: <html>
160: <head>
161: <title>Add an Entry</title>
162: </head>
163: <body>
164: <h1>Add an Entry</h1>
165: <?php echo $display_block; ?>
166: </body>
167: </html>

You can try out this revised script by selecting a record to view and then following the add info link.

Figure 20.7. Adding to a record.

After submitting this form, you can go back through the selection sequence and view the record to verify that your changes have been made.

In this hands-on chapter, you applied your basic PHP and MySQL knowledge to the creation of a personal address book. You learned how to create the database table and scripts for record addition, removal, and simple viewing. You also learned the process for adding multiple records attached to a single master entry.