Is this conversion to PDO correct?

Question

I am attempting to convert the following code to PDO:

    $result = DB::query('SELECT * FROM webchat_lines WHERE id > '.$lastID.' ORDER BY id ASC');

    $chats = array();
    while($chat = $result->fetch_object()){

        // Returning the GMT (UTC) time of the chat creation:

        $chat->time = array(
            'hours'        => gmdate('H',strtotime($chat->ts)),
            'minutes'    => gmdate('i',strtotime($chat->ts))
        );

        $chat->gravatar = Chat::gravatarFromHash($chat->gravatar);

        $chats[] = $chat;
    }

    return array('chats' => $chats);

My FAILED attempt:

    $paramArray = array(
         ":lastID" => $lastID
    );
    $result = DB::query('SELECT * FROM webchat_lines WHERE id > :lastID ORDER BY id ASC', $paramArray);

    $chats = array();
    $chats = array();
    while($chat = $result->fetch(PDO::FETCH_OBJ)){ //CHANGED THIS PART

        // Returning the GMT (UTC) time of the chat creation:

        $chat->time = array(
            'hours'     => gmdate('H',strtotime($chat->ts)),
            'minutes'   => gmdate('i',strtotime($chat->ts))
        );

        $chat->gravatar = Chat::gravatarFromHash($chat->gravatar);

        $chats[] = $chat;
    }

    return array('chats' => $chats);

EDITED:

An ajax request is being done in the attempts to receive the information returned from the previous php code. The original source of the non-PDO code can be found here: http://tutorialzine.com/2010/10/ajax-web-chat-css-jquery/

QUESTION: shouldn't $chat = $result->fetch(PDO::FETCH_OBJ) be equivalent to $chat = $result->fetch_object()? What could I be doing wrong?

EDIT 2:

I have found the following error in the error_log:

PHP Fatal error: Call to a member function fetch() on a non-object in on line 151

It seems that $result isn't being seen as an object. The code that defines $result as been placed above in both samples. The code for function DB::query() is as follows:

public static function query($q, $paramArray=array()){

    $stmt = self::$instance->conn->prepare($q);
    foreach ($paramArray as $key => $value)
    {
        $stmt->bindParam($key, $value);
    }
    return $stmt->execute();

    //the older version of this function had this code:
         //return self::$instance->conn->query($q); 
    //but I replaced it with the previous so that I can bind the
    //parameters and prepare the sql statement
}

shouldn't $result hence be an object? it's returning $stmt->execute();

Answer 1

It's not often possible to use PDO as a direct drop-in replacement for older mysql_*() code, and some conceptual reorganization is necessary here.

PDOStatement::execute() never returns an object of any type. Rather, its return value is a boolean TRUE/FALSE based on success or failure of the SQL statement.

In your previous code, you were returning a result resource from the query() method, and later fetching from it, performing additional logic inside the fetch loop. Since execute() won't return an object that can be used that way, I would suggest instead performing the complete fetch operation inside the query() method then later working with a simple foreach loop to make use of the rowset. It is also possible to return $stmt; from the query() method, but that seems logically muddled.

public static function query($q, $paramArray=array()){

    $stmt = self::$instance->conn->prepare($q);
    foreach ($paramArray as $key => $value)
    {
        $stmt->bindParam($key, $value);
    }
    // Execute the statement
    $stmt->execute();
    // Then fetch and return all rows as an array of objects
    return $stmt->fetchAll(PDO::FETCH_OBJ);
}

Then, to make use of the returned rowset, switch your while fetch loop for a foreach loop:

// $result is now an array of objects...
$result = DB::query('SELECT * FROM webchat_lines WHERE id > :lastID ORDER BY id ASC', $paramArray);

$chats = array();
$chats = array();
foreach ($result as $chat){
    // Returning the GMT (UTC) time of the chat creation:
    $chat->time = array(
        'hours'     => gmdate('H',strtotime($chat->ts)),
        'minutes'   => gmdate('i',strtotime($chat->ts))
    );

    $chat->gravatar = Chat::gravatarFromHash($chat->gravatar);

    $chats[] = $chat;
}
return array('chats' => $chats);
// etc...

From the code in context, it is hard to tell if you have configured PDO to throw exceptions on failure. I would advise doing so, since by default it will error silently when execute() or other methods fail. Where the PDO instance is created, set the appropriate attribute:

// Turn on PDO exceptions when the instance is first created
// That may not have been within this class, but difficult to 
// tell from the limited code posted.
self::$instance->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);