如何在Mysql Union中识别表

Question

I Have Query As the following

SELECT * FROM ( 
    (
        SELECT * FROM comments 
        WHERE user_id = '66' AND product_id = '3' 
        AND status = 1 
    ) 
    UNION ALL 
    (
        SELECT * FROM comments_reply 
        WHERE user_id = '66' AND product_id = '3'
        AND status = 1 ) 
    ) results 
ORDER BY datetime DESC

Which Gets Data from two tabel and shows it in order to datetime.now frontside i am showing this data. but now i wants to know that which data is came from which table.

because I am calling a modal to show a comment in modal by this

<span class="showcomment text-danger"
     data-cid = <?=$row[0]?>>view Comment</span>

But $row[0] Gives me an id But How can i identify that this id is came from which Table ?

Answer 1

I would rewrite your query as with tablename that you want

SELECT 'comments' as table_name, col1, col2,... coln FROM comments c
WHERE user_id = '66' AND product_id = '3' 
AND status = 1  UNION ALL 
SELECT 'comments_reply' as table_name, col1, col2,... coln FROM comments_reply cr
WHERE user_id = '66' AND product_id = '3'
ORDER BY col DESC

And, i suspect if you have user_id, product_id has numeric type then you should use only values without single quote.

where user_id = 66 AND product_id = 3