I often see something similar to this below in PHP scripts using MySQL
query("SET NAMES utf8");
I have never had to do this for any project yet so I have a couple basic questions about it.
Instead of doing this via an SQL query use the php function: mysqli::set_charset mysqli_set_charset
Note: This is the preferred way to change the charset. Using mysqli_query() to set it (such as SET NAMES utf8) is not recommended.See the MySQL character set concepts section for more information.
This query should be written before the query which create or update data in the database, this query looks like :
mysql_query("set names 'utf8'");
Note that you should write the encode which you are using in the header for example if you are using utf-8 you add it like this in the header or it will couse a problem with Internet Explorer
so your page looks like this
<html>
<head>
<title>page title</title>
<meta charset="UTF-8" />
</head>
<body>
<?php
mysql_query("set names 'utf8'");
$sql = "INSERT * FROM ..... ";
mysql_query($sql);
?>
</body>
</html>
Not only PDO. If sql answer like '????' symbols, preset of you charset (hope UTF-8) really recommended:
if (!$mysqli->set_charset("utf8"))
{ printf("Can't set utf8: %s\n", $mysqli->error); }
or via procedure style mysqli_set_charset($db,"utf8")
Getting encoding right is really tricky - there are too many layers:
The SQL command "SET CHARSET utf8" from PHP will ensure that the client side (PHP) will get the data in utf8, no matter how they are stored in the database. Of course, they need to be stored correctly first.
Encoding defined for a table/column doesn't really mean that the data are in that encoding. If you happened to have a table defined as utf8 but stored as differtent encoding, then MySQL will treat them as utf8 and you're in trouble. Which means you have to fix this first.
You need to check in what encoding the data flow at each layer.
If you receive data in e.g. windows-1250, and want to store in utf-8, then use this SQL before storing:
SET NAMES 'cp1250';
If you have data in DB as windows-1250 and want to retreive utf8, use:
SET CHARSET 'utf8';
Thanks @all!
don't use: query("SET NAMES utf8"); this is setup stuff and not a query. put it right afte a connection start with setCharset() (or similar method)
some little thing in parctice:
status:
Store and read data is no problem as long mysql can handle the characters. if you look in the db you will already see there is crap in it (e.g.using phpmyadmin).
until now this is not a problem! (wrong but works often (in europe)) ..
..unless another client/programm or a changed library, which works correct, will read/save data. then you are in big trouble!
The solution is
$conn->set_charset("utf8");
From the manual:
SET NAMES indicates what character set the client will use to send SQL statements to the server.
More elaborately, (and once again, gratuitously lifted from the manual):
SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES 'cp1251' tells the server, “future incoming messages from this client are in character set cp1251.” It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use a SELECT statement.)
Source: Stackoverflow.com