PDO connection simple problem.

[Clanstrom]

Hi, I am chaging from mysql_* to PDO and I found this tutorial in Here
And, so far let's say I chose this query to update a database.

$id = 5;
$name = "Joe the Plumber";
try {
  $pdo = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $stmt = $pdo->prepare('UPDATE someTable SET name = :name WHERE id = :id');
  $stmt->execute(array(
    ':id'   => $id,
    ':name' => $name
  ));
  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

But the above code is composed of:
The connection, The query, and The exception.

My problem now, is that I am trying to seperate the connection and expetion and put in external file, and include them just once, like I used to include the mysql connection files in my page ex: database.php

I can't create external file to store the connection & exception because the query needs to be in the middle.

All I want is just somehow include the database connection externally and only run the statement like the below:

to update database.
Now the problem I need to separet the connection from the actuall code Since I have many queries.

include('pdo_connection_file.php');


  $stmt = $pdo->prepare('UPDATE someTable SET name = :name WHERE id = :id');
  $stmt->execute(array(
    ':id'   => $id,
    ':name' => $name
  ));
  echo $stmt->rowCount(); // 1

How can I do this?

It used to be easier in mysql as all I had to do was just

include('db.php');

$query = "SELECT * FROM TABLE";
$result = mysql_query....
while(...) {
//..echo data
}

[Atli]

There is nothing that says you have to connect and execute queries in the same try block. In fact, I would argue that doing so is in fact the wrong approach. You'll want to catch exceptions where you can deal with them, rather than just using a "catch-all" try clause that deals with everything.

For example, consider if you had this db.php file.

<?php
/** File: db.php */
try {
    $dbLink = new PDO("mysql:etc...", "usr", "pwd");
    $dbLink->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbLink->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
}
catch (PDOException $e) {
    include "fatalError.html";
    trigger_error("MySQL connection failure: " . $e->getMessage(), E_USER_ERROR);
}

The try clause there only catches and deals with connection errors. As that is a fatal error, it will use the "trigger_error" function to log that error to the configured log file (or the screen, if configured like that) and then exit the script, showing some HTML file with a nice, user-friendly error message first.

Now consider this file, which uses the database connection defined there:

<?php
/** File: userProfile.php */
require "db.php";

function getUserData($userID) {
    global $dbLink;
    $sql = "SELECT data FROM user_table WHERE id = :id";
    $stmt = $dbLink->prepare($sql);
    $stmt->bindValue("id", $userID);
    $stmt->execute();
    $row = $stmt->fetch();
    if ($row) {
        return $row;
    }
    else {
        throw new InvalidArgumentException("User ID does not match any users.");
    }
}

try {
    try {
        $user = getUserData($_GET["id"]);
    }
    catch (InvalidArgumentException $e) {
        try {
            $user = getUserData($_SESSION["userid"]);
        }
        catch (InvalidArgumentException $e) {
            include "userError.html";
            trigger_error("Couldn't find info for user profile: " . $e->getMessage(), E_USER_ERROR);
        }
    }
}
catch (PDOException $e) {
    include "fatalError.html";
    trigger_error("User profile query failed: " . $e->getMessage(), E_USER_ERROR);
}

// Display user profile based on the $user data.

This example uses several try-catch blocks, with different purposes. First of all, the PDOException that PDO can trigger is caught with the first try-catch block. Inside that block, however, I am trying to demnstrate the "catch where you can handle" concept. First I try to get the user data base on a GET value, but when that fails and triggers the InvalidArgumentException case, another try clause is used to execute the function again, this time trying to get user info based on a session value. Only if that fails does the script actually error out.

What I'm trying to get at with all this is that, you can use multiple try clauses to catch exceptions in multiple places, even nested within other try clauses. There is no need to put all code that throws an exception into the same try-catch block.

[Clanstrom]

This is very complicated. I just needed to make an external file for the connection, that I can inlcude in my files to run different queryies only. But in your second code, I don't even know what to make of it, it is more than the usually script for try/catch but I don't see how I can from it

[Atli]

If you're having trouble with the exceptions, you can always fall back on using old fashion if-else checks instead. Of course, for OOP code you tend to be better of with exceptions, but PDO does not insist on it. In fact, by default PDO does not use exceptions.

In your connection code, this is the line that tells PDO to use exceptions:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

If you simply remove it, PDO will stop throwing exceptions, and will instead rely on you testing statements and other PDO return values as you would have done with the old MySQL API. The only place you'll have to use exception handling is when you connect.

So, with that in mind, the second example could also be written like this:

<?php
/** File: userProfile.php */
require "db.php";

function getUserData($userID) {
    global $dbLink;
    $sql = "SELECT data FROM user_table WHERE id = :id";
    $stmt = $dbLink->prepare($sql);
    if ($stmt) {
        $stmt->bindValue("id", $userID);
        if ($stmt->execute()) {
            return $stmt->fetch();
        }
        else {
            include "fatalError.html";
            $info = $stmt->errorInfo();
            trigger_error("User profile query failed" . $info[2], E_USER_ERROR);
        }
    }
    else {
        include "fatalError.html";
        $info = $dbLink->errorInfo();
        trigger_error("Failed to prepare user profile query." . $info[2], E_USER_ERROR);
    }   
}

$user = getUserData($_GET["id"]);
if (!$user) {
    $user = getUserData($_SESSION["userid"]);
    if (!$user) {
        include "userError.html";
        trigger_error("Couldn't find info for user profile.", E_USER_ERROR);
    }
}

// Display user profile based on the $user data.