Return MySQL table name as PHP variable

I'm sure it can be done, but how?

[MatthewHSE]

I've got a MySQL query something like the following:

$fields = mysql_query('SELECT ItemID FROM Table1 UNION SELECT ItemID FROM Table2 ORDER BY RAND() LIMIT 1');

There's more criteria than that, but this illustrates the basics of the situation. Essentially I want to randomly select one row from either of the two tables. I've got that part down; the query is returning the results I need.

However, the problem I'm having is finding out which table the query pulled the result from. I'm querying two tables and will return one result - but I need to know which table the result came from. Is there any way to assign it to a variable? If so, how can it be done?

Thanks a lot for any help,

Matthew

[jollymcfats]

Can you add a literal to a union query? (My MySQL doesn't support them, so I'm guessing here.)

SELECT 'Table1' AS TableName, ItemID FROM Table1 UNION SELECT 'Table2' AS TableName, ItemID FROM Table2 ORDER BY RAND() LIMIT 1

A more laborious option is to inspect

ItemID

with mysql_fetch_field [php.net]. I'd be curious to hear what is returned in the table slot for a union query.

[coopster]

Yes, you can use a constant in a UNION query -- UNION became available in MySQL 4.0.0. And you are correct, jollymcfats, that is exactly how to solve this issue. I noticed one thing though, MatthewHSE, and that is your ORDER BY clause. I think you want the sort order to be on both result sets, not just the second SELECT query. If you want to use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one:

(SELECT 'Table1' AS TableName, ItemID FROM Table1) 
UNION 
(SELECT 'Table2' AS TableName, ItemID FROM Table2) 
ORDER BY RAND() LIMIT 1;

[coopster]

I'd be curious to hear what is returned in the table slot for a union query.

I ran a quick test. Nothing.

-- 
--Information for column TableName: 
-- 
blob:    0 
max_length: 6 
multiple_key: 0 
name:    TableName 
not_null:  1 
numeric:   0 
primary_key: 0 
table:     
type:    string 
unique_key: 0 
unsigned:  0 
zerofill:  0 
-- 
-- Information for column ItemID: 
-- 
blob:    0 
max_length: 10 
multiple_key: 0 
name:    ItemID 
not_null:  0 
numeric:   0 
primary_key: 0 
table:     
type:    string 
unique_key: 0 
unsigned:  0 
zerofill:  0 

Without digging in any further (I'm tired ;) ), I would guess that this is because MySQL is probably using a temporary table to store the result set of the UNION query. I could be way off base, but it seems logical.

[jollymcfats]

Interesting. This inspired me to take a peek at the C api, and it looks like mysql_fetch_field is not a per-row function- it reads the metadata for the query as a whole, which is largely perfect for any query type but a UNION*. Given that, I'll hazard a guess that the database isn't even tracking the origin table for each row. It would be a lot of extra work for a corner case with a simple in-SQL solution like the one above.

_{* This would come up for a SELECT COALESCE(Table1.foo,Table2.bar) or other conditional query as well.}