Paginate a Table in Multiple Databases (PHP/MySQL)

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.

 

First pull a count, to see how many there are, then use the SQL LIMIT command to set how many to pull and from what offset in the result list.

SELECT COUNT(id) FROM users

tells you how many... to determine the number of pages you just do:
$pageCount = floor( ($count-1) / $numberToShowPerPage ) + 1;

where $count is the result from the counting query and $numberToShowPerPage is, well... you can figure that out. (It's AMAZING how often people screw up that simple calculation! -- oh, and don't forget to special-case handle 0 results)

SELECT * FROM users LIMIT :start, :count

would pull up a list of :count users starting at :count into what the result would be. (assumes you're using prepared queries and bindparam like a good little doobie). :start would be the page * $numberToShowPerPage, and :count would probably BE $numberToShowPerPage.

 

may I add that you probably want to order by color like this

SELECT * FROM users ORDER BY category_color LIMIT :start, :count

Code (markup):

 

Its not as easy as this. I have many Users tables. They are all in different databases. I need to combine them all, sort, then paginate and display the results similar to the example above.

 

OK maybe you can show us then what tables you have and what their structures are.

Also in my head it doesn't make sense to have multiple user tables.

anyway show us what you have then we can help you better. otherwise it is just guessing.

 

You probably designed your database structure very badly. Think about normalizing your data into a single database.