Clever Geek Handbook

Select a row based on rank and previously selected row.

I'm trying to make a forum rank system that can get one post at a time based on a previous post.

Example table:

| POST_ID | POST_REPLIES |
--------------------------
| 1 | 5 |
| 2 | 2 |
| 3 | 8 |
| 4 | 8 |
| 5 | 12 |
--------------------------

If I make a simple request ORDER BY POST_REPLIES DESC

, I get POST_ID 5, 4, 3, 1, 2

.

But what I want to do is only get the next line (so one line at a time) and based on the message it is currently in.

For example: if I am currently viewing post # 3, a button will appear that says "next entry with most replies", which will point to post # 4.

I am currently having duplicate issues as I am running into a loop between 3 and 4 (3 points to 4 and 4 points to 3, not 5).

I have been playing around by joining a table and comparing the strings to see which one is greater or less, but since I am using the limit of 1, the string is always 1 and therefore useless. So the main query is:

SELECT * FROM posts
   WHERE post_id != '$currentPost' 
   ORDER BY POST_REPLIES DESC, POST_ID DESC LIMIT 1

How can i do this?

+3


source to share


3 answers


The first step you will need is to "rank" the results. The best way I've found to do this in MySQL is with a variable like this:

SELECT posts.post_id, posts.post_replies, @rank := @rank + 1 AS rank
FROM posts, (SELECT @rank := 0) r


Then you probably have to nest this query in another to accomplish what you need. Let me know if this points you in the right direction.

+1


source 


SELECT p.*
FROM (
  SELECT POST_ID, POST_REPLIES
  FROM posts
  WHERE POST_ID = @currentId) as cur
JOIN posts p 
  ON p.POST_REPLIES >= cur.POST_REPLIES
  AND p.POST_ID > cur.POST_ID
ORDER BY p.POST_REPLIES DESC, p.POST_ID
LIMIT 1 ;


+1


source


Limit using a range like

 limit 0,1

where zero is the starting record and one is the number of records to retrieve.

 limit 5,1

you get the sixth album and so on. You can track the page number through post, get, or session and use it to manipulate the request that way.

It is also common to fetch and save all records and then present them on the page, however this can be problematic if you expect to generate a large number of records.

0


source






More articles:


All Articles