CODEWITHSUNDEEP
phpmysqldatabasemysqli
Shane
Shane

Reputation: 2047

ensuring a minimum number of rows returned

I am making a simple message board in PHP with a MySQL database. I have limited messages to 20 a page with the 'LIMIT' operation.

An example of my URL is: http://www.example.com/?page=1

If page is not specified, it defaults to 1. As I mentioned earlier, there is a limit of 20 per page, however if that is out of a possible 30 and I wish to view page 2, I only end up with 10 results. In this case, the LIMIT part of my query resembles LIMIT 20,40 - How can I ensure in this case that 20 are returned?

I would prefer to try and keep this as much on the MySQL side as possible.

EDIT: To clarify, if I am on page 2, I will be fetching rows 20-30, however this is only 10 rows, so I wish to select 10-30 instead.

EDIT: I am currently using the following query:

My query:

SELECT MOD(COUNT(`ID`),20) AS lmt WHERE `threadID`=2;
SELECT * FROM `msg_messages` WHERE `threadID`=2 LIMIT 20-(20-lmt) , 40-(20-lmt) ;

There are 30 records this matches.

Upvotes: 1

Views: 139

Answers (2)

Simon
Simon

Reputation: 3730

I'm not sure to really understand the question, but if I do I think that the best practive would be to prevent users to go to a page with no results. To do so, you can easily check how many rows you have in total even if you are using the "LIMIT" clause using SQL_CALC_FOUND_ROWS.

For example you could do:

Select SQL_CALC_FOUND_ROWS * from blog_posts where balblabla...

Then you have to run another query like this:

Select FOUND_ROWS() as posts_count

It will return the total rows very quickly. With this result and knowing the current page, you can decide if you display next/prev links to the user.

Upvotes: 1

alex stacey
alex stacey

Reputation: 447

You could do a:

SELECT COUNT(*)/20 AS pages FROM tbl;

..to get the max number of pages, then work out if you're going to be left with a partial set and adjust your paging query accordingly.

Upvotes: 0

Related Questions