php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #38328 When using collate no result set returned
Submitted: 2006-08-04 13:31 UTC Modified: 2006-08-09 08:03 UTC
From: bogdan dot enache at intersat-telecom dot ro Assigned: georg (profile)
Status: Not a bug Package: MySQLi related
PHP Version: 5.1.4 OS: Windows XP
Private report: No CVE-ID: None
View Developer Edit
 [2006-08-04 13:31 UTC] bogdan dot enache at intersat-telecom dot ro 
Description:
------------
A query like:

SELECT * FROM view_cl_all WHERE cl_name LIKE '%ab%'

is working both in Query Browser and PHP. BUT,

SELECT * FROM view_cl_all WHERE cl_name LIKE '%ab%' COLLATE utf8_general_ci

works (5 results) in Query Browser. If I run the same query in PHP, query fails (returns NULL resultset).

Table type: InnoDB, charset: UTF8, collation: utf8_romanian_ci

Reproduce code:
---------------
So I have this PHP code:

if ($dbc->query($s)){

$ret = array();
if($res = $dbc->store_result()){
$no = $res->num_rows;
for($i = 0; $i < $no; $i++){
$ret[$i] = $res->fetch_assoc();
}
if($res){
$res->close();
}
return $ret;
}
}
else{

echo 'Query failed: '; var_dump($s);
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
}
exit;
return FALSE;
}

IF I set 

$s="SELECT * FROM view_cl_all WHERE (cl_name LIKE '%al%' )"
it's working, i get a number of results.

But if 
$s="SELECT * FROM view_cl_all WHERE (cl_name LIKE '%al%' COLLATE utf8_general_ci)"
then query silently fails, no error, but NULL resultset.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-08-04 13:56 UTC] tony2001@php.net 
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc.

If possible, make the script source available online and provide
an URL to it here. Try to avoid embedding huge scripts into the report.
 [2006-08-05 09:02 UTC] bogdan dot enache at intersat-telecom dot ro 
Create test table in MySQL:

////////////////
CREATE TABLE `t1` (
  `id` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
  `s1` VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_romanian_ci;

INSERT INTO t1(s1) values ('&#259;?&#351;&#355;?&#258;?&#350;&#354;?');
INSERT INTO t1(s1) values ('qwertzuiop');

////////////////

PHP script to test:

<?php

$dbc = new mysqli("?", "?", "?", "?");
$s1 = "SELECT * FROM t1 WHERE s1 LIKE '%qw%';";
$s2 = "SELECT * FROM t1 WHERE s1 LIKE '%ista%' COLLATE utf8_general_ci;";

if ($res = $dbc->query($s1)) {
	$row = $res->fetch_assoc();
	echo 'Query 1: ';	var_dump($row);
	$res->close();
}

if ($res = $dbc->query($s2)) {
	$row = $res->fetch_assoc();
	var_dump($row);
	$res->close();
}
else{
	echo "Query 2 failed";
}
$dbc->close();
?>
 [2006-08-05 09:28 UTC] bogdan dot enache at intersat-telecom dot ro 
Please replace
INSERT INTO t1(s1) values ('&#259;?&#351;&#355;?&#258;?&#350;&#354;?');

with:

INSERT INTO t1(s1) values (x'C483C3AEC59FC5A3C3A2C482C38EC59EC5A2C382');

because the bug reporting system apparently can't escape unicode correctly.
 [2006-08-09 05:33 UTC] georg@php.net 
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

Use mysqli_set_charset function to change your client encoding to utf8 (should be latin1 by default).
 [2006-08-09 08:03 UTC] bogdan dot enache at intersat-telecom dot ro 
Already tried that, but I get:

Fatal error: Call to undefined method mysqli::set_charset() in D:\_webroot\root\t1.php on line 9

///////////////////////////////////////////////////////

<?php

$mysqli = new mysqli("localhost", "root", "webdev", "test");
if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

if (!$mysqli->set_charset("utf8")) {
   printf("Error loading character set utf8: %s\n", $mysqli->error);
} else {
   printf("Current character set: %s\n", $mysqli->character_set_name());
}

$s1 = "SELECT * FROM t1 WHERE s1 LIKE '%qw%';";
$s2 = "SELECT * FROM t1 WHERE s1 LIKE '%ista%' COLLATE utf8_general_ci;";


if ($res = $mysqli->query($s1)) {
	$row = $res->fetch_assoc();
	echo 'Query 1: ';	var_dump($row);
	$res->close();
}

if ($res = $mysqli->query($s2)) {
	$row = $res->fetch_assoc();
	var_dump($row);
	$res->close();
}
else{
	echo "Query 2 failed";
}
$mysqli->close();
?>


////////////////////////////

I use mysql 5.0.22, PHP 5.1.4. Tried this with both libmysql.dll from the PHP distro, and the latest libmysql.dll available from MySQL site ( http://dev.mysql.com/downloads/connector/php/ ). Method is not recognized....
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Sun Dec 22 05:01:30 2024 UTC