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.

Figure 19.2. The record addition form.

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.

Figure 19.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 are printed to the screen after successful record insertion.