Taking a first look at the AutoCRUD for PHP library
(Page 5 out of 5)Automatically handling relationships
One of the amazing things of the AutoCRUD for PHP library is that it automatically handles relationships between tables. For example, let's consider the article and comment table. If you have a look at the comment table, you'll notice a field called 'article', which will contain the id of the article to which the comment belongs to. So we have a relationship between the article and comment table, which means we should be able to get a list of articles and all of their comments. If you want to do this normally, be prepared to use all kinds of workarounds or really inefficient code whereby a query is executed in a loop (meaning you could be executing hundreds of queries). But AutoCRUD for PHP solves this problem for you.
First, you have to setup a relationship between the two tables, like so:
$crud->comment->addRelationship ($crud->article, 'article', 'articleid', 'many-to-one');
?>
The addRelationship() method is used to setup the relationship. The first argument should be the other CRUD object. The second and third arguments should be the fields used in the relationship. The last argument is used to define the relationship type, which is many-to-one in this case, since there are many comments to one article.
It's also possible to setup this relationship the other way round, like so:
$crud->article->addRelationship ($crud->comment, 'articleid', 'article', 'one-to-many');
?>
Everything is swapped around, but the end result is the same.
Now that the relationship is set up, all you need to do is use the join() method when selecting articles. The below example demonstrates this:
// Setup relationship
$crud->comment->addRelationship ($crud->article, 'article', 'articleid', 'many-to-one');
// Get articles and their comments
$crud->article->join ('comment');
$articles = $crud->article->select();
echo ''
;
print_r ($articles);
echo '';
?>
And that's all it takes to select the related records, and it's also extremely efficient. Instead of executing dozens of queries, only two queries are executed. It's now possible to select comments and the article to which they belong as well, like this:
// Setup relationship
$crud->comment->addRelationship ($crud->article, 'article', 'articleid', 'many-to-one');
// Get comments and their article
$crud->comment->join ('article');
$comments = $crud->comment->select();
echo ''
;
print_r ($comments);
echo '';
?>
AutoCRUD for PHP also supports many-to-many relationships. This kind of relationship is often the hardest type to handle, since it involves three tables: two data tables and a connector table. In the example database we're using the article table is connected with the category table through article2category table. Why do we need a third table for this? Because articles can be in multiple categories, which makes it necessary to use a third table. But this is no problem, as AutoCRUD for PHP can handle this easily.
Like the previous relationship, we have to setup the relationship first. See the example below:
// Setup relationship
$crud->article2category->addRelationship ($crud->category, 'category', 'categoryid', 'many-to-many');
$crud->article2category->addRelationship ($crud->article, 'article', 'articleid', 'many-to-many');
?>
Since this is a many-to-many relationship we need to setup two relationships so that the article2category table is connected with both the article and category table.
Now the relationships have been setup, we only have to use the join() method again to get the related records. See the example below:
// Setup relationship
$crud->article2category->addRelationship ($crud->category, 'category', 'categoryid', 'many-to-many');
$crud->article2category->addRelationship ($crud->article, 'article', 'articleid', 'many-to-many');
// Get articles and the categories they belong to
$crud->article->join ('category');
$articles = $crud->article->select();
echo ''
;
print_r ($articles);
echo '';
?>
Again it's really simple to select the related records, and as far as I know, I've never seen this type of functionality in another library. Needless to say, I'm quite proud of it. Also, it only takes three queries to select the related records.
There are two more relationship types (one-to-one and child-parent) but they are fairly standard. Have a look at the documentation for more information on those.
Conclusion
In this article I've taken you through some of the functionality of AutoCRUD for PHP library, and I've shown you one of its unique features (the ability to automatically handle relationships).
But we've only really scratched the surface, and the library offers much more, including an ActiveRecord implementation, the ability to automatically handle table prefixes and creating table aliases, and much more.
I highly recommend you have a look through the source, and explore some of the other methods and functions in this library. Unfortunately, I haven't got any documentation or API guide yet, but it's in the works, and I'm hoping to have it available in a few weeks.
If you have any questions, comments or suggestions on this article or on the library itself, leave them below or join us at PHPit Forums.
June 8th, 2006 at 10:33 pm
[…] PHPit - Totally PHP » Taking a first look at the AutoCRUD for PHP library In this article we’ll take a look at the AutoCRUD for PHP library, which is a database abstraction library specifically for MySQL. I’ll take you through all major features of this library, and demonstrate everything with examples. […]