Create MySQL table with PHP variable

Question

I'm trying to create a table whose name is the value of what is stored inside the variable $name. I have tried numerous different methods but none seem to work for me. Here is the code I am using currently:

 mysql_connect("localhost", "peltdyou_admin", "123456") or die(mysql_error()); 
 mysql_select_db("peltdyou_orders") or die(mysql_error()); 
 mysql_query("CREATE TABLE '" .$_POST['name']. "' ( name VARCHAR(30), age INT, car VARCHAR(30))");

I know it is something to do with '" .$_POST['name']. "' but I can't work out what. I have tried '$name' in its place which gets it's value from further up in the code.

Any help would be great!

Have you tried outputting the SQL you're generating, so you can be sure it contains what you think it contains? — andrewsi, Jun 12 '12 at 19:48
To my knowledge, MySQL does not require quotes around the table name. Have you tried removing the single quotes? — Jeff Lambert, Jun 12 '12 at 19:48
@Hidde, or GET, or COOKIE, or any data that cannot be trusted. — Brad, Jun 12 '12 at 19:49
@andrewsi I have run the query without a variable and it works perfectly. It is the variable throwing it off.. — user1449737, Jun 12 '12 at 19:50
@user1449737 - so have you tried echoing the SQL that's being generated with the variable, to see what's in there? — andrewsi, Jun 12 '12 at 19:52

Damien Pirsy · Accepted Answer · 2012-06-12T19:58:15.383

8

Use backticks around table name, not quotes. And escape the input! Also, while this works on localhost, make sure that the user running on your production server has the privilege to CREATE tables (usually it's not, AFAIK, on shared hostings of course).

A word of warning: are you really sure you want to create a table on a user input?? how many tables are you going to create in this way? Can't you just redesign the whole thing so that you insert values instead?

$name = mysql_real_escape_string($_POST['name']);
mysql_query("CREATE TABLE `".$name."` ( name VARCHAR(30), age INT, car VARCHAR(30))");

edited Jun 12 '12 at 19:58

answered Jun 12 '12 at 19:50

Damien Pirsy

25,319
8
70
77

Perfect! Works like a charm, thank you. I will accept the answer in 4 minutes – user1449737 Jun 12 '12 at 19:56
The tables will be created by an administrator when adding new clients to the database. So it won't be spammed – user1449737 Jun 12 '12 at 20:03
I'd suggest adding a client in a "client" table, as a new row, not creating a new table... – Damien Pirsy Jun 12 '12 at 20:08
`mysql_real_escape_string` is **absolutely** useless here. – Your Common Sense May 05 '20 at 21:29

Dave_Peachy · Answer 2 · 2012-06-12T20:02:13.887

1

Put it in another variable and it will work, there's a conflict with the "'" character in the POST variable and in the mysql_query.

<?php
mysql_connect("localhost", "peltdyou_admin", "123456") or die(mysql_error()); 
mysql_select_db("peltdyou_orders") or die(mysql_error()); 
$name = mysql_real_escape_string($_POST['name']);
mysql_query("CREATE TABLE '$name' ( name VARCHAR(30), age INT, car VARCHAR(30))");
?>

I posted this code to help you in your code but you should not use the mysql_* functions you should use the mysqli_* functions. You can read more about them here: http://php.net/manual/en/book.mysqli.php

edited Jun 12 '12 at 20:02

answered Jun 12 '12 at 19:48

Dave_Peachy

498
3
12

Do prepared statements work with identifiers? I thought they worked only for binding values – Damien Pirsy Jun 12 '12 at 19:56
You are right they don't! If you look at http://php.net/manual/en/mysqli.prepare.php it tell you :P I will edit that out. That was a mistake sorry!!! – Dave_Peachy Jun 12 '12 at 20:02
`mysql_real_escape_string` is **absolutely** useless here. – Your Common Sense May 05 '20 at 21:30

score 1 · Answer 3 · edited Jun 12 '12 at 21:07

1

You should really be using PDO or MySQLi instead of mysql_* functions. mysql_* functions are in the process of being deprecated and they are full of security holes.

With that said you don't need to quote your table name and instead should use nothing or backticks.

edited Jun 12 '12 at 21:07

Damien Pirsy

25,319
8
70
77

answered Jun 12 '12 at 19:59

Cody Covey

1,060
6
9

How do you bind a variable to an identifier using PDO or mysqli? – Damien Pirsy Jun 12 '12 at 20:00
Your query would look like CREATE TABLE :name (name VARCHAR(30), age INT, car VARCHAR(30) and then $stmt->bindParam(":name", $_POST['name']); For a full example you will want to find a good PDO tutorial that will give you all the basics. – Cody Covey Jun 12 '12 at 20:29
Are you really sure? The docs say otherwise. ALso, read this: http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter – Damien Pirsy Jun 12 '12 at 20:39
Oh yeah you are right identifiers cannot be done with bindParam. I am unsure why you down voted my answer however since the answer makes no mention of binding parameters... – Cody Covey Jun 12 '12 at 21:05
You're right, I misread your answer :). I made a small edit so I could revert my downvote, again sorry! – Damien Pirsy Jun 12 '12 at 21:08

score 0 · Answer 4 · answered Jul 29 '13 at 04:38

Using the newest Mysqli connector, you can do something like this: 1. Create a variable from the user's input like so $variable=$_POST['name'] 2. Use the variable in your query as shown in the complete code below here

$variable=$_POST['name']; mysqli_connect("localhost", "peltdyou_admin", "123456") or die(mysql_error()); mysqli_select_db("peltdyou_orders") or die(mysqli_connect_error()); mysqli_query("CREATE TABLE $variable ( name VARCHAR(30), age INT, car VARCHAR(30))");

score 0 · Answer 5 · edited Jul 16 '18 at 12:22

0

$query = "CREATE TABLE $name" . '(
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    age INT,
    name  varchar(30),
    car VARCHAR(30)
)';

edited Jul 16 '18 at 12:22

Adeel

2,901
7
24
34

answered Dec 01 '16 at 10:04

Anusha

1
5

1

I wanted to know how to use variable as column name. – Anusha Dec 01 '16 at 10:05
can we write create query in foreach loop and how to use.please guide me – Anusha Dec 01 '16 at 10:06

score 0 · Answer 6 · answered Sep 04 '18 at 07:35

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `description` text NOT NULL,
  `price` double NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

Create MySQL table with PHP variable

6 Answers6

Linked