Page 1 of 1

Query Help

Posted: Thu Dec 07, 2006 8:38 am
by shiznatix
Ok so I have a upload script where you upload a picture and put in the date that you finished it (its an art site). I am now trying to order them by date then do a next and previous thing.

The problem is writing the query. I have 1 Id and I need to get that record and then the record with the next date and the record with the next previous date so I would have a total of 3 records returned (unless its the first or the last ID). Is that clear or should I try that again?

Any help would be fantastic.

Posted: Thu Dec 07, 2006 11:23 pm
by califdon
Not sure I totally understand what you are trying to do. What does the upload script have to do with this? Are you querying a table for a particular record and then want to also extract the next and previous record in date order? That could be ambiguous if there are multiple records for any one date, unless you have datetimes stored. As a start, you could ORDER BY datetime and retrieve the entire recordset (no WHERE clause) and then step through the results set with ADODB (.movefirst, .movenext, etc.). There might be a better way, especially if the table has a large number of records.

Posted: Fri Dec 08, 2006 8:28 am
by shiznatix
the upload script was just a quick background thing to help you understand the script. Its meaningless for anything else.

Your description is correct in what I want this query to do. Yes, I could just load the entire table into an array and step though it manually but I thought it would be better to do all of that processing in the SQL statement. I suppose I will just do that though until I can figure out a query.

Posted: Sat Dec 09, 2006 1:51 am
by volka
Please search the board (and/or the web) for the term pagination. There are plenty of threads and articles on that topic.

Posted: Sun Dec 10, 2006 11:23 am
by shiznatix
volka wrote:Please search the board (and/or the web) for the term pagination. There are plenty of threads and articles on that topic.
its not pagination that I want, its making the SQL statement say:

"order the table by the date found in column date_made descending then find the row with id $Id then step up the table 1 row to get the record that is just above it then step down two rows to get the record that is just below it and return all 3 of those rows."

The part I cant get is the 'step up one row' and 'step down 2 rows' to then return those 3 rows.

Posted: Sun Dec 10, 2006 12:04 pm
by John Cartwright
shiznatix wrote:
volka wrote:Please search the board (and/or the web) for the term pagination. There are plenty of threads and articles on that topic.
its not pagination that I want
you are mistaken, it is exactly what you want. More specifically, use the LIMIT keyword.