Web Database Applications with PHP and MySQL, 2nd Edition

Querying with SQL SELECT

The SELECT statement is used to query and retrieve one or more rows from a database. We introduce it in this section, and then show you the WHERE clause for selecting data that matches a condition. The section concludes with an introduction to the more advanced features of SELECT statements and a short case study.

Basic Querying

Consider an example SELECT statement:

SELECT surname, firstname FROM customer;

This outputs the values of the attributes surname and firstname from all rows in the customer table. Assuming we previously inserted four rows when we created the winestore database, the output from the MySQL command interpreter is:

+-----------+-----------+
| surname   | firstname |
+-----------+-----------+
| Marzalla  | Dimitria  |
| LaTrobe   | Anthony   |
| Fong      | Nicholas  |
| Stribling | James     |
+-----------+-----------+
4 rows in set (0.04 sec)

Any attributes of a table may be listed in a SELECT statement by separating them with a comma. If all attributes are required, the shortcut of an asterisk character (*) can be used. Consider the statement:

SELECT * FROM region;

This outputs all the data from the table region:

+-----------+---------------------+ | region_id | region_name | +-----------+---------------------+ | 1 | All | | 2 | Goulburn Valley | | 3 | Rutherglen | | 4 | Coonawarra | | 5 | Upper Hunter Valley | | 6 | Lower Hunter Valley | | 7 | Barossa Valley | | 8 | Riverland | | 9 | Margaret River | | 10 | Swan Valley | +-----------+---------------------+ ...