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.

Figure 19.2. The record addition form.

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.

Figure 19.3. Adding a record.

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.