CODEWITHSUNDEEP
phpmysqlsqlmysqli
Mad Coder
Mad Coder

Reputation: 117

How Can Identify Table in Mysql Union All

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 ?

Upvotes: 3

Views: 586

Answers (3)

adeel iqbal
adeel iqbal

Reputation: 494

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

Upvotes: 0

Tim Thyne
Tim Thyne

Reputation: 1

You can add a column to each of your two tables and populate it with a custom string. For example, the first sub query would start like the following with the new column “table_name”... select *, ‘comments’ table_name from comments...

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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

Upvotes: 4

Related Questions