Sorting by date isn't working.. help?

[Inkybro]

Hello everyone,

I'm working on a website that uses a content management system of my creation. The articles posted must be ordered by date/time and must use pages (10 articles per page). I have one problem, my MySQL query is:

$query = mysql_query("select * from stories where id between $page_min and $page_max order by date desc, time desc");

Now, I know WHY it isn't "ordering right". Techincally, it IS ordering right, but it's only ordering the stories between $page_min and $page_max. I can't think of a solution, I need to order ALL the articles by date and time, and THEN run the BETWEEN statement. Does anyone have any ideas?

[rane500]

The easiest way method-wise would be to simply sort into an array, then use PHP to figure out which rows fell between $page_min and $page_max. Question is, why are you wanting that sort done first? I might be missing the point, but it seems to me that whether you sort first or not, the rows returned between $page_min and $page_max are still going to be the exact same rows and in the exact same order. The between is covering the numeric range of $page_min and $page_max, not rows that might be between those two numbers in a table.

[Inkybro]

Wow -- I must be mentally handicapped today. You're right. I'll just handle it with PHP.

Thanks for your help =)

[Roger_Ramjet]

If you are paginating the results 10 to a page then you want to be using LIMIT rather than a range of IDs. Then your query can sort on any column.

$per_page = 10;
$SQL = "SELECT select * from stories order by date desc, time desc LIMIT $page_min, $per_page";

That will sort them on the columns you want and then return just $per_page=10, starting at whatever offset you want ($page_min). So first page is 0,10: returns 1-10
next page is 10,10: returns 11-20
3rd page is 20,10: returns 21-30
etc

[rane500]

Now THAT is a clever idea. Thank you, I'll have to remember that method when I have to do some sort of pagination myself.