Looping through a CSV does not add strings with special chars in MySQL

Question

I have this simple code to store soem data in a MySQL table from a CSV file:

$file = "GeoLiteCity-Location.csv";
$handle = fopen($file, "r");

//loop through the csv file and insert into database
do {
    $n++;
    if ($data[0]) {
        $city = str_replace('"', '',$data[3]);
        $region = str_replace('"', '',$data[2]);
        $latitude = $data[5];
        $longitude = $data[6];
        $country_id = $mobbuteo::$db->select('_countries', 'country_id', array('code' => str_replace('"', '',strtoupper($data[1]))));
        $latlong = $mobbuteo::$db->select('_cities', '*', array('latitude' => $latitude, 'longitude' => $longitude));

        if ($country_id[0]['country_id'] !== 0 && $country_id[0]['country_id'] !== '' && $country_id[0]['country_id'] !== NULL)
            if (count($latlong) <= 0)
                $result = $mobbuteo::$db->insert_update('insert', '_cities', array('country_id' => $country_id[0]['country_id'], 'city' => $city, 'region' => $region, 'latitude' => $latitude, 'longitude' => $longitude));

        if (!$result) {
            echo($city);
        }
    }
} while ($data = fgetcsv($handle, 1000, ",", "'"));

For some reasons in the DB cities with special characters in their names gets cutted off. For example: L·i ThiÍu becomes only L in my DB table. I am using UFT8 General Ci.

What's the problem there? But most importantly how do I get now all those cities with special characters and fixing their names correctly?

Thanks for any suggestion

How about LOAD DATA ?

Alma Do
– Alma Do

2013-09-17 12:53:21 +00:00
Commented Sep 17, 2013 at 12:53 — Alma Do
– Alma Do, Commented Sep 17, 2013 at 12:53
Might be helpful: stackoverflow.com/questions/13298353/…

AlexP
– AlexP

2013-09-17 12:54:59 +00:00
Commented Sep 17, 2013 at 12:54 — AlexP
– AlexP, Commented Sep 17, 2013 at 12:54

Michał Prajsnar · Accepted Answer · 2013-09-17 12:57:00Z

0

Check if your script is running in UTF-8:

header('Content-Type:text/html;charset=utf-8');

Check if your database connection is set to UTF-8:

->query('SET NAMES utf8');

Instead of str_replace to escape characters, use native database method for that (like real_escape_string for mysqli). You may wanna check if your $mobbuteo::$db has method called escape or similar.

answered Sep 17, 2013 at 12:57

Michał Prajsnar

6723 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

asarfraz · Accepted Answer · 2013-09-17 12:57:45Z

0

Try running the following statements after opening the connection to your mySQL database

@mysql_query('SET NAMES "utf8"', $conn);
@mysql_query("SET CHARACTER SET 'utf-8'", $conn);

answered Sep 17, 2013 at 12:57

asarfraz

5183 silver badges8 bronze badges

Collectives™ on Stack Overflow

Looping through a CSV does not add strings with special chars in MySQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related