CODEWITHSUNDEEP
phpmysql
mongy910
mongy910

Reputation: 497

Mysql query with multiple ands and ors

I'm trying to make a filter function for servers on my site. Users are able to check different options for the categories they want to filter for, and I have an ajax request that returns the servers satisfying their conditions. However, my mysql_query isn't working, I think I might have the wrong syntax.

By default, each category has the option set as 1. My current query is:

$order = "SELECT * FROM `servers` 
    WHERE `size` = '$size' or 
      1 = '$size' and 
      `type` = '$type' or 
      1 = '$type' and 
      `mode` = '$gamemode' or 
      1 = '$gamemode' and 
      `main` = '$main' or 
      1 = '$main' 
    ORDER BY $orderby 
    LIMIT 5";

It doesn't seem to get the correct servers, do I have an error in my query?

Thanks!

Upvotes: 2

Views: 2408

Answers (5)

Yogus
Yogus

Reputation: 2272

"SELECT * FROM `servers` WHERE 
`size` in ('$size', 1) and 
`type` in( '$type' ,1) and 
`mode` in('$gamemode' ,1) and 
`main` in ( '$main' , 1 ) 
ORDER BY $orderby LIMIT 5";

Upvotes: 2

Robbert
Robbert

Reputation: 6592

When mixing and and or in your query you must use parenthesis to group the conditions. Also, I think when you say 1 = '$size', I think you mean `size`=1. 

$order = "SELECT * FROM `servers` 
WHERE 
  (`size` = '$size' or `size` = '1') and 
  (`type` = '$type' or `type` = 1) and 
  (`mode` = '$gamemode' or `mode`= 1 ) and 
  (`main` = '$main' or `main` = 1) 
ORDER BY $orderby 
LIMIT 5";

Upvotes: 5

liyakat
liyakat

Reputation: 11853

you must have to use brackets to use multiple OR condition in query

like

mysql_query("SELECT * FROM servers WHERE email='$Email' AND (date='$Date_Today' OR date='$Date_Yesterday' OR date='$Date_TwoDaysAgo' OR date='$Date_ThreeDaysAgo' OR date='$Date_FourDaysAgo')");

you can change with your column name

and also you can use IN condtion like

mysql_query("SELECT * FROM servers WHERE email='$Email' AND date IN ('$Date_Today','$Date_Yesterday','$Date_TwoDaysAgo')");

pls let me know if i can help you more

Upvotes: 1

jad-panda
jad-panda

Reputation: 2547

Just try to put brace around the or conditions 

$order = "SELECT * FROM `servers` WHERE (`size` = '$size' or 1 = '$size') and (`type` = '$type' or 1 = '$type') and (`mode` = '$gamemode' or 1 = '$gamemode') and (`main` = '$main' or 1 = '$main') ORDER BY '$orderby' LIMIT 5";

Upvotes: 0

Class
Class

Reputation: 3160

You need to add parentheses because or and and have a different order of operations and needs parentheses to allow what you are needing to accomplish

SELECT * FROM `servers` WHERE
(`size` = '$size' or 1 = '$size') and
(`type` = '$type' or 1 = '$type') and
(`mode` = '$gamemode' or 1 = '$gamemode') and
(`main` = '$main' or 1 = '$main')
ORDER BY $orderby LIMIT 5

Upvotes: 1

Related Questions