I am making a sports picks website. I have the winner the game in one table, and then the team that the user picked in another table. I need help writing the php code that would compare these 2 fields, and if theyre the same then you would increment a win variable, and if they werent you would increment the loss column. The problem is my programmer for this group project dropped out on me, and I haven't really done anything with php before. Just getting me to get the picks inserted in the database took me 12 hours today lol. So maybe someone here could help me so it wouldn't take so long. I will post the 2 tables that I will get the information from.
Don't bother with code when SQL can do the trick better. Note that the query below assumes ws.week_no and ws.game_no make a unique game, if not you have some data problems. Either way, it would be best practice for user_picks and weekly_stats to have a unique ID field to relate records and join on. Same case for your weekly_stats_away_fkey and weekly_stats_home_fkey, those should be int keys related because what will you do when the Redskins are finally forced to change their name!
$db = new PDO("pgsql:dbname=YOUR_DB;host=localhost", "username", "password" );
$sql = "select up.username, count(case when up.pick=ws.winner then 1 end) as wins, count(case when up.pick!=ws.winner then 1 end) as losses from user_picks up left join weekly_stats ws on up.week_no = ws.week_no and up.game_no = ws.game_no group by up.username";
foreach ($db->query($sql) as $row)
{
print $row['username'] .': '. $row['wins'] . '/' . $row['losses'] . '<br />';
}