如何使用日期排序合并两个表 MySQL-PHP

发布于 2024-12-10 13:40:58 字数 1717 浏览 1 评论 0原文

我想将两个表的结果合并为一个。 请参考以下表格:

    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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

许你一世情深 2024-12-17 13:40:58

好的,我已经更新了我的答案,您可以根据需要使用 union。

只需具有相同数量的字段,并且因为结果被放置在另一个使用字段之下,所以将一个字段放在另一个字段之下是有意义的。此示例将带来由特定用户(如果 page_review_by_id 实际上指的是用户 ID)创建的且与特定页面相关的所有博客和评论。

( 
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' 
) 

======== 编辑== 建议使用外键的模式 ================

因为如果我可以建议的话,我在您的模式中看不到任何外键我在下面介绍了一种应用外键的模式。

此架构支持这些规则:

  • 博客属于站点而不属于用户,因此不应用删除级联。
  • 博客由用户创建并应用外键,因此当插入用户 ID 时,通过外键确保一致性。
  • 页面也是如此,应用外键时无需使用增量级联。
  • 评论表也是如此
  • 如果删除用户或页面,则不会删除子行

    /********创建***** ***/
    创建表用户 (
    user_id int 无符号 NOT NULL AUTO_INCRMENT,
    用户名 varchar(16) NOT NULL,
    用户密码 BLOB,
    主键(user_id
    ) ENGINE=InnoDB 默认字符集=latin1;

    创建表页面 (
    page_idmediumint 无符号 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_idmediumint 无符号 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_idmediumint 无符号非空,
    主键(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_idpage_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_reviewreview_content
    page_reviewdate_created
    page_review.author_id,
    page_reviewpage_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.

( 
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'
    )

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文