[RESOLVED] MySQL Query Error

msgcrap

Hi, wondering if anyone could please help me fix this code.

$sql = "CREATE TABLE 'mrs_users' (
'user_id' int(4) NOT NULL auto_increment,
'username' varchar(20) NOT NULL default '',
'password' varchar(20) NOT NULL default '',
PRIMARY KEY ('user_id')
) TYPE=MyISAM;" .

"CREATE TABLE 'mrs_requests' (
'id' int(4) NOT NULL auto_increment,
'habbo_name' TEXT NOT NULL ,
'request' TEXT NOT NULL ,
'time_date' TEXT NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";

Thanks, David.

Piranha

It is always good to know what error message you get, makes it easier to help you out. But I can help you with one thing, don't use ' signs to table names or column names, use backticks instead. Think over your column types and use UTF8 instead of latin1.

msgcrap

Thats the problem, I have more code right, and error checking, and all it says is Could Not Execute Query, I suspect it's a syntax error, the code looks dodgy, dunno how to fix it.

Piranha

Doesn't it even say where it fails? Usually the problem is right before what it says.

bpat1434

Where you execute the query, add this:

or die(mysql_error());

directly after the mysql_query() call. So it would look something like:

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

Then give us the output of what it says. It will show where you're having syntactical errors.

msgcrap

Thanks, couldn't remember how to get it to show the error, anyway...

Heres the 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 ''mrs_users' ( 'user_id' int(4) NOT NULL auto_increment, 'username' varchar(20) N' at line 1

Roger_Ramjet

Drop the single quotes around table and column names. If you feel you have to quote your table and column identifiers (names) then use the correct character which is the back-tick `

msgcrap

OK, I edited my code to this:

#execute queries
$sql .= "CREATE TABLE `mrs_users` (
`user_id` int(4) NOT NULL auto_increment,
`username` varchar(20) NOT NULL default '',
`password` varchar(20) NOT NULL default '',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";

$sql .= "CREATE TABLE `mrs_requests` (
`id` int(4) NOT NULL auto_increment,
`habbo_name` TEXT NOT NULL,
`request` TEXT NOT NULL,
`time_date` TEXT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";

Now I am getting this 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 '1CREATE TABLE mrs_requests ( id int(4) NOT NULL auto_increment, habbo_name' at line 6

When I execute the queries seperately, they work fine...

bradgrafelman

msgcrap wrote:
When I execute the queries seperately, they work fine...

That's because the [man]MySQL[/man] extension can't do multiple queries at once. I'm not sure if PDO or MySQLi can or not, but I do know that the MySQL extension can't.

msgcrap

Thanks mate, did what you said, made it so they weren't executed at the same time, now it works fine