EllisLab text mark
Advanced Search
     
Database driven routes in CodeIgniter
Posted: 28 June 2009 01:04 PM
Joined: 2009-06-03
15 posts

See updated version of this here

Using a pre_system hook the routes array can be populated from your database.

A route is a CodeIgniter controller action alias. Used to build search engine friendly sites. eg posts/database-driven-routes-in-codeigniter is mapped to posts/get/2

Step 1

Define the hook by inserting the code below into the hooks.php file ( ..system/application/config/hooks.php ).

require_once BASEPATH."application/config/database.php";

$hook['pre_system'= array(
                                
'class'    => 'Router_Hook',
                                
'function' => 'get_routes',
                                
'filename' => 'Router_Hook.php',
                                
'filepath' => 'hooks',
                                
'params'   => array(
                                    
$db['default']['hostname'],
                                    
$db['default']['username'],
                                    
$db['default']['password'],
                                    
$db['default']['database'],
                                    
$db['default']['dbprefix'],
                                    
)
                                )

This is a pre system hook, which means it is called before any routing has occurred. This hook will call the function get_routes with the database connection details passed as a paramater.

Step 2

Create a file called Router_Hook.php in the hooks directory ( ..system/application/hooks/Router_Hook.php ) and insert the code below.

if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class 
Router_Hook
{
        
/**
         * Loads routes from database.
         *
         * @access public
         * @params array : hostname, username, password, database, db_prefix
         * @return void
         */
    
function get_routes($params)
    
{
        
global $DB_ROUTES;

        
mysql_connect($params[0]$params[1]$params[2]);

        
mysql_select_db($params[3]);

        
$sql "SELECT * FROM {$params[4]}routes";
        
$query mysql_query($sql);

        
$routes = array();
        while (
$route mysql_fetch_array($queryMYSQL_ASSOC)) {
            $routes[$route[
'route']] $route['controller'];
        
}
        mysql_free_result
($query);
        
mysql_close();
        
$DB_ROUTES $routes;
    
}

The database schema

idroutecontrollercreatedupdated 

This function populates a keyed array from the database and makes the array globally available.

Step 3

Insert the code below into the routes.php file ( ..system/application/configs/routes.php ) after the scaffolding trigger.

global $DB_ROUTES;
if(!empty(
$DB_ROUTES)) $route array_merge($route,$DB_ROUTES)

This code merges the array populated from the database with the array defined in the routes.php file.

Step 4

In the config.php file ( ..system/application/configs/config.php ) set enable hooks to TRUE.

$config['enable_hooks'TRUE

Now when CodeIgniter is routing a request it will consider the routes from the database.


This technique will work as it is, but I recommend using DataMapper to save routes to the database aswell. Below is my route datamapper model.

class Route extends DataMapper
{
    
var $validation = array(
        array(
            
'field' => 'route',
            
'label' => 'URL alias',
            
'rules' => array('required''trim''unique''min_length' => 3'max_length' => 255'reserved_word''dash_url''alpha_slash_dot')
        )
,
        array(
            
'field' => 'controller',
            
'label' => 'Controller URL',
            
'rules' => array('required')
        )
    
);


    
/**
     * Constructor
     */
    
function __construct()
    
{
        parent
::__construct();
    
}


    
/**
     * Validation function. Checks field value isn't a controller name.
     *
     * @access private
     * @param  string : field name
     * @return bool
     */
    
function _reserved_word($field)
    
{
        $controller 
= array();

        
$this->load->helper('directory');

        
$map directory_map(BASEPATH."application/controllers");

        foreach (
$map as $value)
        
{

                $filename_array 
explode("."$value);
                if (
$filename_array[1] == EXT)
                
{

                    
//If file extension is php then store filename in array.
                    
$controller[] $filename_array[0];
                
}
        }

        
if (in_array($this->{$field}$controller))
        
{
            
return FALSE;
        
}
        
else
        
{
            
return TRUE;
        
}
    }


    
/**
     * Converts the route into a search engine friendly URL
     *
     * @access private
     * @param string : field value
     * @return void
     */
    
function _dash_url($field)
    
{
        $this
->{$field} dash($this->{$field});
    
}

This provides a great way to check the route to be saved isn’t an existing controller name. Preventing routes with the same name pointing to different controller actions.


Insert the code below at the bottom of the inflector_helper.php file ( ..system/application/helpers/inflector_helper.php )

/**
 * Dash
 *
 * Takes multiple words separated by spaces and dashes them 
 * 
 * @access    public 
 * @param    string 
 * @return    str 
 */
if ( ! function_exists('dash'))
{
    
function dash($str)
    {
        
return preg_replace('/[\s_]+/''-'strtolower(trim($str)));
    
}

This function is called when saving the route to the database. eg. paGe/HELLO WORLD to page/hello-world.

Let me know what you think of this feature.

 Signature 

neillyons.co

 
Posted: 01 July 2009 02:42 PM   [ # 1 ]   [ Rating: 0 ]
Joined: 2009-02-17
8 posts

I am strongly considering implementing this hook solution to my routing issue (I am currently using a flat file that gets read into our route file that works great but harder to manage).

The problem that I had with a similar approach, and the problem I see with this solution is that it still creates two database connections, so this could dramatically effect scale / memory use.

Have you seen any impact to using a solution like this?

 
Posted: 01 July 2009 03:00 PM   [ # 2 ]   [ Rating: 0 ]
Joined: 2007-04-05
320 posts

Alan, the first connection is opened and then closed again in the routes hook, then a new connection will be opened for the controller, etc. This means there will only be one open at any single time. However it will effect the speed of the application slightly as it has to fetch the routes. Maybe it could cache the routes file somewhere that it gets from the database, then if you update a route, add or delete the cache file will be deleted and rebuilt on the next page load. That would decrease the impact a lot.

 
Posted: 01 July 2009 03:32 PM   [ # 3 ]   [ Rating: 0 ]
Joined: 2009-06-03
15 posts

@Alan Wizemann

The reason two database connections are created is because I can’t call CodeIgntier’s get_instance() function to initialize the Database Class. I don’t think this will have a big impact on performance though.

 Signature 

neillyons.co

 
Posted: 01 July 2009 04:41 PM   [ # 4 ]   [ Rating: 0 ]
Joined: 2009-02-17
8 posts

Thanks for your reply, I think the way I am going about it is better for speed (we really need to keep an eye on scale) but I really think your solution is perfect for many apps out there. I use an approach where my admin bakes a serialized array of my routes from its app logic into a static file, the route file loads this and creates its array for CI to use, its very very fast. I would like to see if I can compare the speed of two connections versus reading a file. Regardless, we can push the file array after it has been parsed into the APC of PHP and really see a kick in speed.

 
Posted: 03 July 2009 05:41 PM   [ # 5 ]   [ Rating: 0 ]
Joined: 2009-06-03
15 posts

Database driven routes: Benchmark test.

Default CodeIgniter set up.
$config['enable_hooks'FALSE;
2 routes.
Average Page rendered in 0.072 seconds

Database driven routes
.
$config['enable_hooks'TRUE;
10,000 routes.
Average Page rendered in 0.425 seconds

Routes written to file 
and then included.
$config['enable_hooks'TRUE;
10,000 routes.
Average Page rendered in 0.376 seconds 
 Signature 

neillyons.co

 
Posted: 03 July 2009 06:36 PM   [ # 6 ]   [ Rating: 0 ]
Joined: 2009-02-17
8 posts

Wow, thanks for this benchmark - it really does show a pretty decent speed increase to the include, aside from time though, I bet memory and CPU utilization are also lower. Although this is an extreme case, some of our route files can be upwards of 1000 records, so I have to say this definitively proves we are going the right route for ultimate speed (no pun intended).

I think the include method with APC active, can really make things cook.

Thanks again!

 
Posted: 25 January 2010 08:30 PM   [ # 7 ]   [ Rating: 0 ]
Avatar
Joined: 2007-03-21
282 posts

Bit late to this one and still trying to process the excellent example…

However, I was wondering if I was to create a ‘pages’ and ‘blog’ tables for example. Could the insert/update operations for those tables also post to the routes database or would I not need to go to that extent? Rather than keeping all of the ‘slugs’ in the pages and blog tables?

Thank you.

 
Posted: 21 February 2010 06:22 AM   [ # 8 ]   [ Rating: 0 ]
Avatar
Joined: 2008-10-15
147 posts

edit: nevermind I just underdtood Datamapper was a lib http://stensi.com/datamapper/

 
Posted: 15 May 2010 07:01 AM   [ # 9 ]   [ Rating: 0 ]
Avatar
Joined: 2007-06-06
511 posts

I use also a form of database driven routes in my CI site and it works really straight forward with no speed loss what so ever. It uses only a couple of lines of code.

It doesn’t use hooks or hacks into the system and it uses the standard AR from CI.

In my router file I check if a file exists. If it doesn’t exist it uses the default routes which in all cases forwards you to a controller which generates an array from the database. This array contains the routing information. This array is then saved in a cache file. The controller redirects itself and next time the router is accessed the cache file is loaded. The cache file is updated once a day.

Simple, Effective and no speed loss.

 Signature 

In need for a good host for your Codeigniter Apps? Try Webfaction! It’s fast, reliable, technically sound, in the USA, EU and Asia.

 
Posted: 15 May 2010 09:29 AM   [ # 10 ]   [ Rating: 0 ]
Joined: 2009-06-03
15 posts

@clooner Not sure if you arrived at this forum topic directly or through my more recent post. There I talk about including a cache file in the routes.php file similar to what your describing. Although the cache file is not created when you access a certain controller but whenever an insert/update/delete operation is performed on the route model.

With your solution I think there might be an issue with lag time. For example, say I have just created an article on my website. It has CodeIgniter’s default route of articles/get/1 and an alias of that route like so articles/my-example-article.

I’m assuming your updating your cache file on a cron once a day. So the time between the article being created and the cron being run that route (articles/my-example-article) will produce a 404 error.

Does that make sense?

 Signature 

neillyons.co

 
Posted: 19 May 2010 04:40 PM   [ # 11 ]   [ Rating: 0 ]
Joined: 2010-05-11
1 posts

Hello,

I am using this piece of code and I want to extend it. But I need now a config variable in the Router_Hook.php.
To do this, I thought I do the same as with the database variables:

So in the hook.php file I added the following:

require_once BASEPATH."application/config/config.php"

And I thought now I can access all the config variables. But it isn’t!

if I do for example:

echo $config['base_url']

in the hook.php file. It seems that this variable is empty. if I print the config array I don’t see the content. Even not Array( ).

What is going wrong?
(I’m new to CodeIngiter)

Thanks,
Kristof

 
Posted: 20 May 2010 05:42 AM   [ # 12 ]   [ Rating: 0 ]
Joined: 2009-06-03
15 posts

@Kristof Mariën there’s a function to access configuration variables stored in the config.php file.

$base_url config_item("base_url"); 

Then if you want to print it out:

print $base_url

You can also get the entire config array using:

$config get_config(); 

And print it out like so:

print $config["base_url"]
 Signature 

neillyons.co