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!

Phrasal

Your are just using LIMIT wrong … LIMIT does not take “from row” and “to row” as parameters, but an offset (that’s “from row”, ok), and a row count – that’s the number of rows you want. – Leyes

Sorry but I didn't get your point. If my page is page 4, I get a "$from" equal to 30 and a "$to" equal to 40. So I get a count of 10 rows starting at the 30th row and ending at the 40th one. What's the problem there? So I have to put 10 as a constant for "$to", is it what you are saying? – Phrasal

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
);

Revisal

Answer 2

I will add about pagination, the limit at the beginning can be fixed or change depending on how many elements need to be displayed on the page.

Fixed number of elements per page:

$start = $curent_page = 0;
$limit = $per_page = 15;
 
if (!empty($_GET['page']) and is_numeric($_GET['page']) and $get_curent_page = filter_var($_GET['page'], FILTER_SANITIZE_STRING))
{
  $curent_page = ((!empty($get_curent_page) and is_numeric($get_curent_page)) ? (int)$get_curent_page : 0);
}

$start = ((is_int($curent_page) and $curent_page > 1) ? (($curent_page-1)*$per_page) : 0);

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

Or specify a limit using a filter on the view page, 25, 50, 100 items:

$start = $curent_page = 0;
$limit = $per_page = 15;
 
if (!empty($_GET['page']) and is_numeric($_GET['page']) and $get_curent_page = filter_var($_GET['page'], FILTER_SANITIZE_STRING))
{
  $curent_page = ((!empty($get_curent_page) and is_numeric($get_curent_page)) ? (int)$get_curent_page : 0);
}

if (!empty($_GET['limit']) and is_numeric($_GET['limit']) and $get_curent_limit = filter_var($_GET['limit'], FILTER_SANITIZE_STRING))
{
  $curent_limit = ((!empty($get_curent_limit) and is_numeric($get_curent_limit)) ? (int)$get_curent_limit : 0);

  if (is_int($curent_limit) and ($curent_limit <= 100 and $curent_limit > $per_page))
  {
    $limit = $curent_limit;
  }
}

$start = ((is_int($curent_page) and $curent_page > 1) ? (($curent_page-1)*$per_page) : 0);

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

Ashtray