0

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!

user1637210
  • 11
  • 4
  • Please clarify. What are the columns in all the $aXXXXX array? Is there something that you can use to identify the type of record? And what does "Paginator don't work when rows is different" means? – Moe Tsao Aug 30 '12 at 21:41

1 Answers1

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.

Expedito
  • 7,771
  • 5
  • 30
  • 43
  • Hi, thanks for your reply. The problem is that each queries select from different tables. In total there is around 25 tables... I also try a way for select all in one query... but I don't find any way for do this... a new question? I hope there is a way for make pagination for multi queries, because all scripts is already made... is missing only pagination – user1637210 Aug 30 '12 at 22:27
  • The problem is that with multiple queries there is no way to take advantage of MySQL's limit and offset commands. Without limit and offset there is no other option than to request ALL query results and manage them with PHP. You can do that, but it's very inefficient. – Expedito Aug 31 '12 at 01:26
  • Give it a try. Post your queries, and maybe someone can help combine them all. However, trying to do it with PHP alone would be a nightmare. – Expedito Aug 31 '12 at 01:34
  • Yeah, for me become a nightmare try to do this with PHP... I post the queries... I hope there is a way for make 1 queries... – user1637210 Aug 31 '12 at 07:24
  • @user1637210 You're probably looking for the [UNION](http://dev.mysql.com/doc/refman/5.0/en/union.html) statement. – h2ooooooo Aug 31 '12 at 07:29
  • Hi... thanks for reply... unfortunately UNION can't work... as I know UNION require the same field name in the queries... or can I use alias? – user1637210 Aug 31 '12 at 08:14
  • ok.. seem that I can use alias... I will check into UNION, maybe is the best way. Thanks h2oooooo... if anyone have better solution, please reply. thanks – user1637210 Aug 31 '12 at 09:02
  • hi, check query using UNION. the problem is that show only 10 records and not 70... I use LIMIT 0,10 on each query. Should return 10 rows X 7 query... or not? – user1637210 Aug 31 '12 at 11:52
  • FIXED! I find this question: http://stackoverflow.com/questions/4386224/mysql-union-limit-problem – user1637210 Aug 31 '12 at 11:56
  • @user1637210 :if u found solution yourself,please mark your question answere d yourself. – Notepad Aug 31 '12 at 12:08