Web Development Intensive

Let’s cover the details of the SELECT statement in SQL. The SELECT statement is the command we use to do the Read part of database CRUD. CRUD is an acronym that stands for Create, Read, Update, Delete – the four operations you are most likely to do with a database.  At any given time, you are either creating data, reading data, updating data, or deleting data.

We’ll start with the simplest possible SELECT statement in action, and then move to more nuanced implementations.

Let’s say we run a business managing art collections for wealthy individuals. We might want to build an inventory management application to keep track of which paintings our clients own.

The first step towards that would be to have a table that holds a list of our clients. Let’s call this table “collectors”. And let’s say we put the following information in it:

An easy read

To do a query on the database to get all the information on all the rows in our table, we might do a simple SELECT statement as follows:

SELECT * FROM collectors WHERE 1

And reacall that in PHP, we execute queries on the database using the mysql_query() built-in function:

$myQuery = "SELECT * FROM collectors WHERE 1";
$result = mysql_query($myQuery);

This would return the full data set internally represented in basically the same way as what we’ve already seen.  The order that the data is stored inside of a database is not necessarily chronological order, so the data may show up in a different order if you don’t specify in what order you’d like to receive it.

Selecting specific fields

Let’s hypothetically say we only wanted to get the id and last name for each collector in our table. To do this, we would specify which fields we were interested in as follows:

SELECT id, last_name FROM collectors WHERE 1

Notice that we use a comma delimited list of field names after the SELECT keyword. This query would return a data set that looked like this:

Ordering results

And now let’s say we wanted to order the results alphabetically by last name starting with A and ending in Z:

SELECT id, last_name FROM collectors WHERE 1 ORDER BY last_name ASC

The “ORDER BY” clause takes two parameters: the field we want to order the results by, and the order we want them returned in: either ASC or DESC for ascending and desc ending respectively.

Our example query would return a result set that looked like this:

Modifying the WHERE clause

To do a query that only returned the results for the collector with id #3, we would do a query like this:

SELECT * FROM collectors WHERE id=3

This would return a data set that looked like this:

We can specify any criteria we want in the WHERE clause. If we only wanted to get the data for users who were NOT id #3, we could do this:

SELECT * FROM collectors WHERE id<>3

The <> symbol is the SQL way of saying NOT EQUALS.

Paginating results

Lastly, but not leastly in this set of examples, let’s say we had 400 collectors in our table. And we want to make a web-based interface that only shows 10 at a time, ordered alphabetically by last name. Will will have page number links so users can click on which “page” of results they want to view: 1,2,3,4, etc.

To paginate results, we would require a LIMIT clause on the SELECT query. For example, this query would return only the first 10 rows:

SELECT * FROM collectors WHERE 1 ORDER BY last_name ASC LIMIT 0,10

The LIMIT clause in this case takes two arguments, separated by a comma: the result number to start from, and the number of results to return. In this case, we are telling SQL to start from the first result in the entire data set of results, and return only 10 results total.

To get the next “page” of results, we might make a query to start from the 11th result, and return the next 10:

SELECT * FROM collectors WHERE 1 ORDER BY last_name ASC LIMIT 11,10

And so on…

Joining multiple tables of data

Now that we’ve covered all the basic elements of the SELECT statement, let’s use them to select data from multiple tables.

Let’s say we had a second table in our example. This table was a list of all the paintings that each of our collectors owns. Let’s call this table “paintings”, and it might look like this:

Notice that this table has a foreign key field, “collector_id” that holds the id of the collector who owns each particular piece.

So let’s now say we wanted to combine the two sets of data. We want to get a list of all the paintings in our database, as well as the last names of collectors who own them. We might do a query like this:

SELECT paintings.*, collectors.last_name FROM paintings, collectors WHERE paintings.collector_id=collectors.id

This would return a set of data that looked like this:

Notice that we have merged data from both tables. The result set now has all of the data from the “paintings table”, as well as the last_name field from the “collectors” table.

There are two new things going on in this example. Firstly, when we are dealing with multiple tables, we have to be very clear about which fields we want to get data from, and which table those fields are in.

In this case, we asked for all fields from the “paintings” table by specifying “paintings.*”, and we also asked for only the “last_name” field of the “collectors” table by specifying “collectors.last_name” in the query.

Secondly, the WHERE clause tells SQL how to merge the tables. By specyfing “WHERE paintings.collector_id=collectors.id”, we are telling SQL to match up the two sets of data by the fields “paintings.collector_id” and “collectors.id”. In other words, we are telling SQL that the “collector_id” field in the “paintings” table is a foreign key that points to the “id” field of the “collectors” table.

So for every row in the “paintings” table, SQL looks at the “collector_id” field, and finds the “last_name” field from the corresponding row in the “collectors” table.

This type of merging of data from two tables by matching up a field in one table with another field from another table is known as an inner join in SQL terminology. This is, by far, the most common type of join, or merge, that developers do.

No related posts.

Tagged: class 9