Page 1 of 1

csv import only inserting last line

Posted: Thu Feb 23, 2012 6:05 am
by kevrelland
Anybody have any ideas why is my code only inserting the last line of my csv files.
If i uncomment out the print_r and copy and past it into workbench then run the query, it works fine but the code only inputs the last line of every table.

Code: Select all

$folderlocation = 'uploaded_data/';
			$num = 0; 
			
			function prepare($v) {
				$v = is_array($v) ? array_map("prepare", $v) : addslashes(trim($v));
				return $v;
			}
			
			// Data
			$items = array(
				$items[0] = array(
					'file' => 'ext001.csv',
					'table' => "teams",
					'fields' => "home_alley, alley_contact, team_name, contact_name, contact_number, playing_night, division",
				),
				$items[1] = array(
					'file' => 'ext002.csv',
					'table' => "fixtures",
					'fields' => "fixture_id, played, division_num, home_alley, fixture_date, home_team_id, away_team_id",
				),
				$items[2] = array(
					'file' => 'ext003.csv',
					'table' => 'team_averages',
					'fields' => 'ta_name, ta_games, ta_high, ta_low, ta_average, ta_rank',
				),
				$items[3] = array(
					'file' => 'ext005.csv',
					'table' => 'player_averages',
					'fields' => 'player_name, player_age, player_team, player_sex, player_games, player_total, player_average, player_high, player_low, player_rank',
				),
				$items[4] = array(
					'file' => 'ext008.csv',
					'table' => "league_tables",
					'fields' => "division, team_name, games, won, drawn, lost, points, rank",
				),
				$items[5] = array(
					'file' => 'ext009.csv',
					'table' => "results",
					'fields' => "id, scored, division, alley, date, home_team, home_team_score, home_points, away_team, away_team_score, away_points",
				),
				$items[6] = array(
					'file' => 'ext888.csv',
					'table' => "highest_alley",
					'fields' => "alley, date, name, score",
				),
				$items[7] = array(
					'file' => 'ext999.csv',
					'table' => "trophy_leaders",
					'fields' => "trophy, date, name, score",
				),
				
			);
					
					
					
			foreach ($items as $import) {
				
				mysql_query("TRUNCATE TABLE ".$import['table']."");
				
				$row = 1;
				$handle = fopen($folderlocation.$import['file'], "r");
				while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
					$data = prepare($data);
					$num = count($data);
					$row++;
					//print_r ("INSERT INTO ".$import['table']." (".$import['fields'].") VALUES ('".implode("', '", $data)."'); ");
					$query = "INSERT INTO ".$import['table']." (".$import['fields'].") VALUES ('".implode("', '", $data)."'); ";
				}
				
				fclose($handle);
				
				if(mysql_query($query)) {
					$tableName = ucwords (str_replace("_"," ", $import['table']));
					echo "<span style=\"color: green;\">".$tableName." Updated</span>\r\n";
				}
				else {
					echo "<span style=\"color: red;\">Failed!</span> Reason: ".mysql_error()."<br>\r\n";
				}
				$num++;
			}
			
			mysql_query("UPDATE lastupdate SET date = CURDATE()");
Cheers
Kevin

Re: csv import only inserting last line

Posted: Thu Feb 23, 2012 6:11 am
by Celauran

Code: Select all

while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
    $data = prepare($data);
    $num = count($data);
    $row++;
    //print_r ("INSERT INTO ".$import['table']." (".$import['fields'].") VALUES ('".implode("', '", $data)."'); ");
    $query = "INSERT INTO ".$import['table']." (".$import['fields'].") VALUES ('".implode("', '", $data)."'); ";
}
You're defining the query on each pass, but you're not running it.

Re: csv import only inserting last line

Posted: Thu Feb 23, 2012 6:19 am
by kevrelland
Cheers
Works great, i was having a blonde moment, and just couldn't see it
Kev

Re: csv import only inserting last line

Posted: Thu Feb 23, 2012 6:55 am
by kevrelland
now it is working fine, I have an annoying little bug, my two really long inserts are throwing a mysql error for a duplicate last entry, "Failed to update Results! Reason: Duplicate entry '633' for key 'PRIMARY'"
I have checked the data and there is only one entry 633.
Any ideas???
Kev

Re: csv import only inserting last line

Posted: Thu Feb 23, 2012 7:23 am
by Celauran
How have you modified your code? Did you move the mysql_query call into the loop? Did you remember to remove the one outside the loop? If not, it would try to run the last query twice.

Re: csv import only inserting last line

Posted: Thu Feb 23, 2012 7:36 am
by kevrelland
i've done it like this

Code: Select all

 $folderlocation = 'uploaded_data/';
			$num = 0; 
			
			function prepare($v) {
				$v = is_array($v) ? array_map("prepare", $v) : addslashes(trim($v));
				return $v;
			}
			
			// Data
			$items = array(
				$items[0] = array(
					'file' => 'ext001.csv',
					'table' => "teams",
					'fields' => "home_alley, alley_contact, team_name, contact_name, contact_number, playing_night, division",
				),
				$items[1] = array(
					'file' => 'ext002.csv',
					'table' => "fixtures",
					'fields' => "fixture_id, played, division_num, home_alley, fixture_date, home_team_id, away_team_id",
				),
				$items[2] = array(
					'file' => 'ext003.csv',
					'table' => 'team_averages',
					'fields' => 'ta_name, ta_games, ta_high, ta_low, ta_average, ta_rank',
				),
				$items[3] = array(
					'file' => 'ext005.csv',
					'table' => 'player_averages',
					'fields' => 'player_name, player_age, player_team, player_sex, player_games, player_total, player_average, player_high, player_low, player_rank',
				),
				$items[4] = array(
					'file' => 'ext008.csv',
					'table' => "league_tables",
					'fields' => "division, team_name, games, won, drawn, lost, points, rank",
				),
				$items[5] = array(
					'file' => 'ext009.csv',
					'table' => "results",
					'fields' => "id, scored, division, alley, date, home_team, home_team_score, home_points, away_team, away_team_score, away_points",
				),
				$items[6] = array(
					'file' => 'ext888.csv',
					'table' => "highest_alley",
					'fields' => "alley, date, name, score",
				),
				$items[7] = array(
					'file' => 'ext999.csv',
					'table' => "trophy_leaders",
					'fields' => "trophy, date, name, score",
				),
				
			);
				
			foreach ($items as $import) {
				if (file_exists($folderlocation.$import['file'])) {			
					mysql_query("TRUNCATE TABLE ".$import['table']."");
					
					$row = 1;
					$handle = fopen($folderlocation.$import['file'], "r");
					while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
						$data = prepare($data);
						$num = count($data);
						$row++;
						$query_rsUpdates = "INSERT INTO ".$import['table']." (".$import['fields'].") VALUES ('".implode("', '", $data)."'); ";
						$rsUpdates = mysql_query($query_rsUpdates, $skittles) or die(mysql_error());
					}
					
					fclose($handle);
					$tableName = ucwords (str_replace("_"," ", $import['table']));
					
					if(mysql_query($query_rsUpdates)) {
						echo '<div class="success">';
						echo $tableName." Updated";
						echo '</div>';
					} else {
						echo '<div class="error">';
						echo "Failed to update ".$tableName."! Reason: ".mysql_error();
						echo '</div>';
					}
					//unlink($folderlocation.$import['file']);
				} else {
					echo '<div class="error">';
					echo $import['file'].' File missing please upload to update '.$tableName;
					echo '</div>';
				}		
				
				$num++;
			}
			
			mysql_query("UPDATE lastupdate SET date = CURDATE()");
kev

Re: csv import only inserting last line

Posted: Thu Feb 23, 2012 8:03 am
by Celauran
Looks like it's as I described above.

Code: Select all

while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
    $data = prepare($data);
    $num = count($data);
    $row++;
    $query_rsUpdates = "INSERT INTO ".$import['table']." (".$import['fields'].") VALUES ('".implode("', '", $data)."'); ";
    $rsUpdates = mysql_query($query_rsUpdates, $skittles) or die(mysql_error()); // First query
}

fclose($handle);
$tableName = ucwords (str_replace("_"," ", $import['table']));

if(mysql_query($query_rsUpdates)) { // Second query
    echo '<div class="success">';
    echo $tableName." Updated";
    echo '</div>';
} else {
    echo '<div class="error">';
    echo "Failed to update ".$tableName."! Reason: ".mysql_error();
    echo '</div>';
}

Re: csv import only inserting last line

Posted: Thu Feb 23, 2012 8:26 am
by kevrelland
work a treat,
thank you for your help
Cheers
Kev