Even after using PHP for years, we stumble upon functions and features that we did not know about. Some of these can be very useful, yet underused. Not all of us have read the manual and the function reference from cover to cover!

Read the full article

Regular Expressions are the Swiss Army knife for searching through information for certain patterns. They have a wide arsenal of tools, some of which often go undiscovered or underutilized. Today I will show you some advanced tips for working with regular expressions.

Read the full article

In today’s screencast we are going to learn how to create our own helpers and libraries. We are also going to learn how to extend the existing helpers and libraries that come with CodeIgniter. At the end of the tutorial we will extend the Form Validation library to give it the ability to check for password strength.

Click Here to Watch the Screencast

Today, we are going to take a look at the Shopping Cart library for CodeIgniter. This useful class allows us to add and remove items to a shopping cart, update them, and calculate prices. I will demonstrate how you can build a simple shopping cart system with the help of this library..

Click Here to Watch the Screencast

In today’s episode, we are going to be working with several helper functions, related to files, directories, and downloads. We are going to learn how to read, write, download files, and retrieve information about both files and directories. Also at the end we will build a small file browser that utilizes jQuery as well.

Click Here to Watch the Screencast

Here is my next screencast on CodeIgniter.

In this tenth episode of the CodeIgniter From Scratch screencast series, we will be exploring the Calendar library. We are also going to utilize the database class and jQuery AJAX. I will show you how to build a simple and CSS-styled calendar page, which will have the ability to store and display content for each day.

Click Here to Watch

Nettuts+ published my next screencast on CodeIgniter.

In lesson nine of our CodeIgniter series, we’ll build a small image gallery that allows you to upload files, and automatically create thumbnails.

Click Here to Watch

Today Nettuts+ published my screencast on CodeIgniter.

The CodeIgniter from Scratch series was unexpectedly, and significantly popular. Today, I’m pleased to announce that, with the help of one of my best authors, Burak, we’ll be continuing the series! Additionally, the most often requested topic is the subject for today’s screencast: combining CodeIgniter and jQuery.

Click Here to Watch

In this tutorial we will be looking into Doctrine Record Hooks. This will allow us to trigger certain actions in our Models.

We are going to use this feature to simplify a few things in the existing code, and even gain some performance benefits.

Record Hooks

In the Day 8 article we talked about CodeIgniter Hooks. This time we are going to look at Record Hooks with Doctrine.

Hooks are used to execute certain code when a certain event is triggered. Record Hooks are used when something happens with a Doctrine Record. For example we can use the postInsert hook to do some task every time a new record is inserted.

Here is a list of all available Record Hooks:

• preSave
• postSave
• preUpdate
• postUpdate
• preInsert
• postInsert
• preDelete
• postDelete
• preValidate
• postValidate

To use a one of these hooks, simply add a method with that name into the Doctrine_Record model:

class Post extends Doctrine_Record {

	public function setTableDefinition() {
		// ...
	}

	public function setUp() {
		// ...
	}

	public function postInsert() {
		// a new record was inserted
		// you can put some code here
	}

}

Let’s look at what we are going to be doing today to utilize this feature.

Forum Display Page Improvements

In the Day 9 article we built a page for displaying all Threads under a Forum.

Let’s look at the code we used for getting the list of threads:

// from the Forum model

// ...
	public function getThreadsArray($offset, $limit) {

		$threads = Doctrine_Query::create()
			->select('t.title')
			->addSelect('p.id, (COUNT(p.id) - 1) as num_replies')
			->addSelect('MIN(p.id) as first_post_id')
			->addSelect('MAX(p.created_at) as last_post_date')
			->from('Thread t, t.Posts p')
			->where('t.forum_id = ?', $this->id)
			->groupBy('t.id')
			->orderBy('last_post_date DESC')
			->limit($limit)
			->offset($offset)
			->setHydrationMode(Doctrine::HYDRATE_ARRAY)
			->execute();

		foreach ($threads as &$thread) {

			$post = Doctrine_Query::create()
				->select('p.created_at, u.username')
				->from('Post p, p.User u')
				->where('p.id = ?', $thread['Posts'][0]['first_post_id'])
				->setHydrationMode(Doctrine::HYDRATE_ARRAY)
				->fetchOne();

			$thread['num_replies'] = $thread['Posts'][0]['num_replies'];
			$thread['created_at'] = $post['created_at'];
			$thread['username'] = $post['User']['username'];
			$thread['user_id'] = $post['User']['id'];
			unset($thread['Posts']);

		}

		return $threads;

	}
// ...

There are two main DQL queries. The first one for fetching the Threads, and the second one, inside of a loop, for fetching further details about each Thread, such as number of replies, the username etc…

There is quite a bit going on in there. With the changes we will make today, we will be able to reduce that into a single DQL call.

Adding a “First_Post” Relationship

We first had to get the id of the first post, and then get information on that post in another DQL query. If we had a direct relationship with a “First_Post” record, it would simplify things.

• Edit: system/application/models/thread.php

<?php
class Thread extends Doctrine_Record {

	public function setTableDefinition() {
		$this->hasColumn('title', 'string', 255);
		$this->hasColumn('forum_id', 'integer', 4);
		$this->hasColumn('first_post_id', 'integer', 4);
	}

	public function setUp() {
		$this->hasOne('Forum', array(
			'local' => 'forum_id',
			'foreign' => 'id'
		));
		$this->hasMany('Post as Posts', array(
			'local' => 'id',
			'foreign' => 'thread_id'
		));

		$this->hasOne('Post as First_Post', array(
			'local' => 'first_post_id',
			'foreign' => 'id'
		));
	}

}

The highlighted lines have been added. Now each Thread will have a relationship with a Post record, which will be the first Post in that Thread.

Setting up the Hook

When a new Post is added, if it is the first Post in that Thread, it needs to be assigned to the First_Post relationship. This will be done with the following “postInsert” Record Hook.

• Edit: system/application/models/post.php

<?php
class Post extends Doctrine_Record {

// ...
	public function postInsert() {

		// is this the first post?
		if (!$this['Thread']['First_Post']->exists()) {
			$this['Thread']['First_Post'] = $this;
			$this['Thread']->save();
		}

	}

}

Note that “postInsert” means, “after” insert. It has nothing to do with the class name “Post”.

This function gets invoked right after a new Post record was created.

First we check if a First_Post relationship exists, with the exist() function. If not, we assign “$this”, which is the current Post record, to this relationship with the corresponding Thread record.

Rebuild the Database

• Drop all tables.

You may use this query:

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE category, forum, post, thread, user;

Now we need to rebuild it all.

• Go to: http://localhost/ci_doctrine/doctrine_tools/create_tables and click the button.
• Go to: http://localhost/ci_doctrine/doctrine_tools/load_fixtures and click the button.

Now all the tables are rebuilt and the fixture data is reloaded.

Take a look at your thread table:

You can see the new column. And thanks to the Record Hook, the values have been assigned already.

Simplify Things

Now we can go ahead and simplify the “getThreadsArray” method under the Forum Model.

• Edit: system/application/models/forum.php

<?php
class Forum extends Doctrine_Record {

// ...

	public function getThreadsArray($offset, $limit) {

		$threads = Doctrine_Query::create()
			->select('t.title')
			->addSelect('p.id, (COUNT(p.id) - 1) as num_replies')
			->addSelect('MAX(p.created_at) as last_post_date')
			->addSelect('fp.created_at, u.username')
			->from('Thread t, t.Posts p, t.First_Post fp, fp.User u')
			->where('t.forum_id = ?', $this->id)
			->groupBy('t.id')
			->orderBy('last_post_date DESC')
			->limit($limit)
			->offset($offset)
			->setHydrationMode(Doctrine::HYDRATE_ARRAY)
			->execute();

		foreach ($threads as &$thread) {

			$thread['num_replies'] = $thread['Posts'][0]['num_replies'];
			$thread['created_at'] = $thread['First_Post']['created_at'];
			$thread['username'] = $thread['First_Post']['User']['username'];
			$thread['user_id'] = $thread['First_Post']['User']['id'];

			unset($thread['Posts']);

		}

		return $threads;

	}

}

Line 13: Now we have 2 more relationships in the from() call. First_Post and the User for the First_Post.
Line 12: We select the created_at and username fields from these new relationships.
Line 25,26,27: These lines have been changed to use the new fields.
Also the DQL query inside the loop is now gone. This should have a positive impact on performance.

Note that the loop now is only being used to simplify the $threads array structure, so it’s actually optional.

Testing the Results

We haven’t really changed anything on the actual pages. So everything should look the same as before.

Stay Tuned

There are many things you can do with Hooks and Listeners in Doctrine. Make sure to read the documentation.

I hope you enjoyed this tutorial. See you next time!

Cron Jobs are used for scheduling tasks to run on the server. They’re most commonly used for automating system maintenance or administration. However, they are also relevant to web application development. There are many situations when a web application may need certain tasks to run periodically. Today we are going to explore the fundamentals of Cron Jobs.

Read the full article at Nettuts

Today, we continue our journey into the world of SQL and relational database systems. In this part three of the series, we’ll learn how to work with multiple tables that have relationships with each other. First, we will go over some core concepts, and then will begin working with JOIN queries in SQL.

Read More at Nettuts.com

Today we will be working on Pagination.

Upgrading Doctrine

Before we get started, let’s upgrade Doctrine.

When I wrote the first article in this series, Doctrine 1.1 was the current stable version at the time. Since then, 1.2 has come out. Luckily it is backwards compatible with 1.1.

So, if you are like me and have been using Doctrine 1.1, just follow these steps to upgrade to 1.2.

• Delete folder: system/application/plugins/doctrine/lib
• Download Doctrine 1.2 and extract.
• Copy the lib folder to system/application/plugins/doctrine

One Small Fix

At some point I did find an issue that wasn’t backwards compatible. I modified the previous articles to fix that issue but some of you may not have done this if you read those articles before I changed them.

• Edit: system/application/controllers/home.php

<?php
class Home extends Controller {

	public function index() {

		$vars['categories'] = Doctrine_Query::create()
			->select('c.title, f.title, f.description')
			->addSelect('t.id, COUNT(t.id) as num_threads')
			->from('Category c, c.Forums f')
			->leftJoin('f.Threads t')
			->groupBy('f.id')
			->execute();

		$vars['title'] = 'Home';
		$vars['content_view'] = 'forum_list';
		$vars['container_css'] = 'forums';

		$this->load->view('template', $vars);
	}

}

It’s just the highlighted line. The ‘t.id’ needed to be added to the addselect() or COUNT(t.id) did not work properly.

That is all. If your website is loading without errors, everything should be fine.

What is Pagination?

Displaying data in multiple pages is Pagination.

There are two main parts to this. First we need to generate links to the pages.

Pagination Links

Here is an example from CodeIgniter Forums:

That looks simple, but we also need to consider what happens when we start going to other pages:

Now there is a “Prev” link which we didn’t have before. And we see links to 5 page numbers instead of 3.

Things change even more if we go deeper:

Now there is also a “<< First" link, because the link for Page 1 is no longer visible.

One more:

The “Last >>” link disappeared because we already have a link to Page 89, which is the last page.

Another example from Digg.com:

As you can see they are doing it quite differently. It’s all a matter of preference.

Let’s look at the way they do the links.

CodeIgniter Forums:

http://codeigniter.com/forums/viewforum/59/
http://codeigniter.com/forums/viewforum/59/P25/
http://codeigniter.com/forums/viewforum/59/P50/

Those were the links for the first, second and third page. The first page gets no parameter as it works with default values. But the second and third pages have these “P25″ and “P50″ parameters at the end. Strangely enough these are not page numbers. They actually represent the offset value for the records instead, as they display 25 records per page.

Digg.com:

http://digg.com/all/upcoming
http://digg.com/all/upcoming/page2
http://digg.com/all/upcoming/page3

Again, we see a difference between the two websites. Digg.com prefers to use actual page numbers instead of offset numbers.

Paginating Data

This is the second part to this Pagination subject. Our code needs to be responsible for displaying the correct set of data on each page.

In terms of raw SQL, here is how you can get a set of records for a given page:

SELECT * FROM table LIMIT 50, 25

The LIMIT clause does the job. In this particular query, we fetch 25 records, starting after the first 50 records. So, 50 is the offset and 25 is the limit, i.e. the number of records.

With Doctrine we will be using DQL rather than raw SQL queries.

Let’s say we have a URL like they do on CodeIgniter forums:

http://codeigniter.com/forums/viewforum/59/P125/

That would translate into having the offset 125. And the limit would always be 25, because internally we decide to display 25 threads per page.

If we have a Digg style pagination URL:

http://digg.com/all/upcoming/page7

We need to multiply 7 with the “per page” count. So it could be 175 (7*25) offset and 25 limit, if the per page limit is set to 25. But we are not going to be using this method.

CodeIgniter Pagination Library

CodeIgniter comes with a nice simple Pagination Library, which we will be using. First, I will explain it briefly.

Let’s put some code in a test Controller.

• Edit: system/application/controllers/test.php

class Test extends Controller {

	function paginate() {

		$this->load->library('pagination');

		$config['base_url'] = base_url() . "test/paginate";
		$config['total_rows'] = '200';
		$config['per_page'] = '10';

		$this->pagination->initialize($config);

		echo $this->pagination->create_links();

	}

}

The code is very simple. We just provide the base url, number of total records, and the number of records per page.

The results look like this:

And the links look like this:

http://localhost/ci_doctrine/test/paginate/
http://localhost/ci_doctrine/test/paginate/10
http://localhost/ci_doctrine/test/paginate/20
http://localhost/ci_doctrine/test/paginate/30
http://localhost/ci_doctrine/test/paginate/40

So this library puts the offset number, rather than the page number at the end of the URL’s.

Also, with this library you can customize the HTML structure of the links. You can see more information about that in the documentation.

Adding More Data

First we are going to add some more data to our database so we have something to paginate.

All we have to do is add more stuff to the fixture file we have been using before:

• Edit: system/fixtures/data.yml

User:
  Admin:
    username: Administrator
    password: testing
    email: programming@gmail.com
  Test:
    username: TestUser
    password: mypass
    email: test@test.com
  Foo:
    username: Foobar
    password: mypass
    email: test2@test2.com

Forum:
  Forum_1:
    title: Introduce Yourself!
    description: >
      Use this forum to introduce yourself to the CodeIgniter community,
      or to announce your new CI powered site.
  Forum_2:
    title: The Lounge
    description: >
      CodeIgniter's social forum where you can discuss anything not related
      to development. No topics off limits... but be civil.
  Forum_3:
    title: CodeIgniter Discussion
    description: This forum is for general topics related to CodeIgniter.
  Forum_4:
    title: Code and Application Development
    description: >
      Use the forum to discuss anything related to
      programming and code development.
  Forum_5:
    title: Ignited Code
    description: >
      Use this forum to post plugins, libraries, or other code contributions,
      or to ask questions about any of them.

Category:
  Lounge:
    title: The CodeIgniter Lounge
    Forums: [Forum_1, Forum_2]
  Dev:
    title: CodeIgniter Development Forums
    Forums: [Forum_3, Forum_4, Forum_5]

Thread:
  Thread_1:
    title: Hi there!
    Forum: Forum_1
  Thread_2:
    title: Greetings to all
    Forum: Forum_1
  Thread_3:
    title: Sed vitae ligula erat
    Forum: Forum_3
  Thread_4:
    title: Vivamus laoreet quam vitae mauris tempus
    Forum: Forum_3
  Thread_5:
    title: Maecenas vel dolor odio
    Forum: Forum_3
  Thread_6:
    title: In nec justo at orci fermentum
    Forum: Forum_3
  Thread_7:
    title: Nullam volutpat laoreet orci
    Forum: Forum_3
  Thread_8:
    title: Sed sodales augue vel elit
    Forum: Forum_3
  Thread_9:
    title: Integer posuere luctus metus
    Forum: Forum_3
  Thread_10:
    title: Maecenas ut mauris eget odio pharetra
    Forum: Forum_3
  Thread_11:
    title: Pellentesque lacinia nibh vel lacus
    Forum: Forum_3
  Thread_12:
    title: Nunc facilisis nibh a nulla laoreet in ultricies
    Forum: Forum_3
  Thread_13:
    title: Maecenas pretium nisi eget nunc rutrum quis
    Forum: Forum_3
  Thread_14:
    title: Etiam at ligula leo
    Forum: Forum_3
  Thread_15:
    title: Vivamus tempus semper libero
    Forum: Forum_3
  Thread_16:
    title: Phasellus venenatis consectetur quam
    Forum: Forum_3
  Thread_17:
    title: Nam sagittis elementum turpis
    Forum: Forum_3
  Thread_18:
    title: Sed at odio id ante rutrum sodales
    Forum: Forum_3
  Thread_19:
    title: Praesent eget lorem nec odio
    Forum: Forum_3
  Thread_20:
    title: Donec at enim sit amet quam
    Forum: Forum_3
  Thread_21:
    title: Ut sit amet ante nec leo volutpat
    Forum: Forum_3
  Thread_22:
    title: Pellentesque accumsan orci nec
    Forum: Forum_3
  Thread_23:
    title: Curabitur convallis sapien in dolor feugiat
    Forum: Forum_3
  Thread_24:
    title: Aenean sodales massa in dui ultrices
    Forum: Forum_3

Post:
  Post_1:
    Thread: Thread_1
    User: Test
    created_at: '2009-11-20 01:20:30'
    updated_at: '2009-11-20 01:20:30'
    content: >
      Hello everyone! My name is Test, and I go to school at
      Test Institute of Technology in the US.
      I just found CodeIgniter some time last week and have been
      reading through the documentation trying to get myself acquainted.

      Hopefully the forums will be a great help! I already have some questions.
      Thanks!
  Post_2:
    Thread: Thread_1
    User: Admin
    created_at: '2009-11-20 02:15:33'
    updated_at: '2009-11-20 02:15:33'
    content: Welcome Test! Nice to meet you.
  Post_3:
    Thread: Thread_2
    User: Foo
    created_at: '2009-11-19 12:14:50'
    updated_at: '2009-11-19 12:14:50'
    content: I am new here. Just wanted to say hi.
  Post_4:
    Thread: Thread_3
    User: Foo
    created_at: '2009-12-01 12:14:50'
    updated_at: '2009-12-01 12:14:50'
    content: Vivamus ultricies hendrerit justo, sit amet semper nulla scelerisque pulvinar.
  Post_5:
    Thread: Thread_4
    User: Foo
    created_at: '2009-12-02 12:14:50'
    updated_at: '2009-12-02 12:14:50'
    content: Sed luctus enim ut magna pellentesque mollis.
  Post_6:
    Thread: Thread_5
    User: Foo
    created_at: '2009-12-03 12:14:50'
    updated_at: '2009-12-03 12:14:50'
    content: Nam id nisi dolor, vel interdum turpis.
  Post_7:
    Thread: Thread_6
    User: Foo
    created_at: '2009-12-04 12:14:50'
    updated_at: '2009-12-04 12:14:50'
    content: Donec volutpat accumsan lorem, at euismod metus lobortis viverra.
  Post_8:
    Thread: Thread_7
    User: Foo
    created_at: '2009-12-05 12:14:50'
    updated_at: '2009-12-05 12:14:50'
    content: Nulla vestibulum erat ac nisi convallis rutrum.
  Post_9:
    Thread: Thread_8
    User: Foo
    created_at: '2009-12-06 12:14:50'
    updated_at: '2009-12-06 12:14:50'
    content: Nulla pharetra tortor id ante sollicitudin sodales.
  Post_10:
    Thread: Thread_9
    User: Foo
    created_at: '2009-12-07 12:14:50'
    updated_at: '2009-12-07 12:14:50'
    content: Cras id metus a elit mattis blandit et aliquet diam.
  Post_11:
    Thread: Thread_10
    User: Foo
    created_at: '2009-12-08 12:14:50'
    updated_at: '2009-12-08 12:14:50'
    content: Nunc non felis vitae dolor posuere aliquam non et augue.
  Post_12:
    Thread: Thread_11
    User: Foo
    created_at: '2009-12-09 12:14:50'
    updated_at: '2009-12-09 12:14:50'
    content: Nam et velit ac tellus interdum adipiscing.
  Post_13:
    Thread: Thread_12
    User: Foo
    created_at: '2009-12-10 12:14:50'
    updated_at: '2009-12-10 12:14:50'
    content: Donec viverra leo mauris, ac convallis turpis.
  Post_14:
    Thread: Thread_13
    User: Foo
    created_at: '2009-12-11 12:14:50'
    updated_at: '2009-12-11 12:14:50'
    content: Integer sagittis nisl ut nisi euismod in dignissim massa sagittis.
  Post_15:
    Thread: Thread_14
    User: Foo
    created_at: '2009-12-12 12:14:50'
    updated_at: '2009-12-12 12:14:50'
    content: Integer vel lectus mollis quam sollicitudin porta.
  Post_16:
    Thread: Thread_15
    User: Foo
    created_at: '2009-12-13 12:14:50'
    updated_at: '2009-12-13 12:14:50'
    content: Etiam tempor luctus sem, at consequat enim posuere in.
  Post_17:
    Thread: Thread_16
    User: Foo
    created_at: '2009-12-14 12:14:50'
    updated_at: '2009-12-14 12:14:50'
    content: Proin placerat lectus dolor, quis viverra ante.
  Post_18:
    Thread: Thread_17
    User: Foo
    created_at: '2009-12-15 12:14:50'
    updated_at: '2009-12-15 12:14:50'
    content: Maecenas ullamcorper commodo leo, lobortis molestie turpis cursus sit amet.
  Post_19:
    Thread: Thread_18
    User: Foo
    created_at: '2009-12-16 12:14:50'
    updated_at: '2009-12-16 12:14:50'
    content: Integer tincidunt facilisis dolor, vitae pellentesque turpis rutrum sed.
  Post_20:
    Thread: Thread_19
    User: Foo
    created_at: '2009-12-17 12:14:50'
    updated_at: '2009-12-17 12:14:50'
    content: Donec eget lacus a nibh volutpat venenatis quis a felis.
  Post_21:
    Thread: Thread_20
    User: Foo
    created_at: '2009-12-18 12:14:50'
    updated_at: '2009-12-18 12:14:50'
    content: Nam fringilla tellus quis augue elementum eleifend.
  Post_22:
    Thread: Thread_21
    User: Foo
    created_at: '2009-12-19 12:14:50'
    updated_at: '2009-12-19 12:14:50'
    content: Nullam sollicitudin nulla at orci accumsan eget ultrices felis vehicula.
  Post_23:
    Thread: Thread_22
    User: Foo
    created_at: '2009-12-20 12:14:50'
    updated_at: '2009-12-20 12:14:50'
    content: Nullam sit amet purus nec mauris convallis tincidunt sodales eget nunc.
  Post_24:
    Thread: Thread_23
    User: Foo
    created_at: '2009-12-21 12:14:50'
    updated_at: '2009-12-21 12:14:50'
    content: Praesent in eros non elit ultricies tincidunt a nec tellus.
  Post_25:
    Thread: Thread_24
    User: Foo
    created_at: '2009-12-22 12:14:50'
    updated_at: '2009-12-22 12:14:50'
    content: Aenean ac nisl a sapien pulvinar gravida et quis metus.

Now load this fixture:

• Go to: http://localhost/ci_doctrine/doctrine_tools/load_fixtures
• Hit the button.
• Go home: http://localhost/ci_doctrine/

You should see this:

• Click the “CodeIgniter Discussion” link

You should see:

Sorting By Last Post Date

As it is, the Threads are not being sorted by a specific column, because we did not add that to the DQL query last time. They need to be sorted by the “created_at” date of the last Post in that Thread, in descending order. Let’s fix that now.

• Edit: system/application/models/forum.php

class Forum extends Doctrine_Record {

// ...

	public function getThreadsArray() {

		$threads = Doctrine_Query::create()
			->select('t.title')
			->addSelect('p.id, (COUNT(p.id) - 1) as num_replies')
			->addSelect('MIN(p.id) as first_post_id')
			->addSelect('MAX(p.created_at) as last_post_date')
			->from('Thread t, t.Posts p')
			->where('t.forum_id = ?', $this->id)
			->groupBy('t.id')
			->orderBy('last_post_date DESC')
			->setHydrationMode(Doctrine::HYDRATE_ARRAY)
			->execute();

		foreach ($threads as &$thread) {

			$post = Doctrine_Query::create()
				->select('p.created_at, u.username')
				->from('Post p, p.User u')
				->where('p.id = ?', $thread['Posts'][0]['first_post_id'])
				->setHydrationMode(Doctrine::HYDRATE_ARRAY)
				->fetchOne();

			$thread['num_replies'] = $thread['Posts'][0]['num_replies'];
			$thread['created_at'] = $post['created_at'];
			$thread['username'] = $post['User']['username'];
			$thread['user_id'] = $post['User']['id'];
			unset($thread['Posts']);

		}

		return $threads;

	}

}

The highlighted lines have been added. They should be self-explanatory.

We should also update the View so it shows that date.

• Edit: system/application/views/forum.php

<h2><?php echo $title ?></h2>

<?php foreach($threads as $thread): ?>
<div class="thread">

	<h3>
		<?php echo anchor('threads/display/'.$thread['id'], $thread['title']) ?>
		(<?php echo $thread['num_replies']; ?> replies)
	</h3>

	<div>
		Author: <em><?php echo anchor('profile/display/'.$thread['user_id'],
								$thread['username']); ?></em>,
		Last Post: <em><?php echo $thread['last_post_date']; ?></em>
	</div>

</div>
<?php endforeach; ?>

Pagination Links

Now, to do pagination, we need 3 pieces of information:

• Total number of records. In our case, number of Threads.
• Number of records per page. We can pick any number. I will set it to 4 for now so we can have several pages.
• Current page number or record offset. Since CodeIgniter Pagination library works with the offset number, rather than page number, that’s what we will use.

We need to edit the View, the Model and the Controller.

The View

Let’s set the output of the pagination links in the View.

• Edit: system/application/views/forum.php

<h2><?php echo $title ?></h2>

<?php foreach($threads as $thread): ?>
<div class="thread">

	<h3>
		<?php echo anchor('threads/display/'.$thread['id'], $thread['title']) ?>
		(<?php echo $thread['num_replies']; ?> replies)
	</h3>

	<div>
		Author: <em><?php echo anchor('profile/display/'.$thread['user_id'],
								$thread['username']); ?></em>,
		Last Post: <em><?php echo $thread['last_post_date']; ?></em>
	</div>

</div>
<?php endforeach; ?>

<?php if (isset($pagination)): ?>
	<div class="pagination">
		Pages: <?php echo $pagination; ?>
	</div>
<?php endif; ?>

At line 20, we check to see if the $pagination variable is set, which will contain the pagination links, because sometimes there won’t be enough records to paginate.

The Model

Now let’s modify the Forum Model.

• Edit: system/application/models/forum.php

<?php
class Forum extends Doctrine_Record {

	public function setTableDefinition() {
		$this->hasColumn('title', 'string', 255);
		$this->hasColumn('description', 'string', 255);
		$this->hasColumn('category_id', 'integer', 4);
	}

	public function setUp() {
		$this->hasOne('Category', array(
			'local' => 'category_id',
			'foreign' => 'id'
		));
		$this->hasMany('Thread as Threads', array(
			'local' => 'id',
			'foreign' => 'forum_id'
		));
	}

	public function numThreads() {

		$result = Doctrine_Query::create()
			->select('COUNT(*) as num_threads')
			->from('Thread')
			->where('forum_id = ?', $this->id)
			->setHydrationMode(Doctrine::HYDRATE_ARRAY)
			->fetchOne();

		return $result['num_threads'];

	}

	public function getThreadsArray($offset, $limit) {

		$threads = Doctrine_Query::create()
			->select('t.title')
			->addSelect('p.id, (COUNT(p.id) - 1) as num_replies')
			->addSelect('MIN(p.id) as first_post_id')
			->addSelect('MAX(p.created_at) as last_post_date')
			->from('Thread t, t.Posts p')
			->where('t.forum_id = ?', $this->id)
			->groupBy('t.id')
			->orderBy('last_post_date DESC')
			->limit($limit)
			->offset($offset)
			->setHydrationMode(Doctrine::HYDRATE_ARRAY)
			->execute();

		foreach ($threads as &$thread) {

			$post = Doctrine_Query::create()
				->select('p.created_at, u.username')
				->from('Post p, p.User u')
				->where('p.id = ?', $thread['Posts'][0]['first_post_id'])
				->setHydrationMode(Doctrine::HYDRATE_ARRAY)
				->fetchOne();

			$thread['num_replies'] = $thread['Posts'][0]['num_replies'];
			$thread['created_at'] = $post['created_at'];
			$thread['username'] = $post['User']['username'];
			$thread['user_id'] = $post['User']['id'];
			unset($thread['Posts']);

		}

		return $threads;

	}

}

Line 21: Added new method named “numThreads”, since we are going to need the total number of Threads.

Note: I know that it would have been simpler to just use this code: “$this->Threads->count()”. However, unfortunately Doctrine creates a very inefficient query when we do that, that’s why I used a DQL query instead.

Line 34: Just added 2 parameters to the existing function, since we are only interested in the Thread records for a given page.

Lines 45-46: Here we use the given $offset and $limit parameters to limit the DQL query results.

The Controller

Finally we update the Forums Controller.

• Edit: system/application/controllers/forums.php

<?php
class Forums extends Controller {

	public function display($id, $offset = 0) {

		$per_page = 4;

		$forum = Doctrine::getTable('Forum')->find($id);

		$vars['title'] = $forum['title'];
		$vars['threads'] = $forum->getThreadsArray(
			$offset,
			$per_page
		);
		$vars['content_view'] = 'forum';
		$vars['container_css'] = 'forum';

		$num_threads = $forum->numThreads();

		// do we have enough to paginate
		if ($num_threads > $per_page) {
			// PAGINATION
			$this->load->library('pagination');
			$config['base_url'] = base_url() . "forums/display/$id";
			$config['total_rows'] = $num_threads;
			$config['per_page'] = $per_page;
			$config['uri_segment'] = 4;
			$this->pagination->initialize($config);

			$vars['pagination'] = $this->pagination->create_links();
		}

		$this->load->view('template', $vars);

	}

}

Updated lines are highlighted.

Line 4: Now there is a second parameter named $offset, because the paginated URL’s will contain the offset after the Forum id.

Line 6: Here we pick a number of Threads per page. Normally it could be 10, 20 or 25, but for this test we are picking a smaller number.

Lines 12-13: We send the 2 parameters that the getThreadsArray method is expecting.

Line 21: We only do pagination if there are more records than the $per_page number.

Line 27: We have to set the ‘uri_segment’ setting for the Pagination library. This is the uri segment that will carry the offset number. If not set, it defaults to uri segment 3. For example the URL will look like this:

http://localhost/ci_doctrine/forums/display/53/8

“forums” is uri segment 1, “display” is uri segment 2, “53″ is uri segment 3 and the Forum id, “8″ is the URI segment 4 and the $offset variable.

The Result

• Go to: http://localhost/ci_doctrine/
• Click the forum name: “CodeIgniter Discussion”

You should see:

Continue clicking on the pagination links:

Seems to be working

Stay Tuned

Thank you for reading, and sorry about the delay. We still have more work to do. See you next time!

cURL is a tool for transferring files and data with URL syntax, supporting many protocols including HTTP, FTP, TELNET and more. Initially, cURL was designed to be a command line tool. Lucky for us, the cURL library is also supported by PHP. In this article, we will look at some of the advanced features of cURL, and how we can use them in our PHP scripts.

Read the rest of the article at Nettuts

Another article published on Nettuts

http://net.tutsplus.com/tutorials/other/sql-for-beginners-part-2/

It is important for every web developer to be familiar with database interactions. In part two of the series, we will continue exploring the SQL language and apply what we’ve learned on a MySQL database. We will learn about Indexes, Data Types and more complex query structures.

Read More

Another article published on Nettuts

http://net.tutsplus.com/tutorials/other/sql-for-beginners/

Most modern web applications today interact with databases, usually with a language called SQL. Lucky for us, this language is quite easy to learn. In this article, we are going to start with some basic SQL queries and use them to interact with a MySQL database.

Read More

In this article we are going to create Templates (with CodeIgniter) to keep our Views organized, and avoid repeating the same HTML code. Also we are going to learn about Doctrine Data Hydrators to see alternative ways to structure the data returned from the database.

Templates

What and Why?

The term Template might be a little ambiguous right now. Maybe you are already thinking of Views as Templates. Which is mostly true. However, there is a problem with using separate Views for every single page in our application. There will be headers, footers and other content that will be repeated everywhere. That’s why there is a need for actual Templates for our Views.

For example, we will be creating a new View for the Forum pages. It will look mostly the same as the home page, using the same css stylesheet, same header, same user control panel, etc… There will be other Views later that will be the same way. If we ever make a change to any of these common parts, we don’t want to go through every single View. There should be Views that have the common html output that we can quickly edit. Using Templates will enable us to do just that, and maybe more.

Creating a Template

Let’s create our first Template, which is actually going to be a View itself. The idea is to keep all the common elements in one file, and take out the variable stuff.

For example, take a look at this:

The content section is definitely going to change from page to page. Also, I would like to take out the user controls section, so it can have its own View. We’re going to replace these with some code instead.

• Create: system/application/views/template.php

<!DOCTYPE html>
<html lang="en">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
	<title><?php echo $title; ?> | CI+Doctrine Message Board</title>
	<link rel="stylesheet" href="<?php echo base_url(); ?>css/style.css"
		type="text/css" media="all">
</head>
<body>

<div class="<?php echo $container_css; ?> container">

	<div class="user_controls">
		<?php $this->load->view('user_controls'); ?>
	</div>

	<h1>CI+Doctrine Message Board</h1>

	<?php $this->load->view($content_view); ?>

</div>

</body>
</html>

I have copied the contents of the Home View we created before, and replaced a few things.

Line 14: The code for the user_controls div will now be found in a View named user_controls.php.
Line 19: $content_view is the name of the View that will be loaded to fill up the content area. For example, we will use a View named forum_list for the home page. The controller will have to pass this variable.
Line 5: Now the page title can be passed as a $title variable.
Line 11: We can apply a different css class for the container div on every page.

As you can see, a View can load other Views. That’s a big help in creating Templates. Now, all Controllers can just load this Template View, and they can pass the name for a content View that will be loaded in the middle of the page.

Now let’s create the user_controls View.

• Create: system/application/views/user_controls.php

<?php if ($user = Current_User::user()): ?>
	Hello, <em><?php echo $user->username; ?></em> <br/>
	<?php echo anchor('logout', 'Logout'); ?>
<?php else: ?>
	<?php echo anchor('login','Login'); ?> |
	<?php echo anchor('signup', 'Register'); ?>
<?php endif; ?>

Basically it’s the same code as before, but now it is in its own View file.

Now let’s create the forum_list View.

• Create: system/application/views/forum_list.php

<?php foreach($categories as $category): ?>
<div class="category">

	<h2><?php echo $category->title; ?></h2>

	<?php foreach($category->Forums as $forum): ?>
	<div class="forum">

		<h3>
			<?php echo anchor('forums/display/'.$forum->id, $forum->title) ?>
			(<?php echo $forum->Threads[0]->num_threads; ?> threads)
		</h3>

		<div class="description">
			<?php echo $forum->description; ?>
		</div>

	</div>
	<?php endforeach; ?>

</div>
<?php endforeach; ?>

This code is also taken from the middle section of the old Home View. (I only made a small change to the link at line 10.)

Now we can get rid of the Home View file.

• Delete: system/application/views/home.php

Updating the Home Controller

This new Template needs to know what content to load, what the title of the page is, etc… So we need to update the Home Controller.

• Edit: system/application/controllers/home.php

<?php
class Home extends Controller {

	public function index() {

		$vars['categories'] = Doctrine_Query::create()
			->select('c.title, f.title, f.description')
			->addSelect('t.id, COUNT(t.id) as num_threads')
			->from('Category c, c.Forums f')
			->leftJoin('f.Threads t')
			->groupBy('f.id')
			->execute();

		$vars['title'] = 'Home';
		$vars['content_view'] = 'forum_list';
		$vars['container_css'] = 'forums';

		$this->load->view('template', $vars);
	}	

}

The changes are only the highlighted lines. We are now passing the page title, name of the content View, and a CSS class name for styling purposes. Then we load the View named ‘template’ instead of ‘home’ (since we deleted that one).

See the Results

• Go to: http://localhost/ci_doctrine/

The page should look exactly the same as before:

Later in this article we are going to use this Template to build the Forum page, that lists Threads in a Forum.

Data Hydrators (Doctrine)

Our Doctrine Models extend the Doctrine_Record class. We have been using instances of this class for representing and persisting the database records. Also, we have been using instances of the Doctrine_Collection class when dealing with multiple records (e.g. fetching multiple rows with DQL).

With Data Hydrators, we can work with the data in other structure formats. For example, with DQL we can have it return the results of a SELECT query as an Array instead of Doctrine_Collection.

HYDRATE_SCALAR

This returns an array similar to what you would get from doing raw SQL queries.

$category = Doctrine_Query::create()
	->select('c.*, f.*')
	->from('Category c, c.Forums f')
	->where('c.id = ?', 1)
	->setHydrationMode(Doctrine::HYDRATE_SCALAR)
	->execute();

print_r($category);
/* output:
Array
(
    [0] => Array
        (
            [c_id] => 1
            [c_title] => The CodeIgniter Lounge
            [f_id] => 1
            [f_title] => Introduce Yourself!
            [f_description] => Use this forum to introduce yourself to the CodeIgniter community, or to announce your new CI powered site.

            [f_category_id] => 1
        )

    [1] => Array
        (
            [c_id] => 1
            [c_title] => The CodeIgniter Lounge
            [f_id] => 2
            [f_title] => The Lounge
            [f_description] => CodeIgniter's social forum where you can discuss anything not related to development. No topics off limits... but be civil.

            [f_category_id] => 1
        )

)
*/

We fetched the Category with id 1, and it’s associated Forums. There were 2 Forums under that Category, so the result contains 2 rows of data.

If I run the same raw query from phpMyAdmin, it looks like this:

Note that the Category title is repeated in both rows, because of the nature of JOIN queries. That is also the case in the Array that was returned by DQL.

HYDRATE_ARRAY

This also returns an Array. But this time, it will have a nice multi-tier structure based on the Model relationships.

$category = Doctrine_Query::create()
	->select('c.*, f.*')
	->from('Category c, c.Forums f')
	->where('c.id = ?', 1)
	->setHydrationMode(Doctrine::HYDRATE_ARRAY)
	->execute();

print_r($category);
/* output:
Array
(
    [0] => Array
        (
            [id] => 1
            [title] => The CodeIgniter Lounge
            [Forums] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [title] => Introduce Yourself!
                            [description] => Use this forum to introduce yourself to the CodeIgniter community, or to announce your new CI powered site.

                            [category_id] => 1
                        )

                    [1] => Array
                        (
                            [id] => 2
                            [title] => The Lounge
                            [description] => CodeIgniter's social forum where you can discuss anything not related to development. No topics off limits... but be civil.

                            [category_id] => 1
                        )

                )

        )

)
*/

Now we have a multidimensional Array. As you can see, this time the Category info is not repeated. And Forums is a sub-array under the Category. Also the keys of the array retain their original names, like ‘title’, instead of ‘c_title’.

Other Hydrators

By default Doctrine is using HYDRATE_RECORD, that causes objects to be returned. And there are a few other Hydrators you can read about here: http://www.doctrine-project.org/documentation/manual/1_2/en/data-hydrators

Why?

Hydrators can have certain performance benefits. Creating an array is faster than creating an object that has lots of overhead. And arrays take less memory too.

Also there are things you can do with arrays that you cannot do with a Doctrine object. If you prefer to handle the data as an array, Hydration is the way to go.

We are going to be using HYDRATE_ARRAY to fetch a list of Threads in the following sections, to demonstrate the usage.

CSS Changes

Before we move on, I want to make some CSS changes, so the new pages we build will be styled.

• Edit: css/style.css

body {
	font-family: "Trebuchet MS",Arial;
	font-size: 14px;
	background-color: #212426;
	color: #B9AA81;
}
a			{color: #FFF;}
a:hover		{color: #B9AA81;}
input, textarea, select {
	font-family:inherit; font-size:inherit;	font-weight:inherit;
}
.container	{width: 720px; margin: auto;}
/* FORUMS -----------------------------------------*/
.forums.container h2 {
	font-size: 16px;
	color: #000;
	padding: 5px 10px 5px 10px;
	margin: 0px;
	background-color: #BBB;
	-moz-border-radius-topleft: 6px;
	-moz-border-radius-topright: 6px;
	-webkit-border-top-left-radius: 6px;
	-webkit-border-top-right-radius: 6px;
}
.forums.container h3			{font-size: 15px; margin: 0px;}
.forums.container .category 	{margin-bottom: 40px;}
.forums.container .forum 		{border-bottom: 1px solid #666;	padding: 10px;}
.forums.container .forum .description	{font-size: 14px;}
/* FORUM -----------------------------------------*/
.forum.container h2 {
	font-size: 16px;
	color: #000;
	padding: 5px 10px 5px 10px;
	margin: 0px;
	background-color: #BBB;
	-moz-border-radius-topleft: 6px;
	-moz-border-radius-topright: 6px;
	-webkit-border-top-left-radius: 6px;
	-webkit-border-top-right-radius: 6px;
}
.forum.container h3			{font-size: 15px; margin: 0px 0px 5px 0px;}
.forum.container .thread	{border-bottom: 1px solid #666;	padding: 10px;}
/* SIGNUP FORM ------------------------------------*/
#signup_form	{margin: auto; width: 360px; font-size: 16px;}
#signup_form .heading {
	text-align: center;	font-size: 22px; font-weight: bold;	color: #B9AA81;
}
#signup_form form {
	background-color: #B9AA81;
	padding: 10px;
	-moz-border-radius: 8px;
	-webkit-border-radius: 8px;
}
#signup_form form label {font-weight: bold;	color: #11151E;}
#signup_form form input[type=text],input[type=password] {
	width: 316px;
	font-weight: bold;
	padding: 8px;
	border: 1px solid #FFF;
	-moz-border-radius: 4px;
	-webkit-border-radius: 4px;
}
#signup_form form input[type=submit] {
	display: block;
	margin: auto;
	width: 200px;
	font-size: 18px;
	background-color: #FFF;
	border: 1px solid #BBB;
}
#signup_form form input[type=submit]:hover {border-color: #000;}
#signup_form .error {font-size: 13px; color: #690C07; font-style: italic; }
/* USER CONTROL BOX -------------------------------*/
.user_controls {float: right; text-align: right;}

Forum Pages

Now, let’s use what we have, to build the Forum Pages. These pages will list all the Threads, in a given Forum.

Currently, on the Home page we have links like this:

We created these links, because we expect to have a Controller named ‘Forums’ that has a function named ‘Display’.

There is also an ID number at the end of the URL. That is passed to the Controller function as a parameter.

This Controller is supposed to load a page that lists all the Threads in that particular Forum.

Forums Controller

The skeleton structure first:

• Create: system/application/controllers/forums.php

<?php
class Forums extends Controller {

	public function display($id) {

	}

}

It looks like any other Controller. But this time we are expecting a parameter being passed to the display() method. The string that comes after /forums/display/ in the URL automatically gets passed as the first parameter to that method.

For example:

http://localhost/ci_doctrine/forums/display/1

Will call display(1) in the Forums Class. We are going to use that parameter as the Forum ID. Then we need to display the Forum page.

The Controller needs to obtain the following pieces of data, and pass them on to the View:

• The title of the Forum.
• Each Thread under the Forum.
• For each Thread, we need Thread title, create date, author name and number of replies.

Please note that the create date of the Thread is not part of the Thread Model (or table), because of the way we designed the Models. The first Post inside of the Thread carries a create date already, so we did not want to duplicate that data again. Same thing goes for the author (or user_id) of the Thread.

Now let’s add some code to make this happen:

<?php
class Forums extends Controller {

	public function display($id) {

		$forum = Doctrine::getTable('Forum')->find($id);

		$vars['title'] = $forum['title'];
		$vars['threads'] = $forum->getThreadsArray();
		$vars['content_view'] = 'forum';
		$vars['container_css'] = 'forum';

		$this->load->view('template', $vars);

	}

}

First we get an instance of the Forum object with the id $id. Then we create the array of variables that will be passed to the template View.

At line 9 you can see that I am attempting to call a method named getThreadsArray(). This does not exist yet, so let’s build it.

The idea is to create a method that can return all associated Threads in an array structure. But we also want each Thread to have information such as: number of replies, date (of first post), author of (first post).

• Edit: system/application/models/forum.php

<?php
class Forum extends Doctrine_Record {

// ...

	public function getThreadsArray() {

		$threads = Doctrine_Query::create()
			->select('t.title')
			->addSelect('p.id, (COUNT(p.id) - 1) as num_replies')
			->addSelect('MIN(p.id) as first_post_id')
			->from('Thread t, t.Posts p')
			->where('t.forum_id = ?', $this->id)
			->groupBy('t.id')
			->setHydrationMode(Doctrine::HYDRATE_ARRAY)
			->execute();

		foreach ($threads as &$thread) {

			$post = Doctrine_Query::create()
				->select('p.created_at, u.username')
				->from('Post p, p.User u')
				->where('p.id = ?', $thread['Posts'][0]['first_post_id'])
				->setHydrationMode(Doctrine::HYDRATE_ARRAY)
				->fetchOne();

			$thread['num_replies'] = $thread['Posts'][0]['num_replies'];
			$thread['created_at'] = $post['created_at'];
			$thread['username'] = $post['User']['username'];
			$thread['user_id'] = $post['User']['id'];
			unset($thread['Posts']);

		}

		return $threads;

	}

In the first DQL query:

Look at the from() call first. We are joining 2 Models (Thread, Post) because we need information from both.

Now look at the select() and addSelect() calls to see what we are getting:
- We select the title for every Thread.
- Then we select the number of Posts under each Thread by using COUNT(p.id), and subtract 1 to get number of replies.
- Finally we are also getting the ID for the first Post in that Thread using MIN(p.id). That will be used later in the second DQL query.

where(’t.forum_id = ?’, $this->id) indicates which Forum id we are looking up.

groupBy(’t.id’) groups the results by the Thread id, so the Post count works properly.

Then, we loop through the Threads to get some more data into each Thread:

In the first query we fetched the ID of the first post on each Thread and stored it as first_post_id. So this DQL query now gets data for that Post and also the username of its author by joining to the related User Model.

The rest of the code, I just arranged all the values in $thread so it has a nice structure when returned.

Just to give you a better picture, if you were to print_r $threads , it would look this this:

Array
(
    [0] => Array
        (
            [id] => 1
            [title] => Hi there!
            [num_replies] => 1
            [first_post_id] => 1
            [created_at] => 2009-12-13 13:00:09
            [username] => TestUser
            [user_id] => 2
        )

    [1] => Array
        (
            [id] => 2
            [title] => Greetings to all
            [num_replies] => 0
            [first_post_id] => 3
            [created_at] => 2009-12-13 13:00:09
            [username] => Foobar
            [user_id] => 3
        )

)

I did unset($thread['Posts']) in the code just to keep the resulting array cleaner.

Note that we used HYDRATE_ARRAY, like we talked about earlier. This allowed us to do 2 things that we couldn’t have done with Doctrine_Collection objects:
- When we foreach() the Threads, we are able to get each element by reference (&$thread), and modify them.
- We are able to create new array elements and assign values to them, like this:
$thread['username'] = $post['User']['username'];.

Now, we need to create the View that will be for the content section of this page.

Forum View

In the Controller we passed this: $vars['content_view'] = ‘forum’;
So the Template will look for a View named ‘forum’.

• Create: system/application/views/forum.php

<h2><?php echo $title ?></h2>

<?php foreach($threads as $thread): ?>
<div class="thread">

	<h3>
		<?php echo anchor('threads/display/'.$thread['id'], $thread['title']) ?>
		(<?php echo $thread['num_replies']; ?> replies)
	</h3>

	<div>
		Author: <em><?php echo anchor('profile/display/'.$thread['user_id'],
								$thread['username']); ?></em>,
		Posted: <em><?php echo $thread['created_at']; ?></em>
	</div>

</div>
<?php endforeach; ?>

There is nothing new here. Just putting the data into HTML.

The Result

• Go to the Home Page and click on the first Forum.

You should see this:

Seems to be working!

Stay Tuned

We built another page in our project and explored two more important subjects along the way. Stay tuned for the next article, there is still plenty of work to do

Thank you for reading.

My third article for Nettuts was just published:

“Code readability is a universal subject in the world of computer programming. It’s one of the first things we learn as developers. Readable and maintainable code is something to be proud of in a finished product. We can share it with others, contribute to other projects, and reuse code from applications we wrote months or even years ago.

This article will detail the fifteen most important best practices when writing readable code.”

Read More: http://net.tutsplus.com/tutorials/html-css-techniques/top-15-best-practices-for-writing-super-readable-code/

In this article we are going to look at 3 main subjects: Hooks (with CodeIgniter), Doctrine Profiling and DQL. And we are going to see how we can use these to optimize some of our database interactions.

Before we start going into Hooks, Profiling and DQL, let’s make a small addition to our Message Board home page.

User Controls

In Message Boards, there is usually a section in the top right, with user specific info and links. I am not sure if there is a better name for it, but I’m going to call it “User Controls”.

On the CodeIgniter Forums it looks like this:

And if you are not logged in, it looks like this:

Now we are going add something similar, but simpler, to our Message Board.

Home View

• Edit: system/application/views/home.php

<!DOCTYPE html>
<html lang="en">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
	<title>Home</title>
	<link rel="stylesheet" href="<?php echo base_url(); ?>css/style.css"
		type="text/css" media="all">
</head>
<body>

<div class="forums">

	<div class="user_controls">
		<?php if ($user = Current_User::user()): ?>
			Hello, <em><?php echo $user->username; ?></em> <br/>
			<?php echo anchor('logout', 'Logout'); ?>
		<?php else: ?>
			<?php echo anchor('login','Login'); ?> |
			<?php echo anchor('signup', 'Register'); ?>
		<?php endif; ?>
	</div>

	<h1>CI+Doctrine Message Board</h1>

	<?php foreach($categories as $category): ?>

		<div class="category">

			<h2><?php echo $category->title; ?></h2>

			<?php foreach($category->Forums as $forum): ?>

				<div class="forum">

					<h3>
						<?php echo anchor('forums/'.$forum->id, $forum->title) ?>
						(<?php echo $forum->Threads->count(); ?> threads)
					</h3>

					<div class="description">
						<?php echo $forum->description; ?>
					</div>

				</div>

			<?php endforeach; ?>

		</div>

	<?php endforeach; ?>

</div>

</body>
</html>

We just added the highlighted lines.

Line 13: We are using the Current_User class, to see if the user is logged in. If they are logged in, we will get an instance of the User Model. If not, it will return false.

Lines 15-16: We display the username, and link to the Logout Controller.

Lines 18-19: This section is for people who are not logged in. We link to the Login Controller and Signup Controller.

So we pretty much have just utilized Models and Controllers we have built before.

We need to make a small addition to the css.

• Edit: css/style.css
• Add this to the end of the file.

/* USER CONTROL BOX -------------------------------*/
.user_controls {
	float: right;
	text-align: right;
}

Testing

• Go to: http://localhost/ci_doctrine/

The links are showing up at the top right corner.

• Click Login.
• Login as Testuser:mypass .

Now we can see the username and the Logout link.

We will add more features to this later in other tutorials.

Profiling with Doctrine

Using Doctrine, our code is cleaner, smaller and easier to read and maintain. But it is sometimes a good idea to look at a few things that are going on behind the scenes.

For example, Doctrine executes several SQL queries for us, while we use our Models. It would be nice to know what these queries are, so we can see if there is more room for optimization. This can be accomplished by Profiling.

For this, we are going to be using a Doctrine component called Doctrine_Connection_Profiler.

Profiling the Home Page

Let’s look at what queries are executed on the home page. We are going to make a quick and dirty test for now. Later in the article we’ll make a more re-usable version.

There is going to be lots of new code here, but I will explain them line by line:

• Edit: system/application/controllers/test.php

<?php
class Test extends Controller {

//...

	function home_profiler() {

		// set up the profiler
		$profiler = new Doctrine_Connection_Profiler();
		foreach (Doctrine_Manager::getInstance()->getConnections() as $conn) {
			$conn->setListener($profiler);
		}

		// copied from home controller
		$vars['categories'] = Doctrine::getTable('Category')->findAll();

		$this->load->view('home', $vars);

		// analyze the profiler data
		$time = 0;
		$events = array();
		foreach ($profiler as $event) {
		    $time += $event->getElapsedSecs();
			if ($event->getName() == 'query' || $event->getName() == 'execute') {
				$event_details = array(
					"type" => $event->getName(),
					"query" => $event->getQuery(),
					"time" => sprintf("%f", $event->getElapsedSecs())
				);
				if (count($event->getParams())) {
					$event_details["params"] = $event->getParams();
				}
				$events []= $event_details;
			}
		}
		print_r($events);
		echo "\nTotal Doctrine time: " . $time  . "\n";
		echo "Peak Memory: " . memory_get_peak_usage() . "\n";
	}

}

Lines 15-17 are just copied from the home Controller. Before that, we set up the Profiler, and after that we look at the data gathered by the Profiler.

Lines 9-12: First we create a Profiler object. Then we attach it to every Doctrine connection. In our case, it will be just one connection. Now it’s ready to listen and record all Doctrine events.

Line 22: The Profiler object let’s us loop through it and get each Event object. Events can be executed queries and also other things like fetching row data etc.. But we are only going to look at query events.

Lines 20,23 and 37: Each Event has information on how much time it took. So we will add them up in $time variable and display it at the end.

Line 24: We are only interested in SQL queries. These can be types ‘query’ or ‘execute’. If a query has assigned parameters (for example ‘WHERE id = ?’), then it is of type ‘execute’, otherwise it is ‘query’.

Lines 25-32: We create an array ($event_details) with information about the query. The type, the query SQL, and the time it took to run. Also if there were any parameters, we add that too.

Lines 33 and 36: We add it all into the $events array, and just dump it at the end with print_r().

Line 38: We use the memory_get_peak_usage() PHP function to find out what the maximum memory usage was during the script execution.

• Go to: http://localhost/ci_doctrine/test/home_profiler
• View Source:

Array
(
    [0] => Array
        (
            [type] => query
            [query] => SELECT c.id AS c__id, c.title AS c__title FROM category c
            [time] => 0.000220
        )

    [1] => Array
        (
            [type] => execute
            [query] => SELECT u.id AS u__id, u.username AS u__username, u.password AS u__password, u.email AS u__email, u.created_at AS u__created_at, u.updated_at AS u__updated_at FROM user u WHERE u.id = ? LIMIT 1
            [time] => 0.000310
            [params] => Array
                (
                    [0] => 2
                )

        )

    [2] => Array
        (
            [type] => execute
            [query] => SELECT f.id AS f__id, f.title AS f__title, f.description AS f__description, f.category_id AS f__category_id FROM forum f WHERE f.category_id IN (?)
            [time] => 0.000322
            [params] => Array
                (
                    [0] => 1
                )

        )

    [3] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000208
            [params] => Array
                (
                    [0] => 1
                )

        )

    [4] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000169
            [params] => Array
                (
                    [0] => 2
                )

        )

    [5] => Array
        (
            [type] => execute
            [query] => SELECT f.id AS f__id, f.title AS f__title, f.description AS f__description, f.category_id AS f__category_id FROM forum f WHERE f.category_id IN (?)
            [time] => 0.000322
            [params] => Array
                (
                    [0] => 2
                )

        )

    [6] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000238
            [params] => Array
                (
                    [0] => 3
                )

        )

    [7] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000171
            [params] => Array
                (
                    [0] => 4
                )

        )

    [8] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000160
            [params] => Array
                (
                    [0] => 5
                )

        )

)

Total Doctrine time: 0.013517618179321
Peak Memory: 5849232

As you can see, there were 9 queries executed by Doctrine. That seems too many. This happens because of the way Doctrine does ‘lazy loading’. It fetches data from the database as we access the objects and their parameters.

Later in the article, we are going to see how we can optimize this with DQL.

Let’s see how we can use the Profiler for the entire application, in a re-usable way. For that, we’re going to look at CodeIgniter Hooks.

CodeIgniter Hooks

With Hooks, we can tell CodeIgniter to perform an operation, at a given point in the application flow.

For example, we are going to initialize the Profiler, before any Controller function is called.

Enabling Hooks

Hooks are disabled by default, so we must first enable them.

• Edit: system/application/config/config.php

// ...

/*
|--------------------------------------------------------------------------
| Enable/Disable System Hooks
|--------------------------------------------------------------------------
|
| If you would like to use the "hooks" feature you must enable it by
| setting this variable to TRUE (boolean).  See the user guide for details.
|
*/
$config['enable_hooks'] = TRUE;

// ...

Just changed it from FALSE to TRUE. Now we can use Hooks.

Creating a Profiler Hook

Let’s create the functions that will be called for this hook.

• Create: system/application/hooks/doctrine_profiler_hooks.php

<?php
class Doctrine_Profiler_Hooks {

	public static $profiler;

	public function profiler_start() {

		self::$profiler = new Doctrine_Connection_Profiler();
		foreach (Doctrine_Manager::getInstance()->getConnections() as $conn) {
			$conn->setListener(self::$profiler);
		}

	}

	public function profiler_end() {

		// analyze the profiler data
		$time = 0;
		$events = array();
		foreach (self::$profiler as $event) {
		    $time += $event->getElapsedSecs();
			if ($event->getName() == 'query' || $event->getName() == 'execute') {
				$event_details = array(
					"type" => $event->getName(),
					"query" => $event->getQuery(),
					"time" => sprintf("%f", $event->getElapsedSecs())
				);
				if (count($event->getParams())) {
					$event_details["params"] = $event->getParams();
				}
				$events []= $event_details;
			}
		}

		$output = "<"."?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed'); ?".">\n\n";
		$output .= print_r($events,1);
		$output .= "\nTotal Doctrine time: " . $time  . "\n";
		$output .= "Peak Memory: " . memory_get_peak_usage() . "";

		file_put_contents(BASEPATH."/logs/doctrine_profiler.php", $output);
	}
}

The code is mostly the same as before. This time we are storing the $profiler as a static variable in this class. Also, we are writing the output to a log file instead of dumping it into the browser.

Adding the Hooks

Now we need to add the hooks to the config so they get executed.

• Edit: system/application/config/hooks.php

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/*
| -------------------------------------------------------------------------
| Hooks
| -------------------------------------------------------------------------
| This file lets you define "hooks" to extend CI without hacking the core
| files.  Please see the user guide for info:
|
|	http://codeigniter.com/user_guide/general/hooks.html
|
*/

$hook['post_controller_constructor'][] = array(
	'class'    => 'Doctrine_Profiler_Hooks',
	'function' => 'profiler_start',
	'filename' => 'doctrine_profiler_hooks.php',
	'filepath' => 'hooks',
	);

$hook['post_controller'][] = array(
	'class'    => 'Doctrine_Profiler_Hooks',
	'function' => 'profiler_end',
	'filename' => 'doctrine_profiler_hooks.php',
	'filepath' => 'hooks',
	);

/* End of file hooks.php */
/* Location: ./system/application/config/hooks.php */

So we store the hooks inside the $hook array. The index of the array is name of the hook. In this case we used the ‘post_controller_constructor’ and ‘post_controller’ Hooks.

The array structure should be easy to understand. We point to the class name, function, file path, file name, so that CodeIgniter can find our code for execution.

(Note: I would have preferred to use the “pre_controller” Hook, because it happens before even the Controller constructor is called. However, the plug-ins are not initialized at that point, including Doctrine, so we need to use “post_controller_constructor” instead.)

Testing

• Go to: http://localhost/ci_doctrine/

You should just see the homepage with no errors. If you are getting file access errors, make sure the system/logs folder is writable.

• Open: system/logs/doctrine_profiler.php

You should see something like this:

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed'); ?>

Array
(
    [0] => Array
        (
            [type] => query
            [query] => SELECT c.id AS c__id, c.title AS c__title FROM category c
            [time] => 0.000220
        )

    [1] => Array
        (
            [type] => execute
            [query] => SELECT u.id AS u__id, u.username AS u__username, u.password AS u__password, u.email AS u__email, u.created_at AS u__created_at, u.updated_at AS u__updated_at FROM user u WHERE u.id = ? LIMIT 1
            [time] => 0.000310
            [params] => Array
                (
                    [0] => 2
                )

        )

    [2] => Array
        (
            [type] => execute
            [query] => SELECT f.id AS f__id, f.title AS f__title, f.description AS f__description, f.category_id AS f__category_id FROM forum f WHERE f.category_id IN (?)
            [time] => 0.000322
            [params] => Array
                (
                    [0] => 1
                )

        )

    [3] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000208
            [params] => Array
                (
                    [0] => 1
                )

        )

    [4] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000169
            [params] => Array
                (
                    [0] => 2
                )

        )

    [5] => Array
        (
            [type] => execute
            [query] => SELECT f.id AS f__id, f.title AS f__title, f.description AS f__description, f.category_id AS f__category_id FROM forum f WHERE f.category_id IN (?)
            [time] => 0.000322
            [params] => Array
                (
                    [0] => 2
                )

        )

    [6] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000238
            [params] => Array
                (
                    [0] => 3
                )

        )

    [7] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000171
            [params] => Array
                (
                    [0] => 4
                )

        )

    [8] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000160
            [params] => Array
                (
                    [0] => 5
                )

        )

)

Total Doctrine time: 0.013517618179321
Peak Memory: 5849232

Let’s see how we can optimize this and reduce the number of queries executed.

Optimizing with DQL

In the Day 5 – CRUD article, we had a first look at DQL. Now we are going to use it in our application.

If you are familiar with the CodeIgniter Active Records class, there are some similarities. However DQL is much more powerful.

Reducing the Number of Queries

• Edit: system/application/controllers/home.php

<?php
class Home extends Controller {

	public function index() {

		// $vars['categories'] = Doctrine::getTable('Category')->findAll();
		$vars['categories'] = Doctrine_Query::create()
			->select('c.title, f.title, f.description')
			->from('Category c, c.Forums f')
			->execute();

		$this->load->view('home', $vars);
	}	

}

We just commented out the findAll() line, and added a DQL call instead.

In the select() call we specify which fields we want to fetch.

In the from() call, we put the Models and use short aliases for them (c and f). Note that, we used c.Forums instead c.Forum, even though the Model was named Forum. This is because we are joining two models here, and in the Category Model definition, we referred to the Forum model as Forums:

<?php

// models/category.php

class Category extends Doctrine_Record {

	public function setTableDefinition() {
		$this->hasColumn('title', 'string', 255);
	}

	public function setUp() {
		$this->hasMany('Forum as Forums', array(
			'local' => 'id',
			'foreign' => 'category_id'
		));
	}
}

It was just more suitable to name in plural since it’s a one-to-many relationship.

Another thing to note is that we did not specify an ON clause in DQL. Normally when you join tables in SQL, you would specify the columns you are joining on. But since our model definitions know how these relationships are set up, ON clause is not necessary in this case.

Finally, we run execute() to fetch the Doctrine_Collection object.

Let’s load the home page again, and then look at the profiler output.

• First go to: http://localhost/ci_doctrine/
• Then open: system/logs/doctrine_profiler.php

You should see something like this:

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed'); ?>

Array
(
    [0] => Array
        (
            [type] => query
            [query] => SELECT c.id AS c__id, c.title AS c__title, f.id AS f__id, f.title AS f__title, f.description AS f__description FROM category c LEFT JOIN forum f ON c.id = f.category_id
            [time] => 0.000311
        )

    [1] => Array
        (
            [type] => execute
            [query] => SELECT u.id AS u__id, u.username AS u__username, u.password AS u__password, u.email AS u__email, u.created_at AS u__created_at, u.updated_at AS u__updated_at FROM user u WHERE u.id = ? LIMIT 1
            [time] => 0.000307
            [params] => Array
                (
                    [0] => 2
                )

        )

    [2] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000274
            [params] => Array
                (
                    [0] => 1
                )

        )

    [3] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000172
            [params] => Array
                (
                    [0] => 2
                )

        )

    [4] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000214
            [params] => Array
                (
                    [0] => 3
                )

        )

    [5] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000154
            [params] => Array
                (
                    [0] => 4
                )

        )

    [6] => Array
        (
            [type] => execute
            [query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
            [time] => 0.000311
            [params] => Array
                (
                    [0] => 5
                )

        )

)

Total Doctrine time: 0.012369155883789
Peak Memory: 5856440

Now we went down to 7 queries instead of 9. However, there is still room for improvement.

Getting Count

Currently, most of these queries are caused by the count() call in our Home View.

(<?php echo $forum->Threads->count(); ?> threads)

Now we are going to fetch the thread count with DQL so that these extra queries do not need to run.

• Edit: system/application/controllers/home.php

<?php
class Home extends Controller {

	public function index() {

		// $vars['categories'] = Doctrine::getTable('Category')->findAll();
		$vars['categories'] = Doctrine_Query::create()
			->select('c.title, f.title, f.description')
			->addSelect('t.id, COUNT(t.id) as num_threads')
			->from('Category c, c.Forums f')
			->leftJoin('f.Threads t')
			->groupBy('f.id')
			->execute();
		$this->load->view('home', $vars);
	}	

}

Highlighted lines are where the changes are.

Line 11: Now we are selecting from the Threads Model too, so we can count them. I could have added the ‘Threads’ model into the from() call in Line 10. But I wanted to demonstrate another way you can do joins in DQL. There is also innerjoin() if you need it.

Line 9: COUNT(t.id) will give us the number of threads. I could have added this into the select() in Line 8, but for demonstration purposes again, I used the addSelect() function.

Line 12: Since we want individual Forums returned, and also are counting the Threads per Forum, we are grouping the results by the Forum id field.

If you wanted to do the same thing in raw SQL, the query would look like this:

SELECT c.id, c.title, f.id, f.title, f.description, COUNT(t.id) AS num_threads FROM category c LEFT JOIN forum f ON c.id = f.category_id LEFT JOIN thread t ON f.id = t.forum_id GROUP BY f.id

One More Change

Now we need to do one more change. Since we are going to use the returned ‘num_threads’ value instead of calling $forum->Threads->count(), we need to change a line in the Home View.

• Edit: system/application/views/home.php around line #37
• Just change the highlighted line.

<!-- -->
<div class="forum">

	<h3>
		<?php echo anchor('forums/'.$forum->id, $forum->title) ?>
		(<?php echo $forum->Threads[0]->num_threads; ?> threads)
	</h3>

	<div class="description">
		<?php echo $forum->description; ?>
	</div>

</div>
<!-- -->

At the first glance, the structure might seem a bit odd. Why not ‘$forum->Threads->num_threads’? or even ‘$forum->num_threads’?

This has to do with the way the query is structured. We called COUNT() on the Threads.id field. Therefore the returned data belongs to the Threads relationship, and not to the $forum object directly.

Also, the relationship is one-to-many. Therefore $forum->Threads is a Doctrine_Collection by default, instead of a Doctrine_Record. So we treat it like an array, and add the [0] index first before we can get the data in num_threads.

The result is: $forum->Threads[0]->num_threads

If you are ever unsure about the structure of the returned Doctrine_Collection object, you can convert it to an array using toArray(true) and dump it on the screen. Passing ‘true’ makes it ‘deep’, otherwise you only get the outermost object.

For example, if you do this:

$vars['categories'] = Doctrine_Query::create()
	->select('c.title, f.title, f.description')
	->addSelect('COUNT(t.id) as num_threads')
	->from('Category c, c.Forums f')
	->leftJoin('f.Threads t')
	->groupBy('f.id')
	->execute();
print_r($vars['categories']->toArray(true));

You can get an output like this:

Array
(
    [0] => Array
        (
            [id] => 1
            [title] => The CodeIgniter Lounge
            [Forums] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [title] => Introduce Yourself!
                            [description] => Use this forum to introduce yourself to the CodeIgniter community, or to announce your new CI powered site.

                            [category_id] => 1
                            [Category] =>
                            [Threads] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] =>
                                            [title] =>
                                            [forum_id] => 1
                                            [Forum] =>
                                            [num_threads] => 2
                                        )

                                )

                        )

                    [1] => Array
                        (
                            [id] => 2
                            [title] => The Lounge
                            [description] => CodeIgniter's social forum where you can discuss anything not related to development. No topics off limits... but be civil.

                            [category_id] => 1
                            [Category] =>
                            [Threads] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] =>
                                            [title] =>
                                            [forum_id] => 2
                                            [Forum] =>
                                            [num_threads] => 0
                                        )

                                )

                        )

                )

            [num_threads] => 2
        )

    [1] => Array
        (
            [id] => 2
            [title] => CodeIgniter Development Forums
            [Forums] => Array
                (
                    [0] => Array
                        (
                            [id] => 3
                            [title] => CodeIgniter Discussion
                            [description] => This forum is for general topics related to CodeIgniter.
                            [category_id] => 2
                            [Category] =>
                            [Threads] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] =>
                                            [title] =>
                                            [forum_id] => 3
                                            [Forum] =>
                                            [num_threads] => 0
                                        )

                                )

                        )

                    [1] => Array
                        (
                            [id] => 4
                            [title] => Code and Application Development
                            [description] => Use the forum to discuss anything related to programming and code development.

                            [category_id] => 2
                            [Category] =>
                            [Threads] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] =>
                                            [title] =>
                                            [forum_id] => 4
                                            [Forum] =>
                                            [num_threads] => 0
                                        )

                                )

                        )

                    [2] => Array
                        (
                            [id] => 5
                            [title] => Ignited Code
                            [description] => Use this forum to post plugins, libraries, or other code contributions, or to ask questions about any of them.

                            [category_id] => 2
                            [Category] =>
                            [Threads] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] =>
                                            [title] =>
                                            [forum_id] => 5
                                            [Forum] =>
                                            [num_threads] => 0
                                        )

                                )

                        )

                )

            [num_threads] => 0
        )

)

Profile Again

Let’s look at the profiling results again to see how the new DQL performed.

• First go to: http://localhost/ci_doctrine/
• Then open: system/logs/doctrine_profiler.php

You should see something like this:

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed'); ?>

Array
(
    [0] => Array
        (
            [type] => query
            [query] => SELECT c.id AS c__id, c.title AS c__title, f.id AS f__id, f.title AS f__title, f.description AS f__description, COUNT(t.id) AS t__0 FROM category c LEFT JOIN forum f ON c.id = f.category_id LEFT JOIN thread t ON f.id = t.forum_id GROUP BY f.id
            [time] => 0.000493
        )

    [1] => Array
        (
            [type] => execute
            [query] => SELECT u.id AS u__id, u.username AS u__username, u.password AS u__password, u.email AS u__email, u.created_at AS u__created_at, u.updated_at AS u__updated_at FROM user u WHERE u.id = ? LIMIT 1
            [time] => 0.000354
            [params] => Array
                (
                    [0] => 2
                )

        )

)

Total Doctrine time: 0.011896848678589
Peak Memory: 6010440

Nice, we are all the way down to 2 queries!

The first one is for fetching all Categories, their Forums, and Thread counts all at once. And the second query was done by the Current_User class for getting user details. You can’t go much lower than this.

Conclusions

I admit that our code became a little more complicated when we switched from using findAll() to DQL. But if your web application is expecting a significant amount of traffic, you might need to utilize such optimizations. Also, with DQL you can achieve more complicated queries that would not be practical with the magic functions like findAll() or findBy*().

Optimization is a debatable subject. Some prefer to do it early, in small steps. Some prefer to do it all the way at the end of development. But as you gain experience, your initial code tends to be more optimized in the first place, and may not need many changes later on.

I guess the point is, using an ORM like Doctrine does not magically optimize your database interactions. You still need to put an effort into it.

Stay Tuned

Thank you for reading. Learning more about DQL was an important step, as it is an integral part of Doctrine. Also, Hooks and Profiling gave us an opportunity to look at what is going on behind the scenes.

And thank you for all the encouraging comments. It makes writing these articles even more enjoyable.

My second article for Nettuts was just published:

http://net.tutsplus.com/tutorials/other/http-headers-for-dummies/

Whether you’re a programmer or not, you have seen it everywhere on the web. At this moment your browsers address bar shows something that starts with “http://”. Even your first Hello World script sent HTTP headers without you realizing it. In this article we are going to learn about the basics of HTTP headers and how we can use them in our web applications.

Read More

Nettuts+ just published my article!

http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

Database operations often tend to be the main bottleneck for most web applications today. It’s not only the DBA’s (database administrators) that have to worry about these performance issues. We as programmers need to do our part by structuring tables properly, writing optimized queries and better code. Here are some MySQL optimization techniques for programmers.

Read more