Creation paging for multiple queries

Question

I have 7 queries different and I use this code for merge query and sort query:

$array= array_merge($aPhotos, $aVideos, $aMusics, $aSongs, $aBlogs, $aPolls, $aQuizzes);

foreach ($arry as $k => $v) {   
 $sort[$k] = $v[$sortNode];
}

array_multisort($sort, SORT_DESC, $array);

Now I want create paginator for final array $array

I use on each query the same LIMIT, example LIMIT 0, 10

Sometimes the query return 7 rows, other 10 rows, or 0 rows.

Each query return different number of query.

All work fine with my paginator when the query returned is 70 (7*10)

Paginator don't work when rows is different.

My question is how to create a paginator in this case.

The best solution is use UNION!

Answer 1

I never try to do pagination with multiple queries. I know 7 queries is a lot, but I think it would be worth your while to figure out how to combine them into a single query. MySQL has some powerful features, and in my experience, I was often surprised at what I could accomplish with a single query.