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
– andrewsi, Commented Jun 12, 2012 at 19:48
To my knowledge, MySQL does not require quotes around the table name. Have you tried removing the single quotes? — Jeff Lambert
– Jeff Lambert, Commented Jun 12, 2012 at 19:48
@Hidde, or GET, or COOKIE, or any data that cannot be trusted. — Brad
– Brad, Commented Jun 12, 2012 at 19:49

Damien Pirsy · Accepted Answer · 2012-06-12 19:58:15Z

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, 2012 at 19:58

answered Jun 12, 2012 at 19:50

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

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

4 Comments

user1449737 Over a year ago

Perfect! Works like a charm, thank you. I will accept the answer in 4 minutes

user1449737 Over a year ago

The tables will be created by an administrator when adding new clients to the database. So it won't be spammed

Damien Pirsy Over a year ago

I'd suggest adding a client in a "client" table, as a new row, not creating a new table...

Your Common Sense Over a year ago

mysql_real_escape_string is absolutely useless here.

Dave_Peachy · Accepted Answer · 2012-06-12 20:02:13Z

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, 2012 at 20:02

answered Jun 12, 2012 at 19:48

Dave_Peachy

4983 silver badges12 bronze badges

3 Comments

Damien Pirsy Over a year ago

Do prepared statements work with identifiers? I thought they worked only for binding values

Dave_Peachy Over a year ago

You are right they don't! If you look at php.net/manual/en/mysqli.prepare.php it tell you :P I will edit that out. That was a mistake sorry!!!

Your Common Sense Over a year ago

mysql_real_escape_string is absolutely useless here.

Damien Pirsy · Accepted Answer · 2012-06-12 21:07:52Z

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, 2012 at 21:07

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

answered Jun 12, 2012 at 19:59

Cody Covey

1,0606 silver badges9 bronze badges

5 Comments

Damien Pirsy Over a year ago

How do you bind a variable to an identifier using PDO or mysqli?

Cody Covey Over a year ago

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.

Damien Pirsy Over a year ago

Are you really sure? The docs say otherwise. ALso, read this: stackoverflow.com/questions/182287/…

Cody Covey Over a year ago

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...

Damien Pirsy Over a year ago

You're right, I misread your answer :). I made a small edit so I could revert my downvote, again sorry!

Mbah Kingsley · Accepted Answer · 2013-07-29 04:38:25Z

0

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))");

answered Jul 29, 2013 at 4:38

Mbah Kingsley

312 bronze badges

Comments

Adeel · Accepted Answer · 2018-07-16 12:22:06Z

0

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

edited Jul 16, 2018 at 12:22

Adeel

2,9977 gold badges27 silver badges38 bronze badges

answered Dec 1, 2016 at 10:04

Anusha

15 bronze badges

2 Comments

Anusha Over a year ago

I wanted to know how to use variable as column name.

Anusha Over a year ago

can we write create query in foreach loop and how to use.please guide me

Anusha · Accepted Answer · 2018-09-04 07:35:55Z

0

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 ;

answered Sep 4, 2018 at 7:35

Anusha

15 bronze badges

Collectives™ on Stack Overflow

Create MySQL table with PHP variable

6 Answers 6

4 Comments

3 Comments

5 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

3 Comments

5 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related