SQL - Identifying source table from UNION query

Question

Hi, I'm building an RSS feed in PHP which uses data from three separate tables. The tables all refer to pages within different areas of the site. The problem I have is trying to create the link fields within the XML. Without knowing which table each record has come from, I cannot create the correct link to it.

Is there a way to solve this problem? I tried using mysql_fetch_field, but it returned blank values for the tables.

$sql = "
SELECT Title FROM table1
UNION 
SELECT Title FROM table2
UNION 
SELECT Title FROM table3";

There are other fields involved, but this is basically the query I'm using.

Any help would be appreciated.

Thanks.

Answer 1

Should be easy enough, just do something like this:

$sql = "
SELECT Title, 1 FROM table1
UNION 
SELECT Title, 2 FROM table2
UNION 
SELECT Title, 3 FROM table3";

Answer 2

Just add a constant to your column list as follows:

select 'table1' as table_name, title from table1
union
select 'table2' as table_name, title from table2
union
select 'table3' as table_name, title from table3

which will get you something like:

table_name | title
-----------+-----------------------------
table1     | war and peace
table1     | 1984
table3     | terminator salvation

and so on.

This allows you to have string data types which will likely make your conversion to links easier (especially if you use values that just have to be copied to your page instead of being looked up or converted) and using the as clause will allow you to reference it like any other column (by name).