All geek questions in one place

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

I am learning programming in PHP, so I have set up a test database and try to do different things with it. So the situation is this:

The database mapping is utf8_general_ci.

There is a “book” table created upon request

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 examples of data - note that the text entries are in Russian. This request is saved as utf-8 without the .sql specification and is executed.

 insert into books values ("5-8459-0046-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 view the contents of the created table via phpMyAdmin, I get the correct results.

When I extract data from this table and try to display it through php, I get question marks instead of Russian characters. Here is a snippet 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 conclusion:

 1. : Java 2. ??????????? ???????????? : ????? ?????? ISBN: 5-8459-0046-8 : 34.99

Can someone point out what I'm doing wrong?

0
source share
6 answers

Can someone point out what I'm doing wrong?

Yes I can.
You did not tell the Mysql server which data encoding you want.
Mysql can provide any encoding if the encoding of your page is different from the encoding of the stored data. And transcode it on the fly.
Therefore, you must specify the preferred client encoding (your PHP code is the database client).
By default it is latin1 . Thus, since there are no such characters in the latin1 character table, question marks are returned instead.

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

  • the mysqli_set_charset () function is somewhat preferable (the method in your case).
  • less preferred is a SET NAMES query.

But as long as you use the mysqli extension correctly, it doesn't really matter. (although you do not)

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

+2
source

  • Try setting the output encoding:

    SET NAMES 'utf-8' SET CHARACTER SET utf-8

  • Create a .htaccess file:

    AddDefaultCharset utf-8 AddCharset utf-8 * CharsetSourceEnc utf-8 CharsetDefault utf-8

  • Save files to UTF-8 without specification.

  • Install charset in html head.
+1
source

You probably need to call mysqli_set_charset('utf8'); after setting up your connection with new mysqli(...) since it works with the link, not the global settings.

So..

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

By the way, this query seems to be open for SQL injection if $searchterm not sanitized. Just something to keep in mind, think about how to use prepared statements.

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

+1
source

After your mysql_connect, establish a connection to UTF-8:

 mysql_query("SET NAMES utf8");

Follow Alexander's tips for encoding .htaccess, header and files

+1
source

after adding mysql_query

  @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'; ");
0
source

Try to put also in the HTML document. Run the meta tag:

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

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

-2
source

Source: https://habr.com/ru/post/891866/

More articles:


All Articles