Database-driven Routing in CodeIgniter

CodeIgniter is simple yet elegant, open source PHP framework developed by EllisLab. They also built the well-known ExpressionEngine by using CodeIgniter as a base. So that probably just proves, how powerful the framework is as well. Nevertheless, it is extremely easy learnable. Basically, their User Guide is all you need to craft easy websites.

I chose CodeIgniter over Wordpress because wanted to have my website customized as much as possible and be able to control it all over. This post is probably an exception as I won't be blogging much about server-side things. But this time I just wanted to share a small, but very useful technique that I came up with (a year ago) after long hours of unsuccessful googling.

The Problem

Actually, it's not a problem, not a bug of the framework. It just have this annoying 'feature' that each time you want to add a page/controller, you must define it in application/config/routes.php file by adding a line like this:

$route[ 'journal' ] = 'blog';

It means that Blog controller is executed when someone visits /journal URL. Although array key accepts Regular Expressions, that does not rescue the situation: you still have to edit the file to enable your controllers. Awfully uncomfortable. It limits your abilities to make website dynamic and control pages somewhere in your super-duper backend. But what is the smart way enable the database-driven routing in CodeIgniter?

The Solution

The whole technique consists just of a small MySQL table and a few lines of PHP code. How it works? The table holds our URL → Controller records whereas the code fetches them and lists array entries (like mentioned above) dynamically. It also makes sure that further URL levels are directed to the base controller (or folder).

Database Table

Add the table app_routes to your MySQL database:

CREATE TABLE IF NOT EXISTS `app_routes` (
  `id` bigint(20) NOT NULL auto_increment,
  `slug` varchar(192) collate utf8_unicode_ci NOT NULL,
  `controller` varchar(64) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `slug` (`slug`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

PHP Code

Replace the code in application/config/routes.php with the one below:

$route[ 'default_controller' ]  = 'main';
$route[ '404_override' ]        = 'error404';
 
require_once( BASEPATH .'database/DB'. EXT );
$db =& DB();
$query = $db->get( 'app_routes' );
$result = $query->result();
foreach( $result as $row )
{
    $route[ $row->slug ]                 = $row->controller;
    $route[ $row->slug.'/:any' ]         = $row->controller;
    $route[ $row->controller ]           = 'error404';
    $route[ $row->controller.'/:any' ]   = 'error404';
}

Feel free to change the values of default_controller and 404_override to what you need or already have. That's it! Now you have a database-driven routing in CodeIgniter enabled!

Example

Let's say you have a controller (class) named Portfolio, but prefer to make it reachable not by very long /portfolio URL, but by shorter /work. Simply add a database entry and you're done:

INSERT INTO `app_routes` (`slug`, `controller`) VALUES ('work', 'portfolio');

More

Managing routes via MySQL queries is pretty the same as editing the routes.php file. To make this type of routing meaningful, you should develop a tool for your backend that allows to manage app_routes table. Have a look at mine for an idea: