PDO test if connected

Question

I can't find an answer to this anywhere. Maybe its really simple

I have my mysql PDO connection like this:

try{
$DBH = new PDO("mysql:host=$db_hostname;dbname=$db_database", $db_username, $db_password);
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  
}
catch (PDOException $e){
echo $e->getMessage();
exit;
}

i want to just test if the connection worked, ie. if the password, username, databasename & hostname were spelled correctly.

the try, throw just seems to pick up fundamental errors, like if the driver is spelt wrong. it doesnt throw an error if say the password is wrong.

thanks

Solution 1

In a single click from this question, in the PDO tag wiki lies the exact how-to:

$dsn = "mysql:host=localhost;dbname=test;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn,'root','', $opt);

As well as a warning

DO NOT use try..catch operator just to handle an error message.
Uncaught exception already excellent for this purpose, as it will treat PDO errors just the same way as other PHP errors - so, you can define the behavior using site-wide settings. A custom exception handler could be added later, but not required. Especially for new users, it is recommended to use unhandled exceptions, as they are extremely informative, helpful and secure. More info...

Solution 2

I use the following code to connect:

    <?php
class dbConnection extends PDO{

public function __construct() {    
  switch(DB_TYPE){
    case "mysql":
        $dbconn = "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET;
        break;
    case "sqlite":
        $dbconn = "sqlite:".DB_PATH.";charset=".DB_CHARSET;
        break;
    case "postgresql":
        $dbconn = "pgsql:host=".DB_HOST." dbname=".DB_NAME.";charset=".DB_CHARSET;
        break;
    }

    parent::__construct($dbconn,DB_USER,DB_PASS,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

   }
}
?>

If I give wrong password, I get

Connection error, because: SQLSTATE[28000] [1045] Access denied for user 'microaid_logger'@'localhost' (using password: YES)

As Your common sense pointed out, an exception is already thrown in case the connection is not succesful, which will also trigger if the password is wrong. If you want to format the text of the error message or handle it, just set a custom error handler as described here

Comments

[Your Common Sense]

For catching connection error. It is very bad practice to use try..catch to handle the error message, makes code overbloated. Make your errorHandler function a real exception handler to act exactly the same way, but without the need of writing try..cach for the every query and you'll be okay.

[coderkane]

Thanks for pointing that out, I corrected the answer - and learnt something new in the process :)

[Your Common Sense]

Look, even without catch you will have your exception handled perfectly: it will be either logged or shown on-screen following ini. settings. And even have a backtrace with it. And PHP will send 500 status automatically as well

[coderkane]

Yes, you are right; I added a note to add the custom error handler in case you want to send a http response code, or maybe redirect the user to a generic "oops" page after logging the error.