Php + Mysql Displaying Ranks Over Multiple Pages

[RyanWalsh3387]

How can I set the range of rows to display on an array?

 

For example, I have a query with 20 results.

 

I want page 1 to display rows 1-10 .

I want page 2 to display rows 11-20.

 

Orginially I set this up just using a LIMIT and OFFSET, however that does not work with my ranking field (it would set the first rank of the second page back to 1 rather than its proper rank, and since ties can happen I couldn't just use the value prior to it, as 7, 8, 9, and 10 could all really be rank 7, and if 11 has the same score as 7-10 it too should be rank 7, not 11)

 

 

this is what i have so far:

 

 

$max_results = 10;

$cur_page = 1

 

$query3 = "SET @rank = 0, @prev_rank = -1, @rownum = 0";

$result3=mysql_query($query3);

 

$query = "SELECT *,@rownum := @rownum + 1 AS row,

@rank := if (@prev_rank<>Score, @rownum,@rank) AS ranking,

@prev_rank:= Score AS score

FROM blah blah blah"

$result = mysql_query($query)

 

while ($row = mysql_fetch_assoc($result)) {

echo "".$row['ranking']."";

}

 

 

Thanks for the help!

-Ryan

 

edit--I believe there are two solutions to this which both I can not figure out---

 

1: Find a way to find query the rows prior to the starting row

2: Find a way to display a set range of rows from the query.

Edited October 18, 2012 by RyanWalsh3387

[Christian F.]

There is an existing thread on pagination that explains this quite nicely, and I recommend reading it.

[TOA]

There's also a very good tutorial located in the tutorials on this site.

[RyanWalsh3387]

Thanks for the replies. Pagination is actually set up properly for me. I think what I am looking for is a slightly advanced version of pagination. If anyone can think of a way to do this please help!

 

Thanks!

-Ryan

[ManiacDan]

Wrap an outer query around this:

 

SELECT * FROM (SELECT @whatever_up_there) Foo LIMIT 11,10

[Barand]

I think your problem is caused by

 

$query3 = "SET @rank = 0, @prev_rank = -1, @rownum = 0";

 

I haven't tried this approach yet but if you store your last values of score and rank from each page into session vars and then reset your mysql vars to those saved values in query 3 on the successive page then it may work

[ManiacDan]

Re-setting the variables with sessions is also good, but note that it will screw up your pages if someone has two tabs open to your site. Each new tab will steal and overwrite rankings from the other tabs unless you make unique session arrays for each table you're displaying.

[RyanWalsh3387]

Thanks All,

 

Maniac Dan's solution seemed to fit best for me.

 

Barand, I think that would work if successive was the method, but a user can click on any page they want. I know in my scenario there were only two pages, but the real use case there will be multiple pages that they can jump to. I didn't think of using sessions to store them, I will definitely use that knowledge in the future so thanks for that.

 

Thanks everyone-- I really appreciate the help.