Brave Type Brave Type - 2 months ago 4
MySQL Question

fetch datas from two tables and differentiate between them

I have two tables and want displays rows from the two one in the same page ordered by date created.

Here my query:

SELECT R.*, R.id as id_return
FROM return R
UNION
ALL
SELECT A.*, A.id as id_buy
FROM buy A
WHERE
R.id_buyer = '$user' AND R.id_buyer = A.id_buyer AND (R.stats='1' OR R.stats='3') OR A.stats='4'
ORDER
BY R.date, A.date DESC LIMIT $from , 20


With this query i get this error message:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in ...


And here how i think i can differentiate between the results: (Knowing if the result is from the table
RETURN
or from the table
BUY
)

if(isset($hist_rows["id_return"])) {
// show RETURN rows
} else {
// show BUY rows
}


Please what is wrong with the query, and if the method to differentiate between tables are correct ?

EDIT

Here my tables sample:

CREATE TABLE IF NOT EXISTS `return` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_buyer` INT(12) NOT NULL,
`id_seller` INT(12) NOT NULL,
`message` TEXT NOT NULL,
`stats` INT(1) NOT NULL,
`date` varchar(30) NOT NULL,
`update` varchar(30)
PRIMARY KEY (`id`)
)


CREATE TABLE IF NOT EXISTS `buy` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_buyer` INT(12) NOT NULL,
`product` INT(12) NOT NULL,
`title` VARCHAR(250) NOT NULL,
`stats` INT(1) NOT NULL,
`date` varchar(30) NOT NULL
PRIMARY KEY (`id`)
)

Answer

Be sure the two table return and buy have the same number (and type sequence) of colummns .. if not the query fails

try select only the column you need from both the table and be sure that these are in correspondenting number and type

SELECT  R.col1, R.col2, R.id as id_return
FROM    return R
UNION ALL
SELECT  A.col1, A.col2,  A.id as id_buy
FROM    buy A
WHERE
........

Looking to your code you should select the same number and type of column form boith the table eg de sample below: (where i have added the different column and selecting null from the table where are not present) I have aslore referred the proper where condition to each table ..

SELECT  
     R.'from return' as `source_table`
   , R.`id` 
   , R.`id_buyer` 
   , null as product
   , null as title
   , R.`id_seller` as id_seller
   , R-`message` 
   , R.`stats` 
   , R.`date` 
   , R.`update`
FROM    return R
WHERE R.id_buyer = '$user'
AND (R.stats='1' OR R.stats='3')
UNION ALL 
SELECT  
     A.'from buy'
   , A.`id` 
   , A.`id_buyer` 
   , A.`product` 
   , A.`title` 
   , null 
   , null
   , A.`stats` 
   , A.`date` 
   , null
FROM    buy A
WHERE 
A.id_buyer = '$user'
AND A.stats='4'
ORDER BY `source table`, date DESC LIMIT $from , 20

for retrive te value of the first column you should use in your case

echo  $hist_rows["source_table"];

Otherwise i the two table are in some way related you should look at a join (left join) for link the two table and select the the repated column (but this is another question)

But if you need left join you can try

SELECT  
    R.`id` 
   , R.`id_buyer` 
   , R.`id_seller` as id_seller
   , R-`message` 
   , R.`stats` 
   , R.`date` 
   , R.`update`
   , A.`id` 
   , A.`id_buyer` 
   , A.`product` 
   , A.`title` 
   , null 
   , null
   , A.`stats` 
   , A.`date` 
FROM    return  R
LEFT JOIN buy A ON  R.id_buyer = A.id_buyer 
AND  R.id_buyer = '$user' 
AND (R.stats='1' OR R.stats='3')
AND A.stats='4'
ORDER BY R.date DESC LIMIT $from , 20