Page 1 of 1

all on one page

Posted: Wed Aug 12, 2009 8:29 am
by kevrelland
i have a problem at the moment to update my site i have to import separate csv files to mysql using separate pages
ie to upload fixtures it uses upload_fixture.php and so on, but want i want to do is combine all the uploads so when i call uploads.php all the 6 different csv files are imported into the mysql db.
i have had a go at it but not sure if this is right or could be done a bit better.

Code: Select all

 
<?php
 
// Establish database connection here
mysql_select_db($database_torquay_skittles);
 
// Data
$file = '../uploads/ext009.csv';
$table = "results"; // MySQL table to insert into
$fields = "result_id, scored, result_div, result_alley, result_date, result_home_team, result_home_team_score, home_points, result_away_team, result_away_team_score, away_points";
$s = file_get_contents($file);
 
$rs = "\n"; // Row seperator
$cs = ","; // Column seperator
$esc = "\\"; // Escape character
$quot = "\""; // Column data encloser
 
// Parse input
$row = 0;
$col = 0;
$data = array();
$escape = false;
$quote = false;
for($i = 0; $i < strlen($s); $i++) {
    if(!empty($quot) && ($s{$i} == substr($quot, 0, 1)) && !$escape) {
        $quote = !$quote;
    }
    elseif(!empty($quot) && ($s{$i} == $esc) && !$escape) {
        $escape = true;
    }
    elseif(($s{$i} == $cs) && !$quote) {
        $col++;
        $escape = false;
    }
    elseif(($s{$i} == $rs) && !$quote) {
        $col = 0;
        $row++;
        $escape = false;
    }
    else {
        $data[$row][$col] .= $s{$i};
        $escape = false;
    }
}
 
function prepare($v) {
    $v = is_array($v) ? array_map("prepare", $v) : addslashes(trim($v));
    return $v;
}
// Insert the data
foreach($data as $row) {
    $row = prepare($row);
    $query = "INSERT INTO ".$table." (".$fields.") VALUES ('".implode("', '", $row)."')";
    if(mysql_query($query)) {
        echo "";
    }
    else {
        echo "<span style=\"color: red;\">Failed!</span> Reason: ".mysql_error()."<br>\r\n";
    }
}
?>  
<?php
 
 
// Establish database connection here
mysql_select_db($database_torquay_skittles);
 
// Data
$file = '../uploads/ext005.csv';
$table = "player_averages"; // MySQL table to insert into
$fields = "player_name, player_age, player_team, player_sex, player_games, player_total, player_average, player_high, player_low, player_rank";
$s = file_get_contents($file);
 
$rs = "\n"; // Row seperator
$cs = ","; // Column seperator
$esc = "\\"; // Escape character
$quot = "\""; // Column data encloser
 
// Parse input
$row = 0;
$col = 0;
$data = array();
$escape = false;
$quote = false;
for($i = 0; $i < strlen($s); $i++) {
    if(!empty($quot) && ($s{$i} == substr($quot, 0, 1)) && !$escape) {
        $quote = !$quote;
    }
    elseif(!empty($quot) && ($s{$i} == $esc) && !$escape) {
        $escape = true;
    }
    elseif(($s{$i} == $cs) && !$quote) {
        $col++;
        $escape = false;
    }
    elseif(($s{$i} == $rs) && !$quote) {
        $col = 0;
        $row++;
        $escape = false;
    }
    else {
        $data[$row][$col] .= $s{$i};
        $escape = false;
    }
}
 
function prepare($v) {
    $v = is_array($v) ? array_map("prepare", $v) : addslashes(trim($v));
    return $v;
}
// Insert the data
foreach($data as $row) {
    $row = prepare($row);
    $query = "INSERT INTO ".$table." (".$fields.") VALUES ('".implode("', '", $row)."')";
    if(mysql_query($query)) {
        echo "<span style=\"color: green;\">Player Averages Updated</span>\r\n";
    }
    else {
        echo "<span style=\"color: red;\">Failed!</span> Reason: ".mysql_error()."<br>\r\n";
    }
}
 
?>
 
Cheers
Kevin