In the Beginners MySQL tutorial you learned about WHERE including the use of AND and OR.
Using AND with OR
You might want to get data about customers where they were female and over 60 years old or male and over 65. You could do two queries or you could merge them (which is more efficient):
$query="SELECT personId FROM people WHERE (gender='female' AND age>60) OR (gender='male' AND age>65)";
By using brackets you can achieve pretty much any combination of conditions:
$query="SELECT productId FROM products WHERE (colour='red' AND type='ball') OR type='car'";
That will list all products which are red balls plus all cars.
Comparing numbers
There are a range of ways to compare things in MySQL queries. Many are similar to those used in most scripting or programming languages. These would be used in queries such as:
$query="SELECT productId FROM products WHERE price<=100";
| Operator | Meaning |
|---|---|
| = | Equal to |
| != | Not equal to |
| < | Less than |
| > | Greater than |
| <= | Less than or equal to |
| >= | Greater than or equal to |
| IS NULL | Contains nothing |
| IS NOT NULL | Contains anything |
| LIKE | Allows wildcards* |
* LIKE can replace the equals operator in a query to allow you to match part of a string of text. For example:
$query="SELECT task, tasktime FROM tasks WHERE task LIKE 'Call%'";
will match any task which begins with the word Call. % stands for any number of characters (including none). The underscore stands for any one character.
