MySQL JOIN

[bigheadedd]

Hi,

 

Been looking for a little while on this problem, but can't seem to find a solution.

 

Ok, so I have two tables,

 

gallery & users

 

Gallery contains images, image info etc.

Users contains usernames, emails etc.

 

In users there is a 'photo' column, which references to an ID in the gallery table. This is so each user can have a 'profile' image from a bank of images stored in gallery.

 

What i'm trying to do is have a query that will output all of the images, and then show the usernames of assigned to each photo if there is one.

 

Heres what i've got:

 

$result = mysql_query("SELECT *,COUNT(photo) AS usercount FROM gallery LEFT JOIN users ON gallery.img_id=users.photo GROUP BY img_id ORDER BY date_uploaded ASC");

 

Its all fine, and displays the username, though if there are multiple users using the same photo, it only shows one username.

However, when displaying 'usercount', it will show that there are 2+ or whatever.

 

Short of nesting queries within other loops, is there an alternative solution??

 

Thanks!

Edd

[requinix]

Depends what you want to do with those names. If you're satisfied with a simple list like "Alice, Bob, Cindy" then you can use GROUP_CONCAT like

SELECT gallery.*, GROUP_CONCAT(username SEPARATOR ", ") AS usernames, COUNT(1) AS usercount...

[Psycho]

Hmm, never used GROUP_CONCAT, I'll have to keep that in mind.

 

However, you may need more than just the name. For example, listing the user names with each being a link to the user's profile page would be a common feature. In this case you would only need a simple JOIN. (Also, don't use * in your select queries unless you really need all the data - otherwise you are wasting resources). And, give yourself a fighting chance to debug errors by creating your queries as variables so you can echo to the page if needed.

 

Anyway, the key to the logic is in the output process to 'detect' when there is a change in the parent record (in this case the photo) so you don't duplicate those values. This is only a little trickier since you want a count of users. The logic could look something like this [not tested]:

 

function displayPhotoAndUsers($photo, $users)
{
    $userCount = 0;
    $userOutput = '';
    foreach($users as $user)
    {
        $userCount++;
        $userOutput .= "<li><a href='show_profile.php?id={$user['userID']}'>{$user['username']}</a></li>\n";
    }

    $output = "<h1>{$photo}</h1>\n";
    if(!$userCount)
    {
        $output .= "No users are using this photo.<br>\n";
    }
    else
    {
        $output .= "There are {$userCount} users who are using this photo:\n";
        $output .= "<ul>{$userOutput}</ul>\n";
    }
    return $output;
}

$query = "SELECT p.photo, u.username, u.userID  AS usercount
          FROM gallery AS g
          LEFT JOIN users AS u ON g.img_id=u.photo
          ORDER BY date_uploaded ASC";
$result = mysql_query($query);

$photo = '';
while($row = mysql_fetch_assoc($result))
{
    if($photo != $row['photo'])
    {
        //This photo is different from last
        if($photo!='') //Skip 1st since there was no preceeding data
        {
            //Display data for last photo
            echo displayPhotoAndUsers($photo, $userData);
        }
        //Reset/create $photo var and temp user array
        $photo = $row['photo'];
        $userData = array();
    }

    //Add user data for this photo
    if($row['username']!='') //Don't include nulls (i.e. no users have that photo)
    {
        $userData[$row['userID']] = $row['username'];
    }
}

//Display the last photo
echo displayPhotoAndUsers($photo, $userData);

[bigheadedd]

Thats great, thank you to both of you!

That did the trick perfectly, thanks!

 

Edd