creating mysql tables with variable table names

Question

I am trying to create a platform where tables can be created from a php from and named as the variable that the submitter types. The code I have here has causes a mysql syntax error. I believe it is a matter of parenthesis placement but every combination I have tryed ha been unsuccessful can anyone figure it out? I have taken out the incorrect parenthesis to make it less confusing

    <?php

if (isset($_POST['submit']))
{ 
$name=$_POST['name'];

mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());


mysql_query("CREATE TABLE '$name'(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(30),
age INT)")
or die(mysql_error());  

echo "Table Created!";

?>   <html><form method='POST'>..........</html>

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 ''test'( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), name VARCHA' at line 1 — user1557515
– user1557515, Commented Jul 27, 2012 at 15:41
Use backticks around $name in your query text, not single quotes. The backtick is the default delimiter for object names in MySQL. — spencer7593
– spencer7593, Commented Jul 27, 2012 at 15:55

spencer7593 · Accepted Answer · 2012-07-27 15:57:27Z

2

Use backticks around the table name in your SQL text:

mysql_query("CREATE TABLE `$name`(

The backticks are the default delimiter for identifiers in MySQL. (Note: it is possible to enable other delimiters, but you don't really want to go there.)

The backticks are required if the identifier is a reserved word, contains white space, etc. (The backticks can be omitted in a lot of cases, but it's not wrong to use them when they aren't required. Basically, think of the rule as "always use backticks around identifiers", and omit them when its convenient and you are sure they aren't required.)

answered Jul 27, 2012 at 15:57

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rsz · Accepted Answer · 2014-05-15 09:50:51Z

1

This might work:

if (isset($_POST['submit']))
{ 
$name=$_POST['name'];

mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());


mysql_query("CREATE TABLE ".$name."(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(30),
age INT)")
or die(mysql_error());  

echo "Table Created!";
}
?>

I have tested it and no problems there... it creates the table with this structure.

edited May 15, 2014 at 9:50

answered Jul 27, 2012 at 15:41

rsz

1,1611 gold badge18 silver badges38 bronze badges

Comments

mlishn · Accepted Answer · 2012-07-27 15:42:04Z

0

CHANGE TO : mysql_query("CREATE TABLE ".$name."(

answered Jul 27, 2012 at 15:42

mlishn

1,66714 silver badges19 bronze badges

Comments

Mike Brant · Accepted Answer · 2012-07-27 15:43:34Z

0

You don't single quote the table name. Place it in back-ticks so that it can handle even names that match reserved words or characters. Like this

mysql_query("CREATE TABLE `$name`(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(30),
age INT)")

You could also do without back-ticks like sin other suggested answers, but this is more reliable if you don;t want to have to check the table name against a bunch of MySQL reserved words first.

answered Jul 27, 2012 at 15:43

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Comments

tims · Accepted Answer · 2012-07-27 16:43:23Z

0

Unless you trust the input data completely, it's very important that you validate $name to protect against SQL injection.

answered Jul 27, 2012 at 16:43

tims

831 silver badge4 bronze badges

Collectives™ on Stack Overflow

creating mysql tables with variable table names

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related