CI_Active_Record_Create_&_Update_Queries

CI_Active_Record_Create_&_Update_Queries

Create and Update Queries
Active Record has three functions that help you create new entries in your database. They are $this->db->insert(), $this->db->update(), and $this->db->set().
The difference between a 'create' and an 'update' query is that when you create a new record, there is no reference to any existing record, you are writing 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 you want to leave in the database after your query. Values you don't set will be left unaltered—or, if they didn't exist before, they will still be 'null' after your query.
CI allows you to set your values either with an array, or with $this->db-set(); the difference is only one of syntax.
So, let's add a line to our 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();
We want to add a new site, which has a URL, a name, a type, and a client ID number. As an array, this might be:
$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, we 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');
If we are updating an existing record, then again we can either create an array, or use $this->db->set(). But there are two differences.
Firstly, we have to specify the record we want to update; and second, we need to use $this->db->update(). If I want to update a record (say the record with its 'id' field set to 1) in my sites table, using the data set out in my $data array above, the syntax is:
$this->db->where('id', '1');
$this->db->update('sites', $data);
Or I can set out the information using $this->db->set(), as above.
CI gives you several functions to check what the database has done. Most usefully:
$this->db->affected_rows();
should return '1' after my insert or update statement—but might show more rows if I was altering 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 I didn't set an ID field when I created a new record. That's because we set the database to populate the ID field automatically when a new record is added. But I do have to specify an ID when I update an existing record, otherwise the database doesn't know which one to alter.
If I'm generating a new record, however, I don't know the ID number until I've generated it. If I then need to refer to the new record, I can get the new ID
number with:
$new_id_number = $this->db->insert_id();
(This code has to go, as soon as possible, after the operation that generated the record, or it may give a misleading result.)
For a little more peace of mind, remember that CI 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 they either all succeed, or all fail. This is essential in double-entry book-keeping applications and many commercial sites. For instance, say you are selling theatre tickets. You 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, but 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 that if they all succeed, the transaction is 'committed', and if one or more fails, the transaction is 'rolled back'. We don't need to use this in our example site, but if you want more information see the CI online User Guide.