JulianJ JulianJ -4 years ago 106
MySQL Question

How to get two variables using MYSQL GROUP_CONCAT?

I have a web page that displays photos

, below each photo is a list of users who 'liked' each photo.
Each user is displayed as an html link to their profile. Currently the users are displayed and linked by their user_names:

<a href=\"profile.php?user_name=".$tag.">".$tag."</a>

But I am wondering how I can display the users' name but link to their profile using their
. Something like

<a href=\"profile.php?user_name=".$user_id.">".$tag."</a>

I am using GROUP_CONCAT in the query to get all the user_id's
that liked each photo from
and then joining those results on
to get the
but I can't figure out how to display both the user_id and user_name in the html links. Can anyone tell me how this can be done?

//Database Query
$sql="SELECT up.file,p.user_name,p.user_id, GROUP_CONCAT(cp.user_name)
FROM tbl_uploads up
LEFT JOIN tbl_users p ON up.user_id = p.user_id
LEFT JOIN tbl_collab c ON up.file = c.file
LEFT JOIN tbl_users cp ON cp.user_id = c.collab_userid
GROUP BY up.file";

$result = $mysqli->query($sql);
while($row = $result->fetch_array())
$user_id = explode (",", $row['user_id'] );
$user = explode (",", $row['user_name'] );
$files = explode (",", $row['file']);
$tag_array = explode(',' , $row['GROUP_CONCAT(cp.user_name)']);

foreach($files as $file) {

//File is displayed here

foreach($tag_array as $tag) {

//Links to the users who liked the file

<a href=\"profile.php?user_name=".$tag.">".$tag."</a>


Answer Source

in your SQL get both values in your GROUP_CONCAT:

GROUP_CONCAT(CONCAT(cp.user_id,'~',cp.user_name) SEPARATOR '|') AS tagGroup

in your foreach for the tag array:

$tag_array = explode('|' , $row['tagGroup']);

foreach($tag_array as $tag) {
    list($uid,$uname) = explode('~',$tag,2);

    echo "<a href=\"profile.php?user_id=".$uid.">".$uname."</a>";

(updated to use @Jakumi's comments and more example for clarity -- note i used '|' as the GROUP_CONCAT separator, as the character escaping issues for "\n" are cumbersome here)

good luck!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download