Selecting data from multiple tables and printing them

Question

I have 3 tables: itemsforsale, itemsforrent, wanteditems. Each of these tables have the same number of columns and same name too. I want to select all data from these 3 tables where USERID is equal to an id of my choice and print them as rows. It's not working. Here is my code:

$sql = "SELECT sale.propertype as propertytype,sale.description as description,sale.price as price,sale.telefone1 as telefone1,sale.telefone2 as  telefone2,sale.userid as userid,sale.propertyid as propertyid,sale.area as area,sale.dateposted as dateposted,sale.datebumped as datebumped,sale.images as images,sale.hiddentype as hiddentype,sale.pricetype as pricetype,sale.size as size FROM itemsforsale sale WHERE userid = '1'
UNION SELECT rent.propertype as propertytype,rent.description as description,rent.price as price,rent.telefone1 as telefone1,rent.telefone2 as  telefone2,rent.userid as userid,rent.propertyid as propertyid,rent.area as area,rent.dateposted as dateposted,rent.datebumped as datebumped,rent.images as images,rent.hiddentype as hiddentype,rent.pricetype as pricetype,rent.size as size FROM itemsforrent rent WHERE userid = '1'
UNION select wanted.propertype as propertytype,wanted.description as description,wanted.price as price,wanted.telefone1 as telefone1,wanted.telefone2 as  telefone2,wanted.userid as userid,wanted.propertyid as propertyid,wanted.area as area,wanted.dateposted as dateposted,wanted.datebumped as datebumped,wanted.images as images,wanted.hiddentype as hiddentype,wanted.pricetype as pricetype,wanted.size as size from itemswanted wanted WHERE userid = '1'";
$result = mysqli_query($mysqli, $sql);

Error:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in blahblah on line 290

score 1 · Answer 1 · answered Feb 21 '17 at 22:04

1

You can't use SELECT * with UNION, you have to specify the column names. Here's what the documentation says:

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

Additionally, to apply Ordering, you need to use ORDER BY outside of UNION query.

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT

answered Feb 21 '17 at 22:04

Darshan Mehta

28,982
9
60
90

i wanna select everything in the tables. How is that done? – Ali Feb 21 '17 at 22:05
You can specify comma separated column list for all `SELECT` statements. – Darshan Mehta Feb 21 '17 at 22:06
so i have to select all columns instead of * ? – Ali Feb 21 '17 at 22:14
Yes, e.g. `SELECT column1, column2 from table1 UNION select column1, column2 from table2` etc – Darshan Mehta Feb 21 '17 at 22:17
okay thank you ill give it a try now. – Ali Feb 21 '17 at 22:18
$sql = "SELECT propertytype,description,price,telefone1, telefone2, userid,propertyid, area,dateposted,datebumped,images,hiddentype,pricetype,size FROM itemsforsale WHERE userid = '1' UNION ALL SELECT propertytype,description,price,telefone1, telefone2, userid,propertyid, area,dateposted,datebumped,images,hiddentype,pricetype,size WHERE userid = '1' FROM itemsforrent UNION ALL select propertytype,description,price,telefone1, telefone2, userid,propertyid, area,dateposted,datebumped,images,hiddentype,pricetype,size FROM itemswanted WHERE userid = '1'"; this isnt working still? – Ali Feb 21 '17 at 22:22
Are you getting any error message? – Darshan Mehta Feb 21 '17 at 22:31
error in main post with updated code. – Ali Feb 21 '17 at 22:33
Looks like the query is failing. Try running the query in MySQL console? – Darshan Mehta Feb 21 '17 at 22:34
nothing shows up. – Ali Feb 21 '17 at 22:44
0 `rows` returned? – Darshan Mehta Feb 21 '17 at 22:46

Selecting data from multiple tables and printing them

1 Answers1