mysql union with different field names

[php_begins]

Hi I need to display latest activity of a user on his profile. I need to populate data from different tables and display them according to latest date. In my code below, the table Photos has different field names.

How do i write the echo statement so that the data from the Photos table also get displayed?

 

$q="(SELECT `userid`, `lastupdate`,  `title`,  'classifieds' AS `type` FROM `CLASSIFIEDS_DATA` WHERE `userid` = '$profileuserid') 
      UNION  (SELECT `userid`, `lastupdate`, `title`, 'reviews' AS `type` FROM `REVIEWS_DATA` WHERE `userid` = '$profileuserid') 
    UNION  (SELECT `userid`, `lastupdate`, `title`,  'registry' AS `type` FROM `REGISTRY_DATA` WHERE `userid` = $profileuserid)
    UNION  (SELECT `userid`, `date`, `title`, 'blogs' AS `type` FROM `blog_entry` WHERE `userid` = '$profileuserid')
    UNION  (SELECT `Photo_id`, `User_id` `Date_created`, `Photo_name`, 'Photos' AS `type` FROM `Photos` WHERE     `User_id`= '$profileuserid') ORDER BY `lastupdate` DESC LIMIT 10";

  $q_res=mysql_query($q) or die(mysql_error());
while($q_fetch=mysql_fetch_assoc($q_res))
{

   $date=$q_fetch['lastupdate']."<br>";
    echo "Posted on <a href='asd'>".$q_fetch['title']."</a> in <a href=".'$root_url.$type'.">".$q_fetch['type']."</a>   on ".date  'm/d/Y',$date)."<br><br>";
}

 

[php_begins]

I did something like this, and it seems to work..let me know if there is a better way

 

 

$q="(SELECT `userid`, `lastupdate`,  `title`,  'classifieds' AS `type` FROM `CLASSIFIEDS_DATA` WHERE `userid` = '$profileuserid') UNION  (SELECT `userid`, `lastupdate`, `title`, 'reviews' AS `type` FROM `REVIEWS_DATA` WHERE `userid` = '$profileuserid') UNION  (SELECT `userid`, `lastupdate`, `title`,  'registry' AS `type` FROM `REGISTRY_DATA` WHERE `userid` = $profileuserid)

    UNION  (SELECT `userid`, `date` AS `lastupdate`, `title`, 'blogs' AS `type` FROM `blog_entry` WHERE `userid` = '$profileuserid')

    UNION  (SELECT  `User_id`, `Date_created` AS `lastupdate`, `Photo_name` AS `title` , 'Photos' AS `type` FROM `Photos` WHERE `User_id` = '$profileuserid')

    ORDER BY `lastupdate` DESC LIMIT 10";

[Psycho]

First off, the field names from the different tables do not have to have the same names, but they MUST be the same field types. So, you can't have, for example, the third field from one table as an INT type and the third field form another table as a DATE type. Assuming this is the case, the field names returned for the records from ALL the tables will be the same. Either, the names from the first table will be used OR you can give the same aliases to the fields for each table. Here is a small tutorial on using UNION that may help: http://www.mysqltutorial.org/sql-union-mysql.aspx

 

So, there are some problems with your query above. 1. You have 'user_id' as the first field for the first select statements, but it is the 2nd field in the last select. Also, the last select has 5 fields, whereas the first select statements have four.

 

So, get the fields for each select statement in the proper order: "user_id", "date", "title/name", and "type". Since you also need the photo_id, you can grab the record ID from the other tables to be consistent or you can just define a static value as the 5th element for those tables.

 

Here is one possible solution

$query = "  (SELECT `userid`, `lastupdate` AS `date`, `title`, `classifieds` AS `type`, `classifieds_id` AS 'rec_id`
             FROM `CLASSIFIEDS_DATA` WHERE `userid` = '$profileuserid') 
          UNION
            (SELECT `userid`, `lastupdate` AS `date`, `title`, `reviews` AS `type`, `reviews_id` AS 'rec_id`
             FROM `REVIEWS_DATA` WHERE `userid` = '$profileuserid') 
          UNION
            (SELECT `userid`, `lastupdate` AS `date`, `title`, `registry` AS `type`, `registry_id` AS 'rec_id`
             FROM `REGISTRY_DATA` WHERE `userid` = $profileuserid)
          UNION
            (SELECT `userid`, `date` AS `date`, `title`, `blogs AS `type`, `blogs_id` AS 'rec_id`
             FROM `blog_entry` WHERE `userid` = '$profileuserid')
          UNION
            (SELECT `User_id` AS `userid`,`Date_created` AS `date`, `Photo_name` as `title`, `Photos`AS `type`, `Photo_id` AS `rec_id`
             FROM `Photos` WHERE `User_id`= '$profileuserid')
          ORDER BY `date` DESC LIMIT 10";

 

If one of those tables doesn't have a unique ID for the 5th column you could set a static value like so:

SELECT `userid`, `lastupdate` AS `date`, `title`, `registry` AS `type`, NULL AS 'rec_id`

[php_begins]

thanks, that worked except for a couple of very minor syntax errrors!