How to perform page control?

Question

I mean what the most efficient way to get information about the quantity of your page's items and make sql query with LIMIT that you need. or I should get all items and then crop array with php functions?

now I do 2 queries: first to count all items and second to get items that I need with LIMIT.

OK, I'll be more concrete. For example I need to show a question on my page and 20 answers to this question. At the bottom there shold be page control: links to the next, prev page and so on. I want to show proper number of links (number of answers/20) and when I go to any link I want to recieve proper answers (for example 41 to 60 on the 3d page). So what's the best way to get number of items (answers) to show proper number of links and to get proper answers for each link?

Answer 1

I think you have to be a little more specific with your question. For example, what do you mean by "your page's items"? Are you referring to HTML FORM elements? What do your queries do? What is the goal? Please rephrase.

Answer 2

I'm assuming you want a count of the number of rows you'll be reading so as to do some pagination or similar? I don't understand your need for the LIMIT in the context of your question. However, if you just want a count of how many rows have been found, use one of the following.

You select the count of all rows such as:

select count(*) as counted, name, address
from contact

Or found rows:

SELECT SQL_CALC_FOUND_ROWS, name, address
from contact

This may be mysql specific I'm not sure.

Update:

For pagination you would do something like the following - (Psuedocode)

$rows = array($result)
$num_rows = sql_calc_found_rows
$per_page = 20
$pages = ceil($num_rows / $per_page)

page
$rows_this_page = array()
$rows_this_page = get_values($rows, (min index)$page_number * $per_page - $per_page, (max index)$page_number * $per_page - 1)

Answer 3

I guess your'e trying to say you want to know how many items/answers there is in the query but only read up to 20 items at at time, for pagination.

Firstly: You really should look for a pagination package; lots and lots of people have had the same problem before and there probably exists both free/opensource and proprietary solutions for your programming language and framework. (If you say what language you are using I'm sure someone can reccomend a solution for you.)

Anyway, I know I like to know how things work, so this is how it usually does:

As far as I know the pagination code calculates the pages by doing one query using select count(*) from tblX where something divide this number with the items-per-page number and use ceiling (e.g. 4.1 => 5).

For listing the results per page a new query is required; don't worry the count query is terribly much faster than getting every result discarding the ones you don't need DO NOT DO THAT (that's the recipie for becoming the top story on this page). Something like select * from tblX where something limit Y offset Z where Y is the number of items per page, and Z is the the (requested_page - 1)*Y; page 1 will have offset 0, page 2 have offset 20 (if thats what Y are) etc..

But do not try to implement this manually, it's unneccesary, tedious and error prone, much better to use your time customizing a readymade solution.