PHP/PDO Data access layer query error: “Fatal error: Call to a member function fetch() on a non-object”

Question

I am writing a web application to use CRUD (Create, Retrieve, Update and Delete) methods in a class to reduce code redundancy. When I try to run the invoking code I keep getting a fatal error that my $res is a non-object. Why is my $res not being picked up as a valid object?

Invoking Code

$db = Database::getInstance($username, $password);
$exists = $db->tableExists($dbname, $tablename);

echo ($exists)? "TRUE" : "FALSE";

Database Class

class Database
{
    private static $instance;

    private $dbh;

    private static $dbengine = 'mysql';
    private static $dbname = 'database';
    private static $dbhost = 'localhost';


    function __construct($username, $password)
    {
        $dbname = Database::$dbname;
        $dbhost = Database::$dbhost;
        $dbengine = Database::$dbengine;
        $this->dbh =  new PDO("$dbengine:host=$dbhost;dbname=$dbname", $username, $password);
     }


    public static function getInstance($username, $password)
    {
        if (!isset(self::$instance))
        {
            $object = __CLASS__;
            self::$instance = new $object($username, $password);
        }
        return self::$instance;
    }


    public function tableExists($table)
    {
        $dbname = self::$dbname;
        $sql = $this->dbh->prepare("SHOW TABLES FROM :dbname like :table");
        $sql->bindParam(':dbname' , $dbname);
        $sql->bindParam( ':table', $table);
        $res = $sql->execute();
        $rows = $res->fetch(PDO::FETCH_NUM);
        if ($rows)
        {
            if ($rows[0] == 1)
            {
                return TRUE;
            }
            return FALSE;
        }
        return FALSE;
    }

}

Code Modified from the Following Link

http://code.tutsplus.com/tutorials/real-world-oop-with-php-and-mysql--net-1918

When I run the Invoking Code I get:

Fatal error: Call to a member function fetch() on a non-object

Possible duplicate of [Can PHP PDO Statements accept the table or column name as parameter?](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) — Sean, Dec 17 '15 at 05:39
Your query is failing as you can't bind table names -> `SHOW TABLES FROM :dbname like :table` — Sean, Dec 17 '15 at 05:40

Giacomo1968 · Answer 1 · 2015-12-17T06:05:57.353

Your error is this:

Fatal error: Call to a member function fetch() on a non-object

That kind of error pops up when you have no connection to the MySQL database or the query flat out fails. Your $res is a “non object” since there are no results.

A quick Google search shows tons of results, but all slightly different solutions since everyone’s code is slightly different. But in your case, let’s look at this chunk of code in the constructor:

function __construct($username, $password)
 {
     $dbname = Database::$dbname;
     $dbhost = Database::$dbhost;
     $dbengine = Database::$dbengine;
     $this->dbh =  new PDO("$dbengine:host=$dbhost;dbname=$dbname", $username, $password);
 }

My gut feeling is something in there is not correct or not being set correctly. I would suggest you debug this using try/catch logic to give you more specific errors like this instead:

function __construct($username, $password)
 {
     $dbname = Database::$dbname;
     $dbhost = Database::$dbhost;
     $dbengine = Database::$dbengine;
     try {
       $this->dbh = new PDO("$dbengine:host=$dbhost;dbname=$dbname", $username, $password);
     }
     catch (PDOException $e) {
       echo 'Connection failed: ' . $e->getMessage();
     }
 }

I would also recommend setting your MySQL instance to display connection errors like this:

function __construct($username, $password)
 {
     $dbname = Database::$dbname;
     $dbhost = Database::$dbhost;
     $dbengine = Database::$dbengine;
     try {
       $this->dbh = new PDO("$dbengine:host=$dbhost;dbname=$dbname", $username, $password);
       $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     }
     catch (PDOException $e) {
       echo 'Connection failed: ' . $e->getMessage();
     }
 }

And past the initial connection, then in your tableExists function do something like this; I’m quickly inserting some try/catch in here as well which should work but feel free to adjust to suit your actual coding needs:

public function tableExists($table)
{
    $dbname = self::$dbname;
    try {
      $sql = $this->dbh->prepare("SHOW TABLES FROM :dbname like :table");
      $sql->bindParam(':dbname' , $dbname);
      $sql->bindParam( ':table', $table);
      $res = $sql->execute();
    }
    catch (PDOException $e) {
      echo "Error: " . $e->getMessage() . "<br />\n";
     return FALSE;
    }
    $rows = $res->fetch(PDO::FETCH_NUM);
    if ($rows)
    {
        if ($rows[0] == 1)
        {
            return TRUE;
        }
        return FALSE;
    }
    return FALSE;
}

I started off with a basic try catch wrapper before the response, but it was set to catch general exception not PDOException. I will try this approach now. — Vahe, Dec 17 '15 at 05:53
So are you saying that binding the database and table name in `SHOW TABLES FROM :dbname like :table` is valid? http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter — Sean, Dec 17 '15 at 05:55
@Sean, I changed my variable substitution method to replace the prepared syntax I had before. I now use $this->dbh->query("SHOW TABLES FROM $dbname like '$table'"); Unfortunately the error is still showing up. — Vahe, Dec 17 '15 at 06:00
@Sean No I am not. I did not notice that at first but it seems to make fish. But my approach to debugging stuff like this is to be able to view useful errors which will then most likely explain what the issue is and the end-user can take it from there. I strongly suggest you post an answer here explaining how table names cannot be bound in a PDO statement like this. — Giacomo1968, Dec 17 '15 at 06:01

score 1 · Answer 2 · answered Dec 17 '15 at 06:02

PDOStatement::execute() doesn't return a result set, it simply returns TRUE or FALSE. You should be calling fetch() on the statement object:

$sql->execute();
$rows = $sql->fetch(PDO::FETCH_NUM);

Of course, you should still be doing proper error handling, which I leave as an exercise for the reader.

score 0 · Accepted Answer · answered Dec 17 '15 at 06:23

My invoking code was incorrectly using two params.

$db->tableExists($dbname, $tablename);

So when I resolved it by removing the first parameter it worked.

$db->tableExists($tablename);

My query expected tablename is the first parameter but it saw $dbname which was causing my tableExists method to see the $dbname instead of $tablename. Because of this my query was incorrectly resolving to

show tables from $dbname like '$dbname'

PHP/PDO Data access layer query error: “Fatal error: Call to a member function fetch() on a non-object”

Invoking Code

Database Class

Code Modified from the Following Link

3 Answers3