Posted by D_a_n_i_e_l on 08/21/07 14:30

I have a form which has a field for filtering a query, the code behine
the form uses the LIKE clause. When I enter "a%" in the field the form
returns all records starting with "a" (as expected.) But when I enter
"e%" no rows are displayed even though there is one record on my
database starting with "e". when. An SQL count with the same clauses
returns 1 record (as expected.) I isloated the discrepancy to two SQL
calls:

SELECT COUNT(*) FROM `testtbl` as a LEFT JOIN `testreftbl2` AS b ON
a.`testref2`=b.`id`,`testreftbl1` AS c WHERE a.`testtext` LIKE "e%"
AND a.`testdbl` LIKE "%" AND a.`testref1`=c.`id`;

no. of records=1

SELECT
a.`testid`,a.`testtext`,a.`testdbl`,a.`testopt`,a.`testradio`,b.`disp`,c.`disp`,a.`testchk`
FROM `testtbl` as a LEFT JOIN `testreftbl2` AS b ON
a.`testref2`=b.`id`,`testreftbl1` AS c WHERE a.`testtext` LIKE "e%"
AND a.`testdbl` LIKE "%" AND a.`testref1`=c.`id` ORDER BY a.testtext
ASC,a.testdbl ASC LIMIT 1,10;

no. of records=0

[Back to original message]