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, however, many queries you make in that controller or model, you don't have to repeat the connection. 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.

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 insert escape behind the scenes by using 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 restate the host username and password, make a connection, and then select the database from that connection. Only then do you get to the actual query. CI replaces the connection stuff with one line:

$this->load->database();

You need to put it once in each controller, model, and class constructor that you write. If you feel it's necessary, autoload it. After that, in each function within those controllers, and so on, you go straight into your query. The connection information is stored in your database.php file in the config folder, and CI goes and looks it up there, each time.

So, in each CI function, you go straight to your query. The previous query written in CI comes out as:

$query = $this->db->get('sites');
foreach ($query->result() as $row)
{
print $row->url
}

Simple, isn't it?

But as always, in CodeIgniter there are many hidden functionalities in case you need them. For example, if you need to connect to another database instead of the default one, you could do it very easily as:

$this->load->database('name');

Where name equals to what you have in the database.php config file:

$db['name']['hostname'] = "localhost";
$db['name']['username'] = "root";
$db['name']['password'] = "root";
$db['name']['database'] = "websites";
$db['name']['dbdriver'] = "mysql";
$db['name']['dbprefix'] = "";
$db['name']['pconnect'] = TRUE;
$db['name']['db_debug'] = TRUE;
$db['name']['cache_on'] = FALSE;
$db['name']['cachedir'] = "";
$db['name']['char_set'] = "utf8";
$db['name']['dbcollat'] = "utf8_general_ci";

What if you want to connect to two or more databases, you can do that very easily too:

$database1 = $this->load->database('name', TRUE);
$database2 = $this->load->database('name2', TRUE);

The TRUE parameter indicates to the function to return the database object. Once you have done so you will be able to query both databases, but in a slightly different way, instead of using:

$this->db->query();

You will use:

$database1->query();

In the rest of this chapter you will see ways of making different queries, making them more specific.

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");

The parameter 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 display. 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 is 1. But that's not of much help to us. We don't want to remember all the IDs 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 peopleid in the sites table, look up the information against that ID in the people table as well.

You can play around with the syntax of where statements. For instance, you can:

You can also use $this->db->or_where() to search for alternatives ("WHERE … OR"):

$this->db->where('url !=','www.mysite.com' );
$this->db->or_where('url !=','www.anothersite.com' );

So let's say we've built 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->order_by("name", "desc");
$query = $this->db->get();

The previous snippet should give you the first five websites (ordered by name) belonging to client number 3, and fetch the client's surname, as well as his/her ID number!

A hidden benefit of using Active Record is that data coming in from users is automatically escaped, so you don't have to worry about putting quotes around it. This applies to functions such as $this->db->where(); and also to the data creation and update statements described in the sections that follow.

If you don't use Active Record, it is advisable that you escape your data before sending it to your database. When using Active Record this is done automatically for you, but if you aren't using it, you have to do it manually, using the escape function:

$this->db->escape();

For example:

$sql = "SELECT * FROM people WHERE uname = "'".$this->db->escape($uname)."'";
$this->db->query($sql);

It's an easy way of giving your queries more security if you can't or don't want to use Active Record. There is another way of doing so—query binding. Query binding will escape your data before it is passed to the database. Let's see an example of query binding:

$sql = "SELECT * FROM people WHERE id = ? AND uname = ? AND status = ?";
$this->db->query($sql, array(1, 'Jose', 1));

Now it is for you to decide which method to use. At least you can't say CodeIgniter doesn't gives you choices!

Showing database query results in CI is quite simple. Define your query as you did previously, ending in:

$query = $this->db->get();

Then, if there are multiple results, they can be returned as an array of $row objects, through which you iterate with a foreach loop:

foreach ($query->result() as $row)
{
print $row->url;
print $row->name;
print $row->client;
}

If you only want a single result, it can be returned as an object or as a $row array, as in the following example:

if ($query->num_rows() > 0)
{
$row = $query->row_array();
print $row['url'];
print $row['name'];
print $row['client'];
}

Personally, I prefer the object syntax to the array—less typing!

When you follow the MVC pattern, you will usually want to keep your queries and database interactions in models, and display the information through views.

Active Record has three functions that help you to create new entries in your database. They are $this->db->insert(), $this->db->update(), and $this->db->set().

Let's see the difference between a "create" and an "update" query. When you create a new record, there is no reference to any existing record, you write a new one. When you update, there is an existing record and you are changing it. So, in the second case you have to specify, which record you are changing. In both cases you have to set the values that you want to keep in the database after your query. Values you don't set will be left unaltered. If they didn't exist before, they will still be "null" after your query.

CI allows you to set the values either using an array or $this->db-set(). The only difference is the syntax. So, let's add a line to your sites table in the websites database. We've already connected to this database in our controller. The controller's constructor function included the line:

$this->load->database();

You want to add a new site, which has a URL, a name, a type, and a client ID number. As an array, this can be written as:

$data = array(
'url' => 'www.mynewclient.com',
'name' => 'BigCo Inc',
'clientid' => '33',
'type' => 'dynamic',
);

To add that to the sites table, we follow it with:

$this->db->insert('sites', $data);

Alternatively, you could set each value using $this->db->set():

$this->db->set('url', 'www.mynewclinet.com');
$this->db->set('name', 'BigCo Inc');
$this->db->set('clientid', '33');
$this->db->set('type', 'dynamic');
$this->db->insert('sites');

Of these two, we prefer the first one as it seems clearer. However, there is no difference between them, use the one you are comfortable with.

If you are updating an existing record, then again you can either create an array, or use $this->db->set(). But there are two differences. Firstly, you have to specify the record you want to update. Second, you need to use $this->db->update(). If you want to update a record (say the record with its id field set to 1) in your sites table, using the data set in your $data array as you did earlier, the syntax is:

$this->db->where('id', '1');
$this->db->update('sites', $data);

You can also set the information using $this->db->set(), as you saw earlier. CI gives you several functions to check what the database has done. Most usefully:

$this->db->affected_rows();

This should return "1" after the insert or update statement—it might show more rows if we were to alter several rows of data at one time. You can use it to check that the operation has done what you expected.

You've noticed that we didn't set an ID field when we created a new record. That's because you set the database to populate the ID field automatically when a new record is added. You have to specify an ID when you update an existing record, otherwise the database doesn't know which one to alter.

However, if you're generating a new record, you don't know the ID number until you've generated it. Then, if you need to refer to the new record, you can get the new ID number using:

$new_id_number = $this->db->insert_id();

For a little more peace of mind remember that CI's Active Record functions, including $this->db->insert() and $this->db->update(), automatically escape data passed to them as input.

From version 1.5, CI also includes support for transactions—linking two or more database actions together so that either all succeed or all fail. This is essential in double-entry book keeping applications and many commercial sites. For instance, if you are selling theatre tickets you can record receiving a payment in one transaction, and then allocate a seat to the customer in another. If your system fails, after doing the first database operation and before doing the second, you may end up with an angry customer—who has been charged, but has not had a seat reserved.

CI, now makes it much simpler to link two or more database operations into one transaction. So, if they all succeed, the transaction is "committed". If one or more fails, the transaction is "rolled back". Transactions are enabled by default; you only have to indicate to CodeIgniter that you want to use them:

$this->db->trans_start();
$this->db->query('first sql query');
$this->db->query('second sql query');
$this->db->query('third sql query');
$this->db->trans_complete();

These three queries need to be executed correctly, or all three of them will be rolled back. This is useful for shopping carts, or any query that needs another one to be successful.

Delete queries are perhaps the simplest to describe. All you need is the name of the table and the ID number of the record to delete. Let's say, you want to delete a record in the sites table with the id number 2:

$this->db->where('id', '2');
$this->db->delete('sites');

Please remember to make sure that there is a valid value in the where clause, or you may delete the whole table! Neither the authors nor Packt Publishing will accept any liability if….