How to limit no of items to be displayed per page?

[jainritesh]

Dear all,
I am new to PHP and Mysql, and have created a table in Mysql to store product details and have written a form to upload product details (Title, image url, description, download link, and product rights) through admin section in PHP,
The problem is if i have entered 50 items and when i call db from members area all items are displayed on the same page, How can i restrict no of items to be displayed as 10, and automatically display url for 5 pages at bottom of page, i.e when i upload more products (say now the total is 70) no of pages will also increment automatically. (in this case will be 7 and say when user clicks on say 5 the product will be displayed from 41 to 50)

Please Guide.

Thanxs in advance,

Best Regards,
Ritesh Jain

[ronverdonk]

There are numerous code sets and tutorials about MySQL results pagination on the web. Google for 'mysql results pagination' and you can pick the one you prefer.

Ronald

[coolsti]

For the real answer as the moderator said, you should look for examples on the net, but maybe a few words here could help get you in the right direction.

The way I do this is via a combination of PHP scripting and making use of the capabilities of MySQL. The trick is to push as much of the task of this kind of bookkeeping to MySQL.

You limit the number of rows returned by MySQL in a query using the LIMIT clause at the end of the query. This has the form LIMIT 25 or LIMIT 50,25 which here respectively means return the first 25 rows found and return 25 rows found starting from the 51st row found (skipping the first 50 rows). The numbers that are actually used can be made to depend on how many results are to be shown per page and what page the viewer should be looking at.

To get how many pages actually are available for the user to choose from, you also need to know how many rows would have been returned if the limit clause was not used at all. You can get this information by including in the query statement the keyword SQL_CALC_FOUND_ROWS which should be placed just after the SELECT keyword. This slows the query down slightly, but gives you the information you need. You access this in PHP with another query:

SELECT FOUND_ROWS

which you make immediately after the main query.

The rest is up to you to find out via a search for example code, namely how to then create the HTML that takes this information and makes buttons or links that allow the user to select other pages of results.

Steve, Denmark

[ronverdonk]

Good explanation, Steve.

Ronald

[jainritesh]

Hi!
Thanxs Ronald for providing me the correct word for the same.
Thanxs Steve for explaining the same in such a simple language, i got a clear idea on how to do the same.

Thanxs once again for guiding,

Best Regards,
Ritesh Jain

[ronverdonk]

You are always welcome here. Success and see you some time.

Ronald