PHP & CSV import to SQL

[sharp.mac]

Having a find and replace nightmare and dumbfounded by a way to correct this.

 

I am exporting a large inner join SQL statement in CSV format. I then want to be able to edit and replace values as needed from the other tables.

 

ex:

Orlando = 1

Miami = 2

Jacksonville = 3

 

Well if I want to go through and replace everything 'Orlando' with the id 1, so my inner joins dont break.

I am doing this in the following method.

 

// read the file
$file = file_get_contents('data.csv');
// replace the data

$result = $db->query('SELECT id_baseprices, baseprices FROM baseprices');
while ($line = $db->fetchNextObject($result)) {
$file = str_replace(',"'.$line->baseprices.'",', ',"'.$line->id_baseprices.'",', $file);
}

$result = $db->query('SELECT id_cities, cities FROM cities');
while ($line = $db->fetchNextObject($result)) {
$file = str_replace(',"'.$line->cities.'",', ',"'.$line->id_cities.'",', $file);
}

// write the file
file_put_contents('revamped.csv', $file);

 

Problem with the above is it replaces "Orlando" everywhere with 1. I really only want this to happen inside column 8.

 

I am lost and google must hate me tonight, so thank you for any thoughts or comments.

[objnoob]

If you examin the .CSV file one line at a time,  you can use:

 

  $line_fields = preg_split("/,(?!(?:[^\\\",]|[^\\\"],[^\\\"])+\\\")/", $line_str);

 

to break the fields of the current line into an array, and isolate column 8 using $line_fields[8]. You can then reassign it as such $line_fields[8] = $new_value

 

You may need to check for qualified fields to unqualify, before modifying, and requalify after modifying. A qualifyed field is simply enclosed in double quotes. You can then use implode to turn the $line_fields array into a single string and output to the new file.

 

Remember 0 base indexing rules might cause column 8 to index @ 7