Create mysql table with php variable not working

Question

I'm pretty new to php.. and this is probably a stupid mistake... but I have no idea what is going on. I'm trying to create a table in my database using php. I want to name the table after the username. I'm using the variable $tableusername. Here's my code

$sql="SELECT * FROM userdata WHERE username='$username'";
$result=mysql_query($sql);

while ($row = mysql_fetch_assoc($result))
        {
            $tableusername = $row["username"];
        }

$create = "CREATE TABLE `".$tableusername."` ('
    . ' `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, '
    . ' `please` VARCHAR(50) NOT NULL, '
    . ' `make` VARCHAR(50) NOT NULL, '
    . ' `this` VARCHAR(50) NOT NULL, '
    . ' `work` VARCHAR(50) NOT NULL'
    . ' )'
    . ' ENGINE = myisam;";

mysql_query($create)


?>

<html>
<head>
</head>
<body>
You have successfully signed up. <?php echo $tableusername ?>
</body>
</html>

So- This creates a table named $tableusername. The variable doesn't carry over. BUT- when I echo $tableusername - the variable carries over. I'm pretty new to this - so any help is appreciated.

Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the *[red box](http://goo.gl/GPmFd)*? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). Also see [Why shouldn't I use `mysql` functions in PHP?](http://goo.gl/ycnmO) — Madara's Ghost, Oct 21 '12 at 17:30
`$username` and `$tableusername` are the same according to your code :) — Alexander, Oct 21 '12 at 17:32

score 1 · Answer 1 · answered Oct 21 '12 at 17:30

1

you have invalid concatenation of string, use double quotes instead of single quotes.

$create = "CREATE TABLE `".$tableusername."` ("
    . " `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, "
    . " `please` VARCHAR(50) NOT NULL, "
    . " `make` VARCHAR(50) NOT NULL, "
    . " `this` VARCHAR(50) NOT NULL, "
    . " `work` VARCHAR(50) NOT NULL' "
    . " )"
    . " ENGINE = myisam;";

answered Oct 21 '12 at 17:30

John Woo

258,903
69
498
492

Is there any particular reason you are saying to use double quotes instead of single quotes, rather than just ensuring that they match? – Waleed Khan Oct 21 '12 at 17:37
@WaleedKhan look at the code in the question above, the single quotes are included inside the string. – John Woo Oct 21 '12 at 17:39
I mean to say that it would work equally well if single-quotes were used rather than double-quotes. I'm just wondering why you choose one over the other. – Waleed Khan Oct 21 '12 at 17:41

Phil · Accepted Answer · 2012-10-21T17:51:21.993

Add this after your SQL querys - (It really helps and speeds up error correcting time)

or die("A MySQL error has occurred.<br />Error: (" . mysql_errno() . ") " . mysql_error());

echos this in your instance:

A MySQL error has occurred. Error: (1064) 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 '' . ' ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ' . ' please VARCH' at line 1

This then indicates to me that the error regards " & '.

After changing the code to contain single quotes and executing it, there is now no echo.

   <?php
    $tableusername = "philip";
    $create = "CREATE TABLE `".$tableusername."` ("
        . " `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, "
        . " `please` VARCHAR(50) NOT NULL, "
        . " `make` VARCHAR(50) NOT NULL, "
        . " `this` VARCHAR(50) NOT NULL, "
        . " `work` VARCHAR(50) NOT NULL"
        . " )"
        . " ENGINE = myisam;";

    mysql_query($create)or die("A MySQL error has occurred.<br />Error: (" . mysql_errno() . ") " . mysql_error());

    ?>

Note: Please refer to the MySQLi extension when using SQL embedded in PHP. mysql_* is in a deprecation process.

Hope this helps.

score 1 · Answer 3 · answered Jan 13 '14 at 03:14

// Create MySql table with variable

$tableName = "tb-"."$userEmail";
$tName="Beta";  
$createTable = "CREATE TABLE ".$tName." ( UserName varchar(30), UserPassword varchar(30) )" ;

This above syntax works. Apply this example to your code. Please review some error in your concatenation

score 0 · Answer 4 · answered Oct 21 '12 at 17:34

Use a heredoc:

$create = <<<SQL
CREATE TABLE `$tableusername` (
    `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `please` VARCHAR(50) NOT NULL,
    `make` VARCHAR(50) NOT NULL,
    `this` VARCHAR(50) NOT NULL,
    `work` VARCHAR(50) NOT NULL
)
ENGINE = myisam;
SQL;

Notably, you can use string interpolation in a heredoc, but you should really tend toward parametrized queries.

Create mysql table with php variable not working

4 Answers4

Linked