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, that has two parts: what to do if the form should be displayed (lines 2 through 46) and what actions to take if the form is being submitted (lines 48 through 108). Lines 2 through 46 simply place the contents of the HTML form into a string called $display_block.

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:  <?php echo $display_block; ?>
116:  </BODY>
117:  </HTML>

As we've already noted, this script will perform one of two tasks at any given time: It will either show the record addition form, or it will perform 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[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 455. 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.

The else condition on Line 48 is invoked if the value of $_POST[op] is "add", meaning 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 5053 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 5659. 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 6163. 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, meaning they will occur only if some condition is true. In lines 6869, 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 7074 create and issue the query if this condition is met.

The same principle holds true for adding to the telephone table (lines 7782), the fax table (lines 8489), the email table (lines 9196), and the personal_notes table (lines 98103). 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 110117.

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.