php mysql and pagination

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 ..

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.