Connect to database, handling errors

Question

I try to handle or catch possible errors with PHP/MySQL for security reasons and would like to know if I'm doing it right.

The first case, connection: I use it as a function and call it always when I need a database connection:

function pdo () {
  try {
    $pdo = new PDO('mysql:host=localhost;dbname=dbname', 'user', 'pw');
  }
  catch(PDOException $e) {
    header("Location: handle_error.php");
    exit;
  }
  return ($pdo);
}

The second case, a query: I am not sure how to handle it. I put all prepared statements in IF conditions but that's pretty awkward. And what about $stmt->execute? This could also fail or not? To handle this also in an if condition can really get confusing. I hope there is a better way to go.

if ($stmt = $pdo->prepare("SELECT a FROM b WHERE c = :c")) {
  $stmt->execute(array(':c' => $c));
  $result = $stmt->fetch();
  echo 'All fine.';
}
else {
  echo 'Now we have a problem.';
}

Later, I would like to report the errors properly so the admin can check what's going on.

Answer 1

First of all, it's a good thing that you asked, only a few do care about handling errors.

Unfortunately, the way you choose is frowned upon, in both cases. Luckily, I've got a couple articles that cover your question in every possible detail:

• a generalized one, PHP error reportig will show you the right approach for handling errors in general
• a direct answer to your question, How to connect to MySQL using PDO would provide a ready-made connection code

Error handling

The idea here is that a module or a part of code should never treat errors by itself. This function should be delegated to the application level. Your database interaction code should only raise an error, which would be handled according to the site-wide configuration elsewhere.

So your goal with PDO is just to make it throw an error (in the form of Exception). As to how it will be handled should be defined elsewhere. It will make your error reporting uniform and both programmer- and user-friendly.

Connection

Another issue is a function.

I use it as a function and call it always when I need a database connection.

If you call it more than once, it will create many connections to the database server and this number is not infinite. A connection should be made only once. So in its current form this function is rather useless. Given you have to create a $pdo variable only once, an include file would serve as well.

So let's create it

$host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES   => false,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new \PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

Handling query errors.

Like it was said above, do not handle errors in your code. We already configured PDO to throw exceptions in case of error, that's all. So just write your query execution code right away, without any conditions:

$stmt = $pdo->prepare("SELECT a FROM b WHERE c = :c")) {
$stmt->execute(array(':c' => $c));
$result = $stmt->fetch();
echo 'All fine.';