MySQL and the WHERE part

In the previous chapter, we saw how we could use the SELECT query to get data from the database, but we weren't exactly very picky about which data to get. As you will realize in the following chapters, the SELECT statement is very powerful and allows you to do pick, prioritize and sort your data in all sorts of way before returning it. In this chapter we will have a look at the WHERE part, which allows you to decide which data to select.

An SQL statement with a WHERE part could look like this:

SELECT id, name, country FROM test_users WHERE id > 3

You can of course set more than one criteria:

SELECT id, name, country FROM test_users WHERE id > 3 AND id < 8

And you can use strings as criteria as well:

SELECT id, name, country FROM test_users WHERE country = 'USA'

You can even use MySQL's own functions in the WHERE part. For instance like this, where we use the SUBSTRING() function in MySQL to get all users starting with the letter "S":

SELECT id, name, country FROM test_users WHERE SUBSTRING(name, 1, 1) = 'S'

To test the examples above, you can use this test code we wrote in a previous chapter. Simply put one of the queries inside the mysql_query() function and run the code:

mysql_connect("localhost", "username", "password");
mysql_select_db("my_database");

$query = mysql_query("Insert your SQL query here");
while($row = mysql_fetch_array($query))
    echo $row["name"] . " is from " . $row["country"] . "<br />";