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.
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.
To find all of the SQL Server database views where a table is used, just apply a filter criteria on table_name column of the information schema view INFORMATION_SCHEMA. VIEW_TABLE_USAGE as seen below.
The syntax to get all table names with the help of SELECT statement. mysql> use test; Database changed mysql> SELECT Table_name as TablesName from information_schema. tables where table_schema = 'test'; Output with the name of the three tables.
Just add a constant to your column list as follows:
select 'table1' as table_name, title from table1
union all
select 'table2' as table_name, title from table2
union all
select 'table3' as table_name, title from table3
which will get you something like:
table_name | title
-----------+-----------------------------
table1 | war and peace
table2 | 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).
Note the use of union all
- if you're sure that there will be no duplicate rows from the tables (which is probably true in this case since you have a different table_name
value for each and I'm assuming the titles are unique), the union all
can avoid a wasted sort-and-remove-duplicate operation. Use of union
on its own may cause unnecessary work to be done.
If you want the duplicate removal done, just revert to using union
.
Should be easy enough, just do something like this:
SELECT Title, 1 FROM table1
UNION ALL
SELECT Title, 2 FROM table2
UNION ALL
SELECT Title, 3 FROM table3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With