There are several tables with different column names, in fact they have the same date column by which to sort the sample. So, how can I derive news for the user? All his subscriptions, comments, replies to comments, replies to his posts, etc. I visited UNION, but there Zapara because of the different names in the table Who had experience what to do?

CREATE TABLE `dis` ( `id` int(11) NOT NULL auto_increment, `question_id` int(11) NOT NULL, `text` TEXT NOT NULL, `date` datetime NOT NULL default '0000-00-00 00:00:00', `from` int(11) NOT NULL, `to` int(11) NOT NULL, `ip` varchar(25) NOT NULL default '', `is_a` TINYINT(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=cp1251; CREATE TABLE `quest` ( `id` int(11) NOT NULL auto_increment, `text` TEXT NOT NULL, `date` datetime NOT NULL default '0000-00-00 00:00:00', `from` int(11) NOT NULL, `ip` varchar(25) NOT NULL default '', `is_a` TINYINT(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=cp1251;
  • Through left join it is necessary - Gorets
  • How do different names interfere with union? a different number of fields can interfere, and the names of the role do not play - Yura Ivanov
  • There and different number of fields and different names - Fangog
  • a different number of fields can be reduced to a larger number using constants, the names are not important. show a couple of requests for the relevant samples, without any structure it is difficult to advise anything. - Yura Ivanov
  • the structure of the tables in the studio - Ale_x

1 answer 1

 SELECT * FROM ( SELECT 'dis' as Source, `id` as MessageId, CONCAT('Ответ на вопрос ',qu.nickname,' от ',DATE_FORMAT(q.`date`,'%d.%m.%y %H:%i:%s') as Subject `text` as MessageText, `date` as MessageDate, `from` as AuthorId, `to` as RecipientId, `ip`, `is_a` FROM dis d JOIN quest q on d.question_id=q.id JOIN users qu on q.`from`=qu.id UNION ALL SELECT 'quest', `id`, 'Вопрос', `text`, `date`, `from`, 0,//для всех `ip`, `is_a` FROM quest ) t where t.AuthorId = $currentUser order by t.`date` desc

Then you can join this query with users, in order to pull out their nicknames (for AuthorId and RecipientId) and so on ...