Webeng Webeng - 4 months ago 9
SQL Question

Is this conversion to PDO correct?

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

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);