php_mysql_apache [Electronic resources] نسخه متنی

Adding Subentries to a Record

At this point, you've learned to add, remove, and view records. What's missing is adding those additional entries to the related tablesentries for home versus work telephone number, for example. All you need to do is make a few changes to existing scripts.Listing 18.3, change lines 153154 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 called $master_id.

Now we need to modify the addentry.php script in Listing 18.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:

<?php
if (($_POST[op] != "add") || ($_GET[master_id] != ")) {
//haven't seen the form, so show it
$display_block = "
<h1>Add an Entry</h1>
<form method="post" action="$_SERVER[PHP_SELF]">";
if ($_GET[master_id] != ") {
//connect to database
$conn = mysql_connect("localhost", "joeuser", "somepass")
or die(mysql_error());
mysql_select_db("testDB",$conn)  or die(mysql_error());
//get first, last names for display/tests validity
$get_names = "select concat_ws(' ', f_name, l_name) as
display_name from master_name where id = $_GET[master_id]";
$get_names_res = mysql_query($get_names) or die(mysql_error());
if (mysql_num_rows($get_names_res) == 1) {
$display_name = mysql_result($get_names_res,0,'display_name');
}
}
if ($display_name != ") {
$display_block .= "<P>Adding information for
<strong>$display_name</strong>:</p>";
} 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:

<input type="hidden" name="op" value="add">

Beneath it, add the following:

<input type="hidden" name="master_id" value="$_GET[master_id]">

This modification ensures the known value of master_id is passed along to the next task.

Identify what were lines 4967 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] == ")) &&
($_POST[master_id] == ")) {
header("Location: addentry.php");
exit;
}
//connect to database
$conn = mysql_connect("localhost", "joeuser", "somepass")
or die(mysql_error());
mysql_select_db("testDB",$conn)  or die(mysql_error());
if ($_POST[master_id] == ") {
//add to master_name table
$add_master = "insert into master_name values ('', now(),
now(), '$_POST[f_name]', '$_POST[l_name]')";
mysql_query($add_master) or die(mysql_error());
//get master_id for use with other tables
$master_id = mysql_insert_id();
} 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 REPLACE into to handle an update of the notes field.

The new script should look like Listing 18.5.

Listing 18.5 New addentry.php Script

1: <?php
2: if (($_POST[op] != "add") || ($_GET[master_id] != ")) {
3:    //haven't seen the form, so show it
4:    $display_block = "
5:    <h1>Add an Entry</h1>
6:    <form method="post" action="$_SERVER[PHP_SELF]">";
7:
8:     if ($_GET[master_id] != ") {
9:        //connect to database
10:        $conn = mysql_connect("localhost", "joeuser", "somepass")
11:                     or die(mysql_error());
12:        mysql_select_db("testDB",$conn)  or die(mysql_error());
13:
14:        //get first, last names for display/tests validity
15:        $get_names = "select concat_ws(' ', f_name, l_name) as
16:                display_name from master_name where id = $_GET[master_id]";
17:        $get_names_res = mysql_query($get_names) or die(mysql_error());
18:
19:        if (mysql_num_rows($get_names_res) == 1) {
20:            $display_name = mysql_result($get_names_res,0,'display_name');
21:        }
22:    }
23:
24:    if ($display_name != ") {
25:        $display_block .= "<P>Adding information for
26:                       <strong>$display_name</strong>:</p>";
27:    } else {
28:        $display_block .= "
29:      <P><strong>First/Last Names:</strong><br>
30:      <input type="text" name="f_name" size=30 maxlength=75>
31:      <input type="text" name="l_name" size=30 maxlength=75>";
32:   }
33:   $display_block .= "<P><strong>Address:</strong><br>
34:     <input type="text" name="address" size=30>
35:
36:     <P><strong>City/State/Zip:</strong><br>
37:     <input type="text" name="city" size=30 maxlength=50>
38:     <input type="text" name="state" size=5 maxlength=2>
39:     <input type="text" name="zipcode" size=10 maxlength=10>
40:
41:     <P><strong>Address Type:</strong><br>
42:     <input type="radio" name="add_type" value="home" checked> home
43:     <input type="radio" name="add_type" value="work"> work
44:    <input type="radio" name="add_type" value="other"> other
45:
46:    <P><strong>Telephone Number:</strong><br>
47:    <input type="text" name="tel_number" size=30 maxlength=25>
48:    <input type="radio" name="tel_type" value="home" checked> home
49:    <input type="radio" name="tel_type" value="work"> work
50:    <input type="radio" name="tel_type" value="other"> other
51:
52:    <P><strong>Fax Number:</strong><br>
53:    <input type="text" name="fax_number" size=30 maxlength=25>
54:    <input type="radio" name="fax_type" value="home" checked> home
55:    <input type="radio" name="fax_type" value="work"> work
56:    <input type="radio" name="fax_type" value="other"> other
57:
58:    <P><strong>Email Address:</strong><br>
59:    <input type="text" name="email" size=30 maxlength=150>
60:    <input type="radio" name="email_type" value="home" checked> home
61:    <input type="radio" name="email_type" value="work"> work
62:    <input type="radio" name="email_type" value="other"> other
63:
64:    <P><strong>Personal Note:</strong><br>
65:    <textarea name="note" cols=35 rows=5 wrap=virtual></textarea>
66:    <input type="hidden" name="op" value="add">
67:    <input type="hidden" name="master_id" value="$_GET[master_id]">
68:
69:    <p><input type="submit" name="submit" value="Add Entry"></p>
70:    </FORM>";
71:
72: } else if ($_POST[op] == "add") {
73:   //time to add to tables, so check for required fields
74:   if ((($_POST[f_name] == ") || ($_POST[l_name] == ")) &&
75:   ($_POST[master_id] == ")) {
76:        header("Location: addentry.php");
77:        exit;
78:   }
79:
80:   //connect to database
81:  $conn = mysql_connect("localhost", "joeuser", "somepass")
82:  or die(mysql_error());
83:  mysql_select_db("testDB",$conn)  or die(mysql_error());
84:
85:   if ($_POST[master_id] == ") {
86:       //add to master_name table
87:       $add_master = "insert into master_name values ('', now(),
88:       now(), '$_POST[f_name]', '$_POST[l_name]')";
89:       mysql_query($add_master) or die(mysql_error());
90:      //get master_id for use with other tables
91:      $master_id = mysql_insert_id();
92:   } else {
93:      $master_id = $_POST[master_id];
94:   }
95:
96:    if (($_POST[address]) || ($_POST[city]) || ($_POST[state]) ||
97:         ($_POST[zipcode])) {
98:        //something relevant, so add to address table
99:        $add_address = "insert into address values ('', $master_id,
100:             now(), now(), '$_POST[address]', '$_POST[city]',
101:             '$_POST[state]', '$_POST[zipcode]', '$_POST[add_type]')";
102:        mysql_query($add_address) or die(mysql_error());
103:    }
104:
105:    if ($_POST[tel_number]) {
106:        //something relevant, so add to telephone table
107:        $add_tel = "insert into telephone values ('', $master_id,
108:         now(), now(), '$_POST[tel_number]',  '$_POST[tel_type]')";
109:        mysql_query($add_tel) or die(mysql_error());
110:    }
111:
112:    if ($_POST[fax_number]) {
113:        //something relevant, so add to fax table
114:        $add_fax = "insert into fax values ('', $master_id, now(),
115:         now(), '$_POST[fax_number]',  '$_POST[fax_type]')";
116:        mysql_query($add_fax) or die(mysql_error());
117:    }
118:
119:    if ($_POST[email]) {
120:        //something relevant, so add to email table
121:        $add_email = "insert into email values ('', $master_id,
122:             now(), now(), '$_POST[email]',  '$_POST[email_type]')";
123:        mysql_query($add_email) or die(mysql_error());
124:    }
125:
126:    if ($_POST[note]) {
127:        //something relevant, so add to notes table
128:        $add_note = "replace into personal_notes values ('', $master_id,
129:             now(), now(), '$_POST[note]')";
130:        mysql_query($add_note) or die(mysql_error());
131:    }
132:
133:    $display_block = "<h1>Entry Added</h1>
134:    <P>Your entry has been added.  Would you like to
135:     <a href="addentry.php">add another</a>?</p>";
136: }
137: ?>
138: <HTML>
139: <HEAD>
140: <TITLE>Add an Entry</TITLE>
141: </HEAD>
142: <BODY>
143: <?php echo $display_block; ?>
144: </BODY>
145: </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 Figure 18.7.

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