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 19.2, you can see a basic record addition script, called addentry.php.

Listing 19.2 Basic Record Addition Script Called addentry.php

  1: <?php
  2: if ($_POST[op] != "add") {
  3:      //haven't seen the form, so show it
  4:      $display_block = "<h1>Add an Entry</h1>
  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>
  9:  
 10:     <P><strong>Address:</strong><br>
 11:     <input type=\"text\" name=\"address\" size=30>
 12: 
 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>
 17: 
 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
 22:
 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
 28: 
 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
 34: 
 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
 40: 
 41:    <P><strong>Personal Note:</strong><br>
 42:    <textarea name=\"note\" cols=35 rows=5 wrap=virtual></textarea>
 43:     <input type=\"hidden\" name=\"op\" value=\"add\">
 44: 
 45:    <p><input type=\"submit\" name=\"submit\" value=\"Add Entry\"></p>
 46:    </FORM>";
 47: 
 48: } else if ($_POST[op] == "add") {
 49:    //time to add to tables, so check for required fields
 50:     if (($_POST[f_name] == "") || ($_POST[l_name] == "")) {
 51:        header("Location: addentry.php");
 52:        exit;
 53:     }
 54: 
 55:     //connect to database
 56:     $conn = mysql_connect("localhost", "joeuser", "somepass")
 57:         or die(mysql_error());
 58:     mysql_select_db("testDB",$conn) or die(mysql_error());
 59: 
 60:     //add to master_name table
 61:     $add_master = "insert into master_name values ('', now(), now(),
 62:         '$_POST[f_name]', '$_POST[l_name]')";
 63:     mysql_query($add_master) or die(mysql_error());
 64: 
 65:     //get master_id for use with other tables
 66:     $master_id = mysql_insert_id();
 67: 
 68:     if (($_POST[address]) || ($_POST[city]) || ($_POST[state]) ||
 69:          ($_POST[zipcode])) {
 70:         //something relevant, so add to address table
 71:         $add_address = "insert into address values ('', $master_id,
 72:              now(), now(), '$_POST[address]', '$_POST[city]',
 73:              '$_POST[state]', '$_POST[zipcode]', '$_POST[add_type]')";
 74:         mysql_query($add_address) or die(mysql_error());
 75:     }
 76: 
 77:     if ($_POST[tel_number]) {
 78:         //something relevant, so add to telephone table
 79:         $add_tel = "insert into telephone values ('', $master_id,
 80:          now(), now(), '$_POST[tel_number]', '$_POST[tel_type]')";
 81:         mysql_query($add_tel) or die(mysql_error());
 82:     }
 83: 
 84:     if ($_POST[fax_number]) {
 85:         //something relevant, so add to fax table
 86:         $add_fax = "insert into fax values ('', $master_id, now(),
 87:          now(), '$_POST[fax_number]', '$_POST[fax_type]')";
 88:         mysql_query($add_fax) or die(mysql_error());
 89:     }
 90: 
 91:     if ($_POST[email]) {
 92:         //something relevant, so add to email table
 93:         $add_email = "insert into email values ('', $master_id,
 94:              now(), now(), '$_POST[email]', '$_POST[email_type]')";
 95:         mysql_query($add_email) or die(mysql_error());
 96:     }
 97:
 98:     if ($_POST[note]) {
 99:         //something relevant, so add to notes table
100:         $add_note = "insert into personal_notes values ('', $master_id,
101:              now(), now(), '$_POST[note]')";
102:         mysql_query($add_note) or die(mysql_error());
103:     }
104: 
105:     $display_block = "<h1>Entry Added</h1>
106:     <P>Your entry has been added. Would you like to
107:     <a href=\"addentry.php\">add another</a>?</p>";
108: }
109: ?>
110: <HTML>
111: <HEAD>
112: <TITLE>Add an Entry</TITLE>
113: </HEAD>
114: <BODY>
115: <? print $display_block; ?>
116: </BODY>
117: </HTML>

This script will perform one of two tasks at any given time: It either shows the record addition form, or it performs all the SQL queries related to adding the record. The logic that determines the task begins at line 2, with a test for the value of $_POST[op]. If the value of $_POST[op] is not "add", the user is not coming from the form and therefore needs to see the form. The HTML for the form is placed in a string called $display_block, from lines 4?55. The script then breaks out of the if...else construct and jumps down to line 110, which outputs the HTML and prints the value of $display_block, in this case the form. This outcome is shown in Figure 19.2.

Line 48 begins the second condition if the value of $_POST[op] is "add", meaning the user has submitted the form. For the sake of argument, two fields have been designated as required fields: the first name and last name of the person. So, lines 50?53 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 lines 56?59. 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 61?63. After the insertion is made, the id of this record is extracted using mysql_insert_id() on line 66. 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. This means that they occur only if some condition is true. In lines 68?69, 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 70?74 create and issue the query if the condition is met.

The same principle holds true for adding to the telephone table (lines 77?82), the fax table (lines 84?89), the email table (lines 91?96), and the personal_notes table (lines 98?103). 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 110?117.

An output of the record addition script is shown in Figure 19.3.

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.