CSV with Apostrophe into MySQL using PHP

[morgrar]

Hi all. I'm a relative n00b when it comes to PHP/MySQL, so please excuse me for that. I did a search but did not find too much information, so I'm hoping someone can point me in the right direction.

I'm writing a script to import a CSV file into a MySQL table (nothing fancy), and it works for the most part. Of the 33,000 record file I'm working with, it loads all but ~5 of them. The ~5 it doesn't load have an apostrophe in the field, so for example, if one of the fields is a street address, it's "O'Brian Street" and the ' is causing the MySQL query to fail.

Is there a way I can bypass that MySQL error or force it to accept? I'm guessing this is a simple fix, but any help is greatly appreciated. Thanks in advance.

$handle = fopen('./Addresses.csv', 'r');
        while (($data = fgetcsv($handle, 1000, ',', '"')) !==FALSE)
        {
                $sql = str_replace("' '", mysql_escape_string("NULL"), $sql);
                $query = "INSERT INTO TableName VALUES ('". implode("','", $data)
                ."')";
                $query = @mysql_query($query);
                if (mysql_error()) {
                        echo mysql_error() . "<br>\n";
                }
        }

 

[H-street]

i believe what you are looking for is

mysql_real_escape_string()

 

[BuddhistPunk]

In order to insert the single quote as part of the value it has to be escaped otherwise MySQL will think that the quote delimits the end of the value.

You have a call to mysql_escape_string but this seems to be redundant as it is not operating on any of the data.

Something like this will work as it escapes all the values in the $data array returned from fgetcsv:

$handle = fopen('./Addresses.csv', 'r');
while (($data = fgetcsv($handle, 1000, ',', '"')) !==FALSE)
{
        for($x=0; $x < count($data); $x++)
        {
	        $data[$x] = mysql_escape_string($data[$x]);
        }
        
        $query = "INSERT INTO TableName VALUES ('". implode("','", $data)."')";

        <<<< Add your db call code here >>>>
}

However, this will attempt to escape every value regardless of whether it needs it or not, so if this is something that is going to be heavily loaded you might want to look at other options.

 

[morgrar]

Thank you. I'm going to fiddle with that and see how the results turn out. Much appreciated.

 

[morgrar]

In order to insert the single quote as part of the value it has to be escaped otherwise MySQL will think that the quote delimits the end of the value.

You have a call to mysql_escape_string but this seems to be redundant as it is not operating on any of the data.

Something like this will work as it escapes all the values in the $data array returned from fgetcsv:

$handle = fopen('./Addresses.csv', 'r');
while (($data = fgetcsv($handle, 1000, ',', '"')) !==FALSE)
{
        for($x=0; $x < count($data); $x++)
        {
	        $data[$x] = mysql_escape_string($data[$x]);
        }
        
        $query = "INSERT INTO TableName VALUES ('". implode("','", $data)."')";

        <<<< Add your db call code here >>>>
}

However, this will attempt to escape every value regardless of whether it needs it or not, so if this is something that is going to be heavily loaded you might want to look at other options.

This worked out quite nicely. Thank you for your help. It didn't take much longer to process (nothing noticeable).