JamieRumbelow.net“Mm, that is one tasty burger!”

I was browsing the CodeIgniter forums (as I tend to a lot these days), and I stumbled across a post made by a starter in CI. He was having trouble connecting to the database, mainly because he was looking at the older video tutorial by Derek Jones on the CI Website. This tutorial covered a very old version of CodeIgniter, and it made me realize something.

Why are there no, in-depth tutorials covering databases in CodeIgniter?

I’m talking the full nine miles, including Models, ActiveQuery, Configuration, Security, seriously, the full nine miles. Yes the CI user guide is spectacular, yes, most people know how to do this stuff anyway, but, I did what I could. I would like to introduce you to…

The CodeIgniter Guide To Databases.

Before we start, however, I thought I would ramble on a bit more. Please note that for all examples I will be using MySQL, as this is what I am most familiar with. I will briefly cover some things in other DBs but MySQL will be the focus. Also, this is my longest tutorial ever. Because of this I will split it into two parts. The first part will focus all configuration and connection. The second part will contain queries, advanced stuff, security, meta data and caching.

Back to the Basics

So, what is the point of databases? What are they for?

Well, a database is an engine that stores data, and gives methods to create, read, update and delete data (CRUD). Data is stored in rows, in tables, then in databases. You use the scripting language “SQL” to access the methods.The whole system is called a Database Server.

There are many popular database servers, and each one varies slightly.

You probably already know about this, but the principles are important to understand.

Setting up your Config.

CodeIgniter provides many ways of accessing databases, and configuring them. You can use many different database servers, and many different flavours of SQL that come with the servers.

You can open up the database.php file in your application/config folder. This contains a $db array which by default contains this:

$db[’default’][’hostname’] = “localhost”;

$db[’default’][’username’] = “”;

$db[’default’][’password’] = “”;

$db[’default’][’database’] = “”;

$db[’default’][’dbdriver’] = “mysql”;

$db[’default’][’dbprefix’] = “”;

$db[’default’][’pconnect’] = TRUE;

$db[’default’][’db_debug’] = FALSE;

$db[’default’][’cache_on’] = FALSE;

$db[’default’][’cachedir’] = “”;

$db[’default’][’char_set’] = “utf8”;

$db[’default’][’dbcollat’] = “utf8_general_ci”;

The hostname or the location of the server, the server’s usernameand password, the database name, the database driver (the type of server: MySQL, MSSQL etc.), the table prefix, the choice to keep a persistent connection, the choice to display errorsand cachethe database. The directory for caching(if you are caching.) The character set, and finally the character collation.

Phew, that was a lot of options! But sometimes you need to have multiple database options, for different servers, and that sort of thing. That is why the guys at EllisLabs have helpfully made the $db array a multi-dimensional array! So if you need more database options, copy and paste, and change the ‘default’ key to whatever you need it to be. An example usage for this is like in Ruby on Rails:

$db[’development’][’hostname’] = “localhost”;

$db[’development’][’username’] = “”;

$db[’development’][’password’] = “”;

$db[’development’][’database’] = “”;

$db[’development’][’dbdriver’] = “mysql”;

$db[’development’][’dbprefix’] = “”;

$db[’development’][’pconnect’] = TRUE;

$db[’development’][’db_debug’] = FALSE;

$db[’development’][’cache_on’] = FALSE;

$db[’development’][’cachedir’] = “”;

$db[’development’][’char_set’] = “utf8”;

$db[’development’][’dbcollat’] = “utf8_general_ci”;

$db[’testing’][’hostname’] = “localhost”;

$db[’testing’][’username’] = “”;

$db[’testing’][’password’] = “”;

$db[’testing’][’database’] = “”;

$db[’testing’][’dbdriver’] = “mysql”;

$db[’testing’][’dbprefix’] = “”;

$db[’testing’][’pconnect’] = TRUE;

$db[’testing’][’db_debug’] = FALSE;

$db[’testing’][’cache_on’] = FALSE;

$db[’testing’][’cachedir’] = “”;

$db[’testing’][’char_set’] = “utf8”;

$db[’testing’][’dbcollat’] = “utf8_general_ci”;

$db[’production’][’hostname’] = “localhost”;

$db[’production’][’username’] = “”;

$db[’production’][’password’] = “”;

$db[’production’][’database’] = “”;

$db[’production’][’dbdriver’] = “mysql”;

$db[’production’][’dbprefix’] = “”;

$db[’production’][’pconnect’] = TRUE;

$db[’production’][’db_debug’] = FALSE;

$db[’production’][’cache_on’] = FALSE;

$db[’production’][’cachedir’] = “”;

$db[’production’][’char_set’] = “utf8”;

$db[’production’][’dbcollat’] = “utf8_general_ci”;

Then, in this example, if you want to switch between the three, all you have to do is set the active_group variable in the top of your database.php file. It looks like this:

$active_group = “default”;

$active_record = TRUE;

So, what if you don’t want to use the database.php file? Well, you can manually pass the loader function an array. That brings me onto…

Loading the Class

As most CodeIgniter Libraries, you can use your autoload.php to load your database, but there are a number of other methods available to load the database library into the CodeIgniter system.

Just like regular CodeIgniter libraries, there is an easy function in the Loader class, but it is not the original $this->load->library. Instead use the $this->load->database(). If you want to use a specific database config set, just use the first key in your $dbarray as the first parameter, so:

database.php

$db[’default’][’driver’] = “……….

$db[’my_config_set’][’driver’] = “……

class.php

$this->load->database(”my_config_set”);

You get it!

You can also use the native PHP database connection functions to connect if you need to, but you will have to specify your own options to connect. Plus, you won’t have access to any of the great database features such as caching and active record.

Another method you can use is passing an array to the $this->load->database() function. It’s really easy. Just copy the aray that’s in the database.php file. Try this:

$db = array(

‘hostname’ => “localhost”,

‘username’ => “”,

‘password’ => “”,

‘database’ => “”,

‘dbdriver’ => “mysql”,

‘dbprefix’ => “”,

‘pconnect’ => TRUE,

‘db_debug’ => FALSE,

‘cache_on’ => FALSE,

‘cachedir’ => “”,

‘char_set’ => “utf8”,

‘dbcollat’ => “utf8_general_ci”

);

$this->load->database($db);

So, as you can see, there are many possibilities for loading and configuring the database class! It supports your coding method, preference and whatever suits you better.

That’s all for now, folks!

Well, I’ve wrote quite a lot, and I’ve got a lot more coming. I will release the part two to this guide in about a week, so please keep coming back!

Thanks for reading, and if you have any questions, feel free to comment below.