Part IV: Getting to Know Your Database
Step 2: Defining Your Fields
12. In the eighth row, type date_acq for the Field Name and select date from the Field Type drop-down menu
9. 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.
10. 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.
11. In the seventh row, type my_notes for the Field Name and select text from the Field Type drop-down menu.
12. In the eighth row, type date_acq for the Field Name and select date from the Field Type drop-down menu.
In the next section, you'll create the backend script for this form so that you can click that button and create the table.
Creating the Table-Creation Script
This script will build a SQL statement and then send it to MySQL to create the my_music table.
1. Open a new file in your text editor and start a PHP block:
2. <?
3. Create a variable to hold the name of the database on which the table should reside:
4. $db_name = "testDB";
5. Add the connection information as you have been:
6. $connection = @mysql_connect("localhost", "spike", "9sj7En4") 7. or die(mysql_error());
8. 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 display if the selection of the database fails:
9. $db = @mysql_select_db($db_name, $connection) or die(mysql_error());
10.
Note The mysql_select_db() function requires a database name and the link identifier for the current connection.
11. Start building the query by placing the initial syntax in a variable called $sql:
12. $sql = "CREATE TABLE $_POST[table_name] (";
13. 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:
14. for ($i = 0; $i < count($_POST[field_name]); $i++) { 15.
Note The count() function counts the number of elements in an array.
16. For each new field, you'll need to add the field name and type to the SQL statement:
17. $sql .= $_POST[field_name][$i]." ".$_POST[field_type][$i];
18. Since 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:
19. if ($_POST[field_length][$i] != "") {
20. $sql .= " (".$_POST[field_length][$i]."),";
21. If no length is present, just print the comma to separate the field definitions.
Then close the if…else block:
22. } else {
23. $sql .= ",";
24. }
25. Close the for loop:
26. }
27. 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:
28. $sql = substr($sql, 0, -1);
29.
30.
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.
31. Close the parentheses:
32. $sql .= ")";
33. 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 display if the query fails:
34. $result = mysql_query($sql,$connection) or die(mysql_error());
35.
Note The mysql_query() function requires a SQL statement and the link identifier for the current connection.
36. Test the value of $result. If it's true, the query was successful, and a variable is created to hold a message:
37. if ($result) {
38. $msg = "<P>".$_POST[table_name]." has been created!</P>";
39. } 40.
Note If a connection cannot be made, the script will end with the die () function.
41. Close your PHP block and add HTML:
42. ?>
43. <HTML>
44. <HEAD>
45. <TITLE>Create a Database Table: Step 3</TITLE>
46. </HEAD>
47. <BODY>
48.
49. Add a nice heading so that the user knows what he's viewing. Mingle HTML and PHP to include the value of the $_POST [db_name] variable:
50. <h1>Adding table to <? echo "$db_name"; ?>...</h1>
51. Print the message string:
52. <? echo "$msg"; ?>
53. Add some more HTML so that the document is valid:
54. </BODY>
55.
56. </HTML>
57. Save the file with the name db_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.
Create That Table!
In the next chapter, you'll create an HTML form interface to a script that adds entries to the my_music table.