Echo rows via JOIN in MySQL/PHP (?)

Question

I wanna be able to echo out if Groupname and Username are connected correctly, where the current userid (saved in a session) is $uid.

I've been sitting for hours trying all kinds of JOINs and the closest I've gotten is having it output 1/? members for each team, but not all of them.

EDIT:

$uid = $_SESSION['uid'];
$sql = "SELECT * FROM group
        INNER JOIN usergroup ON group.groupid=usergroup.groupid
        WHERE usergroup.userid=$uid";
$result=$mysqli->query($sql);

if(mysqli_num_rows($result)>0) {
    while($row = mysqli_fetch_array($result)) {
        $gid = $row['groupid'];
        $sql2 = "SELECT * FROM user
                 INNER JOIN usergroup ON user.userid=usergroup.userid
                 WHERE usergroup.groupid=$gid";
        $result2=$mysqli->query($sql2);
        $row2 = mysqli_fetch_array($result2);

        echo "<td>".$row['groupname']."</td>";
        echo "<td>".$row2['username']."</td>";
        echo "<td>".$row['groupid']."</td>";
    }
}

Thing is, that it kinda works well, except that it doesn't print all the groupmembers names out, it prints out just one. Which one seems to depend on the order in the table.

@trincot Edited. Sorry, I'm not as good as I wish I were. I simply wanna echo out all the groups the user is in, and all the members of that particular group. — Mo Mononoke
– Mo Mononoke, Commented Nov 27, 2015 at 22:22
It is hard to understand exactly the output you want, but your inner query calls mysqli_fetch_array() just once. If multiple rows are expected from that query (as it appears would be the case when a group has more than one member) you will need another while loop inside the first one, like while ($row2 = mysqli_fetch_array($result2)) {// output...} — Michael Berkowski
– Michael Berkowski, Commented Nov 27, 2015 at 22:25
@MichaelBerkowski Well shit. If there's something stalling me, I'll guarantee you it's something quite obvious. Thank you so incredibly much my friend. You've saved my evening. — Mo Mononoke
– Mo Mononoke, Commented Nov 27, 2015 at 22:42

trincot · Accepted Answer · 2015-11-27 22:55:26Z

You did not have a loop on the second query's resultset. However, it is not needed to have a second SQL query. Just do it in one go; SQL was designed for that.

Also, you'll have much simpler code:

$uid = $_SESSION['uid'];
// Select everything you need in one go (join user table as well)
$sql = "SELECT group.group_id, group.groupname, user.username
        FROM group
        INNER JOIN usergroup ON group.groupid=usergroup.groupid
        INNER JOIN user ON user.userid=usergroup.userid
        WHERE usergroup.userid=$uid";
$result=$mysqli->query($sql);

// Don't need to call mysqli_num_rows if you continue like this:
while($row = mysqli_fetch_array($result)) {
    echo "<td>".$row['groupname']."</td>";
    echo "<td>".$row['username']."</td>";
    echo "<td>".$row['groupid']."</td>";
}

Maybe you want to echo some <tr> and </tr> tags, or you"ll have everything in one row, like:

    echo "<tr><td>".$row['groupname']."</td>"
        ."<td>".$row['username']."</td>"
        ."<td>".$row['groupid']."</td></tr>";

divix · Accepted Answer · 2015-11-27 22:49:06Z

There you go: (you were missing nested while loop)

if(mysqli_num_rows($result)>0) {
    while($row = mysqli_fetch_array($result)) {
        $gid = $row['groupid'];
        $sql2 = "SELECT * FROM user INNER JOIN usergroup ON user.userid=usergroup.userid WHERE usergroup.groupid=$gid";
        $result2=$mysqli->query($sql2);

        if(mysqli_num_rows($result2)>0) {
            while($row2 = mysqli_fetch_array($result2)) {    
                echo "<td>".$row['groupname']."</td>";
                echo "<td>".$row2['username']."</td>";
                echo "<td>".$row['groupid']."</td>";
            }
        }
    }
}

Side note: You could achieve the same results with just one SQL query, something like:

SELECT 
    *
FROM
    usergroup ug
INNER JOIN
    user u ON ug.userid = u.userid
GROUP BY
    ug.id

and then in PHP (pseudo code just, do not copy-n-paste)

while($row => mysqli_fetch_array($result)) {
    if (!isset($groupsWithUsers[$row['groupid']['users'])) {
        $groupsWithUsers[$row['groupid']['users'] = array()
    }
    $groupsWithUsers[$row['groupid']['users'][$row['userid']] = $row;
}

Collectives™ on Stack Overflow

Echo rows via JOIN in MySQL/PHP (?)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related