Why won't this PHP/SQL create the table?

Question

<?php
try {
  $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
  $sql = $DBH->exec("
    CREATE TABLE `store_config` (
    store_name varchar(30),
    last_update date)
    ");
} catch (PDOException $e) {
  echo "Could not connect to database.";
  $DBH = null;
}
?>

The PDOException is not thrown, there is probably an SQL error but I don't know how to retrieve that error and when I check the database with phpMyAdmin there are no tables created. What did I do wrong?

Thanks for reading.

EDIT: When I executed the same SQL code directly into phpMyAdmin it gave permission denied error...

1142 - CREATE command denied to user '$user'@'$my_ip' for table 'store_config'

I am confused because when I created the user with the web hosts web form it didn't give me options to grant any permissions but only to make this user the database owner - is that what I have to do?

EDIT:

I gave my user account 'DBO access' and it works now (the table is created).

Thanks for your comments.

The SQL works if you just put it into MySQL, so it must be either it's not connecting or your dbname isn't the name of an actual DB on MySQL or that user doesn't have the rights to the DB. — thezboe
– thezboe, Commented Apr 11, 2012 at 21:28
@thezboe please see the edit in my post - I have an error now (permission denied) — Ozzy
– Ozzy, Commented Apr 11, 2012 at 21:31

Nathaniel Ford · Accepted Answer · 2012-04-11 21:28:44Z

1

Try this. It should get you the error you're looking for that will tell you whats going on.

<?php
try {
  $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
  $sql = $DBH->exec("
    CREATE TABLE `store_config` (
    store_name varchar(30),
    last_update date)
    ") or die(print_r($DBH->errorInfo(), true));
} catch (PDOException $e) {
  echo "Could not connect to database.";
  $DBH = null;
}
?>

answered Apr 11, 2012 at 21:28

Nathaniel Ford

21.3k20 gold badges98 silver badges112 bronze badges

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

1 Comment

Ozzy Over a year ago

+1 for the $DBH->errorInfo(), thanks. @NathanielFord please see the updated question with the error

thezboe · Accepted Answer · 2012-04-11 21:34:51Z

0

You need to find a way to grant the create permission to your user from the machine you're running the code from. The user probably only has permissions from localhost, so you'd have to upload the code to the server and run it there to have permission.

edited Apr 11, 2012 at 21:34

answered Apr 11, 2012 at 21:19

thezboe

5521 gold badge5 silver badges13 bronze badges

2 Comments

thezboe Over a year ago

Actually you probably don't need to prepare it. Just usually the way I do it.

Ozzy Over a year ago

Thanks for your answer. I tried that but it still wont create any table.

Collectives™ on Stack Overflow

Why won't this PHP/SQL create the table?

1142 - CREATE command denied to user '$user'@'$my_ip' for table 'store_config'

I gave my user account 'DBO access' and it works now (the table is created).

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1142 - CREATE command denied to user '$user'@'$my_ip' for table 'store_config'

I gave my user account 'DBO access' and it works now (the table is created).

2 Answers 2

1 Comment

2 Comments

Related