Sams Teach Yourself PHP, MySQL and Apache All in One (2012)
Part V. Basic Projects
Chapter 20. Creating an Online Address Book
In this chapter, you learn the following:
• 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
In this chapter, you create a manageable online address book. You learn how to create the relevant database tables, as well as the forms and scripts for adding, deleting, and viewing database records.
Note
These basic concepts are the foundations of developing any application that uses CRUD functionality (create, read, update, delete), which is precisely what a lot of web-based applications are.
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 (or remember) a paper-based address book, you might note there are several entries for one person. Maybe that person has three telephone numbers, or two email addresses, and so forth—whatever didn’t fit in the original template. In an online address book, a set of related tables helps alleviate the redundancy and repetition of information and allows you to display all information in a unified view.
Table 20.1 shows sample table and field names to use for your online address book. In a minute, you 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
Notice the use of date-related fields; each table has a date_added and date_modified field in it. The fields help maintain your data; you might at some point want to issue a query that removes all records older than a certain number of months or years, or removes all records that haven’t been updated within a certain period.
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.
Note
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:
CREATE TABLE master_name (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
date_added DATETIME,
date_modified DATETIME,
f_name VARCHAR (75),
l_name VARCHAR (75)
);
Next, you 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, the date_added field, and the date_modified field, plus the field through which the relationship will be made—the master_id field.
The master_id is 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. You see this in the type field, 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 U.S. addresses, round out the table with address,city, state, and zipcode fields:
CREATE TABLE address (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
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')
);
The telephone, fax, and email tables are all variations on the same theme:
CREATE TABLE telephone (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
master_id INT NOT NULL,
date_added DATETIME,
date_modified DATETIME,
tel_number VARCHAR (25),
type ENUM ('home', 'work', 'other')
);
CREATE TABLE fax (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
master_id INT NOT NULL,
date_added DATETIME,
date_modified DATETIME,
fax_number VARCHAR (25),
type ENUM ('home', 'work', 'other')
);
CREATE TABLE email (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
master_id INT NOT NULL,
date_added DATETIME,
date_modified DATETIME,
email VARCHAR (150),
type ENUM ('home', 'work', 'other')
);
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:
CREATE TABLE personal_notes (
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
master_id INT NOT NULL UNIQUE,
date_added DATETIME,
date_modified DATETIME,
note TEXT
);
Now that your tables are created, you can work through the forms and scripts for managing and viewing your records.
Creating an Include File for Common Functions
In Chapter 19, “Managing a Simple Mailing List,” an included file of common functions was used to make your scripts more concise. The same thing is true in this chapter. Although the only content in the common functions file is the database connection function, this process serves two purposes: to make your scripts more concise and to eliminate the need to modify the database connection information throughout multiple files should that information change. Listing 20.1 contains the code shared by the scripts in this chapter.
Listing 20.1 Common Functions in an Included File
1: <?php
2: function doDB() {
3: global $mysqli;
4:
5: //connect to server and select database; you may need it
6: $mysqli = mysqli_connect("localhost", "joeuser",
7: "somepass", "testDB");
8:
9: //if connection fails, stop script execution
10: if (mysqli_connect_errno()) {
11: printf("Connect failed: %s\n", mysqli_connect_error());
12: exit();
13: }
14: }
15: ?>
Lines 2–14 set up the database connection function, doDB. If the connection cannot be made, the script exits when this function is called; otherwise, it makes the value of $mysqli available to other parts of your script.
Save this file as ch20_include.php and place it on your web server. The other code listings in this chapter include this file within the first few lines of the script.
Creating a Menu
Your online address book contains several actions, so it makes sense to create a menu for your links. Listing 20.2 creates a simple menu for all the scripts you create in this chapter, called mymenu.html
Listing 20.2 Address Book Menu
1: <!DOCTYPE html>
2: <html>
3: <head>
4: <title>My Address Book</title>
5: </head>
6: <body>
7: <h1>My Address Book</h1>
8:
9: <p><strong>Management</strong></p>
10: <ul>
11: <li><a href="addentry.php">Add an Entry</a></li>
12: <li><a href="delentry.php">Delete an Entry</a></li>
13: </ul>
14:
15: <p><strong>Viewing</strong></p>
16: <ul>
17: <li><a href="selentry.php">Select a Record</a></li>
18: </ul>
19: </body>
20: </html>
Figure 20.1 shows the output of Listing 20.2. You tackle each of these items in order, starting with “Add an Entry” in the next section.
Figure 20.1 Address book menu.
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. With practice, you can make these verbose scripts much more streamlined and efficient.
Listing 20.3 shows a basic record-addition script, called addentry.php, which has two parts: what to do if the form should be displayed (lines 4–89) and what actions to take if the form is being submitted (lines 91–187). Lines 3–89 simply place the contents of the HTML form into a string called $display_block.
Listing 20.3 Basic Record-Addition Script Called addentry.php
1: <?php
2: include 'ch20_include.php';
3:
4: if (!$_POST) {
5: //haven't seen the form, so show it
6: $display_block = <<<END_OF_TEXT
7: <form method="post" action="$_SERVER[PHP_SELF]">
8: <fieldset>
9: <legend>First/Last Names:</legend><br/>
10: <input type="text" name="f_name" size="30"
11: maxlength="75" required="required" />
12: <input type="text" name="l_name" size="30"
13: maxlength="75" required="required" />
14: </fieldset>
15:
16: <p><label for="address">Street Address:</label><br/>
17: <input type="text" id="address" name="address"
18: size="30" /></p>
19:
20: <fieldset>
21: <legend>City/State/Zip:</legend><br/>
22: <input type="text" name="city" size="30" maxlength="50" />
23: <input type="text" name="state" size="5" maxlength="2" />
24: <input type="text" name="zipcode" size="10" maxlength="10" />
25: </fieldset>
26:
27: <fieldset>
28: <legend>Address Type:</legend><br/>
29: <input type="radio" id="add_type_h" name="add_type"
30: value="home" checked />
31: <label for="add_type_h">home</label>
32: <input type="radio" id="add_type_w" name="add_type"
33: value="work" />
34: <label for="add_type_w">work</label>
35: <input type="radio" id="add_type_o" name="add_type"
36: value="other" />
37: <label for="add_type_o">other</label>
38: </fieldset>
39:
40: <fieldset>
41: <legend>Telephone Number:</legend><br/>
42: <input type="text" name="tel_number" size="30" maxlength="25" />
43: <input type="radio" id="tel_type_h" name="tel_type"
44: value="home" checked />
45: <label for="tel_type_h">home</label>
46: <input type="radio" id="tel_type_w" name="tel_type"
47: value="work" />
48: <label for="tel_type_w">work</label>
49: <input type="radio" id="tel_type_o" name="tel_type"
50: value="other" />
51: <label for="tel_type_o">other</label>
52: </fieldset>
53:
54: <fieldset>
55: <legend>Fax Number:</legend><br/>
56: <input type="text" name="fax_number" size="30" maxlength="25" />
57: <input type="radio" id="fax_type_h" name="fax_type"
58: value="home" checked />
59: <label for="fax_type_h">home</label>
60: <input type="radio" id="fax_type_w" name="fax_type"
61: value="work" />
62: <label for="fax_type_w">work</label>
63: <input type="radio" id="fax_type_o" name="fax_type"
64: value="other" />
65: <label for="fax_type_o">other</label>
66: </fieldset>
67:
68: <fieldset>
69: <legend>Email Address:</legend><br/>
70: <input type="email" name="email" size="30" maxlength="150" />
71: <input type="radio" id="email_type_h" name="email_type"
72: value="home" checked />
73: <label for="email_type_h">home</label>
74: <input type="radio" id="email_type_w" name="email_type"
75: value="work" />
76: <label for="email_type_w">work</label>
77: <input type="radio" id="email_type_o" name="email_type"
78: value="other" />
79: <label for="email_type_o">other</label>
80: </fieldset>
81:
82: <p><label for="note">Personal Note:</label><br/>
83: <textarea id="note" name="note" cols="35"
84: rows="3"></textarea></p>
85:
86: <button type="submit" name="submit"
87: value="send">Add Entry</button>
88: </form>
89: END_OF_TEXT;
Stop here for a minute and make sure you know what is going on in the listing so far. Before any other code, note that the file with a user-defined function is included on line 2. After that, 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 4, 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 6–89. The script then breaks out of the if...else construct and jumps down to line 189, 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.
Pick up the code in the listing with the else if statement, or what happens if the form has been submitted.
Listing 20.3 (continued)
90: } else if ($_POST) {
91: //time to add to tables, so check for required fields
92: if (($_POST['f_name'] == "") || ($_POST['l_name'] == "")) {
93: header("Location: addentry.php");
94: exit;
95: }
96:
97: //connect to database
98: doDB();
99:
100: //create clean versions of input strings
101: $safe_f_name = mysqli_real_escape_string($mysqli,
102: $_POST['f_name']);
103: $safe_l_name = mysqli_real_escape_string($mysqli,
104: $_POST['l_name']);
105: $safe_address = mysqli_real_escape_string($mysqli,
106: $_POST['address']);
107: $safe_city = mysqli_real_escape_string($mysqli,
108: $_POST['city']);
109: $safe_state = mysqli_real_escape_string($mysqli,
110: $_POST['state']);
111: $safe_zipcode = mysqli_real_escape_string($mysqli,
112: $_POST['zipcode']);
113: $safe_tel_number = mysqli_real_escape_string($mysqli,
114: $_POST['tel_number']);
115: $safe_fax_number = mysqli_real_escape_string($mysqli,
116: $_POST['fax_number']);
117: $safe_email = mysqli_real_escape_string($mysqli,
118: $_POST['email']);
119: $safe_note = mysqli_real_escape_string($mysqli,
120: $_POST['note']);
121:
122: //add to master_name table
123: $add_master_sql = "INSERT INTO master_name (date_added,
124: date_modified, f_name, l_name) VALUES
125: (now(), now(), '".$safe_f_name."', '".$safe_l_name."')";
126: $add_master_res = mysqli_query($mysqli, $add_master_sql)
127: or die(mysqli_error($mysqli));
128:
129: //get master_id for use with other tables
130: $master_id = mysqli_insert_id($mysqli);
131:
132: if (($_POST['address']) || ($_POST['city']) ||
133: ($_POST['state']) || ($_POST['zipcode'])) {
134: //something relevant, so add to address table
135: $add_address_sql = "INSERT INTO address (master_id,
136: date_added, date_modified, address, city, state,
137: zipcode, type) VALUES
138: ('".$master_id."', now(), now(),
139: '".$safe_address."', '".$safe_city."',
140: '".$safe_state."' , '".$safe_zipcode."' ,
141: '".$_POST['add_type']."')";
142: $add_address_res = mysqli_query($mysqli, $add_address_sql)
143: or die(mysqli_error($mysqli));
144: }
145:
146: if ($_POST['tel_number']) {
147: //something relevant, so add to telephone table
148: $add_tel_sql = "INSERT INTO telephone (master_id, date_added,
149: date_modified, tel_number, type) VALUES
150: ('".$master_id."', now(), now(),
151: '".$safe_tel_number."', '".$_POST['tel_type']."')";
152: $add_tel_res = mysqli_query($mysqli, $add_tel_sql)
153: or die(mysqli_error($mysqli));
154: }
155:
156: if ($_POST['fax_number']) {
157: //something relevant, so add to fax table
158: $add_fax_sql = "INSERT INTO fax (master_id, date_added,
159: date_modified, fax_number, type) VALUES
160: ('".$master_id."', now(), now(), '".$safe_fax_number."',
161: '".$_POST['fax_type']."')";
162: $add_fax_res = mysqli_query($mysqli, $add_fax_sql)
163: or die(mysqli_error($mysqli));
164: }
165: if ($_POST['email']) {
166: //something relevant, so add to email table
167: $add_email_sql = "INSERT INTO email (master_id, date_added,
168: date_modified, email, type) VALUES
169: ('".$master_id."', now(), now(), '".$safe_email."',
170: '".$_POST['email_type']."')";
171: $add_email_res = mysqli_query($mysqli, $add_email_sql)
172: or die(mysqli_error($mysqli));
173: }
174:
175: if ($_POST['note']) {
176: //something relevant, so add to notes table
177: $add_notes_sql = "INSERT INTO personal_notes (master_id,
178: date_added, date_modified, note) VALUES
179: ('".$master_id."', now(), now(),
180: '".$safe_note."')";
181: $add_notes_res = mysqli_query($mysqli, $add_notes_sql)
182: or die(mysqli_error($mysqli));
183: }
184: mysqli_close($mysqli);
185: $display_block = "<p>Your entry has been added. Would you
186: like to <a href=\"addentry.php\">add another</a>?</p>";
187: }
188: ?>
189: <!DOCTYPE html>
190: <head>
191: <title>Add an Entry</title>
192: </head>
193: <body>
194: <h1>Add an Entry</h1>
195: <?php echo $display_block; ?>
196: </body>
197: </html>
The else condition on Line 90 is invoked if there is a value in $_POST, meaning that the user has submitted the form. In this simple example, two fields were designated as required fields: the first name and last name of the person. So, lines 92–95 check for values in $_POST['f_name'] and$_POST['l_name'] and redirect the user back to the form if either value is missing.
Note
Because the two required fields in the form are marked as such using the HTML5 required attribute, the form will not be submitted without text in the fields. However, for older browsers or those devices not supporting the HTML5 required attribute in INPUT fields, this server-side check does the trick.
After making it through the check for required fields, the code connects to the database in line 98. Once the database connection is made, you can safely use mysqli_real_escape_string() to sanitize the user input and create “safe” versions of those strings for use in INSERT statements.
Next comes the multitude of insertion statements, only one of which is required: the insertion of a record into the master_name table. This occurs on lines 123–127. After the insertion is made, the id of this record is extracted using mysqli_insert_id() on line 130. You use this value, now referred to as $master_id, in your remaining SQL queries.
The SQL queries for inserting records into the remaining tables are all conditional, meaning they occur only if some condition is true. In lines 132–133, you 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'], and $_POST['zipcode']. Lines 135–143 create and issue the query if this condition is met.
The same principle holds true for adding to the telephone table (lines 146–154), the fax table (lines 156–164), the email table (lines 165–173), and the personal_notes table (lines 175–183). 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 189–197. 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 print 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 create the two-part script that shows you how to select and view records in your database.
Listing 20.4 shows the select-and-view script called selentry.php, which has three parts: the record-selection form (lines 5–42), the code to display the record contents (lines 43–172), and the HTML template that displays the dynamically generated strings (lines 176–184). Because this code is long, the listing is broken into smaller chunks for discussion.
Listing 20.4 Script Called selentry.php for Selecting and Viewing a Record
1: <?php
2: include 'ch20_include.php';
3: doDB();
4:
5: if (!$_POST) {
6: //haven't seen the selection form, so show it
7: $display_block = "<h1>Select an Entry</h1>";
8:
9: //get parts of records
10: $get_listql = "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_listql)
14: or die(mysqli_error($mysqli));
15:
16: if (mysqli_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><label for=\"sel_id\">Select a Record:</label><br/>
25: <select id="sel_id\" name=\"sel_id\" required=\"required\">
26: <option value=\"\">-- Select One --</option>";
27:
28: while ($recs = mysqli_fetch_array($get_list_res)) {
29: $id = $recs['id'];
30: $display_name = stripslashes($recs['display_name']);
31: $display_block .=
32: "<option value=\"".$id."\">".$display_name."</option>";
33: }
34:
35: $display_block .= "
36: </select>
37: <button type=\"submit\" name=\"submit\"
38: value=\"view\">View Selected Entry\"></button>
39: </form>";
40: }
41: //free result
42: mysqli_free_result($get_list_res);
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 the script performs, the database still comes into play. Given that, include the file with the connection function on line 2, and call that function on 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.
Lines 10–12 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 cannot 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, 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 28–33, with form elements written to the $display_block string both above and below it.
This listing stops at line 42, but you’ll soon see lines 43 through the end of the script. If you 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, depending on what is in your database, of course).
Figure 20.4 The record-selection form.
However, you must finish the selentry.php script in order for it to be useful, so continue Listing 20.4 at line 43, which begins the else portion of the if...else statement.
Listing 20.4 (continued)
43: } else if ($_POST) {
44: //check for required fields
45: if ($_POST['sel_id'] == "") {
46: header("Location: selentry.php");
47: exit;
48: }
49:
50: //create safe version of ID
51: $safe_id = mysqli_real_escape_string($mysqli, $_POST['sel_id']);52:
52:
53: //get master_info
54: $get_master_sql = "SELECT concat_ws(' ',f_name,l_name) as display_name
55: FROM master_name WHERE id = '".$safe_id."'";
56: $get_master_res = mysqli_query($mysqli, $get_master_sql)
57: or die(mysqli_error($mysqli));
58:
59: while ($name_info = mysqli_fetch_array($get_master_res)) {
60: $display_name = stripslashes($name_info['display_name']);
61: }
62:
63: $display_block = "<h1>Showing Record for ".$display_name."</h1>";
64:
65: //free result
66: mysqli_free_result($get_master_res);
67:
68: //get all addresses
69: $get_addresses_sql = "SELECT address, city, state, zipcode, type FROM
70: address WHERE master_id = '".$safe_id."'";
71: $get_addresses_res = mysqli_query($mysqli, $get_addresses_sql)
72: or die(mysqli_error($mysqli));
73:
74: if (mysqli_num_rows($get_addresses_res) > 0) {
75: $display_block .= "<p><strong>Addresses:</strong><br/>
76: <ul>";
77:
78: while ($add_info = mysqli_fetch_array($get_addresses_res)) {
79: address = stripslashes($add_info['address']);
80: $city = stripslashes($add_info['city']);
81: $state = stripslashes($add_info['state']);
82: $zipcode = stripslashes($add_info['zipcode']);
83: $address_type = $add_info['type'];
84:
85: $display_block .= "<li>$address $city $state $zipcode
86: ($address_type)</li>";
87: }
88: $display_block .= "</ul>";
89: }
90: //free result
91: mysqli_free_result($get_addresses_res);
Line 43 contains the else portion of the if...else statement and is invoked if the user submits the form and wants to see a specific record. It first checks for a required field, in line 45; in this case, it is checking for the value of $_POST['sel_id']. This value matches the ID from themaster_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 form—you 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'], a safe version of it is created in line 51. Next, you create and issue a query in lines 54–57 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 69–89 represent the query against the address table, and the resulting display string 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 78–87.
Lines 92–168 of Listing 20.4 perform the same type of looping and writing to the $display_block variable, but the tables are different. For instance, lines 92–109 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 114–130 for information from the fax table, lines 135–150 for information from the email table, and lines 155–172 for any content present in the personal_notes table.
Listing 20.4 (continued)
92: //get all tel
93: $get_tel_sql = "SELECT tel_number, type FROM telephone WHERE
94: master_id = '".$safe_id."'";
95: $get_tel_res = mysqli_query($mysqli, $get_tel_sql)
96: or die(mysqli_error($mysqli));
97:
98: if (mysqli_num_rows($get_tel_res) > 0) {
99: $display_block .= "<p><strong>Telephone:</strong><br/>
100: <ul>";
101:
102: while ($tel_info = mysqli_fetch_array($get_tel_res)) {
103: $tel_number = stripslashes($tel_info['tel_number']);
104: $tel_type = $tel_info['type'];
105:
106: $display_block .= "<li>$tel_number ($tel_type)</li>";
107: }
108: $display_block .= "</ul>";
109: }
110: //free result
111: mysqli_free_result($get_tel_res);
112:
113: //get all fax
114: $get_fax_sql = "SELECT fax_number, type FROM fax WHERE
115: master_id = '".$safe_id."'";
116: $get_fax_res = mysqli_query($mysqli, $get_fax_sql)
117: or die(mysqli_error($mysqli));
118:
119: if (mysqli_num_rows($get_fax_res) > 0) {
120: $display_block .= "<p><strong>Fax:</strong><br/>
121: <ul>";
122:
123: while ($fax_info = mysqli_fetch_array($get_fax_res)) {
124: $fax_number = stripslashes($fax_info['fax_number']);
125: $fax_type = $fax_info['type'];
126:
127: $display_block .= "<li>$fax_number ($fax_type)</li>";
128: }
129: $display_block .= "</ul>";
130: }
131: //free result
132: mysqli_free_result($get_fax_res);
133:
134: //get all email
135: $get_email_sql = "SELECT email, type FROM email WHERE
136: master_id = '".$safe_id."'";
137: $get_email_res = mysqli_query($mysqli, $get_email_sql)
138: or die(mysqli_error($mysqli));
139: if (mysqli_num_rows($get_email_res) > 0) {
140: $display_block .= "<p><strong>Email:</strong><br/>
141: <ul>";
142:
143: while ($email_info = mysqli_fetch_array($get_email_res)) {
144: $email = stripslashes($email_info['email']);
145: $email_type = $email_info['type'];
146:
147: $display_block .= "<li>$email ($email_type)</li>";
148: }
149: $display_block .= "</ul>";
150: }
151: //free result
152: mysqli_free_result($get_email_res);
153:
154: //get personal note
155: $get_notes_sql = "SELECT note FROM personal_notes WHERE
156: master_id = '".$safe_id."'";
157: $get_notes_res = mysqli_query($mysqli, $get_notes_sql)
158: or die(mysqli_error($mysqli));
159:
160: if (mysqli_num_rows($get_notes_res) == 1) {
161: while ($note_info = mysqli_fetch_array($get_notes_res)) {
162: $note = nl2br(stripslashes($note_info['note']));
163: }
164: $display_block .= "<p><strong>Personal Notes:</strong><br/>
165: $note</p>";
166: }
167: //free result
168: mysqli_free_result($get_notes_res);
You still have to do a little housekeeping and finish up the script, as shown in the last portion of Listing 20.4.
Listing 20.4 (continued)
169: $display_block .= "<br/>
170: <p style=\"text-align:center\">
171: <a href=\"".$_SERVER['PHP_SELF']."\">select another</a></p>";
172: }
173: //close connection to MySQL
174: mysqli_close($mysqli);
175: ?>
176: <!DOCTYPE html>
177: <html>
178: <head>
179: <title>My Records</title>
180: </head>
181: <body>
182: <?php echo $display_block; ?>
183: </body>
184: </html>
Lines 169–171 simply print a link back to the selection form before closing up the if...else statement in line 172 and the PHP block in the line following. Lines 176 through the end of the script are the generic HTML template that 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.5—your 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 notes—no associated records were entered in those tables.
Creating the Record-Deletion Mechanism
The record-deletion mechanism is nearly identical to the script used to view a record. In fact, you can just take the first 42 lines of Listing 20.4, 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.5.
Listing 20.5 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: }
49:
50: //create safe version of ID
51: $safe_id = mysqli_real_escape_string($mysqli, $_POST['sel_id']);
52:
53: //issue queries
54: $del_master_sql = "DELETE FROM master_name WHERE
55: id = '".$safe_id."'";
56: $del_master_res = mysqli_query($mysqli, $del_master_sql)
57: or die(mysqli_error($mysqli));
58:
59: $del_address_sql = "DELETE FROM address WHERE
60: id = '".$safe_id."'";
61: $del_address_res = mysqli_query($mysqli, $del_address_sql)
62: or die(mysqli_error($mysqli));
63:
64: $del_tel_sql = "DELETE FROM telephone WHERE id = '".$safe_id."'";
65: $del_tel_res = mysqli_query($mysqli, $del_tel_sql)
66: or die(mysqli_error($mysqli));
67:
68: $del_fax_sql = "DELETE FROM fax WHERE id = '".$safe_id."'";
69: $del_fax_res = mysqli_query($mysqli, $del_fax_sql)
70: or die(mysqli_error($mysqli));
71:
72: $del_email_sql = "DELETE FROM email WHERE id = '".$safe_id."'";
73: $del_email_res = mysqli_query($mysqli, $del_email_sql)
74: or die(mysqli_error($mysqli));
75:
76: $del_note_sql = "DELETE FROM personal_notes WHERE
77: id = '".$safe_id."'";
78: $del_note_res = mysqli_query($mysqli, $del_note_sql)
79: or die(mysqli_error($mysqli));
80:
81: mysqli_close($mysqli);
82:
83: $display_block = "<h1>Record(s) Deleted</h1>
84: <p>Would you like to
85: <a href=\"".$_SERVER['PHP_SELF']."\">delete another</a>?</p>";
86: }
87: ?>
88: <!DOCTYPE html>
89: <html>
90: <head>
91: <title>My Records</title>
92: </head>
93: <body>
94: <?php echo $display_block; ?>
95: </body>
96: </html>
Picking up in 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 script redirects the user to the selection form, but if it does exist, a safe version is created in line 51. In lines 54–79, queries delete all information related to the selected individual from all tables. Lines 83–85 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 record—entries 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.4, change lines 185–186 to read as follows:
$display_block .= "<p style=\"text-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 that will become accessible to the next script via $_GET['master_id'].
Now you need to modify the addentry.php script from Listing 20.3 to account for its dual purposes. Here is a summary of the changes to the original script.
Replace the first 16 lines of the original addentry.php script with the following snippet:
<?php
include 'ch20_include.php';
doDB();
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'])) {
//create safe version of ID
$safe_id = mysqli_real_escape_string($mysqli, $_GET['master_id']);
//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 = '".$safe_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
<strong>$display_name</strong>:</p>";
} else {
$display_block .= <<<END_OF_TEXT
<fieldset>
<legend>First/Last Names:</legend><br/>
<input type="text" name="f_name" size="30"
maxlength="75" required="required" />
<input type="text" name="l_name" size="30"
maxlength="75" required="required" />
</fieldset>
END_OF_TEXT;
}
$display_block .= <<<END_OF_TEXT
<p><label for="address">Street Address:</label><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:
<button type="submit" name="submit" value="send">Add Entry</button>
Directly above it, add the following:
END_OF_TEXT;
if ($_GET) {
$display_block .= "<input type=\"hidden\" name=\"master_id\"
value=\"".$_GET['master_id']."\">";
}
$display_block .= <<<END_OF_TEXT
This modification ensures that the known value of master_id is passed along to the next task, if it is present.
Identify what were lines 91–130 of the original script, beginning with the comment time to add to tables and ending with obtaining the value of $master_id. Replace those lines 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");
exit;
}
//connect to database
doDB();
//create clean versions of input strings
$safe_f_name = mysqli_real_escape_string($mysqli,
$_POST['f_name']);
$safe_l_name = mysqli_real_escape_string($mysqli,
$_POST['l_name']);
$safe_address = mysqli_real_escape_string($mysqli,
$_POST['address']);
$safe_city = mysqli_real_escape_string($mysqli,
$_POST['city']);
$safe_state = mysqli_real_escape_string($mysqli,
$_POST['state']);
$safe_zipcode = mysqli_real_escape_string($mysqli,
$_POST['zipcode']);
$safe_tel_number = mysqli_real_escape_string($mysqli,
$_POST['tel_number']);
$safe_fax_number = mysqli_real_escape_string($mysqli,
$_POST['fax_number']);
$safe_email = mysqli_real_escape_string($mysqli,
$_POST['email']);
$safe_note = mysqli_real_escape_string($mysqli,
$_POST['note']);
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(),
'".$safe_f_name."', '".$safe_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 = mysqli_real_escape_string($mysqli, $_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 you want it to, but it skips the addition to themaster_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 = '".$safe_note."',
date_modified = now() WHERE master_id = '".$master_id."'";
The new script should look like Listing 20.6.
Listing 20.6 New addentry.php Script
1: <?php
2: include 'ch20_include.php';
3: doDB();
4:
5: if ((!$_POST) || ($_GET['master_id'] != "")) {
6: //haven't seen the form, so show it
7: $display_block = "
8: <form method=\"post\" action=\"".$_SERVER['PHP_SELF']."\">";
9: if (isset($_GET['master_id'])) {
10: //create safe version of ID
11: $safe_id = mysqli_real_escape_string($mysqli, $_GET['master_id']);
12:
13: //get first, last names for display/tests validity
14: $get_names_sql = "SELECT concat_ws(' ', f_name, l_name) AS display_name
15: FROM master_name WHERE id = '".$safe_id."'";
16: $get_names_res = mysqli_query($mysqli, $get_names_sql)
17: or die(mysqli_error($mysqli));
18:
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: }
25:
26: if (isset($display_name)) {
27: $display_block .= "<p>Adding information for
28: <strong>$display_name</strong>:</p>";
29: } else {
30: $display_block .= <<<END_OF_TEXT <fieldset>
31: <legend>First/Last Names:</legend><br/>
32: <input type="text" name="f_name" size="30"
33: maxlength="75" required="required" />
34: <input type="text" name="l_name" size="30"
35: maxlength="75" required="required" />
36: </fieldset>
37: END_OF_TEXT;
38: }
39: $display_block .= <<<END_OF_TEXT
40: <p><label for="address">Street Address:</label><br/>
41: <input type="text" id="address" name="address"
42: size="30" /></p>
43:
44: <fieldset>
45: <legend>City/State/Zip:</legend><br/>
46: <input type="text" name="city" size="30" maxlength="50" />
47: <input type="text" name="state" size="5" maxlength="2" />
48: <input type="text" name="zipcode" size="10" maxlength="10" />
49: </fieldset>
50:
51: <fieldset>
52: <legend>Address Type:</legend><br/>
53: <input type="radio" id="add_type_h" name="add_type"
54: value="home" checked />
55: <label for="add_type_h">home</label>
56: <input type="radio" id="add_type_w" name="add_type"
57: value="work" />
58: <label for="add_type_w">work</label>
59: <input type="radio" id="add_type_o" name="add_type"
60: value="other" />
61: <label for="add_type_o">other</label>
62: </fieldset>
63:
64: <fieldset>
65:
66: <legend>Telephone Number:</legend><br/>
67: <input type="text" name="tel_number" size="30" maxlength="25" />
68: <input type="radio" id="tel_type_h" name="tel_type"
69: value="home" checked />
70: <label for="tel_type_h">home</label>
71: <input type="radio" id="tel_type_w" name="tel_type"
72: value="work" />
73: <label for="tel_type_w">work</label>
74: <input type="radio" id="tel_type_o" name="tel_type"
75: value="other" />
76: <label for="tel_type_o">other</label>
77: </fieldset>
78:
79: <fieldset>
80: <legend>Fax Number:</legend><br/>
81: <input type="text" name="fax_number" size="30" maxlength="25" />
82: <input type="radio" id="fax_type_h" name="fax_type"
83: value="home" checked />
84: <label for="fax_type_h">home</label>
85: <input type="radio" id="fax_type_w" name="fax_type"
86: value="work" />
87: <label for="fax_type_w">work</label>
88: <input type="radio" id="fax_type_o" name="fax_type"
89: value="other" />
90: <label for="fax_type_o">other</label>
91: </fieldset>
92:
93: <fieldset>
94: <legend>Email Address:</legend><br/>
95: <input type="email" name="email" size="30" maxlength="150" />
96: <input type="radio" id="email_type_h" name="email_type"
97: value="home" checked />
98: <label for="email_type_h">home</label>
99: <input type="radio" id="email_type_w" name="email_type"
100: value="work" />
101: <label for="email_type_w">work</label>
102: <input type="radio" id="email_type_o" name="email_type"
103: value="other" />
104: <label for="email_type_o">other</label>
105: </fieldset>
106:
107: <p><label for="note">Personal Note:</label><br/>
108: <textarea id="note" name="note" cols="35"
109: rows="3"></textarea></p>
110: END_OF_TEXT;
111: if ($_GET) {
112: $display_block .= "<input type=\"hidden\" name=\"master_id\"
113: value=\"".$_GET['master_id']."\">";
114: }
115: $display_block .= <<<END_OF_TEXT
116: <button type="submit" name="submit"
117: value="send">Add Entry</button>
118: </form>
119: END_OF_TEXT;
120: } else if ($_POST) {
121: //time to add to tables, so check for required fields
122: if ((($_POST['f_name'] == "") || ($_POST['l_name'] == "")) &&
123: (!isset($_POST['master_id']))) {
124: header("Location: addentry.php");
125: exit;
126: }
127:
128: //connect to database
129: doDB();
130: //create clean versions of input strings
131: $safe_f_name = mysqli_real_escape_string($mysqli,
132: $_POST['f_name']);
133: $safe_l_name = mysqli_real_escape_string($mysqli,
134: $_POST['l_name']);
135: $safe_address = mysqli_real_escape_string($mysqli,
136: $_POST['address']);
137: $safe_city = mysqli_real_escape_string($mysqli,
138: $_POST['city']);
139: $safe_state = mysqli_real_escape_string($mysqli,
140: $_POST['state']);
141: $safe_zipcode = mysqli_real_escape_string($mysqli,
142: $_POST['zipcode']);
143: $safe_tel_number = mysqli_real_escape_string($mysqli,
144: $_POST['tel_number']);
145: $safe_fax_number = mysqli_real_escape_string($mysqli,
146: $_POST['fax_number']);
147: $safe_email = mysqli_real_escape_string($mysqli,
148: $_POST['email']);
149: $safe_note = mysqli_real_escape_string($mysqli,
150: $_POST['note']);
151:
152: if (!$_POST['master_id']) {
153: //add to master_name table
154: $add_master_sql = "INSERT INTO master_name (date_added, date_modified,
155: f_name, l_name) VALUES (now(), now(),
156: '".$safe_f_name."', '".$safe_l_name."')";
157: $add_master_res = mysqli_query($mysqli, $add_master_sql)
158: or die(mysqli_error($mysqli));
159:
160: //get master_id for use with other tables
161: $master_id = mysqli_insert_id($mysqli);
162: } else {
163: $master_id = mysqli_real_escape_string($mysqli, $_POST['master_id']);
164: }
165:
166: if (($_POST['address']) || ($_POST['city']) ||
167: ($_POST['state']) || ($_POST['zipcode'])) {
168: //something relevant, so add to address table
169: $add_address_sql = "INSERT INTO address (master_id,
170: date_added, date_modified, address, city, state,
171: zipcode, type) VALUES
172: ('".$master_id."', now(), now(),
173: '".$safe_address."', '".$safe_city."',
174: '".$safe_state."' , '".$safe_zipcode."' ,
175: '".$_POST['add_type']."')";
176: $add_address_res = mysqli_query($mysqli, $add_address_sql)
177: or die(mysqli_error($mysqli));
178: }
179:
180: if ($_POST['tel_number']) {
181: //something relevant, so add to telephone table
182: $add_tel_sql = "INSERT INTO telephone (master_id, date_added,
183: date_modified, tel_number, type) VALUES
184: ('".$master_id."', now(), now(),
185: '".$safe_tel_number."', '".$_POST['tel_type']."')";
186: $add_tel_res = mysqli_query($mysqli, $add_tel_sql)
187: or die(mysqli_error($mysqli));
188: }
189:
190: if ($_POST['fax_number']) {
191: //something relevant, so add to fax table
192: $add_fax_sql = "INSERT INTO fax (master_id, date_added,
193: date_modified, fax_number, type) VALUES
194: ('".$master_id."', now(), now(), '".$safe_fax_number."',
195: '".$_POST['fax_type']."')";
196: $add_fax_res = mysqli_query($mysqli, $add_fax_sql)
197: or die(mysqli_error($mysqli));
198: }
199: if ($_POST['email']) {
200: //something relevant, so add to email table
201: $add_email_sql = "INSERT INTO email (master_id, date_added,
202: date_modified, email, type) VALUES
203: ('".$master_id."', now(), now(), '".$safe_email."',
204: '".$_POST['email_type']."')";
205: $add_email_res = mysqli_query($mysqli, $add_email_sql)
206: or die(mysqli_error($mysqli));
207: }
208:
209: if ($_POST['note']) {
210: //something relevant, so add to notes table
211: $add_notes_sql = "UPDATE personal_notes set note =
212: '".$safe_note."', date_modified = now()
213: WHERE master_id = '".$master_id."'";
214: }
215: mysqli_close($mysqli);
216: $display_block = "<p>Your entry has been added. Would you
217: like to <a href=\"addentry.php\">add another</a>?</p>";
218: }
219: ?>
220: <!DOCTYPE html>
221: <head>
222: <title>Add an Entry</title>
223: </head>
224: <body>
225: <h1>Add an Entry</h1>
226: <?php echo $display_block; ?>
227: </body>
228: </html>
You can try out this revised script by selecting a record to view and then following the add info link. You should see a form like that shown in Figure 20.7.
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.
Summary
In this chapter, you applied your basic PHP and MySQL knowledge to create a personal address book. You learned how to create the database table and scripts for record addition, removal, and simple viewing. You also learned how to add multiple records attached to a single master entry.
Q&A
Q. What do I do if I want to add additional sections to my address book, such as entries for a person’s birthday or other information?
A. Different tables are used for address, telephone, fax, email, and personal notes because it is possible for a person to have more than one record containing those types of information. In the case of a person’s birthday, a person has just one of those, so a relational database is overkill because only one record would ever exist per user. So, to add a person’s birthday you should add a field to the master_name table. In the case of adding tables for other information, ask yourself whether a person will only ever have one instance of that information (such as birthday) or multiple instances (such as email addresses). If the latter case, create a table much like the address, telephone, fax, email, or personal_notes tables, which use master_id as a foreign key.
Workshop
The workshop is designed to help you review what you’ve learned and begin putting your knowledge into practice.
Quiz
1. When you are passing a variable through the query string, which superglobal does it belong in?
2. How many records in the address, email, telephone, and fax tables can you have for each individual in your master_name table?
3. Through which database field are additional records attached to a master record?
Answers
1. The $_GET superglobal.
2. As many as you want—it’s relational!
3. The master_id field.
Activities
1. Go through each of the administration scripts and modify the code so that a link to the menu prints at the bottom of each screen.
2. Use the second version of the addentry.php script to add secondary contact information to records in your database. Figure 20.8 shows what a record will look like after the script adds secondary contact information to it.
Figure 20.8 An individual’s record with multiple entries in tables.