Create a Table with name from a form?

[poundandpence]

I have a form that a user enters a restaurant name into. The PHP code should create a MySQL table, using the posted value as the table name. I've spent hours looking over this, but I still keep getting an error, and the table does not get created. Thanks in advance.

-- ERROR -----------------------------

Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource on line 37

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource on line 37
: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''cars' (lineid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, item TEXT, descrip TEXT,' at line 1


-- FORM ------------------------------

<form name="form1" method="post" action="<?php echo $editFormAction; ?>">
<input type="text" name="restname" size="70"/>
<input type="submit" name="Submit" value="Add Restaurant" />
<input type="hidden" name="MM_insert" value="form1">
</form>


-- PHP --------------------------------

<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("CREATE TABLE %s (lineid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, item TEXT, descrip TEXT, price TEXT, empid INT(3))",
GetSQLValueString($_POST['restname'], "text"));

echo mysql_errno($insertSQL).":".mysql_error($insertSQL)."\n";

mysql_select_db($database_menusdb, $menusdb);
$Result = mysql_query($insertSQL, $menusdb) or die(mysql_error());

}
mysql_close($menusdb);
?>

[r937]

i don't do php but the error message

... near ''cars' (lineid INT ...

suggests that you are putting the table name inside single quotes, which is wrong

[kromey]

As far as your PHP errors go, you are trying to pass your SQL query to mysql_errno and mysql_error, which is wrong. You call them with no parameters to get information on the last error, just as you are doing on the line not generating a PHP error:

$Result = mysql_query($insertSQL, $menusdb) or die(mysql_error()); 

[Contrid]

It looks like you are doing this :

CREATE TABLE 'cars'

...where you should be doing this :

CREATE TABLE `cars`

Notice the single quotes in the first query.

[r937]

yeah, what i said

the backticks i.e. `cars` are required only if the name is a reserved word or contains special characters, neither of which is the case here (nor should it ever be), so don't put them in

[superuser2]

Please get in the habit of using syntax hilighting on your code; it makes it much more readable especially to those of us who are used to it that way :-).

Do you really want to create tables based on user input? You might try reading up on one-to-many and many-to-many relationships if that's what your after.

[webnoob]

It seems very odd to have a table for each user restaurant, why not create a table called Restaurants and just insert all the users restaurants in there, give them all a unique Id and you are sorted for anything you could possibly want to do.