PHP MySQL (PDO) CREATE TABLE does not create tables (and in fact, does nothing)

Question

I have been struggling since this morning, trying to write an install.php file that will insert three empty tables (users, sessions, posts) into a database. The SQL code I'm using is valid when I inject it with PHPMyAdmin, but apparently the way I'm handling it in PHP is wrong, because when I run my install.php file my database remains empty. Here's my code:

<?php
try {
 $con = new PDO('mysql:host=omitted;dbname=omitted','omitted','');
 $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
 $q="CREATE TABLE posts (
  title varchar(150) NOT NULL,
  body text NOT NULL,
  created varchar(100) NOT NULL,
  user varchar(40) NOT NULL,
  id int(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id),
  UNIQUE KEY id (id)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

  CREATE TABLE sessions (
  session_id varchar(40) NOT NULL,
  data text NOT NULL,
  last_activity int(11) NOT NULL,
  PRIMARY KEY (session_id)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

  CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(40) NOT NULL,
  password varbinary(250) NOT NULL,
  email varchar(40) NOT NULL,
  salt varchar(20) NOT NULL,
  name varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  UNIQUE KEY email (email),
  UNIQUE KEY username (username),
  UNIQUE KEY id (id)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ";

 $stmt = $con->prepare($q);
 $stmt->execute();
 echo "success";

} catch (PDOException $e) {
 $e->getMessage();
}


?>

I am assuming that it's some idiotic mistake on my part (since my last 3 headaches were also quite silly) but at this moment I really can't figure it out. Any ideas?

the underlying mysql driver in php does not allow multiple queries in a single ->query()-type call as an sql injection attack defence mechanism. while it doesn't stop all injection attacks, it does prevent the classic bobby tables-type attack. — Marc B
– Marc B, Commented Feb 1, 2013 at 14:52

TecBrat · Accepted Answer · 2013-12-31 15:35:35Z

2

You can prepare only one statement per prepare call. To execute multiple statements you have to use PDO::exec method.

For example:

$con = new PDO('mysql:host=localhost;dbname=db','user','pass');
 $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
 $q="CREATE TABLE posts (
  title varchar(150) NOT NULL,
  body text NOT NULL,
  created varchar(100) NOT NULL,
  user varchar(40) NOT NULL,
  id int(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id),
  UNIQUE KEY id (id)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

  CREATE TABLE sessions (
  session_id varchar(40) NOT NULL,
  data text NOT NULL,
  last_activity int(11) NOT NULL,
  PRIMARY KEY (session_id)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

  CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(40) NOT NULL,
  password varbinary(250) NOT NULL,
  email varchar(40) NOT NULL,
  salt varchar(20) NOT NULL,
  name varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  UNIQUE KEY email (email),
  UNIQUE KEY username (username),
  UNIQUE KEY id (id)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ";

try {
 $con->exec($q) or die(print_r($db->errorInfo(), true));;
 echo "Success";

} catch (PDOException $e) {
 $e->getMessage();
}

edited Dec 31, 2013 at 15:35

TecBrat

3,7353 gold badges31 silver badges49 bronze badges

answered Feb 1, 2013 at 14:38

Leri

12.6k7 gold badges46 silver badges60 bronze badges

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

8 Comments

Michael Yamada Over a year ago

This did not work for me. Thanks for the help though! Oh, and to come back on my own negligence; can you please omit the hostname/user/password from line 1? Much appreciated!

Leri Over a year ago

@MichaelYamada Sure, I can. Actually what did not work? I've just tested and it creates three tables as expected. :)

Michael Yamada Over a year ago

The database still turns up empty after running the file.

Leri Over a year ago

@MichaelYamada I've updated code. It will at least output an error. Anyway, both solutions should be working

Michael Yamada Over a year ago

Ah, now that I've instantiated the PDO object without catching the exception, I get an 'unknown server host' error. Anyway, this means I'm a lot closer to the actual problem now :) Thanks

|

Your Common Sense · Accepted Answer · 2013-02-01 14:53:08Z

1

This is not a query but rather set of queries.
Just run them one by one with separate calls.

edited Feb 1, 2013 at 14:53

answered Feb 1, 2013 at 14:37

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

1 Comment

Michael Yamada Over a year ago

Got it! It's working perfectly this way. EDIT: Woops, no it doesn't. I was looking at the wrong phpmyadmin panel. The database in question remains empty even after separating the queries.

Collectives™ on Stack Overflow

PHP MySQL (PDO) CREATE TABLE does not create tables (and in fact, does nothing)

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related