Can't execute multi-statement SQL through PHP code

Question

The SQL code used below works just fine if pasted in PHPMyAdmin. But once I use it in custom PHP code it throws an exception:

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 v2_session ( username varchar(150) DEFAULT '', time varchar(1' at line 2

What am I missing, the code is dead simple, I am so confused right now. This is my PHP code:

$con=mysqli_connect("host","user","password","db");

if (mysqli_connect_errno()) {
  echo "Error: " . mysqli_connect_error();
}

    $sql="
    DROP TABLE IF EXISTS v2_session;
    CREATE TABLE v2_session (
      username varchar(150) DEFAULT '',
      time varchar(14) DEFAULT '',
      session_id varchar(200) NOT NULL DEFAULT '0',
      guest tinyint(4) DEFAULT '1',
      userid int(11) DEFAULT '0',
      usertype varchar(50) DEFAULT '',
      gid tinyint(3) unsigned NOT NULL DEFAULT '0',
      client_id tinyint(3) unsigned NOT NULL DEFAULT '0',
      data longtext,
      PRIMARY KEY (session_id(64)),
      KEY whosonline (guest,usertype),
      KEY userid (userid),
      KEY time (time)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";

if (mysqli_query($con,$sql)) {
  echo 'Success!';
} else {
  echo "Error: " . mysqli_error($con);
}

mysqli_close($con);

what if you remove `DROP TABLE IF EXISTS v2_session;` from the above ? — Abhik Chakraborty, Sep 26 '14 at 09:36
You have to use [*`Mysqli Multi Query`*](http://php.net/manual/en/mysqli.multi-query.php) for multiple statements — M Khalid Junaid, Sep 26 '14 at 09:37
Yup, you guys were all on the right track. Such a silly problem, thanks for the quick help! — Gena, Sep 26 '14 at 09:45

score 2 · Answer 1 · answered Sep 26 '14 at 09:37

These are two statements, and should be executed separately:

$sql = "DROP TABLE IF EXISTS v2_session";
if (!mysqli_query($con,$sql)) {
   echo "Error: " . mysqli_error($con);
   exit(1);
}

$sql="
CREATE TABLE v2_session (
  username varchar(150) DEFAULT '',
  time varchar(14) DEFAULT '',
  session_id varchar(200) NOT NULL DEFAULT '0',
  guest tinyint(4) DEFAULT '1',
  userid int(11) DEFAULT '0',
  usertype varchar(50) DEFAULT '',
  gid tinyint(3) unsigned NOT NULL DEFAULT '0',
  client_id tinyint(3) unsigned NOT NULL DEFAULT '0',
  data longtext,
  PRIMARY KEY (session_id(64)),
  KEY whosonline (guest,usertype),
  KEY userid (userid),
  KEY time (time)
) ENGINE=MyISAM DEFAULT CHARSET=utf8";

if (mysqli_query($con,$sql)) {
  echo 'Success!';
} else {
  echo "Error: " . mysqli_error($con);
}

score 2 · Accepted Answer · answered Sep 26 '14 at 09:38

mysqli_query does not allow for multiple statements concatenated by a semicolon.

Either split the query up into two separate queries, or use mysqli_multi_query() with a very similar syntax instead.

bool mysqli_multi_query ( mysqli $link , string $query )

Executes one or multiple queries which are concatenated by a semicolon.

The downside with mysqli_multi_query is that it makes your code more sensitive to SQL injection (since a whole statement can be injected), but for static queries without parameters it should not cause any problems.

Can't execute multi-statement SQL through PHP code

2 Answers2