Complete CSV Upload/Updater

[RyanMinor]

I have built a PHP/MySQL CSV uploader/updater to update scores for high school football teams.

 

When I ran this script on XAMPP with Windows 7 I got a permissions error for the "LOAD DATA INFILE" query. I checked my permissions within PHPMyAdmin and the user had all permissions so that isn't it.

 

I need to fix that issue first. I ended up getting everything uploaded by running the query directly on PHPMyAdmin. However, when I try to run the two upload queries my script timed out.

 

The process I am going for here is to upload the file and import the records into a new table (csv_data). Then I want to run the two update queries to update the game table with the new data in the csv_data table.

 

The reason for the two queries is because sometimes the wrong team is in the wrong column as far as home/visitor goes. I find that using CONCAT() is really slowing things down.

 

So I need to first fix my permissions issue with the "LOAD DATA INFILE" query and then figure out a way to speed up my update queries. Any ideas?

 

<?php
require_once('global.php');
if (array_key_exists('submit', $_POST)) {
    if ($_FILES['csv']['error'] > 0){
        echo "Error: " . $_FILES['csv']['error'] . "</br>" . "You have not selected a file or there is another error."; 
    } else {
        $tmp = $_FILES['csv']['tmp_name'];
    }
    if (!$_FILES['csv']['type'] == 'text/csv'){
        echo "Please select a CSV File";
    } else {
        $location = dirname(__FILE__) . '/csv/' . basename($_FILES['csv']['name']);
        move_uploaded_file($tmp, $location);
    }
    $errors = array();
    // Need to get this query to work properly regarding permissions
    $query = $db->prepare("LOAD DATA INFILE ? 
        INTO TABLE csv_data 
        FIELDS TERMINATED BY ',' 
        LINES TERMINATED BY '\n' 
        (game_week, home_team, home_score, visitor_team, visitor_score)");
    if ($query->execute(array($location))) {
        try {
            // Need to figure out how to use LIKE instead of = and do it quickly
            $query_two = $db->prepare("UPDATE game g, csv_data c, team home_team, team visit_team 
                SET g.game_home_score = c.home_score, 
                    g.game_visitor_score = c.visitor_score, 
                    g.game_complete = 'Y' 
                WHERE g.game_week = c.game_week 
                    AND home_team.team_name LIKE CONCAT('%', c.home_team, '%') 
                    AND home_team.team_id  LIKE CONCAT('%', g.game_home_team, '%') 
                    AND visit_team.team_name  LIKE CONCAT('%', c.visitor_team, '%') 
                    AND visit_team.team_id  LIKE CONCAT('%', g.game_visitor_team, '%')");
            if (!$query_two->execute()) {
                $errors[] = 'The first update query failed.';
            }
            $query_three = $db->prepare("UPDATE game g, csv_data c, team home_team, team visit_team 
                SET g.game_home_score = c.visitor_score, 
                    g.game_visitor_score = c.home_score, 
                    g.game_complete = 'Y' 
                WHERE g.game_week = c.game_week 
                    AND home_team.team_name  LIKE CONCAT('%', c.visitor_team, '%') 
                    AND home_team.team_id  LIKE CONCAT('%', g.game_home_team, '%') 
                    AND visit_team.team_name  LIKE CONCAT('%', c.home_team, '%') 
                    AND visit_team.team_id  LIKE CONCAT('%', g.game_visitor_team, '%')");
            if (!$query_three->execute()) {
                $errors[] = 'The second update query failed.';
            }
        } catch(PDOException $e) {
            echo $e->getMessage();
        }
    } else {
        $errors[] = 'CSV Upload Query Failed.';
    }
}
?>