Welcome to WebmasterWorld Guest from 54.159.111.156

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

working out what is the current array

     
6:21 am on Nov 19, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?
12:32 pm on Nov 19, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
7:25 pm on Nov 19, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Awesome, I thought there must be some easier way, what would the sql query be?
11:24 pm on Nov 19, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



:-)

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.
3:20 am on Nov 20, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?
10:33 am on Nov 20, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month