CI_Active_Record_Auto_Func_&_Read_Queries

CI_Active_Record_Auto_Func_&_Read_Queries

Active Record

'Active Record' is a 'design pattern'—another of those highly abstract systems like MVC, which provide templates for solving common coding problems and also generate some of the dullest books on the planet. In itself, it isn't code, just a pattern for code. There are several different interpretations of it. At its core is the creation of a relationship between your database and an object, every time you do a query. Typically, each table is a class, and each single row becomes an object. All the things you might want to do with a table row—create it, read it, update it, or delete it,
for example—become 'methods', which that object inherits from its class. Ruby on Rails is built around the Active Record pattern, and so is CI—although the exact implementation in the two frameworks seems to have subtle differences.
Enough theory—what does it mean? Well, simple and clear code statements, if you don't mind putting arrows in them.

Advantages of Using the Active Record Class

Active record saves you time, brings in automatic functionality that you don't have to think about, and makes SQL statements easy to understand.

Saving Time

When you write a normal database query in PHP, you must write a connection to the database each time. With CI, you connect once to the database, by putting the following line in the constructor function of each controller or model:
$this->load->database();
Once you've done this, you don't have to repeat the connection, how many ever queries you then make in that controller or model.
You set up the database details in the config files as we saw earlier in this chapter. Once again, this makes it easier to update your site, if you ever change the database name, password, or location.

Automatic Functionality

Once you've connected to the database, CI's active record syntax brings hidden code with it. For instance, if you enter the following 'insert' query:
$data = array(
'title' => $title,
'name' => $name,
'date' => $date
);
$this->db->insert('mytable', $data);
the values you are inserting have been escaped behind the scenes by this code:
function escape($str)
{
switch (gettype($str))
{case 'string':
$str = "'".$this->escape_str($str)."'";
break;
case 'boolean': $str = ($str === FALSE) ? 0 : 1;
break;
default : $str = ($str === NULL) ? 'NULL' : $str;
break;
}
return $str;
}

In other words, the CI framework is making your code more robust. Now, let's look at how it works.
Firstly, connecting to the database is very simple. In classic PHP, you might say something like this:
$connection = mysql_connect("localhost","fred","12345");
mysql_select_db("websites", $connection);
$result = mysql_query ("SELECT * FROM sites", $connection);
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
foreach ($row as $attribute)
print "{$attribute[1]} ";
}

In other words, you have to re-state the host, username, and password, make a connection, then select the database from that connection. You have to do this each time. Only then, do you get on to the actual query. CI replaces the connection stuff with one line:
$this->load->database();
which you put once, in each controller or model or class constructor that you write. After that, in each function within those controllers, etc., you just go straight into your query. The connection information is stored in your database config file, and CI goes and looks it up there each time.
So, in each CI function, you go straight to your query. The query above written in CI comes out as:
$query = $this->db->get('sites');
foreach ($query->result() as $row)
{
print $row->url
}

Simple, isn't it?
The rest of this chapter sets out ways of making different queries, making them
more specific.

Read Queries
The most common query that we'll write simply retrieves information from the database according to our criteria. The basic instruction to perform a read query is:
$query = $this->db->get('sites');
This is a 'SELECT *' query on the sites table—in other words, it retrieves all the fields. If you prefer to specify the target table (sites) in a separate line, you can do so in this way:
$this->db->from('sites');
$query = $this->db->get();
If you want to 'SELECT' or limit the number of fields retrieved, rather than get them all, use this instruction:
$this->db->select('url','name','clientid');
$query = $this->db->get('sites');
You may want to present the results in a particular order—say by the site name—in which case you insert (before the $this->db->get line):
$this->db->orderby("name", "desc");
desc means in descending order. You can also choose asc (ascending) or
rand (random).
You may also want to limit the number of results your query displays; say you want only the first five results. In this case insert:
$this->db->limit(5);
Of course, in most queries, you're not likely to ask for every record in the table. The power of databases depends on their ability to select—to pick out the one piece of data you want from the piles of stuff you don't. This is usually done by a where statement that CI expresses in this way:
$this->db->where('clientid', '1');
This statement would find all websites linked to the client whose ID was 1. But that's not much help to us. We don't want to remember all the ID's in our people table.
As humans, we prefer to remember human names. So we need to link in the
people table:

$this->db->from('sites');
$this->db->join('people', 'sites.peopleid = people.id');
For each people ID in the sites table, look up the information against that ID in the people table as well.

Note:
Note the SQL convention that if a field name may be ambiguous between two tables, you reference it with the table name first, then a period, then the field name. So sites.peopleid means the peopleid field in the sites table. In fact, there isn't a field called peopleid in both tables, but there is an id field in both sites and people, so the RDBMS will protest if you try to run the query without resolving the ambiguity for it. In any case, it's a good habit to make your meaning explicit, and CI syntax happily accepts the fuller names.

You can play around with the syntax of where statements. For instance, add
negation operators:
$this->db->where('url !=','www.mysite.com' );
or comparison operators:
$this->db->where('id >','3' );
or combine statements ("WHERE… AND…"):
$this->db->where('url !=','www.mysite.com');
$this->db->where('id >', '3');
or use $this->db->orwhere() to search for alternatives ("WHERE … OR"):
$this->db->where('url !=','www.mysite.com' );
$this->db->orwhere('url !=','www.anothersite.com' );
So let's say we've built up a query like this:
$this->db->select('url','name','clientid','people.surname AS client');
$this->db->where('clientid', '3');
$this->db->limit(5);
$this->db->from('sites');
$this->db->join('people', 'sites.clientid = people.id');
$this->db->orderby("name", "desc");
$query = $this->db->get();
This should give us the first five (ordered by name) websites belonging to client number 3, and fetch the client's surname as well as his or her ID number!

A hidden benefit of using Active Record is that data that may have come in from users is automatically escaped, so you don't have to worry about putting quotes around it. This applies to functions like $this->db->where(), and also to the data creation and update statements described in the next sections. (Security warning: this is not the same thing as preventing cross-scripting attacks—for that you need CI's xss_clean() function. It's also not the same as validating your data—for this you need CI's validation class. See Chapter 5.)