Databases and Dreamweaver MX 2004

Typically, incorporating a database with ColdFusion requires the following:

• A database
  
• A data source
  
• SQL queries

SQL queries allow you to select, insert, update, and delete data from a database. A data source is a name that you assign to a database in the ColdFusion Administrator. It is probably easiest to think of a data source as being an alias pointing to the database file. You will use this alias when you create Recordsets in Dreamweaver or write your own queries using CFML. To learn how to set up a data source, refer to the “Setting Up a Data Source” section later in this chapter.

You will use SQL to talk to the database. The four most common SQL statements areSELECT,INSERT,UPDATE, andDELETE. Each type of database application has its own “extensions” to SQL, but these are the four basic SQL commands that are commonly supported. Let’s look at these four actions in more detail.

SELECT

SELECTqueries retrieve data from your database, and this type makes up the majority of queries you will encounter in CFML development. You can return data from a single table, multiple tables, all columns in a table, or only rows in a table that match a certain criteria.

The generic syntax forSELECTstatements is:

SELECT [list of columns]
FROM [table name]
WHERE [column] = [value]
ORDER BY [column] [order];

TheWHEREandORDER BYclauses in theSELECT statement are optional, and it is worth mentioning that you can have as manyWHERE clauses as you need, as long as each one is separated byAND orORkeywords (or some other Boolean operator). You will look at a series ofSELECTclauses later in this chapter.

The simplest form of theSELECTstatement would be:

SELECT [list of columns]
FROM [table name];

This will return all the data in the listed columns from the specified table.

The following is a sampleSELECTquery that retrieves a single record from the Employee table:

SELECT Emp_ID, FirstName, LastName, Dept_ID FROM Employee
WHERE Emp_ID = 24;

To view all of the data in a table, use “*” as the column name.

INSERTqueries are responsible for adding new records into a table. For example, you could use them to allow new members to register on the web site. You could also post new news articles to a web site. Typically, anINSERTquery will add only a single record into a database, but in Chapter 6 you will learn how to use a singleINSERTquery to add multiple records into a table.

The generic syntax forINSERTstatements is:

INSERT INTO [table name] ( [list of columns])
VALUES ( [corresponding list of values] );

A single record is inserted into the table, and the columns are set to the corresponding values. You can see an example of this here:

INSERT INTO Employee( FirstName, LastName, Dept_ID, StartDate, Salary, Contract ) VALUES ( 'test', 'employee', 2, '2-28-2003', 1000, 'No' );

You can see in the preceding code that you must enclose all alphanumeric strings in single quotes; numeric values do not need quotes.

UPDATEqueries are similar toINSERTqueries, except that they update existing records in a table.UPDATE queries can be very dangerous unless you exercise caution, because by default they update all records in a table unless you explicitly specify which records you wish to update (by using aWHEREclause).

The generic syntax forUPDATE statements is:

UPDATE [table name]
SET [column] = [value]
WHERE [column] = [value];

Records in the table are updated to the values specified in theSETlineWHERE the column has a particular value. You can use this behavior a couple of ways. You can update a single record where the table’s primary key has a particular value, like so:

UPDATE Employee
SET Dept_ID = 3
WHERE Emp_ID = 21;

You can also update all records within a table where a column has a particular value. For example, say you wanted to move all employees from one department to another, as follows:

UPDATE Employee
SET Dept_ID = 4
WHERE Dept_ID = 2;

TheUPDATE statement can update multiple columns at the same time as well. Using the following code, you can modify both the salary and department of an employee in a singleUPDATEstatement:

UPDATE Employee
SET Dept_ID = 4,
        Salary = 100000
WHERE Emp_ID = 20;

If you accidentally forget theWHERE clause in anUPDATEstatement, SQL assumes that you want to update every record in the table, which usually produces undesirable results. For this reason, always keep backups of your current data; it is all too easy to make a simple mistake in your SQL statement and lose a large amount of data.

DELETEqueries remove records from a table.DELETEwill remove all records from a table unless you explicitly specify which records to delete, so be very careful when writing these queries.

The generic syntax forDELETEstatements is:

DELETE FROM [table name]
WHERE [column] = [value];

Every row is deleted from the specified tableWHEREa column contains a certain value. There are a few different ways to use this tag. You could delete every record from the Employee table where the Emp_ID equals 6 (where 6 is the value of the primary key), causing one row to be deleted from the database:

DELETE FROM Employee
WHERE Emp_ID = 6;

You could also use the precedingDELETEstatement to delete all records from the Employee table where the employees belong to a certain department. An example of this is shown here:

DELETE FROM Employee
WHERE Dept_ID = 1;

Exercise caution here, just as you did when using theUPDATEstatement; if you accidentally forget theWHEREclause in aDELETEstatement, SQL will assume that you want to delete every record in the table. It is also worth mentioning that SQL is not deleting columns or fields in the table, but entire records! To clear the contents of a column or field, you must use anUPDATEstatement and specify the particular columns you are interested in.

We discuss SQL in more detail later in this chapter and in Chapter 6.