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

Well, we last left off at configuring and loading the database, and now, we will move onto queries, SQL, caching and result management!

This is part two of my two part tutorial, The Guide to CI Databases. If you haven’t already, please have a look at part one. You can find it Here.

I’ve nicked a few bits from the user guide, just FYI

Queries

So! You’ve loaded the database system into your CI app, but now you want to do something with it! This is where you call upon queries. Queries are functions that enable you to make calls to your database, and do certain things with data. Queries, at the root, are simply strings with SQL in. SQL stands for Structured Query Language, and it is a very simple script that your database server interprets and parses for you. The SQL syntax is very easy. To select all fields from the database table users, for instance, the SQL would look like this:

SELECT * FROM users

So as you can see, very easy!

CodeIgniter provides a few functions to make queries like this. The most common one is:

$this->db->query();

It only takes one parameter, the query string, and will return a CI Result object (we will talk about this later).

Another function you can use (but probably won’t that often) is $this->db->simple_query(). This doesnt return a result, but simply TRUE or FALSE (bool) depending on the result of the query.

So as you can see, it is very easy to make queries!

ActiveRecord

One of the best features of CI’s database library is the ActiveRecord pattern. ActiveRecord is a design pattern which is designed to write the SQL queries for you, meaning you only have to write a few functions, and it will do the rest. CI contains a great active record library, and it is enabled by default.

Remember that query I wrote to select all fields from the users table? This is what it looks like in CI’s active record:

$this->db->get(”users”);

Wow! So as you can see it is really easy to perform queries with AR functions. As this is not an SQL tutorial, I won’t go into this much, but CI allows you to make long SQL queries from a few functions. Nearly every SQL command is a function in AR. If we wanted to select the username from the users table, where the user was online and order by the age, we would do this:

$this->db->where(”user_online”, “online”);

$this->db->order_by(”age”, “desc”);

$this->db->select(”username”);

$this->db->get(”users”);

Every function in the ActiveRecord class is fully documented in the CI user guide, so I highly recommend reading it Here.

Results

So, you’ve ran your query, but you now want to do something with the data. Well, when you run your query, CodeIgniter returns a great object ready to do all sorts of things to your data.

The function you will use most often is result(), which returns an object which contains your result data.

You will most often use this in an foreach loop in your view. Imagine you have passed on a variable with your result called $query, using $this->db->get() or whatever, your view might look like this:

<?php foreach ($query->result() as $row): ?>
<h2><?=$row->title?></h2>
<?php endforeach; ?>

Going into more detail, as you can (or can’t) see, the $query->result() function returns an array. This foreach loop gets the contents of each array key, and puts it into a $row variable. Handily, the value of each key is an array, with one row of database results. So, if you had a database query that returns 10 rows, this foreach loop will loop through 10 times and return the title field for each row.

Sounds a bit complicated, doesn’t it? Lets have a look if we var_dump our $query->result() object.

I added five rows to our database table, so ignore the crap, and i separated it out a bit to make it easier to read:

array(5) {

[0]=> object(stdClass)#13 (3) {

["title"]=> string(7) “title 2”

["author"]=> string(4) “bill”

["body"]=> string(14) “Lorem Ipsum…”

}

[1]=> object(stdClass)#14 (3) {

["title"]=> string(4) “sdas”

["author"]=> string(5) “jamie”

["body"]=> string(14) “Lorem Ipsum…”

}

[2]=> object(stdClass)#15 (3) {

["title"]=> string(4) “fght”

["author"]=> string(4) “bill”

["body"]=> string(14) “Lorem Ipsum…”

}

[3]=> object(stdClass)#16 (3) {

["title"]=> string(3) “t54”

["author"]=> string(5) “jamie”

["body"]=> string(14) “Lorem Ipsum…”

}

[4]=> object(stdClass)#17 (3) {

["title"]=> string(4) “qtgj”

["author"]=> string(4) “bill”

["body"]=> string(14) “Lorem Ipsum…”

}

}

So, as you can now see, the $query->result() returns a numerical array! This is handy sometimes, as you can return a specific row, like the third, by doing something like this:

$result = $query->result();

$row3 = $result[3];

//Echo the title

echo $row3->title;

Great! But what if you don’t want to use, (or can’t) an object, but an array? The exact same thing can be done by calling $result->result_array(). So, instead of calling $row3->title in the code above, you would use an associative array, like this: $row3[’title’].

Easy!

If you know you will only have one result, perhaps getting a user, you can use $query->row(), or row_array() to return just the one row, which means there is no need for a foreach loop - there is no numerical array. You can do $row = $query->row() and then use $row->title.

If you have more than one row, but you need to use the row() function, CI provides a few functions to move around rows. Think of a pointer, if you will.

These functions, which are fairly self explanatory are:

$row = $query->first_row()

$row = $query->last_row()

$row = $query->next_row()

$row = $query->previous_row()

By default, these functions return an object, but put a “array” into the first parameter, and they will return an array.

So, CodeIgniter provides a great method to handle result data!

Caching

So, you’ve learned all the basics. Now, you want to make sure that you are using as less memory as you can, and speed up your queries. Caching works by storing a file on your server with a serialized version of the query, then when you run the query, it takes it from the file instead of interacting with the database.

Caching is really easy to run, you can autoload it, by setting the cache_on setting in your database.php to true. Either that, or you can use the $this->db->cache_on / cache_off functions to toggle caching in specific functions.

CodeIgniter places the result of EACH query into its own cache file. Sets of cache files are further organized into sub-folders corresponding to your controller functions. To be precise, the sub-folders are named identically to the first two segments of your URI (the controller class name and function name).

For example, let’s say you have a controller called blog with a function called comments that contains three queries. The caching system will create a cache folder called blog+comments, into which it will write three cache files.

If you use dynamic queries that change based on information in your URI (when using pagination, for example), each instance of the query will produce its own cache file. It’s possible, therefore, to end up with many times more cache files than you have queries.

Since cache files do not expire, you’ll need to build deletion routines into your application. For example, let’s say you have a blog that allows user commenting. Whenever a new comment is submitted you’ll want to delete the cache files associated with the controller function that serves up your comments. You’ll find two delete functions that help you clear data.

To clear cache files for specific functions, call the $this->db->cache_delete(). It takes two parameters. The first tells us the controller, the second, the function. You can use the $this->db->cache_delete_all() function to delete all your cache files!

Thanks!

Well, I hope you’ve learned something, and if you have any questions/ideas/comments please feel free to comment!

Cheers,

Jamie