PHP - Multiple rows as string

Hi Experts

Hopefully a simple one for you, my head is not in coding mode today!

I have designed a student tracking system which is working great and I would now like to assign each student to specific groups. Each student can be in one or multiple groups.

I have a 'groups' table which lists each individual group and its name. I have also created a 'group_assignments' table which lists the user ID along with the group ID.

As an example, student 31 is a member of 3 groups and as a reult has 3 rown in the group_assignments table. I can easily represent this data in a repeating table but how would I return the 3 rows as a comma seperated list? ie.

group1
group2
group3

becomes

group1, group2, group3

Many thanks Experts.
sirnutty1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gr8gonzoConsultantCommented:
I would just do this in PHP. Here's a mockup of the query you might use (I don't know all the field names, so I just guessed):

<?
$studentGroups = array();
$rs = mysql_query("SELECT studentID,ga.groupID,groupName FROM group_assignments ga LEFT JOIN groups g ON g.groupID=ga.groupID WHERE your_where_logic_goes_here")
while($row = mysql_fetch_assoc($rs))
{
  $studentGroups[$row["studentID"]][$row["groupID"]] = $row["groupName"];
}

// Now $studentGroups should look like this:
// Timmy (Student ID 5 who belongs to groups 2 "Science" and 3 "Math"):
// $studentGroups[5][2] = "Science";
//        ............           [3] = "Math";

$timmyGroupsString = join(", ",$studentGroups[5]);
print "Timmy belongs to {$timmyGroupsString}";

// This will result in:
// Timmy belongs to Science, Math

?>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bljakCommented:
Do you want this as return result from SQL?
Or do you want to do this in PHP?
0
sirnutty1Author Commented:
Many thanks gr8gonzo

I am using Dreamweaver throughout the site so the recordsets are quite formatted. Could you possibly take a look at the code below and in terms of achieving the same result?

Sincere thanks
$student_rsGroupAssignments = "31";
if (isset($student_id)) {
  $student_rsGroupAssignments = $student_id;
}
mysql_select_db($database_connEACKDK, $connEACKDK);
$query_rsGroupAssignments = sprintf("SELECT g.group, g.active FROM group_assignments ga JOIN groups g ON g.id = ga.group WHERE ga.id = %s AND g.active = 1", GetSQLValueString($student_rsGroupAssignments, "int"));
$rsGroupAssignments = mysql_query($query_rsGroupAssignments, $connEACKDK) or die(mysql_error());
$row_rsGroupAssignments = mysql_fetch_assoc($rsGroupAssignments);
$totalRows_rsGroupAssignments = mysql_num_rows($rsGroupAssignments);

Open in new window

0
sirnutty1Author Commented:
Just had a chance to take a more detailed look at your solution and worked it out as follows as below.

Many thanks for your help!!


<?php do { ?>
<?php $groups[$row_rsGroupAssignments["id"]] = $row_rsGroupAssignments["group"]; ?>
<?php } while ($row_rsGroupAssignments = mysql_fetch_assoc($rsGroupAssignments)); ?>
<?php 
$groups = implode(", ",$groups);
echo $groups;
?>

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.