Error when upgrading MySQL to PDO: Call to a member function fetch() on a non-object

I’m just learning PHP and MySQL, and I’m trying to put in a comment system. I found this website: http://www.evanpetersen.com/item/php-and-mysql-recursion.html which seems to have what I want. However, it uses MySQL, and I need PDO. I tried to alter the code to make it work, but I’m encountering an error. Here is the original code:

<?php
$db = mysql_connect("$dbHost", "$dbUser", "$dbPass") or die ("Error connecting to database.");
mysql_select_db("$dbDatabase", $db) or die ("Couldn't select the database.");
function getComments($ID) {
   $ticket = mysql_query("SELECT * FROM ticketText WHERE `ID` ='$ID'");
   while ($ticketRow = mysql_fetch_array($ticket)) {
      echo '<p style="">'.$ticketRow['text'].'</p>';
   }
   $thread = mysql_query("SELECT * FROM ticketText WHERE `referenceID` ='$ID'");
   if (mysql_num_rows($thread)>0) {
      echo '<div style="margin-left:10px; border-left: 1px black dotted; ">';
      while ($nextTicket = mysql_fetch_array($thread)) {
         getComments($nextTicket['ID']);
      }
      echo '</div>';
   }
}
getComments(1);
?>

And here is my altered version:

<?php
function getComments($ID) {
    $db = new PDO("mysql:host=$dbHost;dbname=$dbDatabase", "$dbUser", "$dbPass") or die ("Error connecting to database.");

    $ticket = $db->query("SELECT * FROM ticketText WHERE `ID` ='$ID'");
    while ($ticketRow = $ticket->fetch(PDO::FETCH_ASSOC)) {
        echo '<p style="">'.$ticketRow['text'].'</p>';
    }
    $thread = $db->query("SELECT * FROM ticketText WHERE `referenceID` ='$ID'");
    $result = $db->prepare("SELECT * FROM ticketText WHERE `referenceID` ='$ID'");
    $result->execute();
    $number_of_rows = $result->fetchColumn();
    if ($number_of_rows>0) {
        echo '<div style="margin-left:10px; border-left: 1px black dotted; ">';
        while ($nextTicket = $ticket->fetch($thread)) {
            getComments($nextTicket['ID']);
        }
        echo '</div>';
    }
}
getComments(1);
?>

The problem is, I must have altered it incorrectly (not surprising, since I don’t really know what I’m doing yet), because it throws this error: Fatal error: Call to a member function fetch() on a non-object for the line while ($ticketRow = $ticket->fetch(PDO::FETCH_ASSOC)) . Frankly, I don’t even know how to begin to address this problem because most of this is still gibberish to me. What do I do?

Answer

Always prepare the query
Check for the returned values
Print errors
$nextTicket = $ticket->fetch($thread) is really strange
If you query 3 times the same query you doing it wrong.
Enable error mode
no need for recursion, just loop through the records.

function getComments($ID) {
    $db = new PDO("mysql:host=$dbHost;dbname=$dbDatabase", "$dbUser", "$dbPass") 
          or die ("Error connecting to database.");
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "SELECT * FROM ticketText WHERE `ID` = ? OR `referenceID` = ?";

    if($stmt= $db->prepare($sql)){
        if($stmt->execute(array($ID, $ID))){
            if($stmt->rowCount()>0){
                while($ticketRow =$stmt->fetch()){
                    echo '<p style="">'.$ticketRow['text'].'</p>';
                }
            }
        }
    }else{
        echo 'failed to prepare';
        print_r($db->errorInfo());
    }

Advertisement

Answer