I did see your posts in another email list discussing this after I answered your question here. Like so many things, there is more than one way to do this however if your site gets big enough, you're not going to be able to do all your calculations on the fly as the page is displayed. After your site gets enough traffic, you're going to need to do the calculations at an off-peak time so that you can use a simple SELECT statement to show the results. This is one of the main premises of data warehousing. In the solution I gave you, you can set up the number crunching in a CRON job or something in the middle of the night when the server isn't busy and this will allow you to serve many more users without changing your methods.
Best of luck, Ken Krauss KCWebDev http://www.kcwebdev.com --- In php_mysql@yahoogroups.com, John Magee <jema...@...> wrote: > > > > > On 2/2/09 8:24 AM, "cdg1111us" <cdg111...@...> wrote: > > > 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" <jemagee@> 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. > >> > > > > I did figure this out as I posted earlier using PHP and MySQL - however on a > mysql devoted forum (devshed.com) I was given a single line MySQL query that > does all the array work I did before - using case and subqueries which I > haven't been able to find tutorials on (CASE in a query that is) yet...i > went with getting it to work as quickly as possible but now I need to > rewrite the page. > > Anyone interested the site is at > > http://www.basketballjourney.com/PickEm/ >
