Michael Lynch Michael Lynch - 1 year ago 95
PHP Question

How can I select rows from two tables that have slightly different columns? How can I offset them?

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

Subsequent loads are basically the same with an additional offset. The offset is defined by the page number subtracted by one, multiplied by the properties per page.

$page = $_GET[‘page’] - 1;
$propertiesPerPage = 100;
$offset = $page * $propertiesPerPage;

$sql = "SELECT * FROM table WHERE bedrooms = 2 LIMIT 100 OFFSET ".$offset;

This works great. Now I just want to do the same thing, but get properties from two tables instead of just one. I'm trying this:

$sql = "SELECT * FROM table1 WHERE bedrooms = 2";
$sql .= " UNION ALL ";
$sql .= "SELECT * FROM table2 WHERE bedrooms = 2 LIMIT ".$propertiesPerPage." OFFSET ".$offset;

However, because each table has a few different columns than the other, I get this error:

The used SELECT statements have a different number of columns

Both tables have 60 or more columns but most of the columns are the same.
Is there a way to select rows from both tables where
conditions are met?

Also, how does
work with
s? How can I "paginate" the results being selected from two tables?

Answer Source

Your query needs to be similar to this:

$sql = "
(SELECT col1, col2, col3, col4 FROM table1 WHERE bedrooms = 2)
(SELECT col1, col2, col3, NULL AS col4 FROM table2 WHERE bedrooms = 2)
) AS myalias
LIMIT ".$propertiesPerPage." OFFSET ".$offset;

A couple things:

  1. 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)

  2. 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.

  3. 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.

  4. 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download