How to create pagination that based on two sql queries? (PHP)

Question

I have page that display information from two different tables , and for that I have two queries. There is no related info between these two tables. Since both queries may contain a lot of information, I need create pagination. BUT I don't want two separate paginations, I want only one that will contain results from query 1 and query 2 together.

How can I do that?

The only idea I have is to fetch all info of both queries into arrays, then combine the arrays into one, then create pagination that based on that array. That of course would not help save resources.

Sam Peacey · Accepted Answer · 2012-10-10 12:13:36Z

4

You could use a union - the columns you're displaying must line up, so something like this should work:

select
  col1 col1_alias,
  col2 col2_alias,
  ...
from 
  table1
where
  ...

union

select
  col1,
  col2,
  ...
from
  table2
where
  ...
order by col1_alias, col2_alias
limit 10

Basically the union will pull all the data together, and the order by and limit will apply to the whole result set.

The names of the columns don't need to match in the second select, but use column names from the first select for your order by (or create aliases, which is probably more readable depending on your dataset).

edited Oct 10, 2012 at 12:13

answered Oct 10, 2012 at 12:07

Sam Peacey

6,0341 gold badge28 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create pagination that based on two sql queries? (PHP)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related