MySQL Where

In a previous lesson we did a SELECT query to get all the data from our "example" table. If we wanted to select only certain entries of our table, then we would use the keyword WHERE.

WHERE lets you specify requirements that entries must meet in order to be returned in the MySQL result. Those entries that do not pass the test will be left out. We will be assuming the data from a previous lesson for the following examples.

Being Selective With Your MySQL Selection

There are three entries in our "example" table: Tim, Sandy, and Bobby. To select Sandy only we could either specify Sandy's age (21) or we could use her name (Sandy Smith). In the future there may be other people who are 21, so we will use her name as our requirement.
WHERE is used in conjuction with a mathematical statement. In our example we will want to select all rows that have the string "Sandy Smith" in the "names" column (mathematically: {name column} = "Sandy Smith"). Here's how to do it.

PHP & MySQL Code:

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Get a specific result from the "example" table
$result = mysql_query("SELECT * FROM example
 WHERE name='Sandy Smith'") or die(mysql_error());  

// get the first (and hopefully only) entry from the result
$row = mysql_fetch_array( $result );
// Print out the contents of each row into a table 
echo $row['name']." - ".$row['age'];
?>

Display:

Sandy Smith - 21

MySQL Wildcard Usage '%'

If you wanted to select every person in the table who was in their 20's, how could you go about doing it? With the tools you have now, you could make 10 different queries, one for each age 20, 21, 22...but that seems like more work than we need to do.
In MySQL there is a "wildcard" character '%' that can be used to search for partial matches in your database. The '%' tells MySQL to ignore the text that would normally appear in place of the wildcard. For example '2%' would match the following: 20, 25, 2000000, 2avkldj3jklsaf, and 2!
On the other hand, '2%' would not match the following: 122, a20, and 32.

MySQL Query WHERE With Wildcard

To solve our problem from before, selecting everyone who is their 20's from or MySQL table, we can utilize wildcards to pick out all strings starting with a 2.

PHP & MySQL Code:

<?php
// Connect to MySQL

// Insert a row of information into the table "example"
$result = mysql_query("SELECT * FROM example WHERE age LIKE '2%' ") 
or die(mysql_error());  

// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
 // Print out the contents of each row
 echo $row['name']." - ".$row['age']. "<br />";
} 
?>

Display:

Timmy Mellowman - 23
Sandy Smith - 21

You can use this wildcard at the beginning, middle, and end of the string. Experiment with it so you can see for yourself how powerful this little trick can be.
Note: The wildcard was used for example purposes only. If you really wanted to explicilty select people who are in their 20's you would use greater than 19 and less than 30 to define the 20's range. Using a wildcard in this example would select unwanted cases, like a 2 year old and your 200 year old great-great-great-grandparents.

Posted in: MySQL