I have successfully accomplished my first step (link to my G+ post, but you don't have to read it. Everything is here). Now there are some complications. This is demo table:
ID | userid | Age | Name --------------------------- 1 | 1 | 30 | John 2 | 1 | 31 | Mike 3 | 1 | 30 | Whoever 4 | 2 | 32 | Jack 5 | 2 | 31 | Alice 6 | 3 | 30 | Kurt
I would like to display only rows with userid = 1
+ persons with the same age have to be in the same row:
userid | Age | Name --------------------------------- 1 (hidden) | 30 | John, Whoever 1 (hidden) | 31 | Mike
In my database I'm using subject
and grade
instead of age
and name
. This is how my MySQL query looks right now:
SELECT
p.subject as 'subject'
GROUP_CONCAT(grade) as grades
FROM grades p
GROUP BY p.subject
This displays data for every userid
. Userid
isn't always 1 so I can't just say if($userid == 1){/*code*/}else{/*code*/}
or anything similar.
How can I accomplish this?
Some PHP code I'm using to display data:
<?php
$result = $dbc->query("
SELECT
p.subject as 'subject',
id, finished, date,
GROUP_CONCAT(grade) as names
FROM grades p
GROUP BY p.subject
");
?>
<table>
<tr>
<th>Subject</th>
<th>Grade</th>
</tr>
<?php while($row = $result->fetch_assoc()){
$names = split(",",$row["names"]);
?>
<tr>
<td><?php echo $row["subject"] ?> </td>
<td><?php foreach( $names as $name){echo $name . ' ' ; } ?>
</tr>
<?php } ?>
</table>?
Ok I have been playing around with this and I have got it
Looks like you want to group by both user id and age so that you can perform aggregates on data which both have a specific userid and age.
I have included the code to create the table and insert the data incase anyone reading the question wanted to have a quick play.
create table grades
(
id int(2),
userid int(2),
age int(2),
name varchar(20),
)
insert into grades values
(1,1,30,'John'),
(2,1,31,'Mike'),
(3,1,30,'Whoever'),
(4,2,32,'Jack'),
(5,2,31,'Alice'),
(6,3,30,'Kurt');
SELECT
userid, age, GROUP_CONCAT(name)
FROM grades
GROUP BY userid, age
http://sqlfiddle.com/#!9/7d9ac/3
SELECT Age, GROUP_CONCAT(Name)
FROM grades
WHERE userid=1
GROUP BY Age