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.
Download Tizag.com's MySQL Book
If you would rather download the PDF of this tutorial, check out our
MySQL eBook from the Tizag.com store.
You may also be interested in getting the PHP eBook Found Something Wrong in this Lesson?Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time! | 
