I'm using PHP 5.1.6 and MDB2 and trying to wrap my prepare/execute/fetchAll into a class so I can execute a select query with one line. The following code shows the Class that I've created and also doing the same query directly:
<?php
include_once "MDB2.php";
class db {
private static $dsn = array(
'phptype' => "mysqli",
'username' => "username",
'password' => "pass",
'hostspec' => "localhost",
'database' => "dbname"
);
private static $instance = NULL;
private static $statement = NULL;
private static $resultset = NULL;
private function __construct() {}
private function __clone() {}
public static function getInstance() {
if (!self::$instance) {
self::$instance =& MDB2::factory(self::$dsn);
}
return self::$instance;
}
public static function execQuery($sql, $types, $values) {
if (self::$instance === NULL) {
self::getInstance();
}
self::$statement = self::$instance->prepare(
$sql, $types, MDB2_PREPARE_RESULT);
self::$resultset = self::$statement->execute(array($values));
if (PEAR::isError(self::$resultset)) {
// (this is where it fails)
echo('Execute Failed: ' . self::$resultset->getMessage());
return false;
}
return self::$resultset->fetchAll(MDB2_FETCHMODE_ASSOC);
}
}
echo "<pre>";
$dsn = array(
'phptype' => "mysqli",
'username' => "username",
'password' => "pass",
'hostspec' => "localhost",
'database' => "dbname"
);
$sql = "select * from testtable where id = ? order by id LIMIT ?"
$t = array('text','integer');
$v = array('ABC',3);
// GOING DIRECT
$db =& MDB2::factory($dsn);
$stmt = $db->prepare($sql, $t, MDB2_PREPARE_RESULT);
$res = $stmt->execute($v);
$out = $res->fetchAll(MDB2_FETCHMODE_ASSOC);
print_r($out);
// GOING THROUGH CLASS
print_r( db::execQuery($sql, $t, $v) );
?>
The output of going direct works as expected, but the second attempt, going through the class fails with the PEAR error message "MDB2 Error: not found". I can't see what the difference is between these two approaches.
The Class works properly if I only pass a SQL statement with one ? replacement and don't use 'array()' to hold the types and values. If I change these, it works:
$sql = "select * from testtable where id = ? order by id"
$t = 'text';
$v = 'ABC';
I found the problem with my code. The line in the execQuery method that reads:
self::$resultset = self::$statement->execute(array($values));
should be:
self::$resultset = self::$statement->execute( $values );
OOP is new to me so I was convinced the problem was with the class and method.
(I'm not sure if I should be answering my own question, or just put it in the comments)