MySQL Automatic Updater from CSV

[RyanMinor]

I am trying to create an automatic score updater for a football database. My client will be emailing me CSV spreadsheets with the game date, home team, home score, visiting team, and visiting score in them. The problem is that I have a separate team table (team_id, team_name, etc.) and game table as outlined below:

 

team (team_id, team_name, etc.)

game(game_id, game_home_team, game_home_score, game_visitor_team, game_visitor_score, game_date, game_complete)

sample game data(1, 123, 0, 156, 0, 8-31-2012, No) -> I store the team's id instead of their name in the game table.

 

I need to write a script that will update the scores automatically. There will be about 327 scores per week.

 

The CSV will look like the following (with column headings):

game_date, home_team, home_score, visitor_team, visitor_score

(2012-8-31, Forest Hills, 44, Westmont Hilltop, 0) -> sample data

 

How would I first get the team id's, then match them home and visitor id's with their team names, then get the game id that corresponds to the matching team_id's, then update the scores for that game? That's a lot and I am kind of lost on the issue. Any help or even a better way to do this would be greatly appreciated. Below is what i have so far:

 

    UPDATE game SET 
        game_home_score = ?, 
        game_visitor_score = ?, 
        game_complete = 'Yes' 
    WHERE game_id = 
        SELECT game_id 
        FROM game 
        WHERE game_home_team IN (
            SELECT team_id 
            FROM team 
            WHERE team_name = ?
        ) AND game_visitor_team IN (
            SELECT team_id 
            FROM team 
            WHERE team_name = ?
        );

 

I know that query is wrong, but it's what I was thinking as far as the direction I need to go. My MySQL knowledge isn't quite vast enough (I don't think) to get this working.

[computermax2328]

I fiddled around with this a little bit too. Here is a script that I use to do the same thing.

 

if (isset($_POST['submit'])) {
    if ($_FILES['csv']['error'] > 0){
        echo "Error: " . $_FILES['csv']['error'] . "</br>" . "You have not selected a file or there is another error."; 
    }
            else {
            $name = $_FILES['csv']['name'];
            $tmp = $_FILES['csv']['tmp_name'];
            }
            if (!$_FILES['csv']['type'] == 'text/csv'){
                    echo "Please select a CSV File";
            }
            else {
                $location = "tables/";
                $location = $location . basename($_FILES['csv']['name']);
                move_uploaded_file($tmp, $location);
            }
}
$clear = "TRUNCATE TABLE labor";
if (mysql_query($clear)) {
$sql = "LOAD DATA LOCAL INFILE '$location' INTO TABLE table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (`First Name`, `Last Name`, `Rank`)";
if (mysql_query($sql)) {
    header('Location: ../pages/home_DB.php');
    exit;
}
else {
    echo "CSV Upload Query Failed " . mysql_error();
}
}

 

Two things to keep in mind, most importantly and I don't know if this is 100% true, but I always upload as a microsoft CSV. Mac has the ability to convert to that file format. Two, I used "LOAD DATA LOCAL INFILE," some hosts ask you to use different methods. Mine asked me to use this.

 

Also notice in my code that I turncate the table before I insert the new one

[RyanMinor]

Thanks for the reply. I will mess around with this and see what I can come up with.

[PFMaBiSmAd]

Because you are doing this en-mass, I would first retrieve all the team names/ids and store them in a $lookup array, where the index is the team name (converted all to lower-case and with any white-space removed), and the value is the id.

 

At the point of needing the team id, you would simply get the lower-case/all white-space removed team name from the supplied data and use that value as the key to access the correct element in the $lookup array.