Finding next / previous row in MySQL
My PHP page asks for a file table and users can click columns on the page to sort by title, date, size, status, and username. They can then click on each file to view it on a separate player page.
What I would like to do is create the previous / next buttons in the player to go to the previous or next file as indicated on the Files page . This means that the results can be ordered by any of the parameters listed above.
Something like a date, it's pretty simple:
SELECT * FROM Files WHERE date > curdate ORDER BY date LIMIT 1
However, some other parameters are causing me problems:
-
How do I handle strings like the name of the user being loaded?
-
How can I handle situations where the next item in the sorted column has the same value? For example, status is an int between 0 and 3 and most files will have status 0. If I sort by status on the Files page, it first lists all files with status 0, then status 1, etc. So if my current file is in the middle of 0 status files, how do I know what is next, which also has status 0?
(PS I know there are many threads in this thread, but I haven't seen anyone refer to the specific situations above.)
source to share
Q: How do I handle strings like upload username?
A: Likewise, you are dealing with dates and numbers. Strings are also "ordered".
Q: How can I handle situations where the next item in the sorted column has the same value?
A: Likewise, you will be dealing with duplicate values for dates that are not unique. In addition to the "major" sort column, you need another "minor" sort column that is unique, or the combination of "major" and "minor" is unique together.
Ideally, you should have a PRIMARY KEY or UNIQUE KEY for a zero-valued column that can serve as the "lowest" sort order.
the "trick" is to keep the current position in the list by storing the major and minor values in the "last time" string, and then use that information in the query to get the "next" page.
WHERE t.major >= :last_seen_major
AND (t.major > :last_seen_major OR t.minor > :last_seen_minor)
ORDER BY t.major ASC, t.minor ASC
LIMIT 1
From the last row (in this case just one), you want to store the values of the primary and lower columns so that they can be used in the same query to get the next row.
For best query performance, you will need an index with leading columns (major, minor)
.
As per your request, if you have a column id
, you would do something like this:
SELECT f.*
FROM Files f
WHERE f.date >= :last_seen_date
AND (f.date > :last_seen_date OR f.id > :last_seen_id)
ORDER BY f.date ASC, f.id ASC
LIMIT 1
To order a different column, replace f.date
in the WHERE and ORDER BY clauses with something else, eg. f.name
...
Less efficient alternative
Another really popular approach is to use "offset" in the LIMIT clause.
Blush at first, it seems like an elegant solution, but it has some problems.
You can do:
ORDER BY major ASC, minor ASC LIMIT 41,1
For the "next" line, you increment the offset by 1
ORDER BY major ASC, minor ASC LIMIT 42,1
One problem with this approach, if a row is inserted into the range of rows already seen, the "next" query will return the same row. Since the 41st line was now the 42nd line. If someone deletes a line, the "next" request will skip the line. And I don't want to live with such a defect in my "get next line" function. And this approach still has to keep track of the position in the list, but by carrying an extra offset that is not actually part of the string.
Another problem with this approach is that the database has to fetch rows, then order them, and then finally apply a LIMIT clause, which can be a performance issue with large sets.
source to share