Select Row numbers between 2 specific numbers- help please

[tomlam]

Hello Freaks!

 

I was wondering if anyone might be able to help me.

 

I have been searching and trying and trying to find a solution to this problem

 

I am designing a gallery which lets you navigate through pages of photos. (pretty standard)

I want to select a number of rows between a specific range to display just 12 photos at a time, but so that users can go onto the next page, and it show the next 12 entries.

 

I’ve tried this with the following script but it won’t let me enter “WHERE rank BETWEEN 1 and 12″ anywhere inside it. It keeps telling me that rank isn’t a column.

 

SCRIPT I USED:

$result_getentries = mysql_query("select @rownum:=@rownum+1 rank, p.* from `ringspun_entries` p, (SELECT @rownum:=0) r order by id desc;", $conn) or die (mysql_error());

 

(which was taken from : http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/#comment-233)

 

 

 

Any push in the right direction would be much appreciated.

 

Thanks

Tom

[tomlam]

To give you a bit more information, this script works fine and selects all of the rows in my database...

but i want to just select the first 12 rows, then the 2nd 12 rows on the next page and so on.

 

I can't currently do this with the `id` row because when rows are deleted it creates holes in the gallery where entries used to be.

 

I hope I'm giving you enough information.

 

Thanks

Tom

[fenway]

Wait, so now there is no error?

[tomlam]

Apologies, I'll try to clarify.

 

I'm using:

mysql_query("select @rownum:=@rownum+1 rank, p.* from `ringspun_entries` p, (SELECT @rownum:=0) r order by id desc;

 

Which creates a 'virtual' column in my db results in order to count the rows, assigning a row number to each row in the first column.

 

I wanted to change it to ... (for page 1)

 

mysql_query("select @rownum:=@rownum+1 rank, p.* from `ringspun_entries` p, (SELECT @rownum:=0) r order by id desc WHERE rank BETWEEN 1 and 12;

 

 

 

I wanted to change it to ... (page 2)

 

mysql_query("select @rownum:=@rownum+1 rank, p.* from `ringspun_entries` p, (SELECT @rownum:=0) r order by id desc WHERE rank BETWEEN 13 and 24;

 

 

NOTICE that the range of rows that I want to select moves up by 12 each time as i only want to display 12 images per page.

 

You can see it working here using a very round about PHP function that does not work entirely.

http://www DOT candid sky DOT 34sp DOT com/cli ents/ring spun/fancydre ss/gall ery DOT php

 

apologies for splitting up the domain, i dont want it to show in searches.

 

 

[tomlam]

To clarify further...

 

The problem that I'm having is that the SQL code doesnt accept the "BETWEEN" function. I imagine its because the rank column that my SQL script creates on the fly is never a part of the database. Its a part of a $results.

 

So, just wondering if someone could help me build in extra code to select only the rows between a range of numbers. - 12 rows per page

[fenway]

That's simply becuase the rank is an alias, which gets "renamed" after the WHERE clause... try HAVING.

[tomlam]

Thanks for that, very helpful!

 

I've read up on HAVING but am unsure of how to get it to select a range of rows.

 

HAVING COUNT(*) = 12;

 

HAVING COUNT(*) < 12;

 

Could you explain how you thought I should use the HAVING clause.

Thanks in advance for your help!

 

Tom

[fenway]

Whatever expressions are valid for WHERE are valid for HAVING -- so you can use BETWEEN just like before.

[Barand]

Why don't you just paginate using LIMIT?

 

First page

 

SELECT whatever FROM ringspun_entries LIMIT 0, 12;

 

Second page

 

SELECT whatever FROM ringspun_entries LIMIT 12, 12;

 

 

Nth page

 

SELECT whatever FROM ringspun_entries LIMIT (N-1)*12, 12;