PHP and Stuff

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!