Problems in inserting utf-8 string into database and then outputting it to web page_问答_开发者

I am learning PHP programming, so I have setup testing database and try to do various things with it. So situation is like that:

Database collation is utf8_general_ci.

There is table "books" created by query

create table books
(  isbn char(13) not null primary key,
   author char(50),
   title char(100),
   price float(4,2)
);

Then it is filled with some sample data - note that text entries are in russian. This query is saved as utf-8 without BOM .sql and executed.

insert into books values
  ("5-8459-00开发者_如何学运维46-8", "Майкл Морган", "Java 2. Руководство разработчика", 34.99),
  ("5-8459-1082-X", "Кристофер Негус", "Linux. Библия пользователя", 24.99),
  ("5-8459-1134-6", "Марина Смолина", "CorelDRAW X3. Самоучитель", 24.99),
  ("5-8459-0426-9", "Родерик Смит", "Сетевые средства Linux", 49.99);

When I review contents of created table via phpMyAdmin, I get correct results.

When I retrieve data from this table and try to display it via php, I get question marks instead of russian symbols. Here is piece of my php code:

<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
    <title>Books</title>
</head>
<body>
<?php
  header("Content-type: text/html; charset=utf-8");
  mysqli_set_charset('utf8');
  @ $db = new mysqli('localhost', 'login', 'password', 'database');

  $query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
  $result = $db->query($query);
  $num_results = $result->num_rows;

  for ($i = 0; $i < $num_results; $i++) {
     $row = $result->fetch_assoc();
     echo "<p><strong>".($i+1).". Title: ";
     echo htmlspecialchars (stripslashes($row['title']));
     echo "</strong><br />Author: ";
     echo stripslashes($row['author']);
     echo "<br />ISBN: ";
     echo stripslashes($row['isbn']);
     echo "<br />Price: ";
     echo stripslashes($row['price']);
     echo "</p>";
  }
...

And here is the output:

1. Название: Java 2. ??????????? ????????????
Автор: ????? ??????
ISBN: 5-8459-0046-8
Цена: 34.99

Can someone point out what I am doing wrong?

Can someone point out what I am doing wrong?

Yes, I can.
You didn't tell Mysql server, what data encoding you want.
Mysql can supply any encoding in case your page encoding is different from stored data encoding. And recode it on the fly.
Thus, it needs to be told of client's preferred encoding (your PHP code being that database client).
By default it's latin1. Thus, because there is no such symbols in the latin1 character table, question marks being returned instead.

There are 2 ways to tell mysql what encoding we want:

a slightly more preferred one is mysqli_set_charset() function (method in your case).
less preferred one is SET NAMES query.

But as long as you are using mysqli extension properly, doesn't really matter. (though you aren't)

Note that in mysql this encoding is called utf8, without dashes or spaces.

Try to set output charset:

SET NAMES 'utf-8' SET CHARACTER SET utf-8
Create .htaccess file:

AddDefaultCharset utf-8 AddCharset utf-8 * CharsetSourceEnc utf-8 CharsetDefault utf-8
Save files in UTF-8 without BOM.
Set charset in html head.

After your mysql_connect, set your connection to UTF-8 :

mysql_query("SET NAMES utf8");

Follow Alexander advices for .htaccess, header and files encoding

You probably need to call mysqli_set_charset('utf8'); after you set up your connection with new mysqli(...) as it works on a link rather than a global setting.

so..

@ $db = new mysqli('localhost', 'login', 'password', 'database');
mysqli_set_charset($db, 'utf8');
$query = "select * from books where ".$searchtype." like '%".$searchterm."%'";

By the way, that query seems to be open to SQL-injection unless $searchterm is sanitized. Just something to keep in mind, consider using prepared statements.

And using @ to suppress errors is generally not recommended, especially not during development. Better to deal with error-conditions.

after your mysql_query add

        @mysql_query("SET character_set_server='utf8'; ");   
    @mysql_query("SET character_set_client='utf8'; ");
    @mysql_query("SET character_set_results='utf8'; ");   
    @mysql_query("SET character_set_connection='utf8'; ");   
    @mysql_query("SET character_set_database='utf8'; ");   
    @mysql_query("SET collation_connection='utf8_general_ci'; ");   
    @mysql_query("SET collation_database='utf8_general_ci'; ");   
    @mysql_query("SET collation_server='utf8_general_ci'; ");

Try to put also in the HTML document Head the meta tag:

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

this is different to the HTTP header header("Content-type: text/html; charset=utf-8");

Problems in inserting utf-8 string into database and then outputting it to web page

精彩评论

关注公众号

热门标签

图文推荐

Problems in inserting utf-8 string into database and then outputting it to web page

更多 问答 相关资讯：

精彩评论

关注公众号

热门标签

图文推荐

更多问答相关资讯：