Create SQL Table in PHP with variables and text

Question

I'm trying to create a SQL table in PHP using the following code:

$sql = "CREATE TABLE `$DBName`.`$login`_clients  (
  ClientID int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ClientID`),
  AgentClients varchar(15),
  ClientTotal int
)";

The botton script runs fine and saves the database as my $login query. I wanted to save the table as $login_clients however. Ex. $login="Fred", then the table would be named Fred_clients. I have tried a few different methods of combining variables with text but can't get the format down. Any suggestions?

$sql = "CREATE TABLE `$DBName`.`$login`_clients  (
  ClientID int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ClientID`),
  AgentClients varchar(15),
  ClientTotal int
)";

Don't you think it's bad practice to separate data in such manner? — Jovan Perovic
– Jovan Perovic, Commented Oct 29, 2012 at 20:25
@jperovic Clearly not. How about explaining to the OP why it might be a bad practice? — Michael Berkowski
– Michael Berkowski, Commented Oct 29, 2012 at 20:26
It might be just paranoia inside me but I would never grant a CREATE to the front-end without pretty god reason. Instead I would look up table portioning if data separation is indeed needed... — Jovan Perovic
– Jovan Perovic, Commented Oct 29, 2012 at 20:33

doublesharp · Accepted Answer · 2012-10-29 20:25:45Z

4

You just have your back tick in the wrong place, it should go after _clients. The problem you likely ran into was that the PHP interpreter then thought your variable was called $login_clients instead of $login, which can be solved by wrapping the variable in curly braces {}.

$sql = "CREATE TABLE `{$DBName}`.`{$login}_clients`  (
  ClientID int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ClientID`),
  AgentClients varchar(15),
  ClientTotal int
)";

answered Oct 29, 2012 at 20:25

doublesharp

27.9k6 gold badges56 silver badges76 bronze badges

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

5 Comments

tadman Over a year ago

You'll want to be super mega careful that $login does not contain anything harmful here.

Yitzhak Over a year ago

Also note the proper use of encapsulation in the answer. Encapsulation is very important.

Blaine Hurtado Over a year ago

@doublesharp thanks for your help, this clears up my questions on braces! Once I can choose an answer, this will be it. :)

doublesharp Over a year ago

@BlaineHurtado no problem - take note of what @tadman posted as well, you want to make sure that your $login variable is what you expect it to be, if you are accepting input from a form for example this could be used for a SQL injection attack.

Blaine Hurtado Over a year ago

@doublesharp This page requires a valid session cookie through a previous login. Other than sanitizing values and checking for input validations/existing logins, would you recommend other extra securities? Once again, everyone's help is greatly appreciated!

Adi · Accepted Answer · 2012-10-29 20:28:44Z

0

This should work

$sql = "CREATE TABLE `$DBName`.`" . $login . "_clients`  (
  ClientID int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ClientID`),
  AgentClients varchar(15),
  ClientTotal int
)";

answered Oct 29, 2012 at 20:28

Adi

492 bronze badges

Collectives™ on Stack Overflow

Create SQL Table in PHP with variables and text

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related