How to merge two tables MySQL-PHP with date sorting

Question

I want to merge the results of two tables in to one. Please refer the following tables :

    Data from microblog table as Row array
    Array ( [ID] => 46 [userID] => 1 [userNAME] => user [blog_content] => HAI DEAR [page_name] => honda [page_ID] => 31 [post_time] => 2011-10-18 11:06:54 )
 Data from Page_review table as Row array
    Array ( [page_review_id] => 5 [page_id] => 31 [page_review_by_id] => 31 [page_review_by_username] => user [page_review_time] => 2011-10-18 11:43:34 [page_review_content] => hai ) 

Table Microblog MySQL query:

DROP TABLE IF EXISTS `featurezme_store`.`microblog`;
CREATE TABLE  `featurezme_store`.`microblog` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userID` int(10) unsigned NOT NULL,
  `userNAME` varchar(45) NOT NULL,
  `blog_content` text NOT NULL,
  `page_name` varchar(45) NOT NULL,
  `page_ID` int(10) unsigned NOT NULL,
  `post_time` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=latin1;

Table page_review

DROP TABLE IF EXISTS `featurezme_store`.`page_review`;
CREATE TABLE  `featurezme_store`.`page_review` (
  `page_review_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `page_id` int(10) unsigned NOT NULL,
  `page_review_by_id` int(10) unsigned NOT NULL,
  `page_review_by_username` varchar(145) NOT NULL,
  `page_review_time` datetime NOT NULL,
  `page_review_content` varchar(555) NOT NULL,
  PRIMARY KEY (`page_review_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

the table Microblog is used to store blog posts & page_review is used to store reviews about pages[Microblog's are in pages].

My requirement is i want to show Blogposts & page reviews sorted by Date [post_time in Microblog & page_review_time on page_review].

how can i do this ? please help me.

Answer 1

Okay I have updated my answear, you can use union as you wanted.

Just have the same number of fields and because the results are placed the one unde the other use fields that make sense to be the one under the other. This example will bring all blogs and reviews created by a specific user (if page_review_by_id actually refers to the user id) and related to a specific page.

( 
SELECT  
`microblog`.`userID`, 
`microblog`.`blog_content` as `blog or review`, 
`microblog`.`post_time`, 
`microblog`.`page_id` 
from `microblog`
where `microblog`.`page_id`='1' and `microblog`.`userID`='1' 
)
union
( 
SELECT  
`page_review`.`page_review_by_id`, 
`page_review`.`page_review_content`, 
`page_review`.`page_review_time`, 
`page_review`.`page_id`
from `page_review`
where `page_review`.`page_id`='1' and `page_review`.`page_review_by_id`='1' 
) 

======== Edit== Suggesting a schema with foreign keys ================

Because I don't see any foreign keys in your schema if I could suggest optionaly a schema that applies foreign keys I present one below.

These rules are supported by this schema:

• Blogs belong to the site and not to the user so there is not on delete cascade applied.
• Blogs are created by users and a foreign key is applied so when a user id is inserted the consistency is assured through the foreign key.
• The same goes for the page, a foreign key is applid without on delte cascade.
• The same goes for the reviews table

• If a user or page is deleted no child row is deleted

  /********Create ***** ***/ CREATE TABLE user ( user_id int unsigned NOT NULL AUTO_INCREMENT, username varchar(16) NOT NULL, userpassword BLOB,
  PRIMARY KEY (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  CREATE TABLE page ( page_id mediumint unsigned NOT NULL AUTO_INCREMENT, title varchar(55) NOT NULL,
  PRIMARY KEY (page_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  CREATE TABLE microblog ( blog_id int unsigned NOT NULL AUTO_INCREMENT, blog_content text NOT NULL,
  date_created datetime NOT NULL, author_id int unsigned NOT NULL, page_id mediumint unsigned NOT NULL, PRIMARY KEY (blog_id), CONSTRAINT blogfk1 FOREIGN KEY (author_id) REFERENCES user (user_id), /NO ON DELETE CASCADE/ CONSTRAINT blogfk2 FOREIGN KEY (page_id) REFERENCES page (page_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  CREATE TABLE page_review ( review_id int unsigned NOT NULL AUTO_INCREMENT, review_content varchar(555) NOT NULL, date_created datetime NOT NULL, author_id int unsigned NOT NULL, page_id mediumint unsigned NOT NULL,
  PRIMARY KEY (review_id), CONSTRAINT reviewfk1 FOREIGN KEY (author_id) REFERENCES user (user_id), /NO ON DELETE CASCADE/ CONSTRAINT reviewfk2 FOREIGN KEY (page_id) REFERENCES page (page_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /************** ******/

  /** ***Insertions*** **/
  INSERT INTO user ( username,userpassword) VALUES ('username11', AES_ENCRYPT('password1', 'encription_key') ), ('username22', AES_ENCRYPT('password2', 'encription_key') );

  INSERT INTO page ( title) VALUES ('title1'),('title2');

  INSERT INTO microblog (blog_content,date_created, author_id,page_id) VALUES ('blogcontent1','2011-2-2 12:00','1','1'), ('blogcontent2','2011-2-2 12:00','2','2');

  INSERT INTO page_review (review_content, date_created,author_id,page_id) VALUES ('reviewcontent1','2011-2-2 12:00','1','1'), ('reviewcontent2','2011-2-2 12:00','2','2'); /***** *******/

  /******* Queries *** / /Help on Identifing a user/ SELECT username FROM user WHERE username ='username22' and userpassword=AES_ENCRYPT('password2','encription_key')

  ( SELECT
  microblog.blog_content as blog or content, microblog.date_created, microblog.author_id, microblog.page_id from microblog where microblog.author_id='1' and microblog.page_id='1' ) union ( SELECT
  page_review.review_content, page_review.date_created, page_review.author_id, page_review.page_id from page_review where page_review.author_id='1' and page_review.page_id='1' )