Error excuting SQL in mysql_query but not in PHPMyAdmin

Question

I want to create some table through PHP but it fails everytime, but the same code excutes perfectly when excuted through MYsql console or PHPMyAdmin

The SQLs are

$sql = <<<SQL_CODE
CREATE TABLE IF NOT EXISTS `bid` (
  `aid` int(11) unsigned NOT NULL,
  `uid` int(11) unsigned NOT NULL,
  `name` varchar(20) NOT NULL,
  `amount` smallint(6) unsigned NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `item` (
  `aid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `description` varchar(120) NOT NULL,
  `img` int(11) unsigned NOT NULL,
  `amount` smallint(6) unsigned NOT NULL,
  `strtdate` date NOT NULL,
  `enddate` date NOT NULL,
  `uid` int(11) unsigned NOT NULL,
  `uname` varchar(20) NOT NULL,
  `uamount` int(11) unsigned NOT NULL,
  PRIMARY KEY (`aid`)
);

CREATE TABLE IF NOT EXISTS `user` (
  `uid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(40) NOT NULL,
  `password` varchar(40) NOT NULL,
  `nameF` varchar(20) NOT NULL,
  `nameL` varchar(20) NOT NULL,
  `sex` varchar(1) NOT NULL,
  `img` int(11) unsigned NOT NULL,
  `country` varchar(10) NOT NULL,
  `state` varchar(20) NOT NULL,
  `address` varchar(120) NOT NULL,
  `code` varchar(8) NOT NULL,
  `isAdmin` varchar(1) NOT NULL,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `email` (`email`)
);
SQL_CODE;

$sql2 = 'CREATE DATABASE `'.$db.'`;';
$sql3 = 'USE `'.$db.'`; ';
$sql4 = 'drop Database `'.$db.'``;';

if (!mysql_query($sql2)) echo mysql_error();
if (!mysql_query($sql3)) echo mysql_error();
//sleep(1);
if (!mysql_query($sql)) echo mysql_error();
echo $sql2.' '.$sql3.' '.$sql;

Error I'm getting

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 IF NOT EXISTS item ( aid int(11) unsigned NOT NULL AUTO_INCREMENT,' at line 1CREATE DATABASE auction; USE auction; CREATE TABLE IF NOT EXISTS bid ( aid int(11) unsigned NOT NULL, uid int(11) unsigned NOT NULL, name varchar(20) NOT NULL, amount smallint(6) unsigned NOT NULL, time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); CREATE TABLE IF NOT EXISTS item ( aid int(11) unsigned NOT NULL AUTO_INCREMENT, name varchar(40) NOT NULL, description varchar(120) NOT NULL, img int(11) unsigned NOT NULL, amount smallint(6) unsigned NOT NULL, strtdate date NOT NULL, enddate date NOT NULL, uid int(11) unsigned NOT NULL, uname varchar(20) NOT NULL, uamount int(11) unsigned NOT NULL, PRIMARY KEY (aid));CREATE TABLE IF NOT EXISTS user ( uid int(11) unsigned NOT NULL AUTO_INCREMENT, email varchar(40) NOT NULL, password varchar(40) NOT NULL, nameF varchar(20) NOT NULL, nameL varchar(20) NOT NULL, sex varchar(1) NOT NULL, img int(11) unsigned NOT NULL, country varchar(10) NOT NULL, state varchar(20) NOT NULL, address varchar(120) NOT NULL, code varchar(8) NOT NULL, isAdmin varchar(1) NOT NULL, PRIMARY KEY (uid), UNIQUE KEY email (email));

Maybe you can't execute multiple CREATE TABLE in one mysql_query call? — Halcyon
– Halcyon, Commented Mar 17, 2012 at 16:18

Joachim Isaksson · Accepted Answer · 2012-03-17 16:18:23Z

2

From the mysql_query manual;

mysql_query() sends a unique query (multiple queries are not supported)...

You need to split your triple CREATE TABLE statement into 3 separate statements and it will work.

answered Mar 17, 2012 at 16:18

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

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

Comments

Grzegorz Łuszczek · Accepted Answer · 2012-03-17 16:18:38Z

1

I think that is a problem with multiple queries. I think that is solution.

answered Mar 17, 2012 at 16:18

Grzegorz Łuszczek

5481 gold badge3 silver badges16 bronze badges

1 Comment

Sourav Over a year ago

Thats a mysqli but I was using mysql. +1 for your response

FrankieTheKneeMan · Accepted Answer · 2012-03-17 16:19:24Z

1

http://php.net/manual/en/function.mysql-query.php

*mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier*

Just split them up and it will work.

answered Mar 17, 2012 at 16:19

FrankieTheKneeMan

6,8082 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

Error excuting SQL in mysql_query but not in PHPMyAdmin

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related