Php / Mysql join and limit limit

Question

I have 3 tables with the same structure, and I need to query them as one large table, organize and limit this large table as a whole. The limit is the offset since I use 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 10, regardless of the page, but:

On page 1, $ counter = 10

On page 2, $ counter = 20

On page 3, $ counter = 23

On page 4, $ counter = 3

Well, if it is not necessarily 10 for the last page, because I get what is left of the list, but getting 20 and 23 for pages 2 and 3 does not make me feel.

It should be connected with LIMIT, because if I use only one table in the classical way, my counter is always 10 (unless of course not the last page, of course).

What is wrong here?

Thanks!

Answer 1

The limit must have a start and the number of rows required. 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 lines, starting at line 10).

If you still have problems, try putting choices and unions in your own set (). I am not sure about the order of operations, perhaps your limit applies only 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 );