working out what is the current array

[whatson]

So what I am trying to do is have a link at the bottom of my pages saying previous and next article.

The only way I can think of how to do this, is to run a while loop of all the articles, and when it comes to the current article record the number it is in the array, e.g. $row[7], and then link previous to $row[6] and next to $row[8].

However, the issue I have here is I can't work out how to record 7 in some way.

Perhaps my logic is not right here, anyone else have any ideas on how to accomplish this?

[swa66]

I suppose your article come out of a database somewhere. Correct ?

Next and previous imply that you have a scheme that defines an order of things.
Do you have more than one such a scheme ?
Can you define the order easily ? Can you explain what you want at the ends (the fist and last) ?

Data structures such as arrays are pretty easy to find next and previous in, but other means exist as well: you can build a linked list (each entry contains a pointer to the next item).
That way it can be "walked" in one direction.
To walk it in both directions, a double linked list can be built, but take care: maintaining the links quickly becomes difficult as you have more of them to maintain.

If you store it in SQL, you can actually query the database to give you the next and previous one in the given order (as long as you can define the order) - no programming needed: SQL does the heavy lifting for you. It's based on joining the table with itself, where you impose that the returned rows comply to be prior or later, ordering the results in the order you define, you then limit it to return only one row t get teh one row that's closest and satisfies the direction you seek.

[whatson]

Awesome, I thought there must be some easier way, what would the sql query be?

[swa66]

:-)

Note: I'm by far not a SQL specialist and I'm pretty sure this is suboptimal in the eyes of somebody who does know what they are doing in SQL.

That said:

a test table:

mysql> describe widgets;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| widgetid | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| color    | varchar(100)     | YES  |     | NULL    |                |
| rank     | int(10)          | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Some test data:

mysql> select widgetid, color, rank from widgets;
+----------+--------+------+
| widgetid | color  | rank |
+----------+--------+------+
|        1 | Red    |   88 |
|        2 | Green  | 2506 |
|        3 | Blue   |   57 |
|        4 | Pink   | 4711 |
|        5 | Yellow |    1 |
|        6 | Black  | 7799 |
|        7 | cyan   |   42 |
+----------+--------+------+
7 rows in set (0.00 sec)

To get them sorted in order of rank (whatever that means):

mysql> select widgetid, color, rank from widgets order by rank;

[output not show - trivial]


So let's say we've somehow ended up on a given record and now want to get the widgetid from the previous and next rows without querying the entire table (which might be quite substantial in size)

To find the id of the next one:

mysql> select widgetid from widgets where rank > 88 order by rank limit 1;

To get the previous one:

mysql> select widgetid from widgets where rank < 88 order by rank desc limit 1;

Well you can replace the 88 by any value
If you ask for the first or last one you get an empty result set back


Now if we want just to know the rank of the previous or the next:

mysql> select min(rank) from widgets where rank > 88; 

mysql> select max(rank) from widgets where rank < 88;

now let's play a bit:

select cur,widgetid,cur.color, cur.rank, max(prv.rank) as prv_rank , min(nxt.rank) as nxt_rank 
from widgets as cur 
left join widgets as prv 
   on cur.rank > prv.rank 
left join widgets as nxt 
   on cur.rank < nxt.rank 
group by cur.widgetid ;

There you go a table with the added previous and next ranks. But it's probably nicer to have the widgetid than the ranks ...

select ordering.widgetid, ordering.color, ordering.rank, prv.widgetid as prv_id, nxt.widgetid as nxt_id 
from (
  select cur.widgetid, cur.color, cur.rank, max(prv.rank) as prv_rank , min(nxt.rank) as nxt_rank 
  from widgets as cur 
  left join widgets as prv on cur.rank > prv.rank 
  left join widgets as nxt on cur.rank < nxt.rank 
  group by cur.widgetid ) as ordering 
left join widgets as prv on prv.rank = ordering.prv_rank 
left join widgets as nxt on nxt.rank=ordering.nxt_rank;

I'm pretty sure this last one isn;t all that efficient though - but I'm hoping to see others tell how to do better.

[whatson]

Thanks, I got it working. Not perfect, but I think good enough. I still couldnt work out how to order the results, and find the current page within that order. So I just used the ID, so its all sorted by order of entry, which isn't ideal, but is something.
Just cant work out how to sort them differently. Is there a mysql query that can find results after another one alphabetically?

[swa66]

Sure, I used "rank" as a field to define the order. If you chose any other field (text, integers etc: does not matter): use it instead. The order by can sort anything, numbers, dates, text, ... it even understands how text is encoded and does the right thing if you let mysql know of the encoding used and how you want it sorted.

I do think some of my examples rely on the rank being unique, test it well if you could have datasets that have the same value in the order.