CODEWITHSUNDEEP
mysqlsql
behz4d
behz4d

Reputation: 1849

Performing UNION select getting which table the data come from

I have 3 tables which share the exact same structure, like:

id       name_col

I have this query:

$query = mysql_query("SELECT * FROM tbl_1 UNION SELECT * FROM tbl_2 UNION SELECT * FROM tbl_3");

Now:

while($row = mysql_fetch_array($query)){
    names .= $row['name_col'];
    // HERE HOW I COULD KNOW WHICH TABLE THIS DATA IS COMING FROM?
    // THIS NAME IS FROM tbl_1 OR tbl_2 OR tbl_3
}

Upvotes: 0

Views: 105

Answers (2)

didierc
didierc

Reputation: 14730

You can add a constant field to your subqueries in order to index them, as follow:

SQL:

SELECT *, 1 AS tbl ROM tbl_1 
UNION
SELECT *, 2 AS tbl FROM tbl_2 
UNION
SELECT *, 3 AS tbl FROM tbl_3

The column name for that extra field must be consistent accross the subqueries.

PHP:

while($row = mysql_fetch_array($query)){
    names .= $row['name_col'];
    switch ($row['tbl']){
         case 1: // tbl_1
         break;
         // etc.
    }
}

Upvotes: 2

John Woo
John Woo

Reputation: 263733

you can additionally add a custom column which specifies the name of the table where it belongs, eg

SELECT id, name_col, 'table1' tableName FROM table1
UNION
SELECT id, name_col, 'table2' tableName FROM table2
UNION
SELECT id, name_col, 'table3' tableName FROM table3

Upvotes: 3

Related Questions