using a variable to name a database

[amplexus]

Hi,

 

I'm trying to use a variable- "$newdbname" in a script that sets up a new database.  I'm using this variable because I have an include file that sets all login info and the database name as variables.

 

here's the code. or at lest the relevant part.

<?php
require 'dbinfo.inc.php';

$db = mysql_connect($servname,$dbusername,$dbpassword) or 
    die ('Unable to connect. Check your connection parameters.');
mysql_select_db($database, $db) or die(mysql_error($db));

// create the user table
$query = 'CREATE TABLE  ($newdbname)(
        user_id  int(30)	 NOT NULL AUTO_INCREMENT,
        username varchar(20) NOT NULL,
        password varchar(41) NOT NULL,

        PRIMARY KEY (user_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

 

if I run the script like this, I get the following error:

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 '($newdbname)( user_id int(30) NOT NULL AUTO_INCREMENT, usernam' at line 1

 

if I run the code as this, without parentheses around the variable, it creates a table named "$newdbname"

<?php
require 'dbinfo.inc.php';

$db = mysql_connect($servname,$dbusername,$dbpassword) or 
    die ('Unable to connect. Check your connection parameters.');
mysql_select_db($database, $db) or die(mysql_error($db));

// create the user table
$query = 'CREATE TABLE  $newdbname (
        user_id  int(30)	 NOT NULL AUTO_INCREMENT,
        username varchar(20) NOT NULL,
        password varchar(41) NOT NULL,

        PRIMARY KEY (user_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

 

what am I doing wrong?

 

[Alex]

You need to use double quotes because single quotes do not have variable interpolation. And just to nitpick, you're creating a table, not a database.

 

$query = "CREATE TABLE  $newdbname (
        user_id  int(30)	 NOT NULL AUTO_INCREMENT,
        username varchar(20) NOT NULL,
        password varchar(41) NOT NULL,

        PRIMARY KEY (user_id)
    )
    ENGINE=MyISAM";

[Vince889]

Use double quotes instead of single-quotes. Then, encapsulate your $newdbname variable with single quotes.

[amplexus]

okay, so that got me through part of it, but now I get this:

 

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 ''users1'( user_id int(30) NOT NULL AUTO_INCREMENT, username va' at line 1

 

The "users1" part is the string from the variable, thanks for that much so far!

 

what else is going wrong?  what syntax is off?

[trq]

Do not surround $newdbname in single quotes as advised by Vince889, post your code if the problem persists.

[amplexus]

removed quotes as advised.  received following message. 

 

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 '(users234) (user_id, username, password) VALUES (1, "23",' at line 1

 

 

 

which seems to be referring to teh second part of my code:

<?php
require 'dbinfo.inc.php';

$db = mysql_connect($servname,$dbusername,$dbpassword) or 
    die ('Unable to connect. Check your connection parameters.');
mysql_select_db($database, $db) or die(mysql_error($db));

// create the user table
$query = "CREATE TABLE  $newdbname(
        user_id  int(30)	 NOT NULL AUTO_INCREMENT,
        username varchar(20) NOT NULL,
        password varchar(41) NOT NULL,

        PRIMARY KEY (user_id)
    )
    ENGINE=MyISAM";
mysql_query($query, $db) or die (mysql_error($db));


$query = 	"INSERT IGNORE INTO ($newdbname)
        (user_id, username, password) 
    VALUES
        (1, '23', PASSWORD('23')),
        (2, 'amplexus', PASSWORD('password'))";

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

echo 'Success!';
?>

 

[trq]

Remove the () around your table name.

[amplexus]

echo "Success!";

 

thanks!