HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   Programming/Scripts (http://www.howtoforge.com/forums/forumdisplay.php?f=7)
-   -   Help: MySQL WHERE OR AND (http://www.howtoforge.com/forums/showthread.php?t=42123)

edge 23rd December 2009 14:12

Help: MySQL WHERE OR AND
 
Some one here who can explain why this query is working fine:

Code:


$findThis = "testing";
$inCategory = "cat1";

SELECT *
FROM                                products
WHERE                                products.Desc LIKE '%$findThis%'
AND                                category= '$inCategory";

It returns the correct info, from cat1.

But when I want to search in more product fields (in this case also the Title), and when I set the inCategory to an other value it still shows the info from cat1

Code:

$findThis = "testing";
$inCategory = "cat2";

SELECT *
FROM                                products
WHERE                                products.Desc LIKE '%$findThis%'
OR                                products.Title LIKE '%$findThis%'
AND                                category= '$inCategory";

I have also tested this:

Code:

SELECT *
FROM                                products
WHERE                                products.Desc LIKE '%$findThis%' || products.Title LIKE '%$findThis%'
AND                                category= '$inCategory";

But I get the same results as the 2nd query.

For some reason the AND does not work correct!

Anyone here who can show me what the correct syntax is?

edge 23rd December 2009 14:27

As usual I always find the answer after posting it here (I've been at this for 2 days now)

The fix is that I need to use brackets () in the WHERE

Code:

$findThis = "testing";
$inCategory = "cat2";

SELECT *
FROM                                products
WHERE                                (products.Desc LIKE '%$findThis%' products.Title LIKE '%$findThis%')
AND                                category= '$inCategory";


Ben 29th December 2009 15:42

Sorry that I have not seen this post earlier :)

Anyhow your former problem was based on the operator precedence: http://dev.mysql.com/doc/refman/5.0/...recedence.html

the same with 1+2*3 != (1+2)*3 -> 7 vs. 9

And don't forget about escaping and verifying the userinput before performing the queries (and normally the DB extracted input for further DB related operations as well)


All times are GMT +2. The time now is 16:32.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.