Defining the my_contacts Table

Take a moment to think about the kinds of things you'd want in a contact management system: names, addresses, telephone numbers of all sorts, e-mail addresses, and maybe even the person's birthday.

I thought about what I wanted for my own table, which I've decided to call my_contacts. This information appears in Table 18.1.

Table 18.1: Fields for my_contacts
Field Name	Description
id	Creates a unique ID number for the entry
f_name	The person's first name
l_name	The person's last name
address1	First line of the address
address2	Second line of the address
address3	Third line of the address
postcode	ZIP or postal code
country	Country in which the person resides
prim_tel	Primary telephone number
sec_tel	Secondary telephone number
email	E-mail address
birthday	The person's birthday

In the next section, you'll modify the table-creation scripts from Chapter 12, "Creating a Database Table." You'll add the capability to name primary keys and auto-incrementing fields.

Modifying the Table-Creation Scripts

With a few minor modifications to two of the three scripts in the table-creation sequence from Chapter 12, you can add check boxes to the form to handle primary keys and auto-incrementing fields. These types of fields are incredibly useful for ID fields.

Open do_showfielddef.php in your text editor and find the section of $form_block that prints table headings, and add the following before the end of the row:
```
<TH>PRIMARY KEY?</TH><TH>AUTO-INCREMENT?</TH>
```
In the $form_block within the for loop, the next-to-last line prints a text field with a name of field_length[]. After that line, and before the end of the table row, add these two lines:
```
<TD ALIGN=CENTER><INPUT TYPE=\"checkbox\" NAME=\"primary[]\"
VALUE=\"Y\"></TD>
<TD ALIGN=CENTER><INPUT TYPE=\"checkbox\" NAME=\"auto_increment[]\"
VALUE=\"Y\"></TD>
```
Save this file.

Your modified code for this script should look something like this:

<?
//validate important input
if ((!$_POST[table_name]) || (!$_POST[num_fields])) {
     header("Location: http://127.0.0.1/show_createtable.html");
     exit;
}
//begin creating form for display
$form_block = "
<FORM METHOD=\"POST\" ACTION=\"do_createtable.php\">
<INPUT TYPE=\"hidden\" NAME=\"table_name\" VALUE=\"$_POST[table_name]\">
<TABLE CELLSPACING=5 CELLPADDING=5>
<TR>
<TH>FIELD NAME</TH><TH>FIELD TYPE</TH><TH>FIELD LENGTH</TH>
<TH>PRIMARY KEY?</TH><TH>AUTO-INCREMENT?</TH></TR>";

//count from 0 until you reach the number of fields
for ($i = 0; $i <$_POST[num_fields]; $i++) {
     //add to the form, one row for each field
     $form_block .= "<TR>
     <TD ALIGN=CENTER><INPUT TYPE=\"text\"
     NAME=\"field_name[]\" SIZE=\"30\"></TD>
     <TD ALIGN=CENTER>
     <SELECT NAME=\"field_type[]\">
          <OPTION VALUE=\"char\">char</OPTION>
          <OPTION VALUE=\"date\">date</OPTION>
          <OPTION VALUE=\"float\">float</OPTION>
          <OPTION VALUE=\"int\">int</OPTION>
          <OPTION VALUE=\"text\">text</OPTION>
          <OPTION VALUE=\"varchar\">varchar</OPTION>
     </SELECT>
     </TD>
     <TD ALIGN=CENTER><INPUT TYPE=\"text\"
     NAME=\"field_length[]\" SIZE=\"5\"></TD>
     <TD ALIGN=CENTER><INPUT TYPE=\"checkbox\"
     NAME=\"primary[]\" VALUE=\"Y\"></TD>
     <TD ALIGN=CENTER><INPUT TYPE=\"checkbox\"
     NAME=\"auto_increment[]\" VALUE=\"Y\"></TD>
     </TR>";
}

//finish up the form
$form_block .= "<TR>
<TD ALIGN=CENTER COLSPAN=3><INPUT TYPE=\"submit\" VALUE=\"Create Table\"></TD>
</TR>
</TABLE>
</FORM>";
?>
<HTML>
<HEAD>
<TITLE>Create a Database Table: Step 2</TITLE>
</HEAD>
<BODY>
<H1>Define fields for <? echo "$_POST[table_name]"; ?></H1>
<? echo "$form_block"; ?>
</BODY>
</HTML>

Next, you will modify the final part of the table-creation script.

Open do_createtable.php in your text editor.
Within the for loop, the first line appends text to the $sql variable, which holds the SQL statement for table creation. Because you've added two check boxes for additional elements of the SQL statement, you need to check for them. Start by creating an if…else block that checks whether the auto_increment check box has been checked:
```
if ($_POST[auto_increment][$i] == "Y") {
```
If the auto_increment check box has been checked, create a variable to hold additional SQL options:
```
$additional = "NOT NULL auto_increment";
```
Note
When you define a field as auto_increment, it must also be defined as NOT NULL.
If the auto_increment check box hasn't been checked, create the variable but do not place any text in it, and then close the block. This will assist in resetting the value of the string to an empty value as the looping continues:
```
} else {
     $additional = "";
}
```
Create an if…else block that checks whether the primary key check box has been checked:
```
if ($_POST[primary][$i] == "Y") {
```
If the primary key check box has been checked, append the primary key syntax to the $additional variable:
```
$additional .= ", primary key (".$_POST[field_name][$i].")";
```
Note
The syntax for naming a field as a primary key is separated by a comma from the initial field definition. It looks something like this:

primary key (field_name)
If the primary key check box hasn't been checked, append an empty value to the $additional value, and then close the block:
```
} else {
     $additional = "";
}
```
The last change is to the preexisting loop that checks for field length and creates part of the SQL statement. Find the line that looks like this:
```
$sql .= " (".$_POST[field_length][$i]."),";
```
Change the line so that it looks like the following. This ensures that the $additional string is placed in the proper section of the SQL statement:
```
$sql .= " (".$_POST[field_length][$i].") $additional ,";
```
Similarly, find a line that looks like this:
```
$sql .= ",";
```
Change the line so that it looks like the following:
```
$sql .= " $additional ,";
```
Save the file.

Your modified code for this script should look something like this:

<?
//indicate the database you want to use
$db_name = "testDB";

//connect to database
$connection = @mysql_connect("localhost", "spike", "9sj7En4")
     or die(mysql_error());
$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

//start creating the SQL statement
$sql = "CREATE TABLE $_POST[table_name] (";

//continue the SQL statement for each new field
for ($i = 0; $i < count($_POST[field_name]); $i++) {
     $sql .= $_POST[field_name][$i]." ".$_POST[field_type][$i];

     if ($_POST[auto_increment][$i] == "Y") {
         $additional = "NOT NULL auto_increment";
     } else {
         $additional = "";
     }
     if ($_POST[primary][$i] == "Y") {
          $additional .= ", primary key (".$_POST[field_name][$i].")";
     } else {
          $additional = "";
     }

     if ($_POST[field_length][$i] != "") {
          $sql .= " (".$_POST[field_length][$i].") $additional ,";
     } else {
          $sql .= " $additional ,";
     }

}

//clean up the end of the string
$sql = substr($sql, 0, -1);
$sql .= ")";

//execute the query
$result = mysql_query($sql,$connection) or die(mysql_error());

//get a good message for display upon success
if ($result) {
     $msg = "<P>".$_POST[table_name]." has been created!</P>";
}
?>
<HTML>
<HEAD>
<TITLE>Create a Database Table: Step 3</TITLE>
</HEAD>
<BODY>
<h1>Adding table to <? echo "$db_name"; ?>...</h1>
<? echo "$msg"; ?>
</BODY>
</HTML>

In the next section, you use these new scripts to create the my_contacts table.

Creating the my_contacts Table

It's time to create the my_contacts table, complete with one primary key and auto- incrementing field!

Open your web browser and type http://127.0.0.1/show_createtable.html.
In the Table Name field, type my_contacts.
In the Number of Fields field, type 12.
Click on the Go to Step 2 button.

You will see a form with 12 rows, corresponding to the 12 fields you want to create in the my_contacts table. Populate the fields in these next steps:

In the first row, type id for the Field Name, select int from the Field Type drop- down menu, check the check box for Primary Key, and check the check box for Auto-Increment.
In the second row, type f_name for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 75.
In the third row, type l_name for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 75.
In the fourth row, type address1 for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 100.
In the fifth row, type address2 for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 100.
In the sixth row, type address3 for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 100.
In the seventh row, type postcode for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 25.
In the eighth row, type country for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 100.
In the ninth row, type prim_tel for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 35.
In the tenth row, type sec_tel for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 35.
In the eleventh row, type email for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 100.
In the twelfth row, type birthday for the Field Name and select date from the Field Type drop-down menu.

The completed form should look like the following figure.

Click on the Create Table button to create the my_contacts table.

Note

This is a very generic schema for an address book, and obviously you can see where it could be tightened up and made more specific. For example, if you knew your address book was United States-only, you could change the address fields to be two address fields, a two-character state field, and a 10-character ZIP code field. The more you know about the data you want to hold in your table, the more precisely you can define it. In this case, the goal is simply to learn the process in general, and in general this is an adequate—if not a little loose—table structure. After mastering it, branch out on your own!

Congratulations! The table has been created. In the next chapter, you create the record addition interface for this table. You are well on your way to creating a contact management system!