Migrating current code to ADODB

[mgm_03]

All of my php functions are written for mysql. I would like to migrate towards ADODB for the sake of portability. My question is how can I modify my current code to do this? Below is a simple function example :

function randomQuotes($catID) {
$result = mysql_query("SELECT * FROM quotes, cats WHERE quotes.catID=cats.catID AND quotes.catID='$catID' ORDER BY RAND() LIMIT 1 ") or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
$random = $row['quote'].' -'.$row['auth'];
}
return $random;

}

I know how to instantiate ADODB :

require_once("adodb/adodb.inc.php");
$conn = &ADONewConnection('mysql');
$conn->PConnect('localhost','user','pass','test');
$conn->debug = true;

But from there, I am stuck. Do I need to turn my function into a class and extend one class or the other? I have searched google and elsewhere but no luck. Thank you!

[ironik]

You'll need to set the $conn object as a global in order to access it within the function:

function randomQuotes($catID) {

global $conn;

$result = $conn->SelectLimit('SELECT quote, auth FROM quotes, cats WHERE quotes.catID=cats.catID AND quotes.catID=' . $conn->quote($catID) . ' ORDER BY RAND()', 1);

if ($result)
{
$random = $result->fields[0].' -'.$result->fields[1];
}
return $random;

}

You could also set the object as a global by using something like:
$GLOBALS['DB_CONNECTION'] =& $conn;

You can then use the variable $GLOBALS['DB_CONNECTION'] anywhere in your code regardless of scope.

The above is untested, but I think it should work. I've changed your query a little so it'll be more portable. I'm not sure about the RAND() function though, it's probably not compatible with all databases.

I'd suggest reading 'tips on writing portable SQL' in the ADODB manual, it has some good suggestions there. Also, use the quote() or qstr() functions to properly quote your variables. This will make sure that if you ever port your code to another database ADODB will find the right quotes to use around the variables for you. The manual has heaps of information on how to use ADODB as well, probably one of the better manuals out there for an open source library.

SelectLimit() is a special method within ADODB that allows you to use an offset and limit. I think only PostGres and MySQL support the LIMIT clause in an SQL statement in the format you used.

[mgm_03]

Thanks for the reply.

I'll follow your advice and RTM.