Special Characters into MySQL using PHP-ThrowExceptions

Exception or error:

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?

How to solve:

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?

Answer:

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');

Leave a Reply

Your email address will not be published. Required fields are marked *