how to insert special character int mysqldb using PHP

Question

I am trying to insert special character (entities) into mysql db using php.

    <?php
    $servername = "localhost";
  $username = "root";
  $password = "";
  $dbname = "test";

 $autoid = $_REQUEST["autoid"];
// $explanation = htmlspecialchars($_REQUEST["explanation"]);
 $explanation = mysql_real_escape_string("<p>hello how are you Ê</p>");



    echo $explanation;

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

    //$explanation =  utf8_decode(trim(mysql_real_escape_string($explanation));
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    $sql = "UPDATE question_master SET explanation='$explanation' WHERE autoid=$autoid";

    if ($conn->query($sql) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }

    $conn->close();
?>

the string I try to pass is <p>hello how are you Ê</p> but after updating in mysqldb it becomes <p>hello how are you ÃŠ</p>. I am new to mysql, no idea what going wrong.

Table collation is utf8mb4_bin table collation is utf8_bin

http://stackoverflow.com/questions/8568301/how-to-insert-special-language-characters-in-php-into-mysql-database — ashanrupasinghe, Mar 04 '17 at 06:48

Michael Krikorev · Answer 1 · 2017-03-04T07:09:27.510

Try setting the character set to make the escape work as expected before calling mysql_real_escape_string() like this:

mysql_set_charset("utf8mb4");

$explanation = mysql_real_escape_string("<p>hello how are you Ê</p>");

EDIT: I just noted that you are using the depracated mysql_real_escape_string().. Instead you should use mysqli_real_escape_string(). And to be more specific you preferably set the character set for a specific connection by passing the link identifier to your MySQL connection. So the proper way to go would be something like:

$conn = new mysqli($servername, $username, $password, $dbname);

mysqli_set_charset($conn, "utf8mb4");

$explanation = mysqli_real_escape_string($conn, "<p>hello how are you Ê</p>");

Documentation: http://php.net/manual/en/mysqli.set-charset.php http://php.net/manual/en/mysqli.real-escape-string.php

how to insert special character int mysqldb using PHP

1 Answers1