Adding Subentries to a Record

At this point, you've learned to add, delete, and view records. What's missing is adding those additional entries to the related tables?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 19.3, change lines 153?154 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 19.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 49?67 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 Listing 19.5.

Listing 19.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: <? print $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 19.7.