#38328 [Opn->Asn]: When using collate no result set returned
| From: | tony2001@php.net | Date: | Tue, 08 Aug 2006 11:48:08 +0000 |
| Subject: | #38328 [Opn->Asn]: When using collate no result set returned | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-100510@lists.php.net to get a copy of this message | ||
ID: 38328
Updated by: tony2001@php.net
Reported By: bogdan dot enache at intersat-telecom dot ro
-Status: Open
+Status: Assigned
Bug Type: MySQLi related
Operating System: Windows XP
PHP Version: 5.1.4
-Assigned To:
+Assigned To: georg
Previous Comments:
------------------------------------------------------------------------
[2006-08-05 09:28:21] bogdan dot enache at intersat-telecom dot ro
Please replace
INSERT INTO t1(s1) values
('ăîşţâĂÎŞŢÂ');
with:
INSERT INTO t1(s1) values
(x'C483C3AEC59FC5A3C3A2C482C38EC59EC5A2C382');
because the bug reporting system apparently can't escape unicode
correctly.
------------------------------------------------------------------------
[2006-08-05 09:02:41] 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
('ăîşţâĂÎŞŢÂ');
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-04 13:56:01] 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-04 13:31:32] 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.
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=38328&edit=1