SQL Duplicating Field Issue
Have an issue with a fantasy sports script I purchased... not exactly experienced with SQL / PHP, so I can't seem to figure it out.
The issue is that there exists a table called team_details into which the players selected for a team are inserted... when the "week" is advanced, the script is supposed to create team details for the new week consisting of the players chosen, MINUS any dropped PLUS any added in a separate team_changes table...
What's happening is that each time the week is advanced, the script is dropping back an RE-INSERTING all of the players for the previous week... this ends up making a team that has player X multiple times for week Y, so when a score is entered for week Y the team gets credit for those points multiple times.
I think I've got it narrowed to this bit of script... any idea how I can simply stop it from creating multiple identical entries?
// 1. Advance week and update teams details (approve changes)
NDatabase::query("INSERT INTO team_details (team_id, player_id, date, week)
SELECT td.team_id, IFNULL(tc.to_player, td.player_id), now(), ". (int)($current_week+1) ."
FROM teams t
LEFT JOIN team_details td ON ( t.id = td.team_id )
LEFT JOIN team_changes tc ON ( tc.team_id = td.team_id
AND tc.from_player = td.player_id
AND td.week = tc.week )
WHERE t.is_playable = 'Y'
OR t.is_playable = 'N'
OR t.is_playable = 'P'
AND td.week =?", array($current_week));