Special chars get converted when inserted into MYSQL database via PHP

[MikEst]

Everything is ok when I use the same INSERT in phpMyAdmin, but when I use PHP mysql_query() to insert the data, special characters get converted to their respective HTML Names (or Numbers). E.g é gets converted to é, (space) gets converted to   etc.

Please tell me how to fix this problem.

 

here's the mysql code:

INSERT INTO gamelogs (Player, PS, a2P, a3P, FT, REB, `AS`, FO, ST, `TO`, BL, RT, `Date`, WinLoss, StarterSub, Position, HomeAway, GType) VALUES ('Liégeois#player.php?playerid=2332545#', '22', '7/10 (70%)', '1/3 (33%)', '5/8 (63%)', '1+0', '0', '2', '1', '2', '0', '14', '2010-03-17', 'W', 'Starter', 'PG', 'A', 'F')

[taquitosensei]

Decode them on output.

html_entity_decode($string); 

 

[JonnoTheDev]

E.g é gets converted to é

This is a good thing. Not an error.

[MikEst]

Decode them on output.

html_entity_decode($string); 

Thanks, that did fix the chars that I pointed out, but some are still being converted:

č

ć

ę

 

 

E.g é gets converted to é

This is a good thing. Not an error.

I understand that, but I'd still like the chars to be displayed normally in the mysql table as I already have hundreds of rows of data in that table without the conversion and I don't really want to change those rows.

[MikEst]

dp

[JonnoTheDev]

OK, when you connect to your database you must set the encoding. You do this via:

mysql_query("SET NAMES UTF8");

You must do this prior to any insert query as I said best after connecting to the database. You can also set the character encoding in the mysql configuration.

 

http://forums.mysql.com/read.php?103,46870,47245

[MikEst]

OK, when you connect to your database you must set the encoding. You do this via:

mysql_query("SET NAMES UTF8");

You must do this prior to any insert query as I said best after connecting to the database.

 

http://forums.mysql.com/read.php?103,46870,47245

If I use

mysql_query("SET NAMES UTF8");

chars still get converted.

And if I use 

html_entity_decode($string);

in addition, the chars that weren't converted when I only used that, are now deleted.

 

You can also set the character encoding in the mysql configuration.

What exactly should I change and where?

[MikEst]

Anyone?

[MikEst]

OK. Followed the instructions here, but that didn't seem to help.

 

Also used this script (from here) on my db, but that didn't help either.

<?php
// Script written by Vladislav "FractalizeR" Rastrusny
// http://www.fractalizer.ru

//MySQL connection settings
$db_server = 'localhost';
$db_user="root";
$db_password="";

mysql_connect($db_server, $db_user, $db_password) or die(mysql_error());

//Put here a list of databases you need to change charset at or leave array empty to change all existing
$dblist=array();

//If changing at all databases, which databases to skip? information_schema is mysql system databse and no need to change charset on it.
$skip_db_list = array('information_schema', 'mysql');

//Which charset to convert to?
$charset="utf8";

//Which collation to convert to?
$collation="utf8_general_ci";

//Only print queries without execution?
$printonly=true;

//Getting database names if they are not specified
$skip_db_text = '"'.implode('", "', $skip_db_list).'"';
if(count($dblist)<1) {
    $sql="SELECT GROUP_CONCAT(`SCHEMA_NAME` SEPARATOR ',') AS FRST FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME` NOT IN ($skip_db_text)";
    $result = mysql_query($sql) or die(mysql_error());
    $data = mysql_fetch_assoc ($result);
    $dblist=explode(",", $data["FRST"]);
}

//Iterating databases
foreach ($dblist as $dbname) {
    $sql="SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET $charset COLLATE $collation;') as FRST FROM `information_schema`.`TABLES` t WHERE t.`TABLE_SCHEMA` = '$dbname' ORDER BY 1";

    $result = mysql_query($sql) or die(mysql_error());
    while ($row = mysql_fetch_assoc($result)) {
        echo $row["FRST"]."\r\n";
        if(!$printonly) {
            mysql_query($row["FRST"]) or die(mysql_error());
        }
    }
}
?> 

[JonnoTheDev]

As I stated, run the collation query after you connect to the database. This is not going to fix html entities currently in the database but it will prevent more being inserted (if this is the script you insert items to the table). If you do not want to run the query every time you connect to the database then set the encoding to UTF-8 in your mysql configuration on the server.

<?php
// connect to database
mysql_connect($db_server, $db_user, $db_password) or die(mysql_error());
// set encoding
mysql_query("SET NAMES UTF8");
?>

[MikEst]

As I stated, run the collation query after you connect to the database. This is not going to fix html entities currently in the database but it will prevent more being inserted (if this is the script you insert items to the table). If you do not want to run the query every time you connect to the database then set the encoding to UTF-8 in your mysql configuration on the server.

<?php
// connect to database
mysql_connect($db_server, $db_user, $db_password) or die(mysql_error());
// set encoding
mysql_query("SET NAMES UTF8");
?>

Thank you for your reply, but I did exactly that and as I already said, the characters still get converted. The only thing it changed is that when I use "html_entity_decode($string);" too, the characters that weren't converted when I only used "html_entity_decode($string);", now get deleted instead. 

[MikEst]

anyone?

[MikEst]

bump

[niranjan81]

I am not sure if its gonna work or not, but try using a parameterized query

as in

 

mysqli_stmt::bind_param

mysqli_stmt_bind_param

 

OR

 

do specific mysql_set_charset — Sets the client character set

Note: This is the preferred way to change the charset. Using mysql_query() to execute SET NAMES .. is not recommended.

[ignace]

E.g é gets converted to é

This is a good thing. Not an error.

 

The manual doesn't mention it, a custom plugin?