title: Select from three tables - mysql and php
body: <p>I've got the three tables below from which I wish to get the following selects: <br /><br />
  1.) Select all items from <code>collaboration</code> and group them by <code>group_id</code> in <code>group_members</code> for a given user<br />
  This means that when the user logs in, (s)he will see all (and only) <code>collaboration</code> items belonging to the groups where he is group member.<br /><br />
  2.) For each <code>group_id</code>, select all collaboration items.<br />
  This means that when a user selects any group (<code>group_id</code>) from 1 above, he's going to see all the <code>collaboration</code>items belonging to the selected group (<code>group_id</code>)<br /><br /></p>
  
  <p>Constraints: Each user MUST be a group member. The <code>users</code>table is there to supply <code>firstname</code>and <code>lastname</code> of user.<br /><br /></p>
  
  <p>This is what I tried for 1 to no avail! <br /><br />
  <br />
  <br /></p>
  
  <pre>
  
      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);
      }
  
  </pre>
  
  <p><br />
  Here are the tables 
  <br /></p>
  
  <pre>
  
  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;
  </pre>
  
  <pre>
  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;
  </pre>
  
  <pre>
  
  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;
  </pre>
  
  <p>Thanks for your input!</p>