|
|
A two-step form sequence for creating a database table might seem like overkill. After all, you saw a basic table-creation SQL statement in Chapter 1, when you created test_table:
create table test_table (test_id int, test_note text);
When using a PHP script to create a table, all you're doing is sending the exact same query to MySQL. However, you can tie a pretty ribbon around the process (creating a form-based interface) and call it an administrative interface!
In the process of creating the administrative interface, you'll start with an HTML form, and then create a PHP script that takes information from that form and dynamically creates another form. Finally, you'll create a script that sends the actual SQL query.
This HTML form will contain two input fields: one for the name of the table, and one for the number of fields you want your table to contain.
Open a new file in your text editor and type the following HTML:
<HTML> <HEAD> <TITLE>Create a Database Table: Step 1</TITLE> </HEAD> <BODY> <H1>Step 1: Name and Number</H1>
Begin your form. Assume that the method is POST and the action is a script called do_showfielddef.php:
<FORM METHOD="POST" ACTION="do_showfielddef.php">
Create an input field for the table name with a text label:
<P><strong>Table Name:</strong><br> <INPUT TYPE="text" NAME="table_name" SIZE=30></P>
Create an input field for the number of fields in the table with a text label:
<P><strong>Number of Fields:</strong><br> <INPUT TYPE="text" NAME="num_fields" SIZE=5></P>
Add a submit button, and then close your form and add some more HTML so that the document is valid:
<P><INPUT TYPE="submit" NAME="submit" VALUE="Go to Step 2"></P> </FORM> </BODY> </HTML>
Save the file with the name show_createtable.html, and place this file in the document root of your web server.
Open your web browser and type http://127.0.0.1/show_createtable.html.
In the next section, you follow step 2 of the process, and create the script that dynamically creates another form based on the values of $_POST[table_name] and $_POST[num_fields].
In step 1, you created variables to hold the name of the table ($_POST[table_name]) and the number of fields you want to place in the table ($_POST[num_fields]). In this step, you create a PHP script to display additional form elements needed for further definition of the fields: name, type, and length.
Open a new file in your text editor and start a PHP block:
<?
Check that values were actually entered for $_POST[table_name] and $_POST[num_fields]. If they weren't, direct the user back to the form and exit the script:
Start building a string called $form_block, starting with the form action and method. Assume that the method is POST and the action is a script called do_createtable.php. Remember to escape your quotation marks!
$form_block = " <FORM METHOD=\"POST\" ACTION=\"do_createtable.php\">
| Note |
Because the script is creating the next form on-the-fly (dynamically), build one big string so that you can echo just the string after the complicated parsing has taken place. This way you won't be stuck with a half-built page that won't be displayed if an error occurs. |
Add a hidden field to hold the value of $_POST[table_name], which you'll use at the end of the sequence just to show the user that the proper table has been created:
<INPUT TYPE=\"hidden\" NAME=\"table_name\" VALUE=\"$_POST[table_name]\">
Display your form in an HTML table so that the fields line up nicely. Start with a row of column headings, and close the $form_block string for now:
<TABLE CELLSPACING=5 CELLPADDING=5> <TR> <TH>FIELD NAME</TH><TH>FIELD TYPE</TH><TH>FIELD LENGTH</TH></TR>";
Start a for loop to handle the creation of the form fields. Like a while loop, a for loop continues as long as a condition is true. In this case, the for loop starts out with the variable $i having a value of 0, and it continues for as long as $i is less than the value of $_POST[num_fields]. After each loop, $i is incremented by 1:
for ($i = 0; $i <$_POST[num_fields]; $i++) {
Within the for loop, you'll add to the original $form_block. You'll add one row for each field you want to have in your database table. Start with the table row tag and a table data cell containing an input type for the field name:
$form_block .= " <TR> <TD ALIGN=CENTER><INPUT TYPE=\"text\" NAME=\"field_name[]\" SIZE=\"30\"></TD>
| Note |
The use of brackets ([]) after field_name in your input field indicates an array. For each field you define in this form, you'll be adding a value to the $_POST[field_name] array. An array holds many variables in numbered slots, beginning with 0. Slots are added automatically as the array grows. For example, if you are creating a database table with six fields, the $_POST[field_name] array will be made up of six field name variables: $_POST[field_name][0], $_POST[field_name][1], $_POST[field_name][2], $_POST[field_name][3], $_POST[field_name][4], and $_POST[field_name][5]. |
In the next table data cell, create a drop-down list containing some common field types:
<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>
In the final table data cell, create a text field for the length of the field, and close your table row. Also close the $form_block string, because you're done with it for now:
<TD ALIGN=CENTER><INPUT TYPE=\"text\" NAME=\"field_length[]\" SIZE=\"5\"></TD> </TR>";
Close the for loop:
Add the final chunk of HTML to the $form_block string. You'll add one row that holds the submit button, and then close your table and form:
$form_block .= " <TR> <TD ALIGN=CENTER COLSPAN=3><INPUT TYPE=\"submit\" VALUE=\"Create Table\"></TD> </TR> </TABLE> </FORM>";
Close the PHP block and type the following HTML:
?> <HTML> <HEAD> <TITLE>Create a Database Table: Step 2</TITLE> </HEAD> <BODY>
Add a nice heading so that the users know what they are viewing. Mingle HTML and PHP to include the value of the $_POST[table_name] variable:
<H1>Define fields for <? echo "$_POST[table_name]"; ?></H1>
Display the contents of $form_block:
<? echo "$form_block"; ?>
Add some more HTML so that the document is valid:
</BODY> </HTML>
Save the file with the name do_showfielddef.php, and place this file in the document root of your web server.
Your code should look something like this:
<?
//validate important input
if ((!$_POST[table_name]) || (!$_POST[num_fields])) {
header("Location: 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></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>
</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>
In the next section, you prepare to create the table.
You should be able to go from step 1 (naming the table and providing the number of fields) to step 2 (defining the fields) without any problems. Let's try it out.
Open your web browser to http://127.0.0.1/show_createtable.html.
In the Table Name field, type my_music.
In the Number of Fields field, type 8.
Click the Go to Step 2 button. You should see the form shown in the following figure.
There are eight rows, corresponding to the eight fields you want to create in the my_music table. Populate those fields, but hold off on pressing the Create Table button, because you haven't created the script yet!
In the first row, type id for the Field Name, select int from the Field Type drop-down menu, and specify a Field Length of 5.
In the second row, type format for the Field Name, select char from the Field Type drop-down menu, and specify a Field Length of 2.
In the third row, type title for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 150.
In the fourth row, type artist_fn 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 artist_ln 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 rec_label for the Field Name, select varchar from the Field Type drop-down menu, and specify a Field Length of 50.
In the seventh row, type my_notes for the Field Name and select text from the Field Type drop-down menu.
In the eighth row, type date_acq for the Field Name and select date from the Field Type drop-down menu.
| Note |
No field length is specified for the my_notes field, because it is a text field and thus no length is used in its definition, as you learned. Similarly, no length is specified for the date_acq field, because data in these fields is stored in the specific 0000-00-00 format. |
The completed form should look like the following figure.
In the next section, you create the back-end script for this form so that you can click that button and create the table.
|
|