5. Selecting records and reporting

Once the database has been set up and the tables populated with records, it is time to use the database in earnest by selecting records out of it.

The returned results can be used to generate nicely formatted reports or they can be used to process the data in some way.

The most widely used command to extract records from a database is the SELECT command. The example we have used has been expanded slightly to include three records

All records

If we wanted to extract ALL records from the table, the following command is issued

SELECT * FROM `clubs`

The command begins with the word SELECT, then a star * symbol which means 'all fields' in sql followed by the FROM statement and a table name. The generic command is

SELECT {fields to be extracted} FROM {table}

The command extracts all records because there is no WHERE clause to qualify the records. This is quite an useful command of itself if you intend to process every record in the defined table in some way.

Extracting a single record

The sql SELECT command can be extended in order to extract records that meet a specific criteria.

Example: extracting a single record

The easiest way to do this is to target the primary key with a single value. Like this

SELECT * FROM `clubs` WHERE `ClubID`=2

This returns a single record as shown below

The new part of the command is the WHERE clause which in this case only selects the record whose primary key is 2

Extracting a number of records

We have described how to pull all records (absent WHERE clause) and we have described how to extract a single record (WHERE includes single value primary key).

But another very common requirement is to extract a sub-set of the table records.

For instance the command

SELECT * FROM `clubs` WHERE `ClubID`>1

This has a WHERE clause that will select any record whose primary key is greater than one. The result in the example is :

The WHERE clause of an sql query can be very complicated in order to extract some specific records.