query not working correctly

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

query not working correctly

Post 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?
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: query not working correctly

Post by kaisellgren »

Not sure, but maybe you need to use AND instead of &&?
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: query not working correctly

Post 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...???
Defiline
Forum Commoner
Posts: 59
Joined: Tue May 05, 2009 5:34 pm

Re: query not working correctly

Post 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;
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: query not working correctly

Post 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.
Defiline
Forum Commoner
Posts: 59
Joined: Tue May 05, 2009 5:34 pm

Re: query not working correctly

Post 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.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: query not working correctly

Post by kaisellgren »

Yeah, there could also be a config option that makes it behave differently.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: query not working correctly

Post 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'"
 
 
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: query not working correctly

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: query not working correctly

Post by Benjamin »

I provided you the solution.
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: query not working correctly

Post by tomsace »

Oh right thanks works great!
I must have been writing when you provided the answer...
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: query not working correctly

Post by Benjamin »

:arrow: Moved to Databases
Post Reply