Creating the Table-Creation Script

This script will build a SQL statement and then send it to MySQL to create the my_music table.

Open a new file in your text editor and start a PHP block:
```
<?
```
Create a variable to hold the name of the database on which the table should reside:
```
$db_name = "testDB";
```

Add the connection information just as you have been doing:

$connection = @mysql_connect("localhost", "spike", "9sj7En4")
     or die(mysql_error());

Create a variable to hold the result of the mysql_select_db() function. Include the @ to suppress warnings, as well as the die() function to cause the script to end and a message to be displayed if the selection of the database fails:
```
$db = @mysql_select_db($db_name, $connection) or die(mysql_error());
```
Note
The mysql_select_db() function requires a database name and the link identifier for the current connection.
Start building the query by placing the initial syntax in a variable called $sql:
```
$sql = "CREATE TABLE $_POST[table_name] (";
```
Create a for loop to create the remainder of the SQL statement. The loop should repeat for the number of fields contained as elements in the $_POST[field_name] array:
```
for ($i = 0; $i < count($_POST[field_name]); $i++) {
```
Note
The count() function counts the number of elements in an array.
For each new field, you'll need to add the field name and type to the SQL statement:
```
$sql .= $_POST[field_name][$i]." ".$__POST[field_type][$i];
```
Because some field definitions will have a specific length and others will not, start an if…else block to handle this aspect. If a length is present, it must go inside parentheses, followed by a comma to start the next field definition:
```
if ($_POST[field_length][$i] != "") {
     $sql .= " (".$_POST[field_length][$i]."),";
```
If no length is present, just print the comma to separate the field definitions. Then close the if…else block:
```
} else {
     $sql .= ",";
}
```
Close the for loop:
```
}
```
The SQL statement held in $sql still needs some help. It should have an extraneous comma at the end of it, and the parentheses must be closed. Use the substr() function to return the entire string, with the exception of the last character:
```
$sql = substr($sql, 0, -1);
```
Note
The 0 in the substr() 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.
Close the parentheses:
```
$sql .= ")";
```
Create a variable to hold the result of the mysql_query() function. Include the @ to suppress warnings, as well as the die() function to cause the script to end and a message to be displayed if the query fails:
```
$result = mysql_query($sql,$connection) or die(mysql_error());
```
Test the value of $result. If it's true, the query was successful, and a variable is created to hold a message:
```
if ($result) {
     $msg = "<P>".$_POST[table_name]." has been created!</P>";
}
```

Close your PHP block and add HTML:

?>
<HTML>
<HEAD>
<TITLE>Create a Database Table: Step 3</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[db_name] variable:
```
<h1>Adding table to <? echo "$db_name"; ?>...</h1>
```
Print the message string:
```
<? echo "$msg"; ?>
```
Add some more HTML so that the document is valid:
```
</BODY>
</HTML>
```
Save the file with the name do_createtable.php, and place this file in the document root of your web server.

Your code 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[field_length][$i] != "") {
          $sql .= " (".$_POST[field_length][$i]."),";
     } else {
          $sql .= ",";
     }
}
//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>

Go on to the next step, where you get to click a button and create a table.