Preparing SQL Statements

SQL is to databases as HTML is to Web pages. SQL is an amazingly versatile language, as much at home in Microsoft Access as it is in SQL Server or Oracle data sources. SQL can manage records (updating, adding, or deleting), gather subsets, or create tables and even other SQL statements on the fly.

As pointed out in the beginning of this chapter, you don't have to know SQL to build basic applications in Dreamweaver. The key word here is basic; when you start to need more than simple recordsets filtered on one field or derived from a single table and that time will come sooner than later you'll need to write some SQL. Although advanced SQL statements can become involved and difficult to decipher, the language is quite approachable.

SQL Basics

The most common SQL function in data-driven Web applications is undoubtedly creating recordsets. As the name implies, a recordset is merely a subset of all the records. Don't be misled, however; a recordset can also contain all the records. To get all of the records from a data source table, we use two SQL keywords: SELECT and FROM. For example, this SQL statement

 SELECT * FROM mybooks 

returns all the records and all the fields from the mybooks table in the current data source. The asterisk is a special wildcard character that signifies to return all the fields. To return only the authors and titles fields, the SQL statement would appear like this:

 SELECT authors, titles FROM mybooks 

Individual fields are entered in a comma-separated list. But what if you wanted to limit not just the fields, but also the records? To limit or filter a SQL statement, you use the WHERE clause. For example, let's say you wanted to see only those books written by James Ellroy or, in other words, where the author was equal to James Ellroy. Here's the SQL statement you would use:

 SELECT authors, titles FROM mybooks WHERE authors="James Ellroy" 

The WHERE clause of a SQL statement is definitely where the action is, and it has the operators to prove it. Within a WHERE clause, you can use a logical AND to return a recordset where two or more conditions are met. For example, if you wanted to find all the hardback editions by James Ellroy in your data source, this SQL statement would give you the answer:

Note

A couple notes on SQL statement formatting before we proceed: Although it is not mandatory to put the different clauses (SELECT, FROM, WHERE) on different lines, whitespace is irrelevant in SQL, and it greatly helps the readability. Also, uppercasing the SQL keywords is convention and not required; it does, however, make the statements easier to follow as they get more complex.

 SELECT authors, titles, booktype FROM mybooks WHERE authors="James Ellroy" AND booktype="hardback" 

Other SQL logical operators include OR, NOT, and LIKE, among others. Mathematical operators are also supported, including: <, >, <=, >=, =, and <>.

So far, these examples have just output all the requested records in no particular sequence, although in some data sources, such as Access, the records would be returned in the order in which they were entered. To sort the returned records, the ORDER BY key phrase is used. If you're interested in seeing James Ellroy hardbacks in alphabetical order, by title, the SQL would look like this:

 SELECT authors, titles, booktype FROM mybooks WHERE authors="James Ellroy" AND booktype="hardback" ORDER BY titles ASC 

The ASC keyword indicates an ascending order; ASC is the default and could be omitted. To see the titles in reverse alphabetical order, use the keyword DESC.

In a table like this one, several of the authors might have written numerous books. Therefore, you have to use another keyword to see a list of the individual authors, without repeating names. That keyword is DISTINCT, and it is paired with SELECT. For example, the following SQL statement

 SELECT DISTINCT authors FROM mybooks WHERE edition="hardback" ORDER BY authors ASC 

returns a recordset containing just the unique names of the authors who have hardback books.

Combining Data from Multiple Tables

Modern data sources gain much of their power and flexibility from their ability to provide data that relates to each other. SQL statements make it possible to retrieve information from multiple related tables. In this situation, it takes more than just a SQL keyword to get the desired results the connection must be embedded in the tables.

Let's look at an example. To truly make the sample data source discussed in the previous topic more useful, you can split the one mybooks table into two: books and authors. Let's say that mybooks contains the following fields:

• title

• author

• edition

• publication_date

Now, the authors table might have these fields:

• name

• website

• genres

How would you relate the two tables? Putting the question another way, what do these two tables have in common? The answer is the author's name; in the books table, it is the author field, and in the authors table, it is name. So, if you wanted to see a list of all the Web sites by authors of paperbacks, your SQL statement might read like this:

 SELECT authors.websites FROM books, authors WHERE edition="paperback" AND books.author=authors.name 

Immediately you should notice a couple of differences in each clause of the SQL statement. First, in the SELECT clause, we're not just asking for a field, like websites; we're asking for a field name from a specific table, authors.websites. Second, the FROM keyword contains more than one table in a comma-separated list. Third and most importantly the WHERE clause contains a segment that joins one table to another, books.author=author.name.

Before we explore the JOIN keyword, it's important to point out a potential problem with our example database design. To successfully match one table to another, the author's names must be identical in both. Although this might work for a while, sooner or later, trouble will arise. Eventually, the author's name might not be entered the same in one table or the other; you could easily enter T. Clancy knowing you meant Tom Clancy. Or, perhaps, a book written by an author using a pseudonym would be entered. It's far better to create a separate field that uses unique numbers as the primary key in the authors table (call it authorID) and then reference that field in the books table, like this:

 SELECT authors.websites FROM books, authors WHERE edition="paperback" AND books.authorID=authors.authorID 

You can also write this same SQL statement using an INNER JOIN, like this:

 SELECT authors.websites FROM (books INNER JOIN authors ON books.authorID=authors.authorID) WHERE edition="paperback" 

The two SQL statements are functionally the same, but you're more likely to encounter the second syntax in professionally coded queries.

This is just the start of what's possible with SQL, but it should give you a good foundation for understanding some of the advanced SQL used in the recipes.