I have a long text stored in my database.
The collation is latin1_swedish_ci
and when I see it in the database, it is stored correctly. For example:
string= Sally was walking one day and saw Tom. Tom said "Hi, Sally!" Sally's response was "Hi, Tom."
every " or ' shows up as a white question mark on a black diamond.
I want to write
$result=mysqli_query($db,"SELECT string FROM Table WHERE 1")
while($row = mysqli_fetch_assoc($result)){
echo $row['string'];
}
and have all of the characters show up.
After doing some more research, I found a post that didn't come up before. This solution to a similar problem was offered in a 2009 Stack Overflow answer by Emil H:
MySQL performs character set conversions on the fly to something called the connection charset. You can specify this charset using the sql statement
SET NAMES utf8or use a specific API function such as mysql_set_charset():
mysql_set_charset("utf8", $conn);If this is done correctly there's no need to use functions such as utf8_encode() and utf8_decode().
You also have to make sure that the browser uses the same encoding. This is usually done using a simple header:
header('Content-type: text/html;charset=utf-8');(Note that the charset is called utf-8 in the browser but utf8 in MySQL.)
In most cases the connection charset and web charset are the only things that you need to keep track of, so if it still doesn't work there's probably something else your doing wrong. Try experimenting with it a bit, it usually takes a while to fully understand.
After reading that, I looked up the PHP code they were talking about in the PHP Manual and found that there is an SQLi equivalent, with different syntax:
(PHP 5 >= 5.0.5, PHP 7)
mysqli::set_charset -- mysqli_set_charset — Sets the default client character setDescription
Object oriented style
public mysqli::set_charset ( string $charset ): boolProcedural style
mysqli_set_charset ( mysqli $link , string $charset ): boolSets the default character set to be used when sending data from and to the database server.