PDA

View Full Version : update standings



khagler
12 Dec 2012, 05:57 PM
trying to update overall standings in picks contest once i enter game scores in database. this code works, however, it updates win total for each user (column overall_rec) every time page is refreshed. how can i run condition check only once and update column accordingly (just once per each game)??


<!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>
</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> ";
echo "<td>".$row['su']."</td> ";
echo "<td>".$row['ats']."</td></tr>";

}

echo "</table>";



?>