Write PHP PDO queries as `dbName`.`tableName` as opposed to `tableName` - why?

Question

I'm writing a user registration function in PHP PDO, and I have found that my query will only run fine if it is written like this:

<?php
    $dbHost="localhost";
    $dbName="project";
    $dbUser="admin";
    $dbPassword="abcd";
    $dbh=new PDO("mysql:host=$dbHost;dbName=$dbName", $dbUser, $dbPassword);
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $query=$dbh->prepare("INSERT INTO project.users (userName, userEmail) VALUES (?,?)");
.....

On the other hand, it will not run if I write:

...
$query=$dbh->prepare("INSERT INTO users (userName, userEmail) VALUES (?,?)");
...

In that case, I get the following error message:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected' in C:\wamp\www\Tests\Test03\Index.php:11 Stack trace: #0 C:\wamp\www\Tests\Test03\Index.php(11): PDOStatement->execute() #1 {main} thrown in C:\wamp\www\Tests\Test03\Index.php on line 11

Why is it that I need to precise project.users? Why isn't it enough to enter the table name, given that the db name itself is already in the PDO object?

Thank you!

JDelage

UPDATE Please see accepted answer below. Replacing dbName=$dbName with dbname=$dbName solves this problem.

because you have a typo in `dbame`? (**Edit:** Ah, that's not it - just saw your previous question.) — Pekka, Jun 09 '11 at 23:30
However, you didn't follow my advice and turn PDO's error output on as described in [this question](http://stackoverflow.com/q/3726505) using `PDO::ATTR_ERRMODE`. That should be your first step. — Pekka, Jun 09 '11 at 23:33
Fair point, I have edited the question to incorporate your suggestion. — JDelage, Jun 09 '11 at 23:52
Is this a direct copy and paste, or has it been edited? I have a strong suspicion that `dbName` is written as `dBName` or other small case difference, resulting in the connection string being `mysql:host=localhost;dbName=`. Can you echo out your DSN just to be certain? — Conspicuous Compiler, Jun 10 '11 at 00:04
That's a direct copy-paste. However, I am interested in doing anything I can to clarify things. What do you mean by "Can you echo out your DSN"? I'm a beginner and any specifics would be most helpful. Cheers. — JDelage, Jun 10 '11 at 00:09
I had the same problem just by leaving a space before or after the value for the "dbname". — MTVS, Nov 16 '12 at 17:35

score 12 · Accepted Answer · answered Jun 10 '11 at 00:04

12

Apparently PDO was unable to set active database to be "project" and therefore you need to specify it every time.

Try to modify your line to look like this:

$dbh=new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUser, $dbPassword);

The only difference is that dbname is spelled all lower-case instead of yours dbName.

Alternatively, execute this SQL command after successfully establishing a connection: USE project;, e.g.

$dbh->exec('USE project;');

answered Jun 10 '11 at 00:04

LazyOne

158,824
45
388
391

2

Spelling `dbname` instead of `dbName` solves it. Unbelievable - I was certain I had tried that... – JDelage Jun 10 '11 at 00:12
I had the same problem just by leaving a space before or after the value for the "dbname". – MTVS Nov 16 '12 at 17:36

score 0 · Answer 2 · edited Oct 20 '12 at 06:51

0

Try:

"mysql:dbName=$dbName;host=$dbHost"
"mysql:dbName=$dbName;host=$dbHost;"

instead of:

"mysql:host=$dbHost;dbName=$dbName"

sometimes the order is important.

edited Oct 20 '12 at 06:51

user

86,916
18
197
190

answered May 28 '12 at 08:29

Sylwek691

1

Write PHP PDO queries as `dbName`.`tableName` as opposed to `tableName` - why?

2 Answers2

Linked