Reputation: 6398
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.
Upvotes: 0
Views: 937
Reputation: 1462
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:
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'
)
Upvotes: 1