Query help with scores - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Query help with scores (/showthread.php?tid=30006) |
Query help with scores - El Forum - 04-29-2010 [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 Thanks again! Query help with scores - El Forum - 04-29-2010 [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. Query help with scores - El Forum - 04-29-2010 [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 Query help with scores - El Forum - 04-29-2010 [eluser]JamieBarton[/eluser] Thanks both 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 Query help with scores - El Forum - 04-29-2010 [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 Code: member_id name birdies Query help with scores - El Forum - 04-29-2010 [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 Code: member_id name birdies 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 Query help with scores - El Forum - 04-29-2010 [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 */ Query help with scores - El Forum - 04-30-2010 [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 Query help with scores - El Forum - 04-30-2010 [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); |