fgetcsv escaping commas in title

[Big_Pat]

The following code is supposed to take a CSV file and insert to the database. First, I prepare the CSV file by replacing " with \" and , with \, then run a PHP function to prepare for insertion. It's not working the way I want it to. I've tried reading about delimiters and escapes but I've not found a solution. Can you help, please?

 

The excerpted code for the insertion is: 

if ($_FILES[csv][size] > 0) {
 
    $file   = $_FILES[csv][tmp_name];
    $handle = fopen($file, "r");
 
    do {
        if ($data[0]) {
            mysql_query("INSERT INTO songlist (track_number,title,
				etc, etc) VALUES 
			( 
			    '" . mysql_prep($data[0]) . "', 
			    '" . mysql_prep($data[1]) . "',
			    etc, etc
			) 
		    ");
        }
    } while ($data = fgetcsv($handle, 1000, ",", "'"));
    

and mysql_prep's code is: 

function mysql_prep($value)
{
    $magic_quotes_active = get_magic_quotes_gpc();
    $new_enough_php      = function_exists("mysql_real_escape_string"); // i.e. PHP >= v4.3.0
    if ($new_enough_php) { // PHP v4.3.0 or higher
        // undo any magic quote effects so mysql_real_escape_string can do the work
        if ($magic_quotes_active) {
            $value = stripslashes($value);
        }
        $value = mysql_real_escape_string($value);
    } else { // before PHP v4.3.0
        // if magic quotes aren't already on then add slashes manually
        if (!$magic_quotes_active) {
            $value = addslashes($value);
        }
        // if magic quotes are active, then the slashes already exist
    }
    return $value;
}

Is there a better function available? At the moment, commas in titles are being read as end-of-entries, despite being escaped.

[Big_Pat]

I might've solved this myself, actually. When loading the csv file back into Excel, the title entry after the escaped comma has moved to the next column which, of course, I don't want. Seems I need to prepare my CSV file better.

[mac_gyver]

there's no reason for you to modify the csv file. any piece of data that contains the comma delimiter will/should be enclosed in quotes and fgetcsv can correctly parse it.

[Big_Pat]

Well, I tried your suggestion but to no avail. One line of the CSV file reads like this, when opened in a text editor:

1,Ever After,Iron Tongue,"The Dogs Have Barked, the Birds Have Flown",Iron Tongue,08:14,2013,Southern Rock,-1,Neurot,US,,3219,2013

and the result is that the title has split to two columns with no quotes and no comma.

 

I then tried 

1,Ever After,Iron Tongue,"The Dogs Have Barked\, the Birds Have Flown",Iron Tongue,08:14,2013,Southern Rock,-1,Neurot,US,,3219,2013

but the result was " "The Dogs Have Barked" in one column and " the Birds Have Flown" " in the next, with no comma or escape \.

[mac_gyver]

just use the default fgetcsv parameters. you have specifically used a single-quote as the field enclosure character. your data is using double-quotes (the default) as the field enclosure character.

[Big_Pat]

Ah, that's it. Excel added the double quotes around fields it had escaped. This works:

 

Thanks!

7,Said N Done,Iron Tongue,'The Dogs Have Barked\, the Birds Have Flown',Iron Tongue,04:57,2013,Southern Rock,-1,Neurot,US,,3219,2013

[mac_gyver]

you are still doing too much. there's no need to modify the csv data. fgetcsv will read the original csv data by default.

[Big_Pat]

Maybe it's just the vagaries of Excel, then, but if I simply enter the CSV as prepared by Excel it reads the comma in the album's title as a separator. In other words, 

1,Ever After,Iron Tongue,The Dogs Have Barked, the Birds Have Flown,Iron Tongue,08:14,2013,Southern Rock,-1,Neurot,US,,3219,2013

it sees the comma, understandably enough - the album's title is actually 'The Dogs Have Barked, the Birds Have Flown'.