PHP Different Query Results - MySQL Workbench vs PHP Execution

Question

I'm running the following query using MySQL workbench and I'm getting the exact results from the database :

SELECT * 
FROM ODB_RG 
WHERE fullAddressV1 = 'רמת גן חרושת 1' 
    OR fullAddressV2 = 'רמת גן חרושת 1' 
    OR fullAddressV3 = 'רמת גן חרושת 1' 
    OR fullAddressV4 = 'רמת גן חרושת 1'

On the other hand, running the following php code does, that actually generated an equivalent query to the one mentioned above,does not return any record :

$fullAddress = "1 רמת גן חרושת";
$stmt = $con->prepare("SELECT * FROM ODB_RG WHERE fullAddressV1 = :address1 "
        . "OR fullAddressV2 = :address2 OR fullAddressV3 = :address3 "
        . "OR fullAddressV4 = :address4");

$stmt->bindParam(':address1',$fullAddress);
$stmt->bindParam(':address2',$fullAddress);
$stmt->bindParam(':address3',$fullAddress);
$stmt->bindParam(':address4',$fullAddress);

$status = $stmt->execute();

The only reasonable cause that might lead to the difference between the solution is the Hebrew string that is passed as a parameter.

Any of you have any idea what should be done in order to fix it ? Thanks in advance !

EDIT : This is the collation i'm using for the relevant table:

engine=MyISAM charset=UTF8 COLLATE = utf8_general_ci;

What collation do your database/tables/columns use? Use UTF8 if you are not sure. — halfer, Dec 10 '14 at 20:42
Does your configuration support the character set that you are attempting to use? — George Cummins, Dec 10 '14 at 20:42
OT: instead of repeating the address string, you can write `WHERE :address IN (fullAddressV1, fullAddressV2, fullAddressV3, fullAddressV4)`. — Barmar, Dec 10 '14 at 20:44
@GeorgeCummins, what do you mean by configuration ? Where in code should I set it ? Thanks. — Itamar, Dec 10 '14 at 20:44
@Barmar , you're right, but as for now that is not my problem. — Itamar, Dec 10 '14 at 20:44
Also check the collation of your columns too - if they are out of whack that will cause problems. — halfer, Dec 10 '14 at 20:45
@halfer, I think that if wrong columns collation was the problem, I would not get any records from the MySQL Workbench execution.Am I wrong ? — Itamar, Dec 10 '14 at 20:47
@Itamar you get any results, what about error checking do you do any — meda, Dec 10 '14 at 20:52
Just as a "dumb" idea: does your code alogn with `$fullAddress = "1%";` return anything? — dognose, Dec 10 '14 at 21:47

meda · Answer 1 · 2014-12-11T01:31:12.430

1

This is what I meant by error checking:

$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$fullAddress = "1 רמת גן חרושת";
$stmt = $con->prepare("SELECT * FROM ODB_RG WHERE fullAddressV1 = :address1 "
        . "OR fullAddressV2 = :address2 OR fullAddressV3 = :address3 "
        . "OR fullAddressV4 = :address4");

if (!$stmt) {
    echo "\nPDO::errorInfo():\n";
    print_r($con->errorInfo());
    die();
}

$stmt->bindParam(':address1',$fullAddress);
$stmt->bindParam(':address2',$fullAddress);
$stmt->bindParam(':address3',$fullAddress);
$stmt->bindParam(':address4',$fullAddress);

$status = $stmt->execute();

if (!$status ) {
    echo "\nPDO::errorInfo():\n";
    print_r($con->errorInfo());
    die();
}
if($row = $stmt->fetch()){
   var_dump($row);
}else{
   echo 'no row found';
}

I know its ulgy, sometime called defensive programming, but it lets you know whats going on.

edited Dec 11 '14 at 01:31

answered Dec 10 '14 at 21:04

meda

45,103
14
92
122

First of all , thanks for the answer. I've check it , no errors and no results. – Itamar Dec 10 '14 at 21:22
@Itamar try hardcoding the values into the string directly – meda Dec 10 '14 at 21:24
Tried that also, still no results. It's driving me crazy – Itamar Dec 10 '14 at 21:36
@Itamar I dont see any other reasons than unicode, everything needs to be utf8 the column, the connection and even the php file itslef – meda Dec 10 '14 at 21:41
@Itamar read this http://stackoverflow.com/questions/279170/utf-8-all-the-way-through – meda Dec 10 '14 at 21:41

PHP Different Query Results - MySQL Workbench vs PHP Execution

1 Answers1