如何使用日期排序合并两个表 MySQL-PHP
我想将两个表的结果合并为一个。 请参考以下表格:
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 )
表Microblog MySQL查询:
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;
表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;
该表Microblog是用来存储博客帖子和评论的。 page_review用于存储有关页面的评论[微博的评论都在页面中]。
我的要求是我想显示博客文章和内容页面评论按日期排序[微博中的post_time] page_review 上的 page_review_time]。
我该怎么做?请帮我。
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
好的,我已经更新了我的答案,您可以根据需要使用 union。
只需具有相同数量的字段,并且因为结果被放置在另一个使用字段之下,所以将一个字段放在另一个字段之下是有意义的。此示例将带来由特定用户(如果 page_review_by_id 实际上指的是用户 ID)创建的且与特定页面相关的所有博客和评论。
======== 编辑== 建议使用外键的模式 ================
因为如果我可以建议的话,我在您的模式中看不到任何外键我在下面介绍了一种应用外键的模式。
此架构支持这些规则:
如果删除用户或页面,则不会删除子行
/********创建***** ***/
创建表
用户
(user_id
int 无符号 NOT NULL AUTO_INCRMENT,用户名
varchar(16) NOT NULL,用户密码
BLOB,主键(
user_id
)) ENGINE=InnoDB 默认字符集=latin1;
创建表
页面
(page_id
mediumint 无符号 NOT NULL AUTO_INCRMENT,标题
varchar(55) NOT NULL,主键(
page_id
)) ENGINE=InnoDB 默认字符集=latin1;
创建表
微博
(blog_id
int unsigned NOT NULL AUTO_INCRMENT,blog_content
文本不为空,date_created
日期时间不为空,author_id
int 无符号 NOT NULL,page_id
mediumint 无符号 NOT NULL,主键(
blog_id
),约束
blogfk1
外键 (author_id
)参考
用户
(user_id
),/禁止删除级联/
约束
blogfk2
外键 (page_id
)参考
页面
(page_id
)) ENGINE=InnoDB 默认字符集=latin1;
创建表
page_review
(review_id
int 无符号 NOT NULL AUTO_INCRMENT,review_content
varchar(555) NOT NULL,date_created
日期时间不为空,author_id
int 无符号 NOT NULL,page_id
mediumint 无符号非空,主键(
review_id
),约束
reviewfk1
外键 (author_id
)参考
用户
(user_id
),/禁止删除级联/
约束
reviewfk2
外键 (page_id
)参考
页面
(page_id
)) 引擎=InnoDB 默认字符集=latin1;
/************** ******/
/** ***插入*** **/
INSERT INTO
用户
(用户名
,用户密码
)VALUES ('用户名11', AES_ENCRYPT('密码1',
'enscription_key') ),
('用户名22', AES_ENCRYPT('密码2',
'encription_key') );
插入
页面
(标题
)值('标题1'),('标题2');
插入
微博
(blog_content
,date_created
,author_id
,page_id
) 值('blogcontent1','2011-2-2 12:00','1','1'),
('blogcontent2','2011-2-2 12:00','2','2');
插入
page_review
(review_content
,创建日期
、author_id
、page_id
)值('评论内容1','2011-2-2 12:00','1','1'),
('评论内容2','2011-2-2 12:00','2','2');
/******** ****/
/*******查询 ***/
/帮助识别用户/
选择
用户名
FROM
用户
WHERE用户名
='username22'和
userpassword
=AES_ENCRYPT('password2','encription_key')(
选择
微博
。blog_content
作为博客或内容
,微博
.date_created
,微博
.author_id
,微博
.page_id
来自
微博
其中
microblog
.author_id
='1' 和microblog
.page_id
='1')
联盟
(
选择
page_review
。review_content
,page_review
。date_created
,page_review
.author_id
,page_review
。page_id
来自
page_review
其中
page_review
.author_id
='1' 和page_review
.page_id
='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.
======== 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
WHEREusername
='username22'and
userpassword
=AES_ENCRYPT('password2','encription_key')(
SELECT
microblog
.blog_content
asblog or content
,microblog
.date_created
,microblog
.author_id
,microblog
.page_id
from
microblog
where
microblog
.author_id
='1' andmicroblog
.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' andpage_review
.page_id
='1')