mysql query regexp with russian characters works on console but not in php

Question

i have the following mysql query:

SELECT user_boothid,user_id,user_fname,user_lname,user_ts,user_company,user_photo FROM se_users WHERE (user_email REGEXP 'Елена' || user_displayname REGEXP 'Елена' || user_company REGEXP 'Елена' || user_title REGEXP 'Елена' || user_fname REGEXP 'Елена' || user_lname REGEXP 'Елена') && user_eventid='15' && user_id!='799' && user_active='true' && ( user_id!=818 )

if i run this query on the console, i get the result but if i run it from the php script, don't return anything. i'm using a class to manage the DB connection and query's and is this:

function __construct($serverdb,$usernamedb,$passworddb,$namedb){
    //Fixed problem when old connection (from SocialEngine) was used
    //$this->linkDB=mysql_connect($serverdb,$usernamedb,$passworddb)or die("Can't Connect to DB");
    $this->linkDB=mysql_connect($serverdb,$usernamedb,$passworddb,TRUE)or die("Can't Connect to DB");
    mysql_query("SET character_set_results=utf8", $this->linkDB);
    mb_language('uni');
    mb_internal_encoding('UTF-8');
    mysql_select_db($namedb,$this->linkDB)or die("Can't Select DB");
    //mysql_query("set names 'utf8'",$this->linkDB);
    mysql_set_charset('utf8',$this->linkDB);
}

function makeQuery($query){
    mysql_query("SET character_set_client=utf8", $this->linkDB);
    mysql_query("SET character_set_connection=utf8", $this->linkDB);
    //mysql_query("set names 'utf8'",$this->linkDB);
    mysql_set_charset('utf8',$this->linkDB);
    mysql_query($query,$this->linkDB)or die(mysql_error()."Query: ".$query);
    return mysql_insert_id($this->linkDB);
}

function returnQuery($query){
    mysql_query("SET character_set_results=utf8", $this->linkDB);
    //mysql_query("set names 'utf8'",$this->linkDB);
    mysql_query("SET CHARACTER SET 'utf8'",$this->linkDB);
    mysql_set_charset('utf8',$this->linkDB);
    $temp=mysql_query($query,$this->linkDB)or die(mysql_error());
    $num=mysql_num_rows($temp);
    $rows=Array();
    for($i=0;$i<$num;$i++){
    $rows[$i]=mysql_fetch_assoc($temp)or die(mysql_error());    
    }
    return $rows;
}

i tested it and the encoding seems to be ok (if i make a query with the ID, return the row with all the Russian chars as it should) but using the regexp doesn't work.

anyone can give me a hint of where to look?

Regards, Shadow.

UPDATE: this problem happen if i use LIKE or = to search also. UPDATE2: i realized that the problem is the $term var that contain the term to search, if i hardcode the value the query works, but no if i receive the value from post

What encoding is the text file with the code created by your editor? That's gotta be right as well if you hardcode non-ascii characters in code. — Dan Grossman, Feb 23 '11 at 05:19
the value for the regexp is get by POST, and is received correctly (as you see i can print the query whiteout problem) — shadow_of__soul, Feb 23 '11 at 11:23

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

SET NAMES utf8;

SET NAMES utf8 in MySQL?

edited May 23 '17 at 10:24

Community

1
1

answered Feb 23 '11 at 05:19

servermanfail

2,532
20
21

i'm using mysql_set_charset, is the same, i already tested with the 2 options with the same result – shadow_of__soul Feb 23 '11 at 12:49

score 0 · Answer 2 · answered Feb 23 '11 at 13:01

It sounds like the character encoding on the values you retrieve from POST isn't the same as the encoding in the database. Have you set the character encoding for your HTML pages to UTF-8? Also, according to this page the MySQL REGEXP is not multibyte safe and can't be used with multibyte character sets. ie. UTF-8.

score 0 · Accepted Answer · answered Feb 23 '11 at 13:24

0

i was needed to use html_entity_decode() on the $term variable why i was receiving it html signs.

Regards, Shadow.

answered Feb 23 '11 at 13:24

shadow_of__soul

429
1
8
19

mysql query regexp with russian characters works on console but not in php

3 Answers3