Paginate A Table In Multiple Databases (Php/mysql)

[FishSword]

I have 10 MySQL databases that feature the same table structures/names in each.

The table I am partically interested in is the Users

 

I need a way to display/paginate the results from each of the Users tables, and sort the data by category_colour

 

The table fields are:

• id: INT(11)
  
• name: varchar(255) - e.g. Dave, Lisa, Steve
  
• category_colour: char(1) - Can be G = Green, O = Orange, B = Blue, O = Orange.
  

If I was to do this without paginate, there would be too many results, and the server would take an age to display them all.

I need the results to be categorized and in order of category_colour (1st: Red, 2nd: Green, 3rd: Blue, 4th: Orange) - Displaying the users database id

 

OUTPUT EXAMPLE:

 

Category Colour: Red

• Dave - 1
  
• Steve - 5
  
• Fred - 4
  
• etc
  

Category Colour: Green

• Richard - 6
  
• Kelly - 8
  
• etc.
  

Category Colour: Blue

• Emma - 3
  
• Scott - 2
  
• Louise - 7
  
• etc.
  

[Psycho]

There is really no good way to do this that I know of. I don't believe you can do a JOIN or UNION across databases. So, you would have to query ALL the data from each table from each database, dump the results into a temporary table and then query that. Or alternatively, query all the tables and then do all the logic in the PHP code. Either way would take quite a bit of time and resources to complete. If the data doesn't need to be real-time you could have an automated process to grab the data from each table in the separate databases to updated the temp table. Then each page load could just use that data.

 

But, I guess the bigger question is are the different databases really needed?

Edited November 5, 2012 by Psycho

[kicken]

You can do a UNION across different database (in my version of mysql at least, 5.5.25).

SELECT * FROM t1.users UNION ALL SELECT * FROM t2.users UNION ALL SELECT * FROM t3.users;

returns

+----+------+
| ID | name |
+----+------+
|  1 | t1a  |
|  2 | t1b  |
|  1 | t2a  |
|  2 | t2b  |
|  3 | t3a  |
|  4 | t3b  |
+----+------+

 

You can apply your limits for pagination and an order by to the result of the union query.

[DavidAM]

But, I guess the bigger question is are the different databases really needed?

 

Before you even think about implementing kicken's solution (which is correct, by the way); this question really must be answered. If it is a business requirement, then so be it; we've all had to work with less than ideal databases. But if it is a design flaw, it should be addressed as soon as possible.

[FishSword]

I have no choice. The databases have already been created.

 

But, I guess the bigger question is are the different databases really needed?

Edited November 6, 2012 by FishSword

[Muddy_Funster]

I have no choice. The databases have already been created.

Fair enough, they have already been created. Just because something was broken years ago does not meen that it can not now be fixed today. My strong suggestion would be that you press the point to have those databases merged where possible as soon as you can. If you can't get permission, fair enough, as DavidAM said - we've all been there, but at least if you push for it and they refuse then you yourself are coverd when it does go horribly wrong further down the line (as long as you do it in writing).