Special characters can't be inserted into database

Question

Im new to php. Im trying to read a text file and insert line by line data to database. My problem is for Some special character insert query does not works
For example Côte , d.ä. , d.y. , DAB-sändare these all are working. But cant insert d'affaires. If i remove d'affaires then the query will execute otherwise it will not insert any data to database. The php code i used to reaf and insert to database is

mysql_connect("localhost","root","");
    mysql_select_db("testdb");
    $query="INSERT INTO keywords (id, keyword) VALUES ";
    $handle = fopen("Ordlista.txt", "r");
    if ($handle) {
        $i=1;
        while (($line = fgets($handle)) !== false) {
            // process the line read.
           // echo $line.'<br>';

            if($i==1)
            {
            $query.=" ( NULL , '".$line."') ";
            $i++;
            }
            else {
                $query.=" ,( NULL , '".$line."') ";
            }

        }
        $query.=";";
     //   $qr=htmlspecialchars($query,ENT_QUOTES);
        echo $query;
        mysql_query($query);
    } else {
        echo 'error opening the file.';
        // error opening the file.
    } 
    fclose($handle);

UPDATED
I have used this code while creating a plugin in wordpress then the special characters are inserting as '?'. In the previous code it was working file the code change i done is

mysql_query("TRUNCATE TABLE $table");
//    $structure = "INSERT INTO $table (`id`, `keyword`) VALUES (NULL, 'test1'), (NULL, 'test2');"; // Keywords for Testing
//    $wpdb->query($structure);
   //read text file & insert to database start
   $query="INSERT INTO $table (id, keyword) VALUES ";
   $fllocation=PLG_URL.'/Ordlista.txt';
    $handle = fopen($fllocation, "r");
    if ($handle) {
        $i=1;
        while (($line = fgets($handle)) !== false) {
            // process the line read.
            if($i==1)
            {
            $query.=" ( NULL , '".mysql_real_escape_string($line)."') ";
            $i++;
            }
            else {
                $query.=" ,( NULL , '".mysql_real_escape_string($line)."') ";
            }
        }
        $query.=";";
        $wpdb->query($query);
       // echo $query;
       // mysql_query($query);
    } else {
        echo 'error opening the file.';
        // error opening the file.
    } 
    fclose($handle);

Answer 1

Try mysql_real_escape_string();

mysql_connect("localhost","root","");
    mysql_select_db("testdb");
    $query="INSERT INTO keywords (id, keyword) VALUES ";
    $handle = fopen("Ordlista.txt", "r");
    if ($handle) {
        $i=1;
        while (($line = fgets($handle)) !== false) {
            // process the line read.
           // echo $line.'<br>';

            if($i==1)
            {
            $query.=" ( NULL , '".mysql_real_escape_string($line)."') ";
            $i++;
            }
            else {
                $query.=" ,( NULL , '".mysql_real_escape_string($line)."') ";
            }

        }
        $query.=";";
     //   $qr=htmlspecialchars($query,ENT_QUOTES);
        echo $query;
        mysql_query($query);
    } else {
        echo 'error opening the file.';
        // error opening the file.
    } 
    fclose($handle);

Answer 2

The best solution would be to upgrade from mysql_* to PDO or mysqli_*, as these allow you to run prepared queries with parameters. But if you can't do that, you have to escape the data:

    while (($line = fgets($handle)) !== false) {
        // process the line read.
       // echo $line.'<br>';
        $line = mysql_real_escape_string($line);

        if($i==1)
        {
        $query.=" ( NULL , '".$line."') ";
        $i++;
        }
        else {
            $query.=" ,( NULL , '".$line."') ";
        }

    }

Answer 3

First, don't use the mysql extension. It has been officially deprecated.

Second, use a prepared statement with parameters to avoid any problems with SQL injection.

Third, make sure you're using a compatible connection, table and column encoding / character set.

For example, using mysqli...

$con = new mysqli('localhost', 'root', '', 'testdb');
if ($con->connect_errno) {
    throw new Exception($con->connect_error, $con->connect_errno);
}
$con->set_charset('utf8');

$stmt = $con->prepare('INSERT INTO `keywords` (`keyword`) VALUES (?)');
if (!$stmt) {
    throw new Exception($con->error, $con->errno);
}
$stmt->bind_param('s', $keyword);

foreach (file('Ordlista.txt') as $keyword) {
    if (!$stmt->execute()) {
        throw new Exception($stmt->error, $stmt->errno);
    }
}

Answer 4

After reading your update, i think the problem is with the collate and charset of your table, execute this:

ALTER TABLE `keywords` CHARACTER SET = utf8 , COLLATE = utf8_unicode_ci ;