On 2/2/09 8:24 AM, "cdg1111us" <cdg111...@yahoo.com> 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" <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.
>>
>
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/
