php mysql and pagination

Question

I have a problem with displaying data from mysql database. My client requires to display paginated data - i.e.15 rows per page, there sholdn't be any scrollers, and at the same time, data displayed are ordered by category and for each new category I have to add 2 new rows at the table displayed in php file, sth. like:

r1-Category1
r2-item name | item data1 ...
db-item1
db-item2
...new item x

r1-Category2
r2-item name | item data1 ...
db-item 1
db-item 2

...

I tried to call all the data from database, for each category added 2 new rows (such as name of the category and th for the items displayed), put them in an array, then counting all the rows and divided to get pages with 15 results on it, and continue processing with javascript. The cons of this method is, that here is a lot of data and it takes almost a minute to get the job done.

The best solution - to get the data displayed ASAP IMO is to use pagination, but I need to define a limit .. and there is a problem - how can I define a limit if the categories among 15 results can be various (sometimes 3, 4 or just one) ? Thus I tried to find out how many categories are within 15 results from database, then make another call to database with a limit = limit-2x(no of categories) ... for the first page works fine, but for the next pages not, obvious ... for example: in the limit of 15 rows are for example 3 categorires , so I need to change limit to 9 ... if I change limit to 9row, there are only 2 categories ... very complicated, but the point is, that this cannot work from my point of view.

Do you have an idea how to solve this?

the first solution is unacceptable for my customer, and the second impossible from my point of view .. but his opinion is that in programming everything is possible ..

is this true ? You want fetch data from 2 tables with limitation ?i mean you want e.g get 15 rows from category1 and category2 ? — Hamid
– Hamid, Commented Jul 21, 2011 at 7:23
in fact .. there are 2 tables: one with id, category, country, matchid ..., and the other for additional data for matchids .. I need to connect them, display category in one tr, the additional data of matchids in further tr ... if I call 15 matchids, there can be possibility to have inside 2,3 or more categories, thus I woudl have more than 15 rows ... — Angel M.
– Angel M., Commented Jul 21, 2011 at 7:31
See how pagination work in here or even better using a class — Ibu
– Ibu, Commented Jul 21, 2011 at 7:34
I know how the pagination works, I did this before several times, I also have a class ... but even so, the displayed data cannot be 15 .. I cannot predict where the new category come in place, I did a lot of counting ... trying to get data from database manually and after 3rd call I end up in an impasse ... — Angel M.
– Angel M., Commented Jul 21, 2011 at 7:46

tdammers · Accepted Answer · 2011-07-21 07:29:16Z

1

Get 15 items from the database (LIMIT (start, count)). Remember the starting index, and tag your rows with indexes. Build your table, but each time you encounter a heading, subtract 2 from the number of rows to show. When you reach the maximum table height, remember the item that you would have displayed next: this is the starting index for the next page.

Stepping backwards is trickier; you need to remember the starting index of the page you came from in order to do that (if you don't, then you have to fetch all the rows up to the ones you're currently displaying). Another problem with this method is that you won't know how many pages there are beforehand.

An alternative is to write a query that gives you row counts for each category. Fetching only those shouldn't take a whole minute if you have proper indexes in place, and even if it is expensive, you can cache the results. With this information, you can chunk up your data into pages of up to 15 lines, using whole categories (provided no category has more than 13 entries). You can even cache the pagination data. Downsides of this method are that you may have slightly outdated pagination, so when there is a lot of inserting and deleting going on, you may occasionally end up with 16 or 17 lines instead of 15.

On a side note, you need to educate your customer a bit. It's great to look good, but you don't want to give the impression that anything is possible in finite time.

answered Jul 21, 2011 at 7:29

tdammers

20.7k1 gold badge41 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Angel M. Over a year ago

thanks for your answer ... I told him all of that ... I showed him the quieries, tables and the final models ... he is a programmer himself and he told me that there is always a solution in programming .. maybe I need more knowledge, but the requirements are contraindictory ...

Collectives™ on Stack Overflow

php mysql and pagination

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related