Install script MySQL syntax error "You have an error in your SQL syntax; [...] "

Question

I have an install script on my website, and when i run it, it gives me thsi 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 'CREATE TABLE active_guests ( ip varchar(15) collate latin1_general_' at line 2

Here is the code where it seems to bug:

$sql = 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
        CREATE TABLE `active_guests` (
          `ip` varchar(15) collate latin1_general_ci NOT NULL,
          `timestamp` int(11) unsigned NOT NULL,
          PRIMARY KEY  (`ip`)
        );'; 

mysql_query($sql,$con) or die(mysql_error());

$sql = 'CREATE TABLE `active_users` (
           `username` varchar(30) collate latin1_general_ci NOT NULL,
           `timestamp` int(11) unsigned NOT NULL,
           PRIMARY KEY  (`username`)
        );';

mysql_query($sql,$con) or die(mysql_error());

$sql = 'CREATE TABLE `banned_users` (
           `username` varchar(30) collate latin1_general_ci NOT NULL,
           `timestamp` int(11) unsigned NOT NULL,
           PRIMARY KEY  (`username`)
        );';

It continues even more after that, but basically it's this. Anyone could clear things up? Thanks in advance!

Bill Karwin · Accepted Answer · 2010-07-31 04:34:23Z

4

You can only run one statement per call to mysql_query().

So you have to run SET SQL_MODE ... and then separately run the first CREATE TABLE ...

Re your comment: I recommend that you stick with running one statement per call to mysql_query() or its equivalent in mysqli or PDO. There's really no significant downside to doing it, and it's a good way to prevent some forms of SQL injection.

edited Jul 31, 2010 at 4:34

answered Jul 31, 2010 at 1:45

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

blackjak231 Over a year ago

Thanks! I've heard about something like mysqli to run more than one statement at once... I might look into it! thanks again!

Collectives™ on Stack Overflow

Install script MySQL syntax error "You have an error in your SQL syntax; [...] "

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related