Script to create a table and fields in SQL wont work
- by Jacksta
Warning this is lenghty! attack if you knowledagble. well at least more then a newb beginner like me.
This script uses three files as detailed below. It is suppoed to create the database and fields from the form input. It gets to the end and shows my_contacts has been created!. But when i go into phpMyadmin the table has not been created.
I have a file named show_createtable.html which is used to create a table in MySQL
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<h1>Step 1: Name and Number</h1>
<form method="post" action="do_showfielddef.php" />
<p><strong>Table Name:</strong><br />
<input type="text" name="table_name" size="30" /></p>
<p><strong>Number of fields:</strong><br />
<input type="text" name="num_fields" size="30" /></p>
<p><input type="submit" name="submit" value="go to step2" /></p>
</form>
</body>
</html>
This Form Posts to do_showfielddef.php
<?php
//validate important input
if ((!$_POST[table_name]) || (!$_POST[num_fields])) {
header( "location: show_createtable.html");
exit;
}
//begin creating form for display
$form_block = "
<form action=\"do_createtable.php\" method=\"post\">
<input name=\"table_name\" type=\"hidden\" value=\"$_POST[table_name]\">
<table cellspacing=\"5\" cellpadding=\"5\">
<tr>
<th>Field Name</th><th>Field Type</th><th>Table Length</th><th>Primary Key?</th><th>Auto-Increment?</th>
</tr>";
//count from 0 until you reach the number fo fields
for ($i = 0; $i <$_POST[num_fields]; $i++) {
$form_block .="
<tr>
<td align=center><input type=\"texr\" 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 aligh=center><input type=\"checkbox\" name=\"primary[]\" value=\"Y\"></td>
<td aligh=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>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Create a database table: Step 2</title>
</head>
<body>
<h1>defnie fields for <? echo "$_POST[table_name]"; ?>
</h1>
<? echo "$form_block"; ?>
</body>
</html>
Which in turn creates the table and fields with this file do_showfielddef.php
//connect to database
$connection = @mysql_connect("localhost", "user", "pass")
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 giid message for display upon success
if ($result) {
$msg = "<p>" .$_POST[table_name]." has been created!</p>";
}
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Create A Database Table: Step 3</title>
</head>
<body>
<h1>Adding table to <? echo "$db_name"; ?>...</h1>
<? echo "$msg"; ?>
</body>
</html>
