Solved

PHP - Multiple rows as string

Posted on 2009-05-06
4
262 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:sirnutty1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
gr8gonzo earned 500 total points
ID: 24314832
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
 
LVL 4

Expert Comment

by:bljak
ID: 24314842
Do you want this as return result from SQL?
Or do you want to do this in PHP?
0
 

Author Comment

by:sirnutty1
ID: 24315317
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
 

Author Comment

by:sirnutty1
ID: 24315554
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question