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

Question

Hi,
We have a Document Management application. I have 5000 image files loaded in mysql DB. Need to delete them when the folder is deleted at the client.

Using the following code,

public function delete($dbh){

    $sql = "DELETE FROM fileTable WHERE FID=:fid;
            DELETE FROM file_blobTable WHERE FID=:fid";
    $stmt = $dbh -> prepare($sql);
    $stmt -> bindParam(":fid", $this->fid, PDO::PARAM_INT);
    $this -> fdid = -1; //
    if ($stmt -> execute()) {
        return 0;
    }
    return 1;
}

The above function is called in a loop in this manner,

// Loop through the folder and delete all the files it contains.

foreach ($files as $fileID) {
     // Get DB handle
     $dbh1 = DB::getWriteDB();

      $f = new File($fileID);
      $f -> delete($dbh1);
}

This works perfectly when we delete,if the number of images in the DB is less then 500. If more, I am running into the dreaded, "Fatal error: Call to a member function prepare() on a non-object".

Please help.

Thanks
~Jad

Answer 1

Producing a prepared statement for every call to function delete is not only inefficient, it's likely the cause of your error. You only need to prepare a query once, which is one of the two main reasons they exist. You could use a static variable:

public function delete($dbh) {
    static $sql = "DELETE FROM fileTable WHERE FID=:fid;
        DELETE FROM file_blobTable WHERE FID=:fid";
    static $stmt = $dbh -> prepare($sql);

Or, since you're using objects anyway, use a class variable:

static protected function getDeleteQuery($dbh) {
    static $sql = "DELETE FROM fileTable WHERE FID=:fid;
        DELETE FROM file_blobTable WHERE FID=:fid";
    if (is_null(self::$queries['delete'])) {
        self::$queries['delete'] = $dbh->prepare($sql);
    }
    return self::$queries['delete'];
}
public function delete($dbh) {
    $stmt = self::getDeleteQuery($dbh);
    ...

Answer 2

As an alternative to outis' solution, if you, like me, like to avoid static functions and variables, you could redesigned the class to only prepare the statement once, and allow you to use one instance on multiple files.

class FileManager
{
    private $db_link;
    private $delete_stmt;
    private $file_id;

    public function __construct($db_link, $file_id=null)
    {
        $this->db_link = $db_link;
        $this->file_id = $file_id;
    }

    public function setFileID($file_id)
    {
        $this->file_id = $file_id;
    }

    public function delete()
    {
        if(!$this->delete_stmt)
        {
            $sql = "DELETE FROM fileTable WHERE FID=:fid;
                           DELETE FROM file_blobTable WHERE FID=:fid";
            $this->delete_stmt = $this->db_link->prepare($sql);
        }

        $this->delete_stmt->bindParam(":fid", $this->file_id, PDO::PARAM_INT);
        if ($this->delete_stmt->execute()) {
            return true;
        }
        return false;
    }
}

Which you could use like so:

$file = new FileManager($dbh);
foreach($files as $file_id)
{
    $file->setFileID($file_id);
    $file->delete();
}
$file = null;