Chapter 4: Creating and Populating Database Tables

Hopefully, the previous chapter has given you a basic understanding of database connectivity with PHP. Now we'll move past the basic connection and use PHP and HTML forms to create database tables and populate these tables with data.

The following code examples use the MySQL database; however, each of the actions can be carried out on any supported database type. Check the PHP Manual at http://www.php.net/manual/ for the exact function name for your particular database-it won't be vastly different.

Create a Database Table

Essentially, to create a simple database table, you only need to give it a name. But that would make for a boring table, since it wouldn't contain any columns (fields) and couldn't hold any data. So, besides the name, you should know the number of fields and the types of fields you'd like to have in your table.

Suppose that you own a store, and you want to create a table to hold all your products. Think of the types of fields you might need: a product identification number, a title, a description, and the price of the item. Now, think of a name for the table, such as MY_PRODUCTS.

Next, you'll create a sequence of forms that will take your table information and send it to your MySQL database. In this first step, you'll submit the name of the table and the number of fields you want to include. The second step will display additional form fields so that you can define the properties of your table columns. Finally, the third step will send the request to MySQL, verify that the table was created, and display a "Success!" message.

Step 1: Basic Table Definition

To begin, open your favorite text editor, create a file called show_createtable1.html, and set up an HTML "shell":

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Create a Database Table: Step 1</TITLE> </HEAD> <BODY>              <!-- your HTML form will go here --> </BODY> </HTML> 

To create the form code, assume that step two in the sequence will be a PHP script called do_showfielddef.php and that your form will use the POST method:

 <FORM method="POST" action="do_showfielddef.php"> 

Next, create two text fields to capture the values for $_POST[table_name] and $_POST[num_fields]-the name of the new table and the number of fields it contains:

 <P>Table Name:<br><INPUT type="text" name="table_name" size=30></P> <P>Number of Fields:<br><INPUT type="text" name="num_fields" size=5></P> 

Finally, add the "Go to Step 2" button:

 <INPUT type="submit" value="Go to Step 2"> 

Don't forget the closing </FORM> tag!

Your HTML source code should look something like this:

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Create a Database Table: Step 1</TITLE> </HEAD> <BODY> <FORM method="POST" action="do_showfielddef.php"> <P>Table Name:<br><INPUT type="text" name="table_name" size=30></p> <P>Number of Fields:<br><INPUT type="text" name="num_fields" size=5></p> <INPUT type="submit" value="Go to Step 2"> </FORM> </BODY> </HTML> 

Place this file on your Web server, and access it with your browser at its URL, http://127.0.0.1/show_createtable1.html. In your browser window, you should now see what is shown in Figure 4.1.

click to expand
Figure 4.1: Step 1- Name that table

In Step 2, you'll dynamically create parts of the form based on the values sent through the form in Step 1.

Step 2: Field Definitions

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'll create a PHP script to display additional form elements needed for further definition of the fields. To begin, open your favorite text editor and create a file called do_showfielddef.php.

Before your script does anything else, you'll want to check that values were actually entered in the form in Step 1. Set up a statement that looks for these values and, if they don't exist, redirects the user to the form in Step 1:

 <?php if ((!$_POST[table_name]) || (!$_POST[num_fields])) {              header("Location: http://127.0.0.1/show_createtable1.html");              exit; } ?> 

Next, add the HTML "shell" after the if statement:

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Create a Database Table: Step 2</TITLE> </HEAD> <BODY>              <!-- your HTML form will go here --> </BODY> </HTML> 

Now you're ready to build the form. First, though, give the page a heading so that you know what you're doing:

 <h1>Define fields for <?php echo "$_POST[table_name]"; ?></h1> 

To create the form code, assume that Step 3 in the sequence will be a PHP script called do_createtable.php and that your form will use the POST method:

 <FORM method="POST" action="do_createtable.php"> 

Next, add a hidden field to your form to ensure that the value of the $_POST[table_name] variable is passed along to Step 3:

 <INPUT type="hidden" name="table_name" value="<?php echo "$_POST[table_name]"; ?>"> 

The three basic field definitions are field name, field type, and field length. To create the table, you'll need to know these three elements for each field you want to create. For example, to create the MY_PRODUCTS table with four fields (product identification number, product title, product description, and product price), you'll need to provide a field name, field type, and field length for each of those four fields.

Create the beginning of an HTML table to display the three form fields:

 <table cellspacing=5 cellpadding=5> <tr> <th>FIELD NAME</th><th>FIELD TYPE</th><th>FIELD LENGTH</th></tr> 

Now you'll learn a tricky bit of PHP, which will create enough form fields to cover the number of fields you need to define in your database table.

Remember the $_POST[num_fields] variable from the first step? Create a for statement that will loop until that number is reached:

 for ($i = 0 ; $i < $_POST[num_fields]; $i++) {              // more code here } 

The goal is to display three form fields for each field you want to create in your database table. First, open the echo statement, and then start the table row and print the first input field, remembering to escape your double quotes with a backslash:

 echo "<tr> <td align=center> <input type=\"text\" name=\"field_name[]\" size=\"30\"> </td> 

Note the use of [] after field_name. The [] indicates the presence of an array. For each field in your database table, you'll be adding a value to the $_POST[field_name] array. An array holds many scalar variables in numbered slots, beginning with 0. Slots are added automatically as the array needs to grow.

For example, if you are creating a database table with six fields, the $_POST[field_name] array would be made up of six field name variables:

 $field_name[0] // first field name $field_name[1] // second field name ... $field_name[5] // sixth field name 

After creating the first input field, create a drop-down list containing a few field types. The field types used in this example (float, int, text, varchar) are very common field types and are all that's needed for this example. A complete list of valid field types can be found in your database documentation.

 <td align=center> <select name=\"field_type[]\">              <option value=\"float\">float</option>              <option value=\"int\">int</option>              <option value=\"text\">text</option>              <option value=\"varchar\">varchar</option> </select> </td> 

The last field definition is field length. Create a text field for this value, and close your table row as well as the for statement:

 <td align=center> <input type=\"text\" name=\"field_length[]\" size=\"5\"> </td> </tr>"; } 

Putting it all together, your for statement should look something like this:

 for ($i = 0 ; $i < $_POST[num_fields]; $i++) {              echo "              <tr>              <td align=center>              <input type=\"text\" name=\"field_name[]\" size=\"30\">              </td>              <td align=center>              <select name=\"field_type[]\">                   <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>"; } 

To finish this step, create the submit button and close the form and the HTML table:

 <tr> <td align=center colspan=3> <INPUT type="submit" value="Create Table"></td> </tr> </table> </FORM> </BODY> </HTML> 

All in all, the do_showfielddef.php file should look something like this:

 <?php if ((!$_POST[table_name]) || (!$_POST[num_fields])) {              header("Location: http://127.0.0.1/show_createtable1.html");              exit; } ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Create a Database Table: Step 2</TITLE> </HEAD> <BODY> <h1>Define fields for <?php echo "$_POST[table_name]"; ?></h1> <FORM method="POST" action="do_createtable.php"> <INPUT type="hidden" name="table_name" value="<?php echo "$_POST[table_name]"; ?>"> <table cellspacing=5 cellpadding=5> <tr> <th>FIELD NAME</th><th>FIELD TYPE</th><th>FIELD LENGTH</th></tr> <?php for ($i = 0 ; $i <$num_fields; $i++) {              echo "              <tr>              <td align=center>              <input type=\"text\" name=\"field_name[]\" size=\"30\">              </td>              <td align=center>              <select name=\"field_type[]\">              <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>"; } ?> <tr> <td align=center colspan=3> <INPUT type="submit" value="Create Table"> </td> </tr> </table> </FORM> </BODY> </HTML> 

Place this file on your Web server, and go back and access the form in Step 1 at its URL, http://127.0.0.1/show_createtable1.html. Enter MY_PRODUCTS for a table name and 4 for the number of fields. In your browser window, you should now see what is shown in Figure 4.2.

click to expand
Figure 4.2: Step 2- Form field definition table

Step 3: Connect to MySQL and Create the Table

Before you fill out the form created in Step 2, let's create the PHP script that will make it "go." Since the action of the form in Step 2 is do_createtable.php, open your favorite text editor and create a file called do_createtable.php, then add the HTML "shell":

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Create a Database Table: Step 3</TITLE> </HEAD> <BODY>              <!-- your HTML form will go here --> </BODY> </HTML> 

Give the page a heading so that you know what you're doing:

 <h1>Adding table <?php echo "$_POST[table_name]"; ?></h1> 

The next section of PHP code will build the SQL statement that will be sent to MySQL. Remember, the CREATE syntax is

 CREATE TABLE [table name] [(field_name field_data_type,...)] [options] 

Hold the SQL statement in a variable called $sql, and initially populate this variable with the first part of the CREATE statement plus the value of $table_name:

 $sql = "CREATE TABLE $table_name ("; 

Now create the loop that will populate the remainder of the SQL statement. The loop should repeat for as many fields as you want to add to the table, or the number of fields that you defined in Step 2. Since each field definition was placed in an array, you can count the number of elements in the $_POST[field_name] array to get the number of times to run the loop:

 for ($i = 0; $i < count($_POST[field_name]); $i++) { // more code here } 

For each new field, you'll need to add the field name, type, and length to the SQL statement using this syntax:

 field_name field_type (field_length) 

Note 

A comma must separate multiple field definitions.

Immediately inside the loop, add this statement to begin adding to the value of the $sql variable:

 $sql .= $_POST[field_name][$i]." ".$_POST[field_type][$i]; 

Before adding the field length, check to see that a length has been specified, add to the $sql variable accordingly, and then close the loop:

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

This if... else statement looks for a value for $_POST[field_length] and prints it inside a set of parentheses if it's found. Then it adds a comma to separate the value from the next field waiting to be added. If no value is found, just the comma is added to the SQL statement.

The entire loop should look something like this:

 for ($i =0;$i < count($_POST[field_name]);$i++){              $sql .= $_POST[field_name][$i]." ".$_POST[field_type][$i];              if ($_POST[field_length][$i] != "") {                   $sql .= " (".$_POST [field_length][$i]."),";              } else {                   $sql .= ",";              } } 

However, there's still a bit of work to do on the SQL statement: it has an extraneous comma at the end, and the parentheses have yet to be closed. To get rid of the extra comma at the end, use the substr() function to return only part of the string. In this case, you'll be returning the entire string, with the exception of the last character:

 $sql = substr($sql,  0, -1); 

The 0 in the argument list tells the function to begin at the first character, and the -1 tells the function to stop at the next-to-last character.

The final step in the creation of the SQL statement is to close the parentheses:

 $sql .= ")"; 

Now use the basic connection code to connect to and query the MySQL database using your table-creation SQL statement:

 // create connection; substitute your own information $conn = mysql_connect("localhost","joeuser","34Nhjp") or die(mysql_error())"; // select database; substitute your own database name $db = mysql_select_db("MyDB", $conn) or die(mysql_error()); // execute SQL query and get result $sql_result = mysql_query($sql, $conn) or die(mysql_error()); 

Print a nice message upon success:

 if ($sql_result) {              echo "<P>$_POST[table_name] has been created!</p>"; } 

From start to finish, the file do_createtable.php should look something like this:

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Create a Database Table: Step 3</TITLE></HEAD> <BODY> <h1>Adding table <?php echo "$_POST[table_name]"; ?></h1> <?php $sql = "CREATE TABLE $_POST[table_name] ("; for ($i =0;$i < count($_POST[field_name]);$i++){              $sql .= $_POST[field_name][$i]." ".$_POST[field_type][$i];              if ($_POST[field_length][$i] != "") {                   $sql .= " (".$_POST [field_length][$i]."),";              ) else {                   $sql .= ",";              } } $sql = substr($sql, 0, -1); $sql .= ")"; // create connection; substitute your own information $conn = mysql_connect("localhost","joeuser","34Nhjp") or die(mysql_error()); // select database; substitute your own database name $db = mysql_select_db("MyDB", $conn) or die(mysql_error()); // execute SQL query and get result $sql_result = mysql_query($sql, $conn) or die(mysql_error()); //print success message if ($sql_result) {              echo "<P>$_POST[table_name] has been created!</p>"; } ?> </BODY> </HTML> 

Place this file on your Web server, and then go back to your Web browser and the form staring back at you from Step 2. You should see four sets of form fields. Create the following fields:

 FIELD NAME   FIELD TYPE   FIELD SIZE ITEM_ID      int  5 ITEM_TITLE   varchar      50 ITEM_DESC    text ITEM_PRICE   float 

Before you submit the form, it should look something like what is shown in Figure 4.3.

click to expand
Figure 4.3: Preparing to submit the form

Go ahead and click on the Create Table button to execute the do_createtable.php script. You should see a results page like the one shown in Figure 4.4.

click to expand
Figure 4.4: Successful table creation

In the next section, you'll create a form and accompanying PHP script to populate the table. Tables aren't much fun without any data in them!



PHP Essentials
PHP Essentials, 2nd Edition
ISBN: 1931841349
EAN: 2147483647
Year: 2002
Pages: 74

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net