PHP and MySQL Table Wrapper
August 26th, 2005
I have been reading through ‘Agile Web Development with Rails‘ on and off for the past few weeks. The thing that impresses me most about Rails is the removal of tedious programming activities. It allows you to focus on the more advanced, challenging and fun aspects of programming. The other day, as I was working on a project, I became inspired to remove some tedium. Thus was born a few classes and an easy way of removing a lot of SQL from my code.
Basically, I stole a few ideas from rails and made a PHP class that makes it super easy to find, insert, update and delete MySQL data. Here is a zip of the files that I will be referencing in this article. The zip contains three files. The first is a MySQL class which makes it easy to connect to and query a database. I was using PEAR DB for my database access class, but I thought for the sake of making the table class easy for those without PEAR to use, I would just make a quick MySQL class to package with it. The MySQL class is the same class as can be found in the PHP Anthology from Sitepoint with a few modifications to make the API more like PEAR DB. The second file is the table wrapper class (which I wrote) and the third is a test file that includes several examples of how to use the classes.
Disclaimer: This class is basic. I have not attempted to make PHP on Rails (check out Cake if that is what you are looking for). I simply admired some of the functionality of Rails and ported it to PHP. Onward to the examples.
Inserting
Below is a sample of just how easy it is to use this class.
<?phprequire_once 'class.mysql.php';require_once 'class.table.php';// just put the host, username, password and database in place below$db =& new MySQL('localhost', 'root', '', 'class_test');// create a new table wrapper$product =& new table($db, 'products');// you can insert a record by$product->title = 'Title';$product->description = 'Here goes the description.';$product->price = 19.95;$product->date_created = time();$product->save();?>
Above is just an example though. This isn’t just a wrapper for a products table, it’s for any table. Simply create the MySQL instance and pass that and a table name and a class variable is created for each field in the table. The variables are even defaulted to the default value for the field in the table. Change the variables to whatever you want and call the save method. The save does both inserting and updating without you having to tell it which.
Selecting and Updating
Let’s say instead of inserting a new record you want to update one already in the database. Simply find it using the table row id you would like to modify, change the fields to be updated and once again call the save() method like below.
<?php// you can find a record...$product->find(1);// ...change somethings...$product->title = "new product one title";$product->price = 5.99;$product->date_created = time();// ...and save it.$product->save();?>
That’s great John, but I only want to update one attribute, say the title.
<?php// you can find a record...$product->find(4);// ...and update one or more fields...$product->updateAttr( array('title'=>'new title') );// ...or do it all in one line by passing the id you want to find$product->updateAttr( array('title'=>'new title'), 4 );?>
Want to deal with more than one record at a time? That is also easy to do.
<?php// find a bunch of rows based on where and/or order by; returns array of objects if any found - false if none are found$by_title = $product->findMany('WHERE price >= 5', 'ORDER BY title');if ($by_title) {// if any found show the id and titleecho '<ul>';foreach($by_title as $p) {echo '<li>' . $p->id . ': ' . $p->title . '</li>';}echo '</ul>';} else {// else let the user know that no records were foundecho '<p>No products found based on the criteria.</p>';}?>
Deleting
So it slices and dices but can it delete? I’m glad you asked.
<?php// just hand it an id$product->destroy(4);?>
I have also thrown in some debugging methods setDebug() and dump() to figure out what is going wrong in the unfortunate event that something does.
Again, here is the zip of the two classes and the dinky little test file. The test file isn’t much, but I have commented the code of both the classes pretty good. Download the classes and give them a try. If you have any questions or improvements, let me know in the comments or by contacting me.
Change Log
- v1.01 - added a find($result->insertID()) after saving a new record
- v1.0 - original release
Subscribe
Hi John, I saw you placed your class on phpclasses.org, great! Indeed, it certainly deserves a place there. Already started playing with it and so far no problems. Will let you know if I have any questions. Thanks, Matthijs
Glad its working out for you. I have always been a fan of the code at phpclasses.org, but man do I wish they would redesign (usability and aesthetics). If it wasn’t for the great classes I don’t think I could stand to look at it and use it.
Yes, you’re so right about that. It wasn’t untill I knew there is good stuff there that I started revisiting the site to browse the classes. But you really need to learn how the site works and were to find everything, otherwise you get desperately lost … if you have the time to wait for the pages/ads to download that is.
This is absolutely awesome! Great job.
Question: I have tested this by substituting an existing database object, created using ADODB: so far it seems to work: can you see any reasons why it would not work?
@James - As long as the database object has the same methods and those methods return in the same format you shouldn’t have problems. I used this with PEAR::DB for a while. The only thing that may cause issues is the error checking. What you could do though is modify the error checking to fit your database object.
Thanks John: great job on the code, it certainly takes away a lot of the work I was about to get started on…
Props!
Very Cool! I find the concept quite similar with another tool, PHP Object Generator, I sometimes use, except that yours, the table/objects are specified at runtime… I can see myself using your tool when performance is not critical or for small projects. Great job.
@euginio - Thanks. Just so you and anyone else knows, I won’t be updating this class at all as I am switching to Rails. Why not use the real thing, eh?
Nooooooooo! :)
I’m mainly a CF programmer, but my co-worker, a PHP programmer, has assigned a challenge to me. I read about this class recently so I thought I’d use it.
I’m MUCH more familiar with writing raw SQL than with classes like this. How do I perform a join? I need to join a table to itself to get a breadcrumb trail but I can’t see how this class would let me perform that.
@Andy - This class doesn’t handle joins. It was merely a concept that I fleshed out on a Saturday. It works great for simple stuff but complex queries such as joins I just didn’t have time for.
Gotcha…that explains why I couldn’t get it working. Thanks Jon. I wrote something really similar in CF but only for INSERT, DELETE and UPDATES for that same reason. There’s too many variables when it comes to SELECT statements.
Keep up the good work.
File class.table.php Line 238
A modification:
// there hasn’t been a find so we are inserting
#modificado: ” por NULL en valor de ID
$sql = “INSERT INTO $this->table (” . implode(’,', $this->fields) . “) VALUES (NULL,”;
Autoincrements integer fields need that.
Great class, thanks!
Bye!
PD: there are a new version?
@Gaston - Nope, no new versions. I have switched to Ruby from PHP so I don’t keep up on this.
Hi can anyone show me how to do a join table query with this wrapper ?
and a sample of query of update statement that have multiple condition in the “WHERE” clause.
thanks.
@Tay - See comments #9 and #12. If you need more complex functionality, you’ll have to write it yourself. Sorry.
I’ve been using CakePHP from long and I really loved its Mysql database model class, and I’m really enjoying your code. I’m gonna add more features to it soon and extend it…. Thanks for your great work !!
@Max - Perhaps you’d like to share your efforts?