Distinguishing between rows from a union query.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
drayarms
Forum Contributor
Posts: 134
Joined: Fri Dec 31, 2010 5:11 pm

Distinguishing between rows from a union query.

Post by 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:




Code: Select all

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


Code: Select all

//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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Distinguishing between rows from a union query.

Post by Weirdan »

Code: Select all

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
and then decide the code path to follow based on $row['type']
drayarms
Forum Contributor
Posts: 134
Joined: Fri Dec 31, 2010 5:11 pm

Re: Distinguishing between rows from a union query.

Post by drayarms »

@weirdan, 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Distinguishing between rows from a union query.

Post by Weirdan »

Did you mean 'publications' as type and 'images' as type ?? Notice that my table names are publications and images, not publication and image.
It doesn't really matter as long as that virtual field contains distinct value for every single table. Could be 1 and 2 or 'foo' and 'bar' respectively.
Basically you add that field on the database side and parse its contents on the php side - so as long as query agrees on the values with parsing code it should be fine.
drayarms
Forum Contributor
Posts: 134
Joined: Fri Dec 31, 2010 5:11 pm

Re: Distinguishing between rows from a union query.

Post by drayarms »

@ weirdan, i noticed that even b4 reading ur reply. thanks.
Post Reply