[RESOLVED] Creating a MYSQL table from a variable

[Timsoft]

I am attempting to use a form field to create a variable, and then use the variable to create a table in a database.

However when I use:

$query = 'CREATE TABLE $code ( 'cid INT NOT NULL AUTO_INCREMENT, '......

It creates a table named $code, and not the variable.

I did some google searches, and browsed the posts on this site, that suggested enclosing the variable in " or ', both of which I tried to no avail...

This seems pretty basic, gotta be a way to do it....

Any pointers??

[reddrum]

You could try somthing like this to see where the problem may be.

mysql_query($query) or die(mysql_error());

[NogDog]

Variables are not interpolated within single-quoted strings in PHP. You'll either need to use a double-quoted string, string concatenation (the "." operator), or a "heredoc" string. See http://www.php.net/manual/en/language.types.string.php for more info.

[Timsoft]

I actually did have that, I just included a piece of the code in the post. The rest of the code works, because when I replace the variable with test, it works no problem. When I put it in double quotes, it gives me an error:

Notice: 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 '"$code" ( cid INT NOT NULL AUTO_INCREMENT, leaguename VARCHAR(35) NULL, teamname' at line 1

Nogdog, I followed that link and read every word, but couldn't seem to grok it. What do I need to do to $code? I haven't really used the "." operator before...

[Timsoft]

<?php

$username = "xxxx";
$password = "xxxxx";
$hostname = "p50mysql179.secureserver.net";

$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
if (!$dbh)
{
die('Could not connect: ' . mysql_error());
}

else echo ("Looks good!");

mysql_select_db("Tpowell04", $dbh);
$leaguename = $POST["leaguename"];
$commish = $POST["commisioner_name"];
$commishemail = $POST["commisioner_email"];
$code = $POST["league_code"];
$code = mysql_real_escape_string( $_POST['league_code'] );

$query = 'CREATE TABLE "$code" ( '.
'cid INT NOT NULL AUTO_INCREMENT, '.
'leaguename VARCHAR(35) NULL, '.

	 'teamname VARCHAR(50) NULL, '.
	 'password VARCHAR(50) NULL, '.
	 'firstname VARCHAR(50) NULL, '.
	 'lastname VARCHAR(50) NULL, '.
	 'email VARCHAR(50) NULL, '.


     'player1 VARCHAR(30) NULL, '.
	 'player2 VARCHAR(30) NULL, '.
	 'player3 VARCHAR(30) NULL, '.
	 'player4 VARCHAR(30) NULL, '.
	 'player5 VARCHAR(30) NULL, '.
	 'player6 VARCHAR(30) NULL, '.
	 'player7 VARCHAR(30) NULL, '.
	 'player8 VARCHAR(30) NULL, '.
	 'player9 VARCHAR(30) NULL, '.
	 'player10 VARCHAR(30) NULL, '.
	 'player11 VARCHAR(30) NULL, '.
	 'player12 VARCHAR(30) NULL, '.
	 'player13 VARCHAR(30) NULL, '.

     'PRIMARY KEY(cid))';

$result = mysql_query($query);
if(!$result)
{
user_error(mysql_error() . "<br />\n$query");

}

?>

[reddrum]

Try a double-quoted string per NogDog

$query = "CREATE TABLE '$code' ( '.
'cid INT NOT NULL AUTO_INCREMENT, '.
'leaguename VARCHAR(35) NULL, '.

'teamname VARCHAR(50) NULL, '.
'password VARCHAR(50) NULL, '.
'firstname VARCHAR(50) NULL, '.
'lastname VARCHAR(50) NULL, '.
'email VARCHAR(50) NULL, '.


'player1 VARCHAR(30) NULL, '.
'player2 VARCHAR(30) NULL, '.
'player3 VARCHAR(30) NULL, '.
'player4 VARCHAR(30) NULL, '.
'player5 VARCHAR(30) NULL, '.
'player6 VARCHAR(30) NULL, '.
'player7 VARCHAR(30) NULL, '.
'player8 VARCHAR(30) NULL, '.
'player9 VARCHAR(30) NULL, '.
'player10 VARCHAR(30) NULL, '.
'player11 VARCHAR(30) NULL, '.
'player12 VARCHAR(30) NULL, '.
'player13 VARCHAR(30) NULL, '.

'PRIMARY KEY(cid))'";

[NogDog]

Also, use the "" (back-tick) character to quote column/table names in MySQL:
[code=php]
$query = "CREATE TABLE$code` ( ".
"cid INT NOT NULL AUTO_INCREMENT, ".
"leaguename VARCHAR(35) NULL, ".
"teamname VARCHAR(50) NULL, ".
"password VARCHAR(50) NULL, ".
"firstname VARCHAR(50) NULL, ".
"lastname VARCHAR(50) NULL, ".
"email VARCHAR(50) NULL, ".
"player1 VARCHAR(30) NULL, ".
"player2 VARCHAR(30) NULL, ".
"player3 VARCHAR(30) NULL, ".
"player4 VARCHAR(30) NULL, ".
"player5 VARCHAR(30) NULL, ".
"player6 VARCHAR(30) NULL, ".
"player7 VARCHAR(30) NULL, ".
"player8 VARCHAR(30) NULL, ".
"player9 VARCHAR(30) NULL, ".
"player10 VARCHAR(30) NULL, ".
"player11 VARCHAR(30) NULL, ".
"player12 VARCHAR(30) NULL, ".
"player13 VARCHAR(30) NULL, ".
"PRIMARY KEY(cid))";
[/code]

[NogDog]

PS: You might find the heredoc quoting mechanism convenient for this sort of thing:

$query = <<<END
CREATE TABLE `$code` ( 
cid INT NOT NULL AUTO_INCREMENT, 
leaguename VARCHAR(35) NULL, 
teamname VARCHAR(50) NULL, 
password VARCHAR(50) NULL, 
firstname VARCHAR(50) NULL, 
lastname VARCHAR(50) NULL, 
email VARCHAR(50) NULL, 
player1 VARCHAR(30) NULL, 
player2 VARCHAR(30) NULL, 
player3 VARCHAR(30) NULL, 
player4 VARCHAR(30) NULL, 
player5 VARCHAR(30) NULL, 
player6 VARCHAR(30) NULL, 
player7 VARCHAR(30) NULL, 
player8 VARCHAR(30) NULL, 
player9 VARCHAR(30) NULL, 
player10 VARCHAR(30) NULL, 
player11 VARCHAR(30) NULL, 
player12 VARCHAR(30) NULL, 
player13 VARCHAR(30) NULL, 
PRIMARY KEY(cid))
END;

[Timsoft]

Nogdog, you are the F%#@ing man!

Thanks for you help gentleman.

I will mark this post as solved....