• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Query help with scores

#1
[eluser]JamieBarton[/eluser]
Hi guys!

I'm currently creating a website for a golf society that stores all of the scores for each round that members play in a database.

I want to create a leader board calculating the total number of birdies (1 under par) that each member has achieved throughout their rounds.

For example:

Member Name / No of birdies

Daniel Grant / 6
Steven Page / 5
Michael Harp / 5
Jamie Spence / 4

My database structure as is follows:

Holes
hole_id
hole_par

Scores
score_id
member_id
hole_id
meeting_id
nett_score
gross_score

I need to calculate the number of times each members gross_score is 1 less than the hole_par of that particular hole. I then need to count how many times each member achieved this and display it in the table.

Any help would be greatly appreciated Smile

Thanks again!

#2
[eluser]nevsie[/eluser]
i would have thought you would actually like to count anything under par for this...

But depending upon how you caching this i think you are maybe going about this a little backward. Everytime someone runs this query a bugger load of processing will need to be done - whereas if you calculate how many birdies someone has had when they enter the score, you only process it the once, and can store that value in a field ready to access... This might be as a value, or as a birdy flag (1/0) or maybe a shot count (-3,-2,-1,0,1,2,3,4,5,6,etc).

Then you just query the number of shots of -1 or below, group them by member id and meeting id (accumulating the result and use that...

You can do it your way, and it might be possible to do this with lower overheads than i am currently thinking of, but this seems a good alternative direction to me.

#3
[eluser]Kevv[/eluser]
Something like this should do what you want;
Code:
SELECT s.member_id, 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
WHERE h.hole_id=2 /* limit to specific hole */
GROUP BY s.member_id
ORDER BY COUNT(s.member_id) DESC
You'll probably want to add another join to get the member name too.

#4
[eluser]JamieBarton[/eluser]
Thanks both Smile

I think what nevsie was probably the better way to do things, however, to begin with, before I change all of my database..


Kevv,

How would I go about showing these results now?

I'm wanting to show the member name (id for now) and number of birdies..


Thanks again Smile

#5
[eluser]Kevv[/eluser]
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.

#6
[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 Smile

#7
[eluser]Kevv[/eluser]
[quote author="Jamie B" date="1272567810"]
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.[/quote]
Depends on how you query the database, but a simple example would be something like;
Code:
$result = mysql_query($query); /* where $query is the query above */
if($result) {
  while($row=mysql_fetch_assoc($result)){
    echo $row['name'] . ' : ' . $row['birdies'];
  }
}
else {
  die(mysql_error());
}
mysql_free_result($result);

#8
[eluser]JamieBarton[/eluser]
Thanks for the in-depth response and your help so far with this.

I'm having issues still, if I do a var dump on the result I am getting 'resource(3) of type(Unknown)'.

Not sure what to do next..

Thanks again Smile

#9
[eluser]Kevv[/eluser]
Yeah, mysql_query() returns a resource (think of it like a resultset pointer), to get the actual data you then need to fetch each row like I've done above or you can grab them all at once;
Code:
$result = mysql_query($query);
$leaderboard = mysql_fetch_array($result);
There may well be a better way tbh I'm not overly familiar with "raw" mysql access.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.