mysql - 从三个表中选择 - mysql 和 php

Question

我有下面的三个表，我希望从中获得以下选择:

1.) 从 collaboration 中选择所有项目，并按给定用户的 group_members 中的 group_id 对它们进行分组
这意味着当用户登录时，他将看到所有(且仅)collaboration 项目属于他是组成员的组。

2.) 对于每个 group_id，选择所有协作项目。
这意味着当用户从上面的 1 中选择任何组 (group_id) 时，他将看到属于所选组 (group_id)

约束:每个用户必须是组成员。 users 表用于提供用户的 firstname 和 lastname。

这是我试了1次都没用!

    function OrderByGroup_id($username) {      $data = array();      $currenttime = time();      $q = "      SELECT *      FROM collaboration      INNER JOIN group_members ON collaboration.group_id = group_members.group_id      INNER JOIN users ON users.username = group_members.username      WHERE collaboration.parent_id IS NULL and collaboration.is_comment = 0       AND group_members.username = :user group by collaboration.group_id";      $sq = $this->connection->prepare($q);      $sq->execute(array(':user' => $username));    while($row = $sq->fetch()) {      $json = array();      $json['title'] = $row['title'];      $json['question'] = $row['content'];      $json['firstname'] = $row['firstname'];      $json['lastname'] = $row['lastname'];      $json['timestamp'] = $row['timestamp'];      $json['key'] = $row['group_id'];      $data[] = $json;     }      $allposts =json_encode($data);      return $allposts= json_decode($allposts, true);    }

这是表格

CREATE TABLE IF NOT EXISTS `collaboration` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `parent_id` int(11) DEFAULT NULL,  `group_id` varchar(255) DEFAULT NULL,  `author` varchar(30) NOT NULL,  `title` varchar(255) DEFAULT NULL,  `content` text NOT NULL,  `is_comment` tinyint(1) unsigned NOT NULL,  `file` tinyint(1) unsigned NOT NULL DEFAULT '0',  `points` int(11) DEFAULT NULL,  `timestamp` int(11) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `users` (  `firstname` varchar(30) NOT NULL,  `lastname` varchar(30) NOT NULL,  `username` varchar(30) NOT NULL,  PRIMARY KEY (`username`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `group_members` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(30) NOT NULL,  `group_id` varchar(50) NOT NULL,  `status` tinyint(1) unsigned NOT NULL,  `timestamp` int(11) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

感谢您的意见!

Answer 1

以下选择确实提供了问题 1 的请求。但是，我知道查询不是最优的，因为我确实选择了目前不需要的条目。例如使用 *
我只想选择 title、content、timestamp、group_id，来自 collaboration 和 firstname、lastname，来自 users

        SELECT * FROM collaboration    INNER JOIN group_members ON collaboration.group_id = group_members.group_id    INNER JOIN users ON users.username = collaboration.author    WHERE collaboration.parent_id IS NULL and collaboration.is_comment = 0     AND group_members.username = :user     GROUP BY collaboration.group_id    ORDER BY collaboration.timestamp DESC