Adding prefixes to database tables!

not working but why?

[dreamcatcher]

Hi Guys,

I`ve done this script and for anyone using it with only one database, I want prefixes before the table names.

$db_prefix = "mw_";

For an example the first table that gets created is called entries.

$query = "CREATE TABLE entries (
id INT(5) NOT NULL auto_increment,
date VARCHAR(40),
caldate DATE NOT NULL,
subject VARCHAR(50) NOT NULL,
message TEXT,
PRIMARY KEY(id)) TYPE=MyISAM";
$result = mysql_query($query);

When I click install, this adds to the database ok. So I changed that to:

$query = "CREATE TABLE " . $db_prefix . " entries (
id INT(5) NOT NULL auto_increment,
date VARCHAR(40),
caldate DATE NOT NULL,
subject VARCHAR(50) NOT NULL,
message TEXT,
PRIMARY KEY(id)) TYPE=MyISAM";
$result = mysql_query($query);

Now what happens is I get the following error message:

You have an error in your SQL syntax near 'entries ( id INT(5) NOT NULL auto_increment, date VARCHAR(40),' at line 1

Can anyone see what the problem is?

Thank you!

[RobinC]

Only thing I can see is you're putting a space after the $db_prefix - try changing it from -
$query = "CREATE TABLE " . $db_prefix . " entries (
- to -
$query = "CREATE TABLE " . $db_prefix . "entries (

See?

[lorax]

I often get tripped up over when to concantenate a string like you did with " . $db_prefix . " versus just placing the var within the string. Try just using the var without the concantenation like so 'CREATE TABLE $db_prefix entries'.

[dreamcatcher]

Thanks guys. I`ve tried both and the same error appears. Like I say, if I remove the $db_prefix and just have say entries it works fine.

I`ve been trying to wrap my head round why this isn`t working and I just keep drawing a blank. I use an e-card script on my site that uses exactly the same method and that works fine.

Strange.

[dreamcatcher]

Oh, you know what? I`ve got it working. RobinC was on the right track, only it was the gaps with the concantenation too.

" . $db_prefix . " entries

should be:

".$db_prefix."entries

No gaps.

Phew, thank god for that.

:)

[lorax]

LOL - glad you found it. I've spent many hours chasing down silly mistakes like that. More than I care to think about. :)

[dreamcatcher]

I used to keep telling myself it was all part of the fun in learning. I`m over that phase now! haha!