mysql_field_table() Not Working With My Union Query

[JustinK101]

Hello I am trying to retrieve the table name of a given row returned. I am using the following code:

$sql = "(SELECT id, first_name, last_name, job_title_occupation, current_clock_status, last_succ_login FROM employees) UNION ALL (SELECT id, first_name, last_name, job_title_occupation, current_clock_status, last_succ_login FROM inactive_employees) ORDER BY last_name ASC";
$result = mysql_query($sql) or die(fatal_error_alert(mysql_error(), $sql));
echo "The Table Is: " . mysql_field_table($result, 0);

Nothing is being returned though from the mysql_field_table() function call. Does this function work with UNION mysql operations? Thanks.

[JustinK101]

Anybody know?

Shoot I dont think the UNION works with mysql_field_table(). That sucks, that seems like the entire point of this function, to determine which rows are coming from which table. Any help much appreciated!

[JustinK101]

I got the fix, thought kind of dirty. Simply put the table name in the select that why you reference it for every row.

(SELECT 'employee', id, first_name, last_name, job_title_occupation, current_clock_status, last_succ_login FROM employees) UNION ALL (SELECT 'inactive_employee', id, first_name, last_name, job_title_occupation, current_clock_status, last_succ_login FROM inactive_employees) ORDER BY last_name ASC

[btherl]

I think that's the best solution, dirty though it is.  Once you've unioned two result sets, it's impossible to tell which set the data originally came from unless you add a marker like you have there.

Another option would be to do two selects and merge them and sort them in php.  Merging and sorting is usually easier in sql though :)