Using Conditionals


The problem with the SELECT statement as I have used it thus far is that it will automatically retrieve every record. While this isn't a big issue when dealing with a few rows of information, it will greatly hinder the performance of your database as the number of records grows. To improve the efficiency of your SELECT statements, you can use different conditionals in an almost limitless number of combinations. These conditionals use the SQL term WHERE and are written much as you'd write a conditional in PHP.

 SELECT * FROM tablename WHERE columnname  = 'value SELECT email FROM users WHERE last_name  = 'Lennon SELECT name FROM people WHERE birth_date  = '2005-01-26 

Table 4.7 lists the most common operators you would use within a WHERE conditional. These operators can be used together, along with parentheses, to create more complex expressions.

 SELECT * FROM users WHERE (user_id >= 10)  AND (user_id <= 20) SELECT * FROM users WHERE (last_name =  'Bank) OR (last_name = 'Banks') 

Table 4.7. These MySQL operators are frequently (but not exclusively) used with WHERE expressions.

MySQL Operators

OPERATOR

MEANING

=

equals

<

less than

>

greater than

<=

less than or equal to

>=

greater than or equal to

!=

not equal to

IS NOT NULL

has a value

IS NULL

does not have a value

BETWEEN

within a range

NOT BETWEEN

outside of a range

OR (also ||)

where one of two conditionals is true

AND (also &&)

where both conditionals are true

NOT (also !)

where the condition is not true


To demonstrate using conditionals, I'll retrieve more specific data from the sitename database. The examples that follow will be just a few of the possibilities. Over the course of this chapter and the entire book you will see any number of variants on SELECT conditionals.

To use conditionals

1.

Select the records for every user registered on a specific date (Figure 4.15).

 SELECT * FROM users WHERE  (registration_date > '2005-02-23  00:00:00) AND (registration_date  < '2005-02-24 00:00:00); 

Figure 4.15. Using WHERE with two statements and the AND allows me to pinpoint which records are returned.


To get the users who registered on February 23, 2005, I select those whose registration date is greater than midnight on that day and less than midnight on the following day (I could have also used the BETWEEN operator). If the registration_date was of type DATE (meaning of the form YYYY-MM-DD), I could use an equals here (registration_date = '2005-02-23').

2.

Select all the first names of users whose last name is Simpson (Figure 4.16).

 SELECT first_name FROM users WHERE  last_name = 'Simpson; 

Figure 4.16. All of the Simpsons who have registered.


Here I'm just returning one field (first_name) for each row. The returned records themselves are determined by the value of another field (last_name).

3.

Select everything from every record in the users table that does not have an email address (Figure 4.17).

 SELECT * FROM users WHERE email IS  NULL; 

Figure 4.17. No records are returned by this query because email cannot have a NULL value. So this query did work; it just had no matching records.


The IS NULL conditional is the same as saying does not have a value. Keep in mind that an empty string is the same thing as a value, in NULL terms, and therefore would not match this condition. Such a case would, however, match

 SELECT * FROM users WHERE email=''; 

4.

Select the record in which the password is password (Figure 4.18).

 SELECT * FROM users WHERE password=  SHA('password); 

Figure 4.18. Use the same encryption function again (SHA()) to check against encrypted, stored values.


Since the stored passwords were encrypted with the SHA() function, you can find a match by comparing the stored version against an encrypted version. SHA() is case-sensitive, so this query will work only if the passwords (stored vs. queried) match exactly.

Tips

  • Strange as it may seem, you do not have to select a column on which you are performing a WHERE.

     SELECT user_id FROM users WHERE  first_name = 'Brent 

    The reason for this is that the columns listed after SELECT indicate only what fields to return and the columns listed in a WHERE indicate which records to use as a basis for retrieval.

  • You can also use the IN and NOT IN operators to determine if a column's value is or is not one of a listed set of values.

     SELECT * FROM people WHERE  birth_date IN ('2005-01-24,  '2004-04-26, '2004-04-28') 

  • You can perform mathematical calculations within your queries using the numeric addition (+), subtraction (-), multiplication (*), and division (/) characters.

  • Although the previous demonstrations use conditionals with SELECT, they will be used with many types of queries, as you'll see later in this chapter.




    PHP and MySQL for Dynamic Web Sites. Visual QuickPro Guide
    PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition)
    ISBN: 0321336577
    EAN: 2147483647
    Year: 2005
    Pages: 166
    Authors: Larry Ullman

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