And we're back with part 3 of our incredible CI tutorial. In part 1, we installed and configured CodeIgniter. In part 2, we took a basic look at how CodeIgniter handles the MVC architecture. Now we'll check out CodeIgniter's database handling including the Active Record implementation, performing simple CRUD (Create, Read, Update, Delete) operations, and sending query data from models to controllers to views.

NOTE: This section may seem like a lot to take in. If it does, be sure and read CI's database user guide. It will tell you everything that I won't.

Now then! Without getting too technical, CI's Active Record method basically means that you can do a lot of work with a database with only a little bit of code. In part 1, we set up "config/database.php" to our database so CI knows how to connect and you don't have to manually tell it each time. If you're going to be using a database a lot, make sure you autoload the database library in "config/autoload.php". If you're only going to be using it in a few places, you might as well just load it in each controller that needs it by using:

Using Handwritten Queries

Now, we're free to focus on the query itself. For those who prefer to use standard handwritten SQL queries, you can still do this in CI. Here's an example model function that does just that (pretend that this is in blog_model.php):

This will return an associative array of all containing the title, body, and author of all of the blogs. Here's an example controller method that uses this method.

This puts all of the blogs into the $data array to be passed into the view. Inside the view, you can then use that array to output the information, as follows:

Get the picture? If you don't, stop right now, figure out what is causing trouble (views? queries? model methods?) and read the corresponding section of the CodeIgniter User Guide. Got it? Good. Now let's take a look at Active Record methods.

Using CI's Active Record

For those of you who prefer simplicity, security, and beauty (oh now you're listening?) CI presents us with a better option than handwritten queries. Let there be Active Record! One of the coolest things about CI is the ease of forming queries to the database with AR. Each of the major SQL keywords (SELECT, FROM, WHERE, UPDATE, etc.) all have corresponding CI Active Record methods. For example, instead of using "SELECT * FROM table_name", you can just use the following:

Cool right? You can use this same format for most SQL keywords. For example, to generate "SELECT title, body, author FROM blogs WHERE id=$id AND author=$author", you'd do something like this:

The main functions that will end the query are get(), update(), insert(), and delete(). Any functions up to those four functions will just keep adding to the query.

Benefits of Active Record

What are the benefits of this? It just looks like more typing! Well, for one, you don't have to remember the SQL syntax or worry about the order of things. CI does all of that for you.

Secondly, it will escape data for you in insert() and update() queries, meaning you don't have to worry about php's addslashes() or whatever it is you use to escape data. We'll see more about CI's security measures in the next part of this tutorial series.

Another HUGE benefit of this is that it makes it trivial to develop database independent applications, because the queries are generated depending on which database adapter is being used, so the same code can work for MySQL, SQLite, and PostgreSQL by simply changing the driver in application/config/database.php.

CI's Active Record also makes it really easy to make applications that are completely removed from the particular names of the database tables, attributes, etc. For example, you could simply put all of this information in a config file, and then load the names of everything from that config file. That way, if a different user puts your application on a different database, all he would have to do is change the config file rather than having to change every single query (this would just involve creating a new config file in the "config" folder and then setting it to autoload in "config/autoload.php").

Well I guess that's enough for now. Check back soon for part 4, which will deal with PHP security and how CI helps us out with it!

This entry was posted on Friday, May 30th, 2008 at 11:04 pm and is filed under CodeIgniter, PHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.