Tag: MySQL

Error message: Incorrect integer value: ” for column

Error description: the error occurs when inserting an empty value in a column.

Solution: This behavior is normal and shows that your sql-mod is set to strict. To change that access your server and look for the my.ini file. Locate the sql-mode line and set it as sql-mod=””

Special character issue in php query can be solved by using:

WHERE _utf8'string'

https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

this unfortunately does not work for prepared statement.

Instead set the character set for the connection to utf-8 using

$conn->set_charset("utf8")

https://www.php.net/manual/en/mysqli.set-charset.php

if (!$conn->set_charset("utf8")) {
    printf("Error loading character set utf8: %s\n", $conn->error);
    exit();
} else {
    printf("Current character set: %s\n", $conn->character_set_name());
}

If your string might include backslash, escaping the string will do the trick:

$city = mysqli_real_escape_string($link, $city);

php.net/manual/en/mysqli.real-escape-string.php

Special characters may not be displayed correctly in your mysql data base when you pass php string to mysql statement.

https://github.com/phpmyadmin/phpmyadmin/wiki/Garbled_data

First thing you should do is to make sure that your mysql database encoding is set to utf-8. You can easily do that through phpmyadmin.

Then make sure the string you send are properly encoded in UTF-8 by using the php function http://php.net/manual/en/function.mb-convert-encoding.php

string mb_convert_encoding ( string $str , string $to_encoding [, mixed $from_encoding = mb_internal_encoding() ] )

Example:

$name=mb_convert_encoding($response,"UTF-8");

Lastly make sure that the first query you send to you databse is SET NAMES utf8  like in the example below:

$con=mysqli_connect("localhost","my_user","my_password","my_db");
$con->query("SET NAMES utf8");