I’m building a search function for a real estate app and I’m trying to display 100 filtered properties per page.
My SQL for the first load looks like this:
$sql = "SELECT * FROM table WHERE bedrooms = 2 LIMIT 100";
$page = $_GET[‘page’] - 1;
$propertiesPerPage = 100;
$offset = $page * $propertiesPerPage;
$sql = "SELECT * FROM table WHERE bedrooms = 2 LIMIT 100 OFFSET ".$offset;
$sql = "SELECT * FROM table1 WHERE bedrooms = 2";
$sql .= " UNION ALL ";
$sql .= "SELECT * FROM table2 WHERE bedrooms = 2 LIMIT ".$propertiesPerPage." OFFSET ".$offset;
The used SELECT statements have a different number of columns
Your query needs to be similar to this:
$sql = " SELECT * FROM ( (SELECT col1, col2, col3, col4 FROM table1 WHERE bedrooms = 2) UNION ALL (SELECT col1, col2, col3, NULL AS col4 FROM table2 WHERE bedrooms = 2) ) AS myalias LIMIT ".$propertiesPerPage." OFFSET ".$offset;
A couple things:
Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (http://dev.mysql.com/doc/refman/5.7/en/union.html)
Let's say you want it to return all the rows but there are data type mismatches or one table has entirely different columns. You can still retrieve the data from table1 that has the data but if table2 does not have that column then you would need something like
NULL AS col4.
The reason we use the union as a nested query is because the limit will be applied after both the queries have been joined together.
Generally when doing unions you'll be very specific on which columns you'll be fetching. If you want just 30 columns, you'll need to specify all of those columns in your select queries used for the union. If it seemed necessary, you could use a PHP variable to define it to reduce the repetition.