If I can follow correctly what you're doing here, I think this is what you need to do.
Your tables: PICKS --- id (auto-increment, primary key -- add if you don't have it) userid gameid pickedteamid won (you need to add this column to your prev table) RESULTS --- id (auto-increment, primary key -- add if you don't have it) gameid winningteamid STATS (you need to add this table to your database) --- id (auto-increment, primary key) userid gamesplayed gameswon winpercentage First, update your picks table to show whether people have winning guess: update picks, results set picks.won = '1' where picks.pickedteamid = results.winnerid Then, load all new players into your STATS table: insert into stats (userid) SELECT DISTINCT picks.userid FROM picks WHERE picks.userid NOT IN (SELECT stats.userid FROM stats ) Now, just update your STATS table: update stats, (SELECT userid, count( gameid ) as count FROM picks GROUP BY userid) as tmp set gamesplayed = count where stats.userid = tmp.userid update stats, (SELECT userid, count( won ) as count FROM picks where won='1' GROUP BY userid) as tmp set gameswon = count where stats.userid = tmp.userid update stats set winpercentage = (gameswon / gamesplayed) Your final SELECT: select userid, winpercentage, gamesplayed from stats order by winpercentage desc, gamesplayed desc Hope this helps! Ken Krauss KCWebDev http://www.kcwebdev.com --- In php_mysql@yahoogroups.com, "John Magee" <jema...@...> wrote: > > Ok > > I may have done this wrong - but I'm looking for some direction (not a > specific solution) to > see if I can fix what I've done. > > I've created a (what I thought at least) simple web site where players pick > the winners on > the daily NBA schedule. > > What I want to be able to do is list the 'standings' based on the percentage > of picks a > player has correct, and each player will have a different number of picks. > > I have a 'picks' table - the relevant fields > UserID > GameID > PickedTeamID > > I have a 'results' Table with the relevant fields > GameID > WinnerID > > What I need is a query that gets all the picks that a user has made - as a > sum/total - and > also gets the number of 'correct' (based on winner ID for gameID) picks a > user has made > so i can calculate the percent correct. > > I can write the PHP for an individual user - i could write the PHP to list > all the users in any > particular order - but what I want to list is based on that percentage which > means (I think) > that I need to have ALL the values in MySQL so i can order by before moving > to the PHP to > display the data (I calculate an individuals percentage using PHP > /SessionID)... > > I'm hoping i can get some help / direction here. I've tried some outer and > left and right > joins and either I worded them improperly or it's just not the right > direction. > > I also hope this makes sense. >
