CODEWITHSUNDEEP
phpmysql
Mike L.
Mike L.

Reputation: 1966

Using WHERE, AND, & OR in same MySQL query help

Will this query work? Is it most efficient?

SELECT * FROM `posts`
WHERE MATCH (`title`, `body`)
AGAINST ('search terms' IN BOOLEAN MODE)
AND `price` BETWEEN '100' AND '1000'
AND (`postinto` = 'cat1' OR `postinto` = 'cat2')
AND (`location` = 'loc1' OR `location` = 'loc2')
ORDER BY `id` DESC
LIMIT 0, 100;

Note: values for postinto and location will be contained in a PHP arrays, so if this will work I plan on looping their the arrays to generate the query terms. Is there a way to pass the entire array to MySQL? Also, these two conditions have a possibility of being quite long (a dozen values). Is there a better way?

specifically my question is about this:

AND (`postinto` = 'cat1' OR `postinto` = 'cat2')
AND (`location` = 'loc1' OR `location` = 'loc2')

an example of possible values would be "community|groups", "buy-sell-trade|electronics" where before the | is a category and after | is a sub category. If I am searching an entire category I would want to change that part of the query to:

AND (`postinto` LIKE 'category|%' OR `postinto` = 'this'

I have the proper indexes for the fulltext search, my question is about the OR clause. Is there a maximum number of times you can use OR in one query? Is this syntax even correct?

Thanks

Upvotes: 2

Views: 1770

Answers (2)

John Watt
John Watt

Reputation: 1

You can use AND and OR in a WHERE as often as you want, but to keep your sanity you need to obey a few simple rules.

1) Do not mix them, constructing a query with WHERE AND OR AND OR will probably work but the results will be unpredictable and the result set may contain multiple instances of the same record! (You are effectively turning the OR construct into an EITHER!

2) I would recommend always putting all of the OR statements before any AND statements. It will be easier for you to understand and for anybody else to maintain.

3) It is probably not a necessity but I always if possible enclose the OR statements in brackets.

I Maintain a Sports database and run some queries that can have multiple options in the select.

Hopefully the following examples will simplify the explanation.

// Make the query to retrieve the set of Singles Games.

 $query = "SELECT * from Games WHERE Season = '$season' 
 AND GameNo < 8 
 OR Player1 = '$playerName' ORDER by MatchNo ASC";
 $result = @mysql_query($query); // Run the query.
 $num = mysql_num_rows($result);

This will run, it is syntactically correct but the results will be unpredictable! If in these circumstances you wish to put the OR after the AND then you need to 'AND' the OR.

// Make the query to retrieve the set of Singles Games.(This is correct)

 $query = "SELECT * from Games WHERE (Player1 = '$playerName' 
 OR Player3 = '$playerName') AND Season = '$season' 
 AND GameNo < 8 ORDER by MatchNo ASC";
 $result = @mysql_query($query); // Run the query.
 $num = mysql_num_rows($result);

This will run and produce a list of Singles games in which a player has participated.

The following is the same statement with the OR after the AND. Notice you need to AND the OR to the end of the statement.

// Make the query to retrieve the set of Singles Games.(This is also correct)

 $query = "SELECT * from Games WHERE Season = '$season' 
 AND GameNo < 8 AND (Player1 = '$playerName' 
 OR Player3 = '$playerName') ORDER by MatchNo ASC";
 $result = @mysql_query($query); // Run the query.
 $num = mysql_num_rows($result);

This will run and produce a list of Singles games in which a player has participated.

Finally a couple of examples, why I prefer the OR first

 $query = "SELECT * from Games WHERE (Player1 = '$playerName' 
 OR Player2 = '$playerName' OR Player3 = '$playerName' 
 OR Player4 = '$playerName') AND Season = '$season' 
 AND GameNo > 7 ORDER by MatchNo ASC";
 $result = @mysql_query($query); // Run the query.
 $num = mysql_num_rows($result);

This will produce a result set containing all the League doubles that a player has participated in. Either as home player1 or 2, or away player 3 or 4.

To produce a query with multiple separate OR statements. Then place an AND between the OR statements.

$query = "SELECT * from CupGames WHERE (Player1 = '$playerName' 
OR Player2 = '$playerName' OR Player3 = '$playerName' 
OR Player4 = '$playerName' OR Player5 = '$playerName'
OR Player6 = '$playerName') AND (CupID = 'CS' OR CupID = 'ND') 
AND Season = '$season' AND GameNo < 4 ORDER by CupRound ASC";
$result04 = @mysql_query($query); // Run the query.
$num = mysql_num_rows($result04);

This will produce a result set containing all the Cup Triples that a player has participated in. Either as home player1,2 or 3, or away player 4, 5 or 6. In either of the two selected cups. CS(Coronation Shield) or ND(Norman Day Cup) for the selected season.

If you wish to keep your hair I suggest following these rules. (I have retired from IT and have a full head of hair!) Best of Luck!

Upvotes: 0

Richard Tuin
Richard Tuin

Reputation: 4562

Actually there is. The IN statement can help you here.

Your query would then become like this:

SELECT * FROM `posts`
WHERE MATCH (`title`, `body`)
AGAINST ('search terms' IN BOOLEAN MODE)
AND `price` BETWEEN '100' AND '1000'
AND `postinto` IN ('cat1', 'cat2')
AND `location` IN ('loc1', 'loc2')
ORDER BY `id` DESC
LIMIT 0, 100;

Upvotes: 5

Related Questions