query not working on script but works on phpMyAdmin

Question

This is the code:

$paramater = "@CityNameWithñ";

$stmt = $connection->prepare("
    SELECT `col1`,`col2`
    FROM `table` 
    WHERE MATCH (`col3`,`col4`)
    AGAINST ( ? IN BOOLEAN MODE) 
    AND `status` = 'a'");
$stmt->bind_param("s",$parameter);
$stmt->execute();
$stmt->bind_result($r1,$r2);
while($stmt->fetch()){
    var_dump($r1);
}

The above doesn't return anything. But if $parameter has a value of for example @CityName it works. I think the ñ is the one causing the problem?

I tried it in phpMyAdmin

    SELECT `col1`,`col2`
    FROM `table` 
    WHERE MATCH (`col3`,`col4`)
    AGAINST ( '@CityNameWithñ' IN BOOLEAN MODE) 
    AND `status` = 'a'

It works. It returned 980 results.

Any idea what is making it fail in my query?

Where is the code where the MySQL variable @CityNameWithin gets set in PHP? The MySQL variable is probably not being carried because the PHP MySQL connection is different. — virmaior, Feb 01 '14 at 15:27
@virmaior a `var_dump()` on `$parameter` just before the `bind_param` returns a string with a value `@CityNameWithñ` and the connection is ok. Again like mentioned above the script works if the value of `$parameter` does not have `ñ` but returns nothing if it does have an `ñ`. — Jo E., Feb 01 '14 at 15:31
what is the connection charset for your PHP code vs phpmyadmin? — virmaior, Feb 01 '14 at 15:37
What is the file encoding? It can be a reason to missing result/ — bksi, Feb 01 '14 at 15:38
The table and its columns are `utf8_unicode_ci` and when I pass `$parameter` to `mb_detect_encoding()` it returns `utf8` — Jo E., Feb 01 '14 at 15:42
NOT the PHP encoding setting. The PHP MySQL connector encoding setting. See here http://stackoverflow.com/questions/4361459/php-pdo-charset-set-names — virmaior, Feb 01 '14 at 15:53

score 0 · Answer 1 · answered Feb 01 '14 at 15:58

0

After reading the comments and some more searching I found out that the problem was a charset problem.

adding $connection->set_charset("utf8") to the script after establishing the mysqli connection helped.

Thanks!

answered Feb 01 '14 at 15:58

Jo E.

7,822
14
58
94

query not working on script but works on phpMyAdmin

1 Answers1