PHP PDO: Unable to connect, Invalid catalog name

问题:

I am trying to set up a new site on my hosting (Host route if it matters) but i keep getting this error when i try using PDO (first PDO site im trying):

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected' in /home/kennyi81/public_html/gamersite/login.php:36 Stack trace: #0 /home/kennyi81/public_html/gamersite/login.php(36): PDOStatement->execute() #1 {main} thrown in /home/kennyi81/public_html/gamersite/login.php on line 36

When i use these settings:

$dbh = new PDO("mysql:91.146.107.11;dbname=kennyi81_gamersite", "kennyi81_gamer", "***************");
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

....

$stmt = $dbh->prepare('SELECT * FROM USERS WHERE ID = :id LIMIT 1');

How the database is laid out:

I am able to use mysqli connect fine on my other sub domains / main site, but i just cannot get PDO to work.

I've tried this, which i have seen around:

 $stmt = $dbh->prepare('SELECT * FROM gamersite.USERS WHERE ID = :id LIMIT 1');

but it retuns a syntax error.

Anyone have any idea what may be causing this?

---

This is all working on my local server, nothing changed on upload apart from connect line.

回答1:

Instead of:

$dbh = new PDO("mysql:91.146.107.11;dbname=kennyi81_gamersite", "kennyi81_gamer", "***************");

Try:

$dbh = new PDO("mysql:host=91.146.107.11;dbname=kennyi81_gamersite", "kennyi81_gamer", "***************");

(add host=)

And it most likely works on your local server, because you have mysql:localhost... or mysql:127.0.0.1... there and it's ignored (cause it's missing host= aswell) and by default it's localhost.

回答2:

From the PDO manual page, you can see that you need to wrap the connection in a try/catch block. This way if something goes wrong with the connection, it will tell you. Something like this:

try {
    $dbh = new PDO("mysql:91.146.107.11;dbname=kennyi81_gamersite", "kennyi81_gamer", "***************");
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";

    // Then actually do something about the error
    logError($e->getMessage(), __FILE__, __LINE__);
    emailErrorToAdmin($e->getMessage(), __FILE__, __LINE__);
    // etc.
    die(); // Comment this out if you want the script to continue execution
}

The reason you are getting this error is because there is an error with your connection, but since you don't tell your script to stop, it doesn't. Look at the error message produced, and how to fix it should be made obvious. It appears that Michael Prajsnar's answer is correct in that you aren't setting a "host".

Edit:

As it turns out, PDO doesn't complain if you leave out your host or dbname in the PDO connection DSN part (at least on Unix). I tested it and leaving it blank will default it to "localhost" and I was therefore able to connect perfectly fine leaving this out completely for localhost connections, which would explain why it worked on your local server but not on your production server. In fact, it is completely possible to connect supplying absolutely nothing in the DSN except for the database engine like this:

$dbh = new PDO("mysql:", "kennyi81_gamer", "***************");

The only problem is that it won't be using a database, so to USE a database, just do:

if ($dbh->query("USE kennyi81_gamersite") === false)) {
     // Handle the error
}

However with that said, I have my doubts that you actually tried connecting using a try/catch block (as you mention in your comments) unless you somehow provided valid database credentials. The ONLY way that doing it this way did not produce any sort of error is if you actually connected correctly and selected the database kennyi81_gamersite. If not, you would have seen a message like this:

Unable to connect to database. "mysql" said: SQLSTATE[28000] [1045] Access denied for user 'kennyi81_gamer'@'localhost' (using password: YES)

In summary, always wrap your connection in a try/catch block if you want to find errors during connection. Just make sure not to re-throw (and not catch) the PDOException's getMessage() or you could expose your login credentials.