Jump to content


Photo

mysql_field_table() Not Working With My Union Query


  • Please log in to reply
3 replies to this topic

#1 JustinK101

JustinK101
  • Members
  • PipPipPip
  • Advanced Member
  • 503 posts
  • LocationSan Diego, California, US

Posted 26 October 2006 - 05:51 PM

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.

#2 JustinK101

JustinK101
  • Members
  • PipPipPip
  • Advanced Member
  • 503 posts
  • LocationSan Diego, California, US

Posted 26 October 2006 - 06:47 PM

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!

#3 JustinK101

JustinK101
  • Members
  • PipPipPip
  • Advanced Member
  • 503 posts
  • LocationSan Diego, California, US

Posted 26 October 2006 - 10:53 PM

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

#4 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 27 October 2006 - 02:08 AM

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 :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users