all on one page
Posted: Wed Aug 12, 2009 8:29 am
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.
Cheers
Kevin
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";
}
}
?>
Kevin