Query help with scores

[eluser]JamieBarton[/eluser]
[quote author="Kevv" date="1272567605"]Assuming you've got a members table, this query;

Code:

SELECT s.member_id, m.name, COUNT(s.member_id) AS 'birdies' FROM scores s
JOIN holes h ON s.hole_id=h.hole_id AND s.gross_score+1=h.hole_par
JOIN members m ON s.member_id=m.member_id
WHERE h.hole_id=2 /* limit to specific hole */
GROUP BY s.member_id
ORDER BY COUNT(s.member_id) DESC

would give you a resultset like this (ignore my dummy member_id's)

Code:

member_id   name          birdies
---------   ----          -------
1           Daniel Grant  6
4           Steven Page   5
2           Michael Harp  5
3           Jamie Spence  4

Which should be just the data you need to plug into your leaderboard view.[/quote]


Yeah, I'm just trying to do everything from what you're teaching me in this thread, but in the PHP, what do I need to do to echo those member id's and birdies out?

Say just normal php, outside of CI, doing a while loop on the query.

Thanks