Page 1 of 1
Identify first/last row in database?
Posted: Mon Jul 25, 2005 2:46 pm
by charp
First a quick description of what I'm trying to do: User clicks on a link to see one row from a database. User then may click on links for the 'previous' or 'next' entry in the database. I'm using this code for the queries:
Code: Select all
$result=mysql_query("SELECT * FROM $table WHERE date>=$post_date ORDER BY date ASC LIMIT 1");
$result=mysql_query("SELECT * FROM $table WHERE date<=$post_date ORDER BY date DESC LIMIT 1");
So far, I have it all working just as I wish except for one detail: how do I identify when either the first or last entry from the database is being viewed so that I can turn off the 'previous' or 'next' links?
Thanks in advance.
Posted: Mon Jul 25, 2005 3:29 pm
by timvw
Well, if you perform the query, and there isn't a row returned.. So if you COUNT(*) == 0 then there isn't a next/previous...
Posted: Mon Jul 25, 2005 3:56 pm
by charp
I may be missing your point here, but doesn't that only work after the user clicks on the 'previous' or 'next' link? I'd like to know when the last row in the database has been queried so that I can remove the appropriate link and prevent the user from clicking a link that won't return any results.
Thanks.
Posted: Mon Jul 25, 2005 4:04 pm
by nielsene
Well I think you could do something like:
1. Get the number of rows in the DB
2. Keep track of the current "row index" (this won't be the primary key in most cases)
3. if row_index==0, first roww, don't show preb
if row_index==num_rows, last row, don't show next.
Posted: Mon Jul 25, 2005 4:11 pm
by timvw
You would need 3 queries in each page..
one to retrieve the data
one to check if there is a previous..
one to check if there is a next..
Probably you should reconsider your method of pagination, and start using LIMIT offset, count...
Posted: Mon Jul 25, 2005 4:18 pm
by charp
nielsene, keep in mind that the newbie in me may not have the lingo down properly, but here goes...
I tried to use the prev() and next() functions for arrays, but I could never get it to work. My problem there is probably that I don't fully understand how to deal with the query as an array.
I also though about basing it on the unique ID, auto-incrementing value in each row. I didn't take this too far since the order of the entries has to be by date and this particular database allows for post dated entries.
After a bit more browsing for a solution, I'm wondering if I can somehow use the max() and min() functions to look for the earliest and most recent dates (all unix timestamps) and use these values to test if an entry is the first or last. Any thoughts?
Posted: Mon Jul 25, 2005 4:24 pm
by nielsene
Sure you could do a "SELECT MAX(postdate), MIN(postdate) FROM table;";
Then compare if the current entry's date matches one of those. (You'd still need two queries per page, but its slightly better than the 3....
Posted: Mon Jul 25, 2005 4:37 pm
by timvw
Imho the OP better chooses "regular" pagination.
1-) determine the number of rows
save it in a session var for better performance
2-) calculate last page: ceil(number of rows / rows per page)
3-) calculate offset: (page - 1) * rows per page
4-) test if there is a previous page: page > 1 ? true : false
5-) test if there is a next page: page < last page ? true : false
Posted: Mon Jul 25, 2005 5:18 pm
by charp
Hey guys, I got it working. Thanks for your help!
I went with the max and min method since it seemed easier for me to implement. But something nielsene said has me wondering about something else.
Is it possible to combine these two queries into a single query?
Code: Select all
$result=mysql_query("SELECT * FROM $table WHERE date>=$post_date ORDER BY date ASC LIMIT 1");
$check=mysql_query("SELECT MAX(date) FROM $table");
For the first query, I would use the following to extract values, row by row:
Code: Select all
$row=mysql_fetch_array($result);
$post_id=$row["post_id"];
$date=$row["date"];
$title=$row["title"];
$entry=$row["entry"];
If combined, how would I extract the max value?