JulianJ JulianJ - 3 months ago 10
MySQL Question

How to get two variables using MYSQL GROUP_CONCAT?

I have a web page that displays photos

($file)
, 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
$user_id
. 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
($tag)
that liked each photo from
tbl_collab
and then joining those results on
tbl_users
to get the
user_name
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

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!

Comments