Databases Lesson 5:
Queries
The WHERE Command
The most powerful part of MySQL is using it to perform WHERE queries, which specify exactly what you are looking for. For example, you could create a query in the example videogame database to find all the games for the Wii. In our examples we will be creating a new PHP file for each query, but in Advanced Web Design you can learn a more efficient way to do this using a technology known as Ajax.
- 1. Save a copy of your index.php project file and call it "wii.php."
- 2. Replace your $query= line with the following, noting that you will need to change it to match your database:
$query = "SELECT * FROM `List` WHERE `System` LIKE 'Wii'";
The above command selects everything (because of the *) from the table called List, but the WHERE command limits it to only those records that have a system equal to "Wii." While you can use LIKE for checking if two things are equal you would use the following for other comparisons:
| Symbol | Function |
|---|---|
| = | Equals (For Numbers) |
| LIKE | Equals (For Strings) |
| < | Less Than |
| <= | Less Than or Equal To |
| > | Greater Than |
| >= | Greater Than or Equal To |
| <> | Not Equal To |
| != | Not Equal To |
| OR | Join Statements Using Or |
| AND | Join Statements Using And |
Note that there are two ways to write not equal to, but there are not differences between the two so use whichever you want. OR and AND can be used to join two or more statements together. Statements joined with OR will be two if any of the conditions are true while statements joined with AND will only be true if all of the statements are true.
The ORDER BY Command
MySQL makes it very easy to sort your data using the ORDER BY command. We will modify the query from above to sort our data.
- 3. Add the following text in bold to the end of your query, again modifying it for your database:
$query = "SELECT * FROM `List` WHERE `System` LIKE 'Wii' ORDER BY `Name`";
ORDER BY can sort by any of your fields, and can actually sort by multiple fields although that is beyond the scope of this lesson. You do not need to use ORDER BY with a WHERE command so you could sort the entire table by a certain field.
The Wildcard
The wildcard character (%) can be used to find partial matches in your database. For example, if you wanted to find all the Zelda games in the videogame database you would use the following query:
$query = "SELECT * FROM `List` WHERE `Name` LIKE '%Zelda%'"
Putting the wildcard character around the word Zelda means Zelda can appear anywhere in the Name field. The next example would be if you wanted Zelda to appear at the beginning of the field:
$query = "SELECT * FROM `List` WHERE `Name` LIKE 'Zelda%'"
There is no wildcard before Zelda so no text can appear before Zelda, but the wildcard after Zelda means any text can come after it. This last example would be used if you want Zelda to appear at the end of the field:
$query = "SELECT * FROM `List` WHERE `Name` LIKE '%Zelda'"
Since the wildcard comes before Zelda any text can show up before the word, but there is no wildcard character after the word so Zelda must be the last word in the field.