Extracting 'group by' query results in a table
Posted: Thu Feb 23, 2006 12:40 am
feyd | Please use
The data currently returned is:
username round number points
There will eventually be numerous rows of data returned for each username. ie for each user there will be up to 26 rounds and 26 points that are relevant.
I need to reference username in a table once only with the related 'round'
and 'points' data in the same row and then total the points for each username. eg
I'm currently using mysql_fetch_assoc() like this.
I'm not sure how to get the data in the correct row/column and only displaying 'username' once.
Would a foreach loop help with this? If so how would this work?
Really appreciate any suggestions
David
feyd | Please use
Code: Select all
andCode: Select all
tags where appropriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
Hi all
I currently have a query that looks something like this:Code: Select all
SELECT tips.username, fixtures.round,
SUM(CASE
WHEN tips.tip='draw' AND fixtures.result='draw' then 1
WHEN tips.tip=fixtures.result then 1
else 0 end) points
FROM fixtures
JOIN tips on (fixtures.round=tips.round AND fixtures.game=tips.game)
GROUP BY tips.username, fixtures.round
LIMIT 0,50username round number points
Code: Select all
Angela 10 8
Angela 11 2
ehmer 10 5
ehmer 11 7I need to reference username in a table once only with the related 'round'
and 'points' data in the same row and then total the points for each username. eg
Code: Select all
username round10 round11 round12 round13 total
Angela 8 2 -- -- 10
ehmer 5 7 -- -- 12I'm currently using mysql_fetch_assoc() like this.
Code: Select all
while ($row = mysql_fetch_assoc($result)) {
echo $row["username"];
echo $row["round"];
echo $row["points"];
}Would a foreach loop help with this? If so how would this work?
Really appreciate any suggestions
David
feyd | Please use
Code: Select all
andCode: Select all
tags where appropriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]