Php / Mysql Union and Limit offset

Question

I have 3 tables with the exact same structure and I need to query them as one big table, order and limit this big table as a whole. The limit is an offset as I use a pagination.

This is what I have so far:

$from = (($page_number-1)*10);
$to   =  ($page_number)*10;     

$request = mysql_query("
    (SELECT * FROM table_a) 
    UNION ALL 
    (SELECT * FROM table_b) 
    UNION ALL 
    (SELECT * FROM table_c) 
    ORDER BY title ASC 
    LIMIT ".$from.",".$to 
);

$z=0;

while ($result = mysql_fetch_array($request)) 
{
    ....
    $z++;
};

$counter = $z;

I expect the $counter to be equal to 10, whatever the page but:

On page 1, $counter = 10

On page 2, $counter = 20

On page 3, $counter = 23

On page 4, $counter = 3

Ok if it's not necessarily equal to 10 for the last page because I get what's left from the list but getting 20 and 23 for page 2 and page 3 doesn't make any sens to me.

It's has to be related to the LIMIT because, if I only use a single table in a classic way, my counter is always equal to 10 (unless it's the last page, of course).

What's wrong here?

Thanks!

Answer 1

Limit should have the start and the number of rows you want. So 0, 10 if you want the first ten, and then 10, 10 if you want the next 10 (not 10, 20 which will give you 20 rows starting at row 10).

If you are still having issues, try putting the selections and unions in their own set of (). I'm not sure about the order of operations, it is possible your limit is only applying to the last table.

$request = mysql_query("
    ( 
    (SELECT * FROM table_a) 
    UNION ALL 
    (SELECT * FROM table_b) 
    UNION ALL 
    (SELECT * FROM table_c)
    ) 
    ORDER BY title ASC 
    LIMIT ".$from.",10
);