Page 1 of 1

query not working correctly

Posted: Sat May 09, 2009 5:13 am
by tomsace
Hey,

I have made my database results able to be shown or hidden from my site by setting a 'visible' value to '0' for hidden or '1' for show. I have added WHERE visible = '1' to my queries to show only 'visible' results. When I try adding this to my search query it still shows results with a value of '0'.

Heres my code:

Code: Select all

"SELECT * FROM games WHERE title like '%$search%' OR description like '%$search%' OR category like '%$search%' OR author like '%$search%'"
I have tried adding the value here:

Code: Select all

"SELECT * FROM games WHERE title like '%$search%' OR description like '%$search%' OR category like '%$search%' OR author like '%$search%' && visible = '1'"
Any ideas anyone?

Re: query not working correctly

Posted: Sat May 09, 2009 8:49 am
by kaisellgren
Not sure, but maybe you need to use AND instead of &&?

Re: query not working correctly

Posted: Sat May 09, 2009 9:37 am
by tomsace
Hi, just tried changing && to AND but still no luck, the query still works fine, no errors but still shows visible when set to 0 when I ask it to only show visible when set to 1...???

Re: query not working correctly

Posted: Sat May 09, 2009 10:02 am
by Defiline
You must point the field.

Code: Select all

SELECT * FROM `games` 
WHERE `title` LIKE '%$search%'  
              OR `description` LIKE '%$search%' 
              OR `category`    LIKE '%$search%' 
              OR `author`      LIKE '%$search%' 
              AND `visible`    = 1;
I do not know which field you have, but if your field is INTEGER, the value has to be INTEGER too.
Do not use quotes.

Example:

Code: Select all

SELECT `row` FROM `table` WHERE `visible` = 1;

Re: query not working correctly

Posted: Sat May 09, 2009 10:06 am
by kaisellgren
Defiline wrote:I do not know which field you have, but if your field is INTEGER, the value has to be INTEGER too.
Do not use quotes.
To my experience it would work the same way regardless.

Re: query not working correctly

Posted: Sat May 09, 2009 10:10 am
by Defiline
kaisellgren wrote:To my experience it would work the same way regardless.
I do not know, really.
But I used to take into account any difference between types.

Re: query not working correctly

Posted: Sat May 09, 2009 10:11 am
by kaisellgren
Yeah, there could also be a config option that makes it behave differently.

Re: query not working correctly

Posted: Sat May 09, 2009 10:13 am
by Benjamin
You need to force precedence with parenthesis.

Code: Select all

 
SELECT
  *
FROM
  games
WHERE
  (
    title LIKE '%$search%'
    OR description LIKE '%$search%'
    OR category LIKE '%$search%'
    OR author LIKE '%$search%'
  )
  AND visible = '1'"
 
 

Re: query not working correctly

Posted: Sat May 09, 2009 10:13 am
by tomsace
I have already tried that, I have tried every combination I can think.
If I remove:

Code: Select all

title like '%$search%' OR description like '%$search%' OR category like '%$search%' OR author like '%$search%'
and just leave visible = '1' it works, it shows all results, but only the results that visible = '1' but I just cant get the search part if the query to work in this query??
I have used the visible = '1' now throughout my whole website, it just won't work in this query...

Any other ideas?

Re: query not working correctly

Posted: Sat May 09, 2009 10:14 am
by Benjamin
I provided you the solution.

Re: query not working correctly

Posted: Sat May 09, 2009 10:34 am
by tomsace
Oh right thanks works great!
I must have been writing when you provided the answer...

Re: query not working correctly

Posted: Sat May 09, 2009 11:07 am
by Benjamin
:arrow: Moved to Databases