Special Characters into MySQL using PHP

Question

I have set up a table in my database to load records from a json file that include special characters. I am using the following PHP code to upload the records.

<!DOCTYPE html>
<html>
<head>
    <title>Insert Records</title>
</head>

<body>
<h1>Insert OER Records from JSON File</h1>

<h2>Outputs:</h2>

<?php

ini_set('memory_limit','2000M');

$url = 'path/filename.json';
$contents = file_get_contents($url);
$contents = utf8_encode($contents);
$results = json_decode($contents, true);

include 'connection.php'; 
// Counter for number of records (x)
$x = 0;

foreach($results as $key => $value) {
    $x = $x + 1;    

    foreach($value as $k => $v) {

            $type = $value['type'];
            $title = $value['title'];
            $title = str_replace("'", "\'", $title);
            $title = str_replace("(", "\(", $title);
            $title = str_replace(")", "\)", $title);
            $author = $value['author'];
            $author = str_replace("'", "\'", $author);
            $author = str_replace("(", "\(", $author);
            $author = str_replace(")", "\)", $author);
            $link = $value['link'];
            $source = $value['source'];
            $source = str_replace("'", "\'", $source);
            $source = str_replace("(", "\(", $source);
            $source = str_replace(")", "\)", $source);
            $description = str_replace("'", "\'", $description);
            $description = str_replace("(", "\(", $description);
            $description = str_replace(")", "\)", $description);
            $description = $value['description'];
            $base_url = $value['base_url'];
            $isbn_number = $value['isbn_number'];
            $e_isbn_number = $value['e_isbn_number'];
            $publication_date = $value['publication_date'];
            $license = $value['license'];
            $subject = $value['subject'];
            $image_url = $value['image_url'];
            $review = $value['review'];
            $language = $value['language'];
            $loc_collection = $value['loc_collection'];
            $license_url = $value['image_url'];
    }

    $sql = "INSERT INTO oer_search (type, title, author, link, source, description, base_url, isbn_number, e_isbn_number, publication_date, license, subject, image_url, review, language, license_url) VALUES ('$type', '$title', '$author', '$link', '$source', '$description', '$base_url', '$isbn_number', '$e_isbn_number', '$publication_date', '$license', '$subject', '$image_url', '$review', '$language', '$license_url')";


    if (mysqli_query($conn, $sql)) {

    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }

}

echo "This many records were entered into the database: " . $x;
    mysqli_close($conn);

?>
<br/><br/><br/><br/><br/>

</body>
</html>

I have my database set to utf8_general_ci. However, the records with special characters are not displaying correctly. I am getting results like this MuhÌ£ammad Kurd Ê»AliÌ. Is there something I am missing?

It is a very bad idea to print `mysqli_error($conn);` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Nov 11 '19 at 18:18
Does this answer your question? [UTF-8 all the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Dharman, Nov 11 '19 at 18:18

score 1 · Accepted Answer · answered Nov 11 '19 at 18:24

You are not using mysqli properly. (Not your fault, since the PHP manual doesn't explain it well enough)

To open the connection to mysqli you must at least do the following:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // enable error reporting
$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset('utf8');

set_charset() method ensures that the data sent to the DB is using the correct character set. Without it the data might get mangled up.

You also must enable proper mysqli error reporting with this line:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Without it you would need to use mysqli_error($conn) to check for errors, which could leak sensitive information to the users if misused.

Do not use utf8_encode(). Most of the time you do not need this function. Unless you really know what you are doing, get rid of it.

Warning:
utf8_general_ci is a very old and obsolete charset. If you are using MySQL 5.5.3 or later or MariaDB you should be using utf8mb4 charset. See What is the difference between utf8mb4 and utf8 charsets in MySQL?

That worked for getting the records to load correctly into the database, but now I am having an issue with how they are displaying on my website. Here is an example `Tat?wi?r al-ta?li?m al-s?ina??i? fi? al-?Ira?q`. I have the meta tag set to UTF-8. — brawlins4, Nov 11 '19 at 22:10
Did you read the article I linked under your question in the comments? — Dharman, Nov 11 '19 at 22:11
Got it! I forgot to add `charset=utf8` to my PDO connection in my config file. Thanks @Dharman for the help! — brawlins4, Nov 12 '19 at 02:34

score -1 · Answer 2 · answered Nov 11 '19 at 15:06

you have to use same encode in your files.

Your database have created in utf8, your php files to and you have use this code in your php file crud.

    mysqli_query("SET NAMES 'utf8'");
    mysqli_query('SET character_set_connection=utf8');
    mysqli_query('SET character_set_client=utf8');
    mysqli_query('SET character_set_results=utf8');

Special Characters into MySQL using PHP

2 Answers2