CODEWITHSUNDEEP
phphtmlmysqlmysqli
Mo Mononoke
Mo Mononoke

Reputation: 175

Echo rows via JOIN in MySQL/PHP (?)

enter image description here

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.

Upvotes: 0

Views: 75

Answers (2)

trincot
trincot

Reputation: 350365

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>";

Upvotes: 1

divix
divix

Reputation: 1364

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;
}

Upvotes: 0

Related Questions