In my previous post on using the Fuel framework I looked at the basics behind getting a site up and running. This time I want to take it a step further and tell you a bit about my experiences getting a Fuel application to talk to a database (in my case MySQL). To learn the process, I fell back on one of my standby sites that’s been rewritten in a few different frameworks now including Solar and a really, really old version of the Zend Framework. It’s not an overly complex site, but the version is starting to show its age. So, I usually take that as an opportunity to rewrite the site in something different, just for the experience.

I’ve already started on a version with the latest download of Fuel and, as a part of it, explored the database model connectivity feature. Now, all of this code is generated from me just looking at the source (since there’s no docs to speak of *cough*) so there’s probably a better way to do some of this that I don’t know. So, lets get started with a basic model.

In my database, I have a few tables – two of them are the “news” and “news_tags” tables. The relationship between the two is news.ID -> news_tags.news_id. Thankfully it works out that this is the default mapping for the relationships between the models. More on that later though…here’s my sample SQL:

[php]
create table news (
news_title VARCHAR(200),
news_story TEXT,
date_posted INT,
ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID)
);
create table news_tags (
news_id INT,
tag_name VARCHAR(50),
ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID)
);
[/php]

Let’s start with the model files – one for each of the tables. Here’s the basic structure of the file:

[php]
<?php
namespace FuelAppModel;
use ActiveRecord;

class News extends ActiveRecord/Model
{
// define a sample method
public function getLatestNews()
{
$results = $this->find(‘all’,array(
‘select’ => array(‘news_title’,’news_story’,’date_posted’,’ID’),
‘limit’ => 10,
‘order’ => array(‘date_posted’,’desc’)
));
return $results;
}
}
?>
[/php]

This is enough for Fuel to now know about the “news” database table. Obviously, you’ll need to have the database connection information correct in your db.php to connect to your instance. A few things to note here – the namespace loads in the base Model functionality so the News class can extend the ActiveRecord bits it needs. The ActiveRecord functionality is contained in a package in the standard Fuel download.

In this model, I’ve defined a simple function to pull out the latest news from our “news” database table. This uses the “find()” method that comes with the ActiveRecord stuff. That first parameter can be one of a few things.

• An ID number of the record you’re looking for
• The string “all” to return all matching rows from the where
• The string “first” that essentially puts a “limit 1” on the end of the statement

The second parameter sets up the rest of the SQL statement – the “select” array lists the columns to fetch, the “limit” restricts the results and the “order” allows you organize the results by a column. There’s also “offset” and “where” that can help get it more specific. The results are then returned back from the method. If you’re more in the “heavy controller” camp and want to do more complex stuff in there, you can also make a model object inside your controller:

[php]
<?php
namespace FuelAppController;

class News extends BaseController
{
public function action_index()
{
$News = new FuelAppModelNews;
$results = $News->find(‘all’,array(
‘select’ => array(‘news_title’,’news_story’,’date_posted’,’ID’),
‘limit’ => 10,
‘order’ => array(‘date_posted’,’desc’)
));
}
}
?>
[/php]

This essentially does the same thing. Now, lets talk about conventions for models here. If your table name is one word, it’s simple enough – for the “news” table, the model’s name is “News” and the file in /classes/models is “news.php”. For something a bit more complex like “news_tags”, you name the file “newstag.php” and the class “NewsTag”. Note that it looks for the singular version of things – “NewsTag” even though the table is “news_tags”.

Simple, right? Well, if all you need to do is make basic queries and pull that data back out, you’re all set. You can stop here. If, however, you need something a bit more complex like say, table relationships, keep reading. Sure, you can manually join all of your data together, making multiple queries and looping through endless loops to match things up. If you want to save some time you can use the table relationships build into Fuel to do the work for you. There’s currently three different kinds – belongs to, has many, and has one. For the sake of example, I’m only going to show you a “has many” example on our “news” to “news_tags” tables.

Setting up the relationship is super simple. In our News model, we’re going to set a new class property to tell the framework about the relationship:

[php]
<?php
namespace FuelAppController

class News extends BaseController
{
public $has_many = array(‘news_tags’);

public function getNewsDetail($newsId)
{
$results = $this->find($newsId,array(‘include’=>array(‘news_tags’)));
return $results;
}
}
?>
[/php]

Don’t look at the “find()” call yet, lets concentrate on the new property. This “$has_many” property lets us define the table names we want relations to. By default Fuel tries to look at the other table for a column matching the [current table]_id pattern. Thankfully, we have one of those: the “news” table’s ID relates to “news_tags.news_id”. Works like magic! Additionally you can also define other parameters to manually specify things like the column name to map to.

Okay, let’s look at that find now…we have our relationship defined to the other table, but Fuel knows we might not always want to include that data on every call. To let it know when to pull the news_tags data in, you have to specify it in the “include” parameter of the find() options. When the query runs and the data’s returned, you’ll have the news_tags data in $results->news_tags for your consumption.

There’s one slightly tricky thing that I came across when working with the model features. When it tries to do the join, it looks at the name of the model class it’s trying to use and pulls the name from there. In our case “NewsTag” isn’t the right name for the table. You can however define the table’s name in the $table_name property of the model, but that didn’t seem to cooperate either. It looks like the fault lies in this chunk of code in the “hasmany.php” extension of the ActiveRecord component, around line 212:

[php]
<?php
//….
$dest_table = AppInflector::tableize($this->dest_class);
$source_table = AppInflector::tableize($this->source_class);
$source_inst = new $this->source_class;
$dest_inst = new $this->dest_class;
$columns = $dest_inst->get_columns();
//….
?>
[/php]

In the above snippet it tries to run the “tableize” inflector on the model class’ name to pull out the table. In our case, though, the names don’t match and unfortunately the join() method doesn’t seem to care about the $table_name value and just keeps going on its merry way. It’s a pretty simple fix, thankfully:

[php]
<?php
$dest_table = (isset($dest_inst->table_name)) ? $dest_inst->table_name : $dest_table;
?>
[/php]

With this defined, you can tell it that “$table_name = ‘news_tags’;” and it’ll respect it.

For those used to the more object oriented method of models, there’s also the usual CRUD methods you can use to manipulate the objects:

• save()
• update()
• destroy()
• and, of course, the find() call

I hope this was helpful and like I said, I’m sure there’s a few things that I didn’t get quite right just because I was fishing through the source to figure them out. I’ve been working on some simple templating for the site, so maybe I’ll write up a post on that next. As always, please point out my mistakes 🙂 That’s the only way to learn!