[SOLVED] Show table name on Union query

[steelerman99]

i have a query that uses the union command with 2 tables.  when i am printing out the values from the query in a table, i would like to print the table name that corresponds to the row/record i'm printing.

 

i traverse through the result with:

 

while ($field = mysql_fetch_field($result))
        {  //print table headers aka column names }

while ($row = mysql_fetch_assoc($result))
{
     print "<tr>";

         foreach ($row as $col=>$val)
          {    //print each value in a <td> tag   }

     print "</tr>";
}

 

 

 

How do i know what table the specific row i'm on is from?

 

Thanks!!

[btherl]

SELECT 'foo' AS tablename, foo.* FROM foo
UNION ALL SELECT 'bar' AS tablename, bar.* FROM bar

 

Normally union will eliminate table names, so you need to record them yourself.  UNION ALL does not eliminate duplicates, which is what you want in this case, because duplicates cannot exist when you've added the "foo" and "bar" column.

[steelerman99]

Perhaps i worded my original question poorly...

 

I'm ok with the SQL and the Union statement.  That's no problem.

 

My question:  when i am traversing through my query result and printing each record as a row in a table(as in the code snippet i provided), how do i know which table the particular row i'm printing belongs to? (because i use an 'order by' statement in my SQL query, which mixes up the rows between tables, so it's not just printing one table, then the next, etc all in order).

 

Thanks

[Barand]

Perhaps i worded my original question poorly...

 

I think you read the reply poorly.

[steelerman99]

 

I think you read the reply poorly.

 

 

I probably did.  I'll give my table structure here and my previous sql query and let me know what to change since i don't totally understand what's going on in btherl's post (sorry, bare with the newb here!!).

 

I have 2 tables with the same structure named 'schedule' and 'newsched'

 

Column names (in order):  Date, Time, Away, Home, Field, Ump1, Ump2, Status

 

Here is my sql query that i used before that is not currently doing what i want it to be doing.  I'm trying to find all games that a particular umpire is scheduled to be on in 2 different leagues, which each have their own table (schedule and newsched, respectively):

 

"select * from schedule where ump1 = '$name' or ump2 = '$name' UNION ALL select * from newsched where ump1 = '$name' or ump2 = '$name' order by date"

 

 

So is there an object that i can reference to give me the name of the table as i'm printing it out (with code in original post), or how does this work?

 

 

Thanks a billion once again!

[Barand]

What btherl said is to include the table names as literals in the query

 

"select 'schedule ' as tablename, schedule.*

from schedule

where ump1 = '$name' or ump2 = '$name'

UNION ALL

select 'newsched' as tablename, newsched.*

from newsched

where ump1 = '$name' or ump2 = '$name'

order by date"

 

Now each row contains the the tablename from which it originated.

[steelerman99]

Alright cool!  The code works!

 

Thanks again!  I've never learned about 'literals' in SQL, so that was new to me.