Codeigniter Doctrine CRUD stands for Create, Read, Update and Delete, the basic operations performed on the database. With regular CodeIgniter setups, most developers would use the Active Record Class for performing such operations. But we are not going to be doing that.

In this episode of the series, I will show you how to perform CRUD with Doctrine.

"CodeIgniter and Doctrine from Scratch" Series:

In Doctrine, there can be multiple ways of accomplishing the same task. This gives you flexibility to pick the best approach suitable for each situation. Therefore we shall explore these different methods for each part of the CRUD.

1. CREATE

The act of inserting new data/records into the database:

Record Object

This is the way we have been creating our user records in the previous tutorials. It’s pretty simple:

$u = new User();

$u->username = 'myuser';
$u->password = 'mypass';

$u->save();

When the save() method is called, the new record is created.

fromArray()

Sometimes you have all the data already in an array. Instead of assigning every value to the record object separately, you can simply use the fromArray() method.

$data = array(
	'username' => 'myuser',
	'password' => 'mypass',
	'email' => 'my@email.com'
);

$u = new User();

$u->fromArray($data);

$u->save();

flush()

When you call the flush() method on the Doctrine_Connection object, all unsaved record objects automatically get saved. And Doctrine performs this in a single transaction.

$u = new User();
$u->username = 'myuser';
$u->password = 'mypass';

$u2 = new User();
$u2->username = 'foouser';
$u2->password = 'foopass';

$conn = Doctrine_Manager::connection();

$conn->flush();

This way you don’t need to call the save() method for each object.

Raw SQL

We can just give a plain SQL query to the Doctrine_Connection object and call the execute() method.

This also supports binding of values to the query (with the usage of ‘?’), which automatically filters against SQL injection.

$data = array('myuser','mypass');

$conn = Doctrine_Manager::connection();

$conn->execute('INSERT INTO user (username, password) VALUES (?,?)',  $data);

2. READ

The act of fetching records from the database.

Most of these are performed on Doctrine_Table objects, which is obtained by calling Doctrine::getTable() and passing the model class name to it.

find()

The find() method fetches a record by the Primary Key value.

$user_id = 1;

$u = Doctrine::getTable('User')->find($user_id);

echo $u->username;

findOneBy*()

This is a magic method. You can simply append the column name, and it will search the table by that column.

For example, to find a user record by username:

$username = 'myuser';

$u = Doctrine::getTable('User')->findOneByUsername($username);

echo $u->username;

It fetches only one record.

findBy*()

Another magic method, which works similarly, but fetches multiple rows. It returns a Doctrine_Collection object.

You can treat this returned object just like an array, to get to the individual record.

(let’s assume we have a column named ’status’):

$users = Doctrine::getTable('User')->findByStatus('active');

echo $users[0]->username;
echo $users[1]->username;

Even if the actual column name is all lowercase, it can be used as capitalized in that magic function call (status vs. Status).

DQL

This is our first look at DQL in these tutorials. It stands for Doctrine Query Language. It is actually a major feature. Most advanced queries are performed using DQL.

In this example, we first create the query object, and add the components of the query to it. All the method calls can be chained nicely. Finally we execute the query, which returns us a Doctrine_Collection object, like that last example.

$status = 'active';

$q = Doctrine_Query::create()
	->select('username')
	->from('User')
	->where('status = ?', $status)
	->limit(20);

$users = $q->execute();

echo $users[0]->username;
echo $users[1]->username;

We will see many more examples of DQL in our project. But if you want more info right now, there is a long documentation chapter here.

toArray()

A Doctrine_Record object contains a lot of stuff behind the scenes. If you ever try to print_r() that object directly, you will see a long list of data.

However, when you simply want the data of the record, you can convert it to an array with the toArray() call.

$user_id = 1;

$u = Doctrine::getTable('User')->find($user_id);

$u_arr = $u->toArray();

print_r($u_arr);

What you get will be similar to what you get from a mysql_fetch_assoc() call.

3. UPDATE

The act of updating existing records.

Record Object

You can make direct changes to any Doctrine_Record object. Once you call save(), it will perform an UPDATE.

$user_id = 1;

$u = Doctrine::getTable('User')->find($user_id);

$u->password = 'newpassword';

$u->save();

DQL

When you want to update multiple rows at once, the preferred way is to use DQL.

$status = 'active';

$q = Doctrine_Query::create()
	->update('User')
	->set('status', '?', $status)
	->where('id < 10');

$numrows = $q->execute();
echo "$numrows records updated";

The execute() call in this case returns the number of updated rows.

4. DELETE

The act of deleting records from the database.

Record Object

You can simply call the delete() method to delete a Doctrine_Record object directly.

$user_id = 1;

$u = Doctrine::getTable('User')->find($user_id);

$u->delete();

DQL

When deleting multiple records, use DQL.

$q = Doctrine_Query::create()
	->delete('User')
	->where('id < 10');

$numrows = $q->execute();
echo "$numrows records deleted";

The execute() method call will return the number of deleted records.

Stay Tuned

Today, we took a short break from coding our project and explored some important subjects regarding the usage of CRUD with Doctrine. What we learned today will be very useful as we continue building our project in the next tutorials.

See you next time!

"CodeIgniter and Doctrine from Scratch" Series: