Create MySQL Table using PHP form

PHP
1

I am trying to setup a form so that a user can create a new mysql table.
I have setup the following scripts:

This is the form script:

<form name="form1" method="post" action="new-process.php" enctype="multipart/form-data">
<table width="80%" border="0" cellpadding="3" cellspacing="3">
  <tr>
    <td width="24%">Spreadsheet Name</td>
    <td width="29%"><input type="text" name="Name" id="Name"></td>
  </tr>
  <tr>
    <td>Column 1</td>
    <td><input type="text" name="ColumnA" id="ColumnA"></td>
  </tr>
  <tr>
    <td>Column 2</td>
    <td><input type="text" name="ColumnB" id="ColumnB"></td>
  </tr>
  <tr>
    <td>Column 3</td>
    <td><input type="text" name="ColumnC" id="ColumnC"></td>
  </tr>
  <tr>
    <td>Column 4</td>
    <td><input type="text" name="ColumnD" id="ColumnD"></td>
  </tr>
  <tr>
    <td>Column 5</td>
    <td><input type="text" name="ColumnE" id="ColumnE"></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td><input type="submit" name="Submit" id="Submit" value="Save Spreadsheet"></td>
  </tr>
</table>
</form>

This is my process script:


mysql_connect("localhost", "DBUsername", "DBPassword") or die(mysql_error());
mysql_select_db("DBName") or die(mysql_error());

$tablename = $_POST[Name];
$columnA = $_POST[ColumnA];
$columnB = $_POST[ColumnB];
$columnC = $_POST[ColumnC];
$columnD = $_POST[ColumnD];
$columnE = $_POST[ColumnE];


// Create a MySQL table in the selected database
mysql_query("CREATE TABLE '$DBName'.'$tablename'(
'Id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 '$columnA' TEXT NOT NULL,
 '$columnB' TEXT NOT NULL,
 '$columnC' TEXT NOT NULL,
 '$columnD' TEXT NOT NULL,
 '$columnE' TEXT NOT NULL)")
 or die(mysql_error());

echo "Table Created! and entered into logging system";

I keep getting the following error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

Can anyone help me with this please? Can’t work out where I’m going wrong.

Thanks in advance.

2

You did not define $DBName in your query. Also, you need to lose the apostrophes in the query.



$tablename = $_POST[Name];
$columnA = $_POST[ColumnA];
$columnB = $_POST[ColumnB];
$columnC = $_POST[ColumnC];
$columnD = $_POST[ColumnD];
$columnE = $_POST[ColumnE];

$DBName = "test";

$dbTable = $DBName.".".$tablename;


// Create a MySQL table in the selected database
$sql = "CREATE TABLE $dbTable (Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, $columnA TEXT NOT NULL, $columnB TEXT NOT NULL, $columnC TEXT NOT NULL, $columnD TEXT NOT NULL, $columnE TEXT NOT NULL)";

print $sql;

I always save the query name to a variable and do a print on it. I then run the query on the MySQL table to see what is wrong.

3

As a side remark, if your application needs to create database tables it usually means you’re doing something wrong.
I would recommend you to take a look at Database normalization.