I am having trouble creating a SQL table with a PHP variable.
Let's say $tbl_date = "data";
mysql_query('CREATE TABLE '.$tbl_date.'
(
Test varchar(15),
Yes varchar(15),
Very int
)');
It doesn't create the table.
-
1Did you try it yourself?liquorvicar– liquorvicar2012-04-16 12:36:01 +00:00Commented Apr 16, 2012 at 12:36
-
@liquorvicar, Yes I did. It won't create for some reason? It will create if I change it to a regular word.homework– homework2012-04-16 12:36:46 +00:00Commented Apr 16, 2012 at 12:36
-
Try echoing out 'CREATE TABLE '.$tbl_date.' ( Test varchar(15), Yes varchar(15), Very int )' and see what you get.Cameron– Cameron2012-04-16 12:38:13 +00:00Commented Apr 16, 2012 at 12:38
-
1Then use backticks (`) around the table name.dan-lee– dan-lee2012-04-16 12:38:16 +00:00Commented Apr 16, 2012 at 12:38
-
@Homework Chances are the name you were trying to use is a reserved word. You should aboid naming objects as one these to prevent exactly this problem. This is also why I quote absolutely every object name in every query, especially when you are using user input. Although you should also never use user input in an object name in a query, since it cannot be easily escaped.DaveRandom– DaveRandom2012-04-16 12:44:13 +00:00Commented Apr 16, 2012 at 12:44
Add a comment
|
5 Answers
It looks like it's not working because your table name is not in the quotes. Try this:
mysql_query('CREATE TABLE `'.$tbl_date.'`
(
`Test` varchar(15),
`Yes` varchar(15),
`Very` int
)');
And the same applies for field names.
Also try this, it's easier to read by humans:
mysql_query("CREATE TABLE `{$tbl_date}`
(
`Test` varchar(15),
`Yes` varchar(15),
`Very` int
)");
Please note different types of quotes:
- single quotes and double quotes for building some string (eg.SQL query) in PHP
- back quotes used specially as a part of SQL query for table names and column names
2 Comments
Ashim Saha
shall we use back quote to encompass the table name - ( ` ).
Enriqe
@Homework You can mark it as
Accepted answer if you wish ;)I think you are missing the formatting ( single or double quotes etc) try this
$sql = "CREATE TABLE `user` (`id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `address` TEXT NOT NULL) ENGINE = MyISAM;";
Comments
Here is my work around:
$cars = "CREATE TABLE $tbl_date (
Test varchar(15),
Yes varchar(15),
Very int
)";
$results = mysql_query($cars) or die (mysql_error());
Comments
Yes but you must be careful that the user does't inject the code. Look into mySQL injection to ensure that your code is safe, otherwise a user could create or delete tables that you don't want them to have access to. Also, I don't see why you would want to, surely it would be better to create a table in the database and then add fields with rows that can be added afterwards.
Comments
Yes you can.
mysql_query takes a string so you can basically attach any dynamic string.
As suggested, you need to use backticks before and after the name of the table (and check if the user of your connection has the rights to create tables).
The main problem with this usage is that you are vulnerable to SQL injection (as long as you don't escape the $tbl_date), so I suggest you to add far more controls to the variable used.
Also, adding dynamic tables to a database (excluding noSQL databases) is normally a BAD usage (check if you REALLY need this).
