Blog system: show neighboring entries Jun05 '05

A typical blog stamps each entry with a date and a time.

Using these values, how can one find the previous entry, and the next entry – in chronological order, with efficiently minimal markup, and as few queries as possible?

Let’s say the current entry you are reading has a date of 2004-12-14, and a time of 09:36:12.

'LIMIT 3' approach

The 'LIMIT 3' approach works by pulling out only three entries: one for the previous; one for the current; and one for the next. Essentially, you can picture it like this:

1. Previous: ??
2. Current: 2004-12-14 09:36:12
3. Next: ??

As you can see, we always know the middle, or second, date and time. But how do we grab the first and third dates and times?

The first and third dates and times are completely unrelated to the date and time of the current entry. This means there is no date or time function (in SQL) that we could toss in there to automatically generate the "neighboring entries."

There is no correlation; no connection. As far as MySQL is concerned, they are completely random dates.

The "golden three"

MySQL allows you to specify how many rows you’d like returned, as mentioned above – with the LIMIT clause.

We want to limit our query result to three rows, which will call the "golden three," for the sake of this example.

Skip over

It makes sense to limit the query results to three rows – but how does MySQL know which two rows (other than the current, which we already know) to pull?

It seems LIMIT is not enough, for this case. We also need to tell MySQL how many entries we want to skip over, before we reach our "golden three."

You can include, in the LIMIT clause, another number – this one being the number of rows that exist (in chronological order) before the current entry date and time:

LIMIT 251, 3

The above example skips over the first 251 rows in the result set, and then once it passes those 251 rows – the very next three are returned.

This approach will work fine, and we will now be able to generate the "golden three."

First query, and an array

First, we must find out how many entries exist before the current entry (2004-12-14 09:36:12).

To do this, we write a query that grabs all the entries, in ascending order:

SELECT datetime FROM entries ORDER BY datetime ASC

This will present us with all of our entries, in chronological order.

But we are only after three of those entries, correct?

Using PHP, we can take each row, and insert the date and time stamp into an array:

$entries_array = array();

for ( $i=0; $i<$numrows; $i++ )
{
$row = mysql_fetch_array($result);

$entries_array[$i] = $row['datetime'];
}

This will give us an array containing the date and time stamps of every entry, in chronological order.

Now, all we have to do is find the index (or key) of the date and time of the current entry, and then subtract 1 from that number:

$currentEntry_key = array_search( '2004-12-14 09:36:12', $entries_array ) - 1;

This will give us a variable that contains the number, in chronological order, of the current entry’s position, in relation to all the entries in the database.

In other words, let’s say our current entry (2004-12-14 09:36:12) is in the 56th spot, in chronological order.

That means it’s the 56th entry (over time), that has been written.

Second query, with more details

Next, we write another query, which includes a few more details:

SELECT headline, entry, datetime FROM entries ORDER BY datetime ASC LIMIT $currentEntry_key, 3

Notice the variable, $currentEntry_key, in the LIMIT clause. The $currentEntry_key variable tells MySQL how many entries to skip over, before it arrives at the "golden three."

For our example, the full query being sent looks like this:

SELECT headline, entry, datetime FROM entries ORDER BY datetime ASC LIMIT 55, 3

Remember, we subtracted 1 from the index position of the current entry, which was 56.

If the current entry is 56, then that means the previous entry is 55, and the next entry is 57.

Again, the current entry is in the middle.

Here’s the rows that are returned, with the numbering starting at zero:

0. Entry 55
1. Entry 56 (current entry)
2. Entry 57

Cycle through, and print out

Now that we have our "golden three," all that’s left to do is cycle through the three rows, and output only rows 0 and 2. We don’t want to output row 1, since that is our current entry.

for ($i=0; $i<$numrows; $i++)
{

$row = mysql_fetch_array($result);

if ($i != 1)
{
echo $row['entry'];
}

}

Wrap–up

This is just one approach to gathering "neighboring entries," for a typical blog system. There may be easier ways to achieve the same result.

Categories: PHP , SQL , Tutorials , Web Development

Add Feedback (view all)