I was looking for a database wrapper / abstraction layer to use for PHP a couple weeks ago. I’m working on a large PHP project and its using Oracle. I started out with the app using oci calls throughout. Launched the beta – and its working pretty well. Now I’m going through cleaning up sections of code gearing up for a 1.1 release and thought I’d look for something to make the database coding easier. Not that its difficult, but each database call is bloated with multiple lines of code for each call, I wanted something easier. Enter ezSQL.
ezSQL was created by Justin Vincent – and its nothing short of amazing. ezSQL can sit on a multitude of databases including – mySQL / Oracle8/9 / InterBase/FireBird / PostgreSQL / MS-SQL / SQLite / SQLite c++ … I mean WOW. One thought I had was to possibly have a development version of the database as MySQL – and the production version as Oracle. The data structure won’t change, I’m pretty much building screens off of that, so table changes would be minimal. But seriously, in practice who does that ? Hmmm, maybe me. We’ll see after I get out from under the mound of work I have. My problem is – while I have VPN access to the Oracle database, if I don’t have a net connection – I can’t work on the app – since I have no database, not good. So this may help me be more productive, if I’m sitting at the pool waiting for my kids to finish swim practice, I can code, or if I’m out in the park or at my mother-in-law’s place (yea – she’s got no wireless). Anyway, the more I think about this – I could easily create a config option as to which database to use.
Back to ezSQL…
Once configured (which took a whole 2 minutes, and that was because I moved the files from the demo into my app) you can make calls like this –
$users = $db->get_results("SELECT name, email FROM users");
Typically, results are stored in an object, but you can alter the call to return associative arrays, or numerical arrays.
So using the example above, you’d pull out the name and email like this –
foreach ( $users as $user )
{
// Access data using object syntax
echo $user->name;
echo $user->email;
}
If you have just one row you use get_row, if you’re simply getting 1 item like a count, you use get_var. A simple to read instruction page explains it all. There are many examples on that page to help get you started.
Probably the best feature is $db->debug(); this little gem (can’t thank Justin enough for this one) spits out a pile of information about the query, as well as the data that’s coming back. So if you’re having a problem with the database call – toss in this command and it’ll help you out.
Another gem is the $db->vardump($users); which displays the contents and structure of the variable / object. Again great for determining what is happening with a query.
I know I sound like a paid advertisement for this, but the support was amazing too. I had a problem with the version I had downloaded, and emailed Justin. The next day – he fixed it right away and posted a new version, which is currently posted on his site (2.10)
Overall, this is a big win for me.