How do I manage a page?
I mean, the most efficient way to get information about the number of your page elements and make a sql query with the LIMIT you need. or should I get all elements and then trim the array using php functions?
now i make 2 queries: first for counting all items and for getting items i need with LIMIT.
Okay, I'll be more specific. For example, I need to show a question on my page and 20 answers to this question. Below is the page control: links to the next, previous page, and so on. I want to show the correct number of links (number of responses / 20) and when I go to any link, I want to get the correct answers (e.g. 41 to 60 on the third page). So what's the best way to get the number of items (responses) to show the correct number of links and get the correct answers for each link?
source to share
I am assuming that you want to count the number of lines you will be reading to do pagination or the like? I don't understand your need for LIMIT in the context of your question. However, if you just need to count the number of rows, use one of the following values.
You select a count of all rows, for example:
select count(*) as counted, name, address
from contact
Or the lines found:
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)
source to share
I am assuming you are trying to say that you want to know how many elements / responses there are in the request, but only read up to 20 elements at a time for pagination.
First: you really need to look for the pagination package; many, many people have had the same problem, and there are probably both free / open source and proprietary solutions for your programming language and framework. (If you say which language you are using, I am sure someone can recommend a solution for you.)
Anyway, I know that I like to know how things work, so this is how it is usually done:
As far as I know, the pagination code calculates the pages by making a single request using select count(*) from tblX where something
, divide that number by the number of items per page, and use a ceiling (e.g. 4.1 => 5).
A new query is required to list the results on the page; don't worry about the count query being scary much faster than getting each result discarding the ones you don't need. DO NOT DO THIS (to make the recipe the top page on this page ). Something like select * from tblX where something limit Y offset Z
, where Y is the number of items on the page and Z is (requested_page - 1)*Y
; page 1 will have offset 0, page 2 will have offset 20 (if that's what Y), etc.
But don't try to implement it manually, it is unnecessary, tedious and error prone, it is much better to use your time to customize the finished solution.
source to share