PHP, mysql encoding UTF-8

Question

I'm making basic PHP & MySQL searching. Our country usually use character encoding which is utf-8 or euc-kr .

when I input the keyword that is English, the result is shown well. but, input the Korean keyword, the result doesn't shown on the screen. (result count doesn't shown) I'm coding on Eclipse PDT, every html,php document's encodings are EUC-KR. I set up property. and MySQL's table collation is euckr_korean. I don't know what to do. I'm newbie on php.

Code is simple. there are 2 document.

index.html

<body>
    <form action="./search.php" method="get">
    <label> 
        Search
        <input type="text" name="keywords"> 
        <input type="submit" value="Search">
    </label>
    </form>
</body>

search.php

<?php



$db = new mysqli('localhost', 'root', 'apmsetup', 'consen');

if(isset($_GET['keywords'])){

$keywords = $db->escape_string($_GET['keywords']);

$query = $db->query("
        SELECT title
        FROM data
        WHERE title LIKE '%{$keywords}%' 
        ");
?>


<div class="result-count">
    Found <?php echo $query->num_rows; ?> results. </div>


<?php 
}

?>

Database Table

text         |  code

삼성전자      | 005930
현대차        |    005380
LG           |  003550

when I input 'LG (English) ' , the result's count is 1 (correctly shown)

How to solve this problem? I need your help... Thanks.

enter image description here

We really don't need to see your mysqli DB connection variables. You're unnecessarily exposing your account login and password. — light, Jun 13 '15 at 02:36

light · Answer 1 · 2015-06-13T02:50:19.853

You have not specified your charset in search.php. You have also not shown us the <meta charset> setting in your index.html

I'm guessing what is probably happening is this:

index.html sends GET variables (keywords) in UTF-8 / EUC-KR.
search.phpreceives a bytestream, and processes it as UTF-8 (problem here).
When search.php queries the DB, it is using some gibberish for non-English characters. It matches no row in MySQL and hence shows no results.

You need to keep in mind that every PHP file is actually ultimately an HTML file on the client-side. In your case, your search.php evaluates to a single-line HTML file with the following content:

<div class="result-count">Found X results.</div>

Notice that this document is missing the <!DOCTYPE> and <meta charset> declarations. HTML4 browsers typically default to ISO-8859-1, while HTML5 browsers default to UTF-8, so therein lies the problem.

To solve it, always start every HTML page with:

<!DOCTYPE html>
<html lang="en"> <!-- or whatever language code -->
   <head>
       <meta charset="utf-8"> <!-- or whatever charset -->
       <title>...</title>
       ...
   </head>

Lastly, strongly consider using UTF-8. It's better for the web.

Thanks for your kind answer. It's is very helpful for me. But "meta charset" declaring is already including on html file. "" I'm searching another solution... when I find it, I will write on this page. Thanks... I changed euc-kr to utf-8... Thanks... — ton1, Jun 13 '15 at 03:06
So... are you using EUC-KR or UTF-8? You need to make sure you have the same encoding everywhere. — light, Jun 13 '15 at 03:50

ton1 · Answer 2 · 2015-06-15T09:44:48.330

I finally got a clue, it is a problem about default mysql-character set. To adding 'binary()' on search.php, the result was shown well.

$query = $db->query("
    SELECT title
    FROM data
    WHERE binary(title) LIKE '%{$keywords}%' 
    ");
?>

To avoid same problem later time, you need to set up character-set value on mysql, (my.cnf in linux, my.ini in window). You can check your default character set on mysql console by

mysql > show variables like '%char%';

First, You need to set the charset to another, by editing your documnet my.cnf or my.ini on mysql folder.

Second, Import in your php document that

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

Third, Import in you html document that

<meta charset="utf-8">

PHP, mysql encoding UTF-8

2 Answers2

Linked