Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12
  1. #1
    Junior Member weblebowski's Avatar
    Join Date
    Mar 2012
    Posts
    19
    Member #
    31067
    Liked
    1 times
    I am attempting to check results of sporting events by comparing the final scores (when entered) of the games to the selections made by the users. if correct, I want to add one to total of each record in 'overall_rec' column in users table, otherwise leave as is. the code below works logically, the only problem i am having now is that the number in the overall_rec column is being updated every single time the page is viewed (essentially multiplying each users' number of wins on every refresh; see lines 62 and 69, commented out right now). i only want this to happen once for each game having been completed. how can i do this?

    PHP Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>

    <style>

    body { font-family: Verdana, Arial, sans-serif; }

    table, th, tr, td { border-collapse: collapse; }
    .su_win { background-color: yellow; }
    .ats_win { background-color: #94d67a; }
    .loss { color: red; border-color: black; text-decoration: line-through; }

    </style>

    </head>

    <body>

    <?php

    //connects to Db
    $db_host "localhost";
    $db_username "root";
    $db_pass "";
    $db_name "pete_pool";

    @
    mysql_connect("$db_host","$db_username","$db_pass") or die("Could not connect to MySQL");
    @
    mysql_select_db("$db_name") or die("No database");
       
    $query =    "SELECT * FROM picks
        INNER JOIN schedules
            ON picks.game_id = schedules.game_id
        INNER JOIN players
            ON picks.user = players.user_name"
    ;
     
    $result mysql_query($query) or die(mysql_error());
      
    echo 
    "<table border cellpadding=3>";
    echo 
    "<tr>";
    echo 
    "<th>User</th>";
    echo 
    "<th>Game</th>";
    echo 
    "<th>SU</th>";
    echo 
    "<th>ATS</th>";
    echo 
    "</tr>";

    while(
    $row mysql_fetch_array($result)){
     
    // add one to overall_rec column for every win

    if ($row['su'] == $row['home_team']) {
        if (
    $row['home_score'] > $row['away_score']) {
            
    //mysql_query("UPDATE players SET overall_rec = overall_rec + 1 WHERE user_name='{$row['user']}'");
      
        
    }
    } else if (
    $row['su'] == $row['away_team']) {
        if (
    $row['home_score'] < $row['away_score']) {
            
    //mysql_query("UPDATE players SET overall_rec = overall_rec + 1 WHERE user_name='{$row['user']}'");
     
        
    }
    }
     
    // display picks in html table
        
    echo "<tr><td>".$row['user']."</td> ";
        echo 
    "<td>".$row['home_team']." ".$row['spread']." vs. ".$row['away_team']." (".$row['ml_odds'].")</td> ";

    if (
    $row['su'] == $row['home_team']) {
        if (
    $row['home_score'] > $row['away_score']) {
            echo 
    "<td class='su_win'>"// highlight su wins yellow
        
    } else {
            echo 
    "<td class='loss'>"// strikethrough losses
        
    }
    } else if (
    $row['su'] == $row['away_team']) {
        if (
    $row['home_score'] < $row['away_score']) {
            echo 
    "<td class='su_win'>";    // highlight su wins yellow
        
    } else {
            echo 
    "<td class='loss'>"// strikethrough losses
        
    }
    }

        echo 
    $row['su']."</td> ";

    if (
    $row['ats'] == $row['home_team']) {
        if (
    $row['home_score'] + $row['spread'] > $row['away_score']) {
            echo 
    "<td class='ats_win'>"// highlight ats wins green
        
    } else {
            echo 
    "<td class='loss'>"// strikethrough losses
        
    }
    } else if (
    $row['ats'] == $row['away_team']) {
        if (
    $row['home_score'] + $row['spread'] < $row['away_score']) {
            echo 
    "<td class='ats_win'>"// highlight ats wins green
        
    } else {
            echo 
    "<td class='loss'>"// strikethrough losses
        
    }
    }

        echo 
    $row['ats']."</td></tr>";

    }

    echo 
    "</table>";

    ?>
     
    </body>
    </html>

  2.  

  3. #2
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,716
    Member #
    5580
    Liked
    718 times
    I think the answer to this is in the structure of your tables.

    Can you list the column names of each table?

    My thought process here ...
    You have one table for all of the games and scores (you call it 'schedules').
    You have one table for all of the players (you call it 'players').
    You have one table for the picks (you call it 'picks').

    It's the 'picks' table that will be the key to this.

    A row is INSERTED each time a player makes a pick.
    unique Row ID
    Player name or ID
    The game they picked
    Whether they picked home or away
    Result (1 or 0)

    The 'Result' is a zero (0) when the row is inserted.
    After the game is played, you update the 'schedules' table with the game result (home win or away win).
    You then update all rows in the 'picks' table where the game they picked matches the schedule and the game result matches their pick. If all of them match, you update the Result column to one (1).
    This updates ALL rows where the game result matches the ones picked.

    So you don't have a column called "overall_rec" ....
    You only need to query 'picks' for a particular user name and add up the "Result" columns that contain a one (1). The 1 means they successfully picked the correct game result.

    There is another advantage to doing this ... you can give stats on how many they picked correct, and how many they picked wrong. Also, do other queries on specific ranges of dates, or games.
    You can also add another column in "picks" table for a timestamp - when the player made the pick.
    It might be important to know if the pick was made 1 month before the game, or 1 week before the game.

    Your 'picks' table will become quite large, but that's OK.
    If you have 200 people picking each game for one season, you might have 20000 rows.
    That's no big deal for MySQL.
    After the season is over, you can squirrel it away in an archive and start with a fresh 'picks' table.
    Your "players" table remains intact for the next season.
    Your "schedules" table gets updated for the next season's schedule.
    You refer back to the previous year's archived "picks" table to let players compare their statistics.


  4. #3
    Junior Member weblebowski's Avatar
    Join Date
    Mar 2012
    Posts
    19
    Member #
    31067
    Liked
    1 times
    thanks for the reply. i think i kinda know what you are getting at. right now my table structure looks like this:

    PLAYERS
    -user
    -pw
    -overall_rec
    -ats_rec

    SCHEDULES
    -game_id
    -time
    -week_number
    -home_team
    -away_team
    -home_score
    -away_score

    PICKS
    -user
    -pick_id
    -game_id
    -su (this is the pick)
    -ats (second pick against a point spread, not worrying about this yet)

    so if i add a new column to 'schedules' called 'game_result' can i still have that update by putting in the scores? could u show me how the code would look in your scenario if you don't mind?

  5. #4
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,716
    Member #
    5580
    Liked
    718 times
    Let's just look at this:

    SCHEDULES
    -game_id
    -time
    -week_number
    -home_team
    -away_team
    -home_score
    -away_score

    When you create that game in the schedule, you have all columns filled-in except
    the home_score and away_score. You won't know those until the game has been played.

    After the game is over, you can fill in the two scores. You don't need a 'game_result'
    column because you already know the two scores 'home_score' and 'away_score'.

    Later on, you will do a query to determine the outcome where 'home_score' is > 'away_score' or
    'home_score < away_score' ... if ties are possible ... home_score = away_score.

    So is your question about "how to put in those scores"?

    Or is your question about "how to use those two scores to update the "points" for any
    user in the "picks" table that correctly picked the winner or loser?
    Likewise for point spread. Each row can have a value assigned to 'ats_points'.

    In my scenario, you have some new columns in "picks" ... see the reds one I entered:

    PICKS
    -user
    -pick_id
    -game_id
    -su (this is the pick)
    -ats (second pick against a point spread, not worrying about this yet)
    -points
    -ats_points

    Each time you (admin) or some event occurs, the PHP script goes through the 'schedules'
    table and looks for all games that have been played. It determines who won or lost by
    the 'home_score' VS 'away_score'. It then loops through all 'picks' to determine which
    rows correctly guessed the outcome. If everything matches, including the point spread,
    it updates the 'points' column for that particular row (or 'ats_points').

    If you query a 'user' in the picks table, it will give you a total (accumulative) score by
    adding up the 'points' column.

    So, as I mentioned before, there is no 'overall_rec' column.

    Describe which part you don't understand.
    It might be hard for me to give a working example, but maybe just understanding the MySQL UPDATE query might be all you need to continue the scripting.


  6. #5
    Junior Member weblebowski's Avatar
    Join Date
    Mar 2012
    Posts
    19
    Member #
    31067
    Liked
    1 times
    well as i had before, i was trying to update the overall_rec column by one every time someone got a pick correct. so say i add 'points' column to 'picks 'table. if everything checks out, update this column to 1, otherwise it stays 0. i think this is what you are saying. So then how do I get the total from points column for a particular user? i.e. how do you add up the wins?

  7. #6
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,716
    Member #
    5580
    Liked
    718 times
    Your problem with the -overall_rec column is that you won't know if you already counted
    a pick or not. That's why you are adding one each time. How does it know that you already
    counted that pick? It doesn't.

    So, you are correct in adding a 'points' column to the 'picks' table.
    For each 'user' id in the 'picks' table, you GROUP them and add the 'points' column as part of each group.

    Scenerio ...

    You have already entered 130 games into the 'schedule' table. None of them have yet been played.

    Let's say you have 40 users in your database (in the 'players' table).
    Each one of those players start picking games. In fact, they are picking
    several games, or all of the games. Each pick they make is adding a row
    in the 'picks' table. Your 'picks' table builds-up to 5000 or more rows.

    As the games are played, you update the 'schedule' table with the scores.
    Each time you do that, the PHP script also goes through the 'picks' table and
    finds out which 'users' have successfully determined the outcome. At that
    point you update the 'points' column for those rows.

    At any time, you can see the score for an individual player by querying 'picks' for
    a 'user' (player name) and a then a total of the 'points' column. That can be done
    with one query.

    At any time you can see the entire players scorecards by a query that does a GROUP
    by 'user' and a total 'points' count. The result of that query will be 40 rows, because
    the 'picks' table would only have that many individual 'user' (player names). But
    each of those 40 rows will have a value for the total of the 'points' column. That total
    value you get for each player is your 'overall record' for each player. Part of another
    query might be to add 'points' based on the user's point spread. You also have other
    stats for each player ... how many 'home' wins, how many 'away' wins, the percent of
    the successful picks, etc.

    The only time you do anything with the 'players' table is when a new player registers,
    or is deleted, or a player alters their password, login, info, etc.

    I don't have time to sit down and do all of the scripting for that. You'll also have to
    find examples of doing queries that involve GROUP and counts along with various
    JOIN, etc. The queries will be sort of complex, but they can be done.


  8. #7
    Junior Member weblebowski's Avatar
    Join Date
    Mar 2012
    Posts
    19
    Member #
    31067
    Liked
    1 times
    got it. thanks so much

  9. #8
    Junior Member weblebowski's Avatar
    Join Date
    Mar 2012
    Posts
    19
    Member #
    31067
    Liked
    1 times
    put this aside for a bit and just now revisiting. i now have database updating properly. one thing i'm having trouble with now is outputting data to an html table. for instance, using the while loop seems to constrict me in terms of format of html table. for instance, as of now, html form shows up like this:



    it makes sense why this happens, but i obviously don't want the username to echo out 3 times, only once. i also want the games ($row['home_team']." ".$row['spread']." vs. ".$row['away_team']) to show up as headers across the top. i want my html table to display like so:



    Right now, code looks like this:

    PHP Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>

    <style>

    body { font-family: Verdana, Arial, sans-serif; }

    table, th, tr, td { border-collapse: collapse; }
    .su_win { background-color: yellow; }
    .ats_win { background-color: #94d67a; }
    .loss { color: red; border-color: black; text-decoration: line-through; }

    </style>

    </head>

    <body>

    <?php

    //connects to Db
    $db_host "localhost";
    $db_username "root";
    $db_pass "";
    $db_name "pete_pool";

    @
    mysql_connect("$db_host","$db_username","$db_pass") or die("Could not connect to MySQL");
    @
    mysql_select_db("$db_name") or die("No database");

    $query =    "SELECT * FROM picks
        INNER JOIN schedules
            ON picks.game_id = schedules.game_id
        INNER JOIN players
            ON picks.user_id = players.user_id WHERE week=14"
    ;

    $result mysql_query($query) or die(mysql_error());

    echo 
    "<table border cellpadding=3>";
    echo 
    "<tr>";
    echo 
    "<th>User</th>";
    echo 
    "<th>Game</th>";
    echo 
    "<th>SU</th>";
    echo 
    "<th>ATS</th>";
    echo 
    "</tr>";

    while(
    $row mysql_fetch_array($result)){

    // display picks in html table
        
    echo "<tr><td>".$row['user_name']."</td> ";
        echo 
    "<td>".$row['home_team']." ".$row['spread']." vs. ".$row['away_team']." (".$row['ml_odds'].")</td> ";

    if (
    $row['su'] == $row['home_team']) {
        if (
    $row['home_score'] > $row['away_score']) {
            if(
    $row['su_grade'] == NULL) {
                
    mysql_query("UPDATE picks SET su_grade = 1 WHERE pick_id = ".$row['pick_id']);
            }
            echo 
    "<td class='su_win'>"// highlight su wins yellow
        
    } else {
            if(
    $row['su_grade'] == NULL) {
                
    mysql_query("UPDATE picks SET su_grade = 0 WHERE pick_id = ".$row['pick_id']);
            }
            echo 
    "<td class='loss'>"// strikethrough losses
        
    }
    } else if (
    $row['su'] == $row['away_team']) {
        if (
    $row['home_score'] < $row['away_score']) {
            if(
    $row['su_grade'] == NULL) {
                
    mysql_query("UPDATE picks SET su_grade = 1 WHERE pick_id = ".$row['pick_id']);
            }
            echo 
    "<td class='su_win'>";    // highlight su wins yellow
        
    } else {
            if(
    $row['su_grade'] == NULL) {
                
    mysql_query("UPDATE picks SET su_grade = 0 WHERE pick_id = ".$row['pick_id']);
            }
            echo 
    "<td class='loss'>"// strikethrough losses
        
    }
    }

        echo 
    $row['su']."</td> ";

    if (
    $row['ats'] == $row['home_team']) {
        if (
    $row['home_score'] + $row['spread'] > $row['away_score']) {
            if(
    $row['ats_grade'] == NULL) {
                
    mysql_query("UPDATE picks SET ats_grade = 1 WHERE pick_id = ".$row['pick_id']);
            }
            echo 
    "<td class='ats_win'>"// highlight ats wins green
        
    } else {
            if(
    $row['ats_grade'] == NULL) {
                
    mysql_query("UPDATE picks SET ats_grade = 0 WHERE pick_id = ".$row['pick_id']);
            }
            echo 
    "<td class='loss'>"// strikethrough losses
        
    }
    } else if (
    $row['ats'] == $row['away_team']) {
        if (
    $row['home_score'] + $row['spread'] < $row['away_score']) {
            if(
    $row['ats_grade'] == NULL) {
                
    mysql_query("UPDATE picks SET ats_grade = 1 WHERE pick_id = ".$row['pick_id']);
            }
            echo 
    "<td class='ats_win'>"// highlight ats wins green
        
    } else {
            if(
    $row['ats_grade'] == NULL) {
                
    mysql_query("UPDATE picks SET ats_grade = 0 WHERE pick_id = ".$row['pick_id']);
            }
            echo 
    "<td class='loss'>"// strikethrough losses
        
    }
    }

        echo 
    $row['ats']."</td></tr>";

    }

    echo 
    "</table>";

    ?>

    </body>
    </html>
    Any ideas?

  10. #9
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,716
    Member #
    5580
    Liked
    718 times
    What you're trying to do is all in the query ... but I'm not a MySQL query expert. This is going to involve GROUP, ORDER BY, and a mess of query tricks. I'm not a MySQL expert so you should go into the MySQL part of this forum, or another forum and ask your question again. I'm sure there's a way, but I just can't wrap this one around my head.

    I'm feeling that it will provide your result with one query. MySQL is very powerful.


  11. #10
    Senior Member Webzarus's Avatar
    Join Date
    May 2011
    Location
    South Carolina Coast
    Posts
    3,322
    Member #
    27709
    Liked
    770 times
    Yup, its all in the queries

    I'm no expert either, but what I see is a select * statement... Your queries define the level of detail for the records you want....


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:45 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com