Distinguishing between rows from a union query.

[drayarms]

Hello all,  I'm trying to use a union query to fetch rows from unrelated tables, and display all the rows, sorting by some common critarion (in this case, the time the entry was posted/registered/uploaded).  The tables are named publications and images.  So here is the select statement and associated code:

 

 

 

 

<?php

					//Connect to database
                                                require("config.php");

					//Query the database.

					$query = "SELECT publication AS a, cartegory AS b, pub_time AS c FROM publications

						  UNION ALL

						  SELECT image AS a, image_cartegory AS b, image_time AS c FROM images ORDER BY c DESC";

					$result = mysql_query($query);

					if(!$result)

						{

    								die('<p>Could not retrieve the data because: <b>' . mysql_error(). '</p>');

    								// Handle as desired

						}else{  //If query is valid.

    								if(mysql_num_rows($result) > 0){



       			 						while ($row = mysql_fetch_assoc($result)){ 

								echo "<p>".$row['a']."</p>";


								}//End of while loop


							}else{//If no rows were returned

								echo "no rows returned";

							}

						}//Closes if query is valid statement.


?>

Well as expected, all the contents from the image and publication columns are displayed in the order specified.  But now my problem is, I want to be able to distinguish between the rows, depending on their table of origin, in order to format them differently.  For example, let's say I want the rows from the image table to be printed out with one font style and the rows from the publications table to be printed with a different font style, how do I go about that?

 

So far I have tried things like:

 

 

 

//For the sake of simplicity, I won't include the html formatting here

while ($row = mysql_fetch_assoc($result)){ if ($row['a'] == $row['publication']){echo $row['publication'];} else{echo $row['image'];}

//OR

if ('a' == 'publication'){echo $row['a'];} elseif   ('a' == 'image'){echo $row['a'];}

Well I hope you get the idea what I'm trying to achieve here.  So far, these and similar lines I've tried only yield a blank page.  I'm starting to wonder if it is even possible to format the rows differently based on their table of origin.  So folks, please tell me if there is anyway what I'm trying to do can be done. 

[phpSensei]

You don need to assign new names for the columns.. use UNION without it..

 

 

[AdRock]

Why don't you have an extra field in each table with their table name as the data so in the publications tanle have a field called "tname" or whatever and whenever an insert is made, insert "publications" into the column?

[Muddy_Funster]

Why don't you have an extra field in each table with their table name as the data so in the publications tanle have a field called "tname" or whatever and whenever an insert is made, insert "publications" into the column?

 

Erm...why?

 

The question is about formating the output, it's nothing to do with the SQL or column names, or even table names - although, on the subject of table names, why didn't you just name your tables a, b, c and your fields a,b,c,d it would have saved you having to type the full names at all?

 

[AdRock]

Erm...why?

 

The question is about formating the output, it's nothing to do with the SQL or column names, or even table names - although, on the subject of table names, why didn't you just name your tables a, b, c and your fields a,b,c,d it would have saved you having to type the full names at all?

 

 

I know....

 

If he wants to format based on the table name, having the table name in the query will help him distingiush whcih table it's from

[AbraCadaver]

SELECT publication AS a, cartegory AS b, pub_time AS c, 'publication' AS `type` FROM publications
UNION ALL
SELECT image AS a, image_cartegory AS b, image_time AS c, 'image' AS `type` FROM images
ORDER BY c DESC

[drayarms]

Thanks for all the suggestions.

 

@ AbraCadave

 

Did you mean 'publications' as type  and  'images' as type  ??  Notice that my table names are publications and images, not publication and image.  those are columns that correspond to the respective tables. 

 

Thanks for the suggestion anyways.

[AbraCadaver]

Thanks for all the suggestions.

 

@ AbraCadave

 

Did you mean 'publications' as type  and  'images' as type  ??  Notice that my table names are publications and images, not publication and image.  those are columns that correspond to the respective tables. 

 

Thanks for the suggestion anyways.

 

It doesn't matter what you call them.  It is just the value that will be returned for the pseudo column `type'  You can use p and i or 0 and 1 if you want.  It is just what $row['type'] will contain and corresponds to what table it came from.

[drayarms]

@AbraCadave, I noticed that b4 reading your reply.  Thanks.

[phpSensei]

im just going to say this one more time OP, you dont need to define new names for  your ROWS!!!!!!!