Using WHERE in Your Queries


Using WHERE in Your Queries

You have learned numerous ways to retrieve particular columns from your tables, but not specific rows. This is when the WHERE clause comes in to play. From the example SELECT syntax, you see that WHERE is used to specify a particular condition:

 SELECT expressions_and_columns FROM table_name [WHERE some_condition_is_true] 

An example would be to retrieve all the records for items with a quantity of 500:

 mysql> select * from grocery_inventory where curr_qty = 500; +----+-------------------+------------------------+------------+----------+ | id | item_name         | item_desc              | item_price | curr_qty | +----+-------------------+------------------------+------------+----------+ |  2 | Bunches of Grapes | Seedless grapes.       |       2.99 |      500 | |  5 | Pears             | Anjou, nice and sweet. |        0.5 |      500 | +----+-------------------+------------------------+------------+----------+ 2 rows in set (0.00 sec) 

As shown previously, if you use an integer as part of your WHERE clause, quotation marks are not required. Quotation marks are required around strings, and the same rules apply with regard to escaping characters, as you learned in the section on INSERT.

Using Operators in WHERE Clauses

You've used the equal sign (=) in your WHERE clauses to determine the truth of a conditionthat is, whether one thing is equal to another. You can use many types of operators, with comparison operators and logical operators being the most popular types.

Table 16.1. Basic Comparison Operators and Their Meanings

Operator

Meaning

=

Equal to

!=

Not equal to

<=

Less than or equal to

<

Less than

>=

Greater than or equal to

>

Greater than


There's also a handy operator called BETWEEN, which is useful with integer or data comparisons because it searches for results between a minimum and maximum value. For example

 mysql> select * from grocery_inventory     -> where item_price between 1.50 and 3.00; +----+------------------------+---------------------+------------+----------+ | id | item_name              | item_desc           | item_price | curr_qty | +----+------------------------+---------------------+------------+----------+ |  2 | Bunches of Grapes      | Seedless grapes.    |       2.99 |      500 | |  3 | Bottled Water (6-pack) | 500ml spring water. |       2.29 |      250 | |  4 | Bananas                | Bunches, green.     |       1.99 |      150 | +----+------------------------+---------------------+------------+----------+ 3 rows in set (0.00 sec) 

Other operators include logical operators, which enable you to use multiple comparisons within your WHERE clause. The basic logical operators are AND and OR. When using AND, all comparisons in the clause must be true to retrieve results, whereas using OR allows a minimum of one comparison to be true. Also, you can use the IN operator to specify a list of items that you want to match.

String Comparison Using LIKE

You were introduced to matching strings within a WHERE clause by using = or !=, but there's another useful operator for the WHERE clause, when comparing strings: the LIKE operator. This operator uses two characters as wildcards in pattern matching.

  • %Matches multiple characters

  • _Matches exactly one character

For example, if you want to find records in the grocery_inventory table where the first name of the item starts with the letter A, you would use

 mysql> select * from grocery_inventory where item_name like 'A%'; +----+-----------+-------------------------+------------+----------+ | id | item_name | item_desc               | item_price | curr_qty | +----+-----------+-------------------------+------------+----------+ |  1 | Apples    | Beautiful, ripe apples. |       0.25 |     1000 | |  6 | Avocado   | Large Haas variety.     |       0.99 |      750 | +----+-----------+-------------------------+------------+----------+ 

By the Way

Unless performing a LIKE comparison on a binary string, the comparison is not case sensitive. You can force a case-sensitive comparison using the BINARY keyword.




Sams Teach Yourself PHP MySQL and Apache All in One
Sams Teach Yourself PHP, MySQL and Apache All in One (4th Edition)
ISBN: 067232976X
EAN: 2147483647
Year: 2003
Pages: 333
Authors: Julie Meloni

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net