Php mysql query charset

Question

I have problem with query charset for mysql bd in php script.

I'm placing GET parameter inside select query and it work good for all Latin characters, but with Cyrillic characters it returns me empty table. If I place some value with Cyrillic in query instead of GET parameter, query works as I want. Likewise, I get result query inside php-Admin and it works.

All Cyrillic characters in result shows me correct.

I've checked everything: php-file is utf8, GET parameter value is utf8, mysql_client_encoding() returns me utf8.

I've tried all I found - nothing helps me.

header('Content-type:text/html; charset=utf-8');
mysql_set_charset('utf8', $link);
mysql_query("SET NAMES utf8", $link);
mysql_query("SET CHARACTER SET 'utf8'", $link);
mysql_query("SET SESSION collation_connection = 'utf8_general_ci'", $link);
mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'", $link);

All of this DOESN'T HELP.

Here is my php script.

<?php
    $link = mysql_connect('localhost', 'r96036lg_searche', 'Jh1ZT4]^');
    //mysql_set_charset('utf8', $link);
    //mysql_query("SET NAMES utf8", $link);
    //mysql_query("SET CHARACTER SET 'utf8'", $link);
    //mysql_query("SET SESSION collation_connection = 'utf8_general_ci'", $link);
    mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'", $link);
    //echo mysql_client_encoding($link);
    mysql_select_db('r96036lg_searche', $link);
    //mysqli::set_charset('utf8');
    $query = "select id, name, typeid from MainObjects where used = 1";
    if(isset($_GET['name']) && !empty($_GET['name'])) {
        //$name = mb_convert_encoding($_GET['name'], "UTF-8", "win1251");
        $name = $_GET['name'];
        //echo mb_detect_encoding($name, "auto", false);
        //$name = iconv(mb_detect_encoding($name, "auto", false), "UTF-8", $name);
        //echo $name;
        $query .= " and typeid = 1 and (name like '%".$name.
                "%' or id in (select parentid from MainObjects where name like '%"
                .$name."%' and used = 1))";
    }
    //echo $query;
    $result = mysql_query($query, $link);
    if(!$result) echo mysql_error();
    else {
        $out = "[";
        while($row = mysql_fetch_row($result)) {
            ... //here is myoutput
        }
        $out .= "]";
        echo $out;
  }
?>

You didn't escape the $_GET parameter before injecting it into the query. Do you know about SQL Injection ? If not, read about it before you proceed. — Lorenz Meyer
– Lorenz Meyer, Commented Jun 20, 2015 at 6:18
It looks like you generate some JSON. If that's true, use json_encode instead. — Lorenz Meyer
– Lorenz Meyer, Commented Jun 20, 2015 at 6:19
What do you get when you echo $query); ? Did you already try to run this in phpmyadmin ? — Lorenz Meyer
– Lorenz Meyer, Commented Jun 20, 2015 at 6:20
Yes, I know about injections. Now it is not important. @LorenzMeyer — Ircover
– Ircover, Commented Jun 20, 2015 at 6:21
JSON used only for output - there is no problem with it. And yes, I've tried it in phpadmin - it works. @LorenzMeyer — Ircover
– Ircover, Commented Jun 20, 2015 at 6:23

Lorenz Meyer · Accepted Answer · 2015-06-20 06:59:26Z

1

This is how you should do it. It will not solve the caracter encoding thing, but it will help with the reste. (And perhaps even solve your problem, because it might just be related to not escaping the values).

$query = "select id, name, typeid from MainObjects where used = 1";
if(isset($_GET['name']) && !empty($_GET['name'])) {
    $name = mysql_real_escape_string($_GET['name']);
    $query .= " and typeid = 1 and (name like '%".$name.
            "%' or id in (select parentid from MainObjects where name like '%"
            .$name."%' and used = 1))";
}
//echo $query;
$result = mysql_query($query, $link);

I this does not help there is a character coding mismatch somewhere. There are three points:

GET data: did you try to encode the url like example.com/index.php?name=%d0%ba (cyrillic small letter ka)
communication with server. If you did really try all you said, there should not be an error
data in the tables: Does the collation of the table really correspond to its content. What I might suspect is that there is a mismatch. If the data is in UTF-8, but the table isn't, mysql will try to convert the data from one character set into another, and as a result it will not much.

edited Jun 20, 2015 at 6:59

answered Jun 20, 2015 at 6:24

Lorenz Meyer

20.1k23 gold badges83 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lorenz Meyer Over a year ago

You didn't post the data I asked for. I don't have your data. I can't test.

Ircover Over a year ago

With your name parameter value it works. How did you convert it? And table field is utf8 too.

Lorenz Meyer Over a year ago

%d0%ba is the standard url encoding for a Cyrillic ka. This means that your $_GET value is not encoded in utf8. Normally, if the originating html page is encoded in utf8, the form data will be encoded also in utf8.

Ircover Over a year ago

Well, it works. I used URLEncoder in my java app to send correct GET value. Thanks, you helped me.

Collectives™ on Stack Overflow

Php mysql query charset

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related