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

• Next in forum: Posting with &
• Prev in forum: Re: Question on refresh
• Thread view: unusual behaiour with MySQL LIKE and LEFT join

[Reply to this message]