PEAR::MDB2 migration

March 9th, 2006

Finally finished the migration to PEAR::MDB2. This took about two days of work for Geneone and a bit extra for neonDragon.net (issues with sequences).

Also determined some slow queries and added indexes. neonDragon keeps going on about a couple of queries now being 1,000,000% faster because of indexes.

I said functions such as autoExecute and query with arguments as the second string didn't exist in MDB2. I was wrong: they do exist and are part of the extended class although I didn't know that due to the poor documentation.

Geneone , PHP
Comments(3)

3 Responses to “PEAR::MDB2 migration”

# Dazzaon 13 Jun 2006 at 10:33 pm

Hi,

I’ve also been searching for what sems like ages to find a way round rewriting all my queries that use placeholders .. I don’t suppose you could give me a quick example of how you managed to get this working in MDB2 could you ??

Cheers.
# Khloon 13 Jun 2006 at 11:11 pm

Hi there,

You just have to make sure you use prepare/execute.

$res = $db->query ("select * from blah where test=?", array(1));

Becomes:

$q = $db->prepare("select * from blah where test=?");
$res = $q->execute(array(1));

It’s not particularly quick but it’s a one off job and if you use the search functionality to find all files with queries you should be able to change them all pretty quickly.

It’s worth noting that ! also can’t be used as placeholder anymore.
# Stoyanon 19 Jun 2006 at 11:28 pm

BTW, you can also use named parameters in MDB2:

$data = $db->getAll("select * from blah where test=:id", null, array(’id’ => 1));

And also if you dislike the idea of changing all your query()s to prepare/execute()s, you can do it only once, in a custom module:

class MDB2_Mything extends MDB2_Module_Common {

function myQuery($sql, $data) {
    $db =& $this->getDBInstance();
    $q = $db->prepare($sql);
    $res = $q->execute($data);
    $return $res;
}

}

Put this in Mything.php in an MDB2 dir inside the include path, then in your scripts do:

$db->loadModule(’Mything’);
$res = $db->myQuery("select * from blah where test=?", array(1)); // php5
$res = $db->mything->myQuery("select * from blah where test=?", array(1)); // php4