Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

Using Database in Web Development – Part 7

Tuesday, June 3rd, 2014 by Servage

o

A WHERE clause allows you to filter out any unwanted data so that the data you do get is exactly what you are looking for. Let’s look for all of the people named “Paul” in our table.

 

  SELECT first_name, email
    FROM contacts
    WHERE first_name = 'Paul';

Using the WHERE clause tells the DBMS you are only interested in data that has “Paul” in the “first_name” column. In our case we will get only one result because there is only one person in our “contacts” table with the name of “Paul”.

So, what if I want to filter my data using a number like in the “contact_id” column? Can I do that?

Absolutely. To search for the person that has the “contact_id” of 3 your SELECT statement would look like this:

  SELECT first_name, email
    FROM contacts
    WHERE contact_id = 3;

Notice there are no single quotes around the number 3 this time. That’s because we are filtering by a column that is numeric, not text.

Can I filter data in other ways?

You bet. There are a whole host of  operators that you can use to make comparisons and filter data. Keep in mind, though, each DBMS can be different and the operators they choose to use can change from DBMS to DBMS.

Here are some of the most common operators that DBMSs use.

Operator Description
= Compares the data to your criteria to see if it is equal
<> Compares the data to your criteria to see if it is not equal
< Compares the data to your criteria to see if the data is less than your criteria
<= Compares the data to your criteria to see if the data is less than or equal to your criteria
> Compares the data to your criteria to see if the data is greater than your criteria
>= Compares the data to your criteria to see if the data is greater than or equal to your criteria
IS NULL Checks the data to make sure that there is no data in the column

 

Alright, you can get your data back out now. Let’s look at some ways to make your DBMS do some extra work for you by sorting, grouping and narrowing down choices.

Some more about WHERE

 

Earlier you learned about using WHERE to filter your data. What if you have a much more complex set of criteria that you would like to apply to filter? Well, you’re in luck. SQL gives you quite a lot of flexibility in defining how you filter your data.

What if I have more than one criteria? You can use AND and OR. Both AND and OR do exactly what you might think, they allow you to string as many conditions together as you like. Here’s and example of both:

  SELECT *
  FROM table
  WHERE first_name='John' AND
     (last_name='Lennon' OR last_name='Mellencamp');

So, how does that work? It simply states that we are looking for anyone with the first name of John and the last name of Lennon or Mellencamp. They must have the first name of John but can have either the last name of Lennon or Mellencamp.

The parentheses not only help you to visually see how things are grouped together but they also let the DBMS know what to do. Imagine if you moved the parentheses around to look something like this:

  SELECT *
  FROM table
  WHERE (first_name='John' AND last_name='Lennon')
   OR last_name='Mellencamp';

Now what will the DBMS look for? In this case the DBMS will send back anyone named John Lennon or anyone with the last name of Mellencamp. Herman Mellencamp would be a valid name using this criteria but would not be valid in the first example.

How about this scenario. You want to find the data on John Mellencamp but you can’t remember how to spell his last name. (Just play along …) You do remember that the last 4 characters are “camp”, though. How are you going to find him?

Well, SQL has included several wildcards to help you out. Different wildcards tell the DBMS to do different things. And just to make things more confusing, different DBMS’s use different characters for their wildcards, so be sure to check your DBMS before you start throwing wildcards around.

In our scenario above this is how the wildcard would work:

  SELECT *
  FROM table
  WHERE last_name LIKE '%camp';

Hey! What’s that LIKE doing there? The LIKE lets the DBMS know that we won’t be doing a strict comparison like = or < or > but we will be using wildcards in our comparison.

So, can I use LIKE and = in the same WHERE clause? You sure can just as long as you don’t try to combine them into a single comparison like last_name = LIKE ‘%camp’.

Something like this would be valid, though:

  WHERE first_name='John' AND
     last_name LIKE '%camp'

Now, back to the wildcard itself. The wildcard tells the DBMS to look for anyone that has “camp” as the last 4 characters in their last name. The % says everything before the last 4 characters is irrelevant. So, what if you moved that % after the “p”?

  WHERE last_name LIKE 'camp%'

It will return all last names beginning with “camp” which probably will be none since we never capitalized the “c”. Remember, searching for a string (set of alphanumeric characters) is case sensitive. If you are ever unsure of the case of something you may want to convert everything to upper or lower case before you make your comparisons. I’ll show you how to do this later in this series.

Alright, how about something like this now:

  WHERE last_name LIKE '%camp%'

This will search for anyone with the characters “camp” anywhere in the last name. It will return all of these examples: “camp”, “campbell” and “mocamp”.

As you can see, it is very important where you place your wildcards. It is also important that you don’t overuse the wildcards. Wildcards can easily get out of control if you overuse them.

There are other wildcards as well like the brackets [] and the underscore _. These wildcards are a bit more complex and beyond the scope of this lesson but we may come back to them later.

Sources for Further Reading

Using Database in Web Development - Part 7, 5.0 out of 5 based on 1 rating
Categories: Guides & Tutorials

Keywords:

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.