Clever Geek Handbook

Mysql select statement with limit offset greater than actual records

I am creating a mysql statement that paginates the results with LIMIT x, n

(where x

is the offset and the n

records returned).

The offset is created using GET-Vars on the form page=x

.

Google now has some weird entries in their index that come from old workarounds where the page variable exceeds the actual number of entries in the result set.

So a query created with a page variable results in something like LIMIT 1000, 30

- but the query will only return tags 900

(since the contents of the table have changed in the meantime. This returns an empty result set of course.

Is there a way to tell mysql what if the offset is greater than the records returned to just show the last possible result? I don't want to do an extra query using COUNT()

in the first place, as this would double the load on the mysql server (I am currently using SQL_CALC_FOUND_ROWS

to determine the total number of records that a query will return without LIMIT

-Statement.

+3


source to share


1 answer


Get all requested rows with at least one row (MySQL 5.6):

SELECT *
FROM `table`
LIMIT 1000, 30
UNION ALL (
    SELECT *
    FROM `table`
    WHERE FOUND_ROWS() = 0
    ORDER BY `id` DESC
    LIMIT 1
)


SQL Fiddle

+2


source






More articles:


All Articles