#1  
Old 23rd December 2009, 14:12
edge edge is offline
Moderator
 
Join Date: Dec 2005
Location: The Netherlands
Posts: 2,034
Thanks: 265
Thanked 151 Times in 131 Posts
Default 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?
Reply With Quote
Sponsored Links
  #2  
Old 23rd December 2009, 14:27
edge edge is offline
Moderator
 
Join Date: Dec 2005
Location: The Netherlands
Posts: 2,034
Thanks: 265
Thanked 151 Times in 131 Posts
Default

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";
Reply With Quote
The Following User Says Thank You to edge For This Useful Post:
falko (24th December 2009)
  #3  
Old 29th December 2009, 15:42
Ben Ben is offline
Moderator
 
Join Date: Jul 2006
Posts: 1,029
Thanks: 7
Thanked 62 Times in 56 Posts
 
Default

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)
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
ISPConfig3 Mail Warn Errors reason8 General 3 25th November 2009 13:58
Add ons for ISPConfig 3 virtue Installation/Configuration 24 16th October 2009 17:30
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 09:11
Messed up ISPConfig-2.2.8 Upgrade Morons Installation/Configuration 4 29th November 2006 12:17
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 16:04


All times are GMT +2. The time now is 01:39.


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