在mysql_query中执行SQL时出错,但在PHPMyAdmin中未执行


Error excuting SQL in mysql_query but not in PHPMyAdmin

我想通过PHP创建一些表,但每次都失败了,但当通过MYsql控制台或PHPMyAdmin 执行时,相同的代码可以完美地执行

SQL是

$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;

错误我正在获取

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));

来自mysql_query手册;

mysql_query()发送一个唯一的查询(多个查询不是支持)。。。

您需要将三个CREATE TABLE语句拆分为3个单独的语句,它就会起作用。

我认为这是多个查询的问题。我认为这就是解决方案。

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

*mysql_query()向服务器上与指定的link_identifier*关联的当前活动数据库发送一个唯一的查询(不支持多个查询)

只要把它们分开就行了。

相关文章: