SQL primer for Dreamweaver users
Modified
16 April 2007
Requirements
User level
Beginning
Required products
- Dreamweaver CS3 (Download trial)
Note: This article was written for Dreamweaver CS3. However, the content is still valid.
This article describes how to use Structured Query Language (SQL) to define recordsets in Adobe Dreamweaver. SQL (pronounced sequel) is a language that lets you read and write data from a database. The language has only a few keywords and simple syntax rules, but still allows you to perform sophisticated database operations.
Syntax basics
One of the most frequently used SQL statements is the SELECT statement, which extracts specified columns from one or more database tables to build a recordset. Here’s the basic syntax for a SELECT statement:
SELECT ColumnName FROM TableName
You can add line breaks, tabs, and other white space to your statements to clarify the logic: SQL ignores all white space. The following example shows a valid statement:
SELECT PaidDues
FROM Members
Note that a query inserted in a dynamic page does not need a semicolon at the end of it. However, a semicolon is required if you address the database directly, such as with the MySQL monitor.
The following keywords identify commonly used SQL commands:
| Keyword | Description |
|---|---|
| SELECT | Retrieves the specified records from a database |
| INSERT | Adds a new record in a database table |
| UPDATE | Changes values in specified database records |
| DELETE | Removes specified database records |
The following keywords are used to refine SQL statements:
| Keyword | Description |
|---|---|
| FROM | Names the data source for an operation |
| WHERE | Sets one or more conditions for the operation |
| ORDER BY | Sorts the recordset rows in a specified order |
| GROUP BY | Groups the recordset by the specified select list items |
The following operators specify conditions and perform logical and numeric functions:
| Operator | Meaning |
|---|---|
| = | Equal to |
| LIKE | Pattern matches (wildcards OK) |
| <> | Not equal to |
| NOT LIKE | Pattern does not match (wildcards OK) |
| < | Less than |
| > | Greater than |
| <= | Less than or equal to |
| >= | Greater than or equal to |
| AND | Both conditions must be met, such as Louisiana AND Texas |
| OR | At least one condition must be met, such as Smith OR Smyth |
| NOT | Exclude the condition following, such as Paris NOT France |
If the item being compared is text, place it in single quotes as in the following example:
...WHERE Country = 'Germany'
If the item being compared is a date and you’re working with a MySQL database, use the date format YYYY-MM-DD and place the date in single quotes:
...WHERE DateOfBirth < '1970-01-01'
If you're working with a Microsoft Access database, use the MM-DD-YYYY format and place the date in # signs:
...WHERE DateOfBirth < #01/01/1970#
Other databases may have their own date conventions. Consult the database system’s documentation.Some database systems may use non-standard SQL syntax in their products. Check your database system’s documentation.
Defining the columns of a recordset
You can use SQL to define recordsets for your pages. A recordset is a subset of records extracted from a database.
Here’s the basic SQL syntax to define the columns of a recordset:
SELECT ColumnName1, ColumnName2, ColumnNameX FROM TableName
If you want to include all the columns of a table in the recordset, you can use the wildcard character *, as follows:
SELECT * FROM TableName
For example, suppose you have a table called Customers. To extract all the columns, you would type the following SELECT statement:
SELECT * FROM Customers
Suppose you only need the data contained in two columns of the Customers table: the YearBorn column and the DateLastPurchase column. To create a recordset containing only the data from these two columns, you would type the following SELECT statement:
SELECT YearBorn, DateLastPurchase
FROM Customers
Limiting the records in a recordset
Use a WHERE clause to limit the number of records in the recordset. For example, you may want to include only those customers who earn more than $50,000 a year. Assume you have a column in your table called Earnings that tells you how much each customer earns. Your SELECT statement would read as follows:
SELECT YearBorn, DateLastPurchase FROM Customers
WHERE Earnings > 50000
You specify one or more conditions in a WHERE clause to filter out records in the database. The following sections describe ways to filter records with the WHERE clause:
- Filtering records based on the equality of two values
- Filtering records based on the likeness of two values
- Filtering records based on a range of values
- Filtering records based on a combination of search conditions
Filtering records based on the equality of two values
You can filter records in a database based on the equality of a parameter’s value with a record column’s value.
Suppose you decide to let users search the database by department. The following logic is required to build the search results recordset:
- Check a record in the database table.
- If the value in the department column of the record is equal to the department name submitted by the user, then include that record in the search results recordset.
- Check the next record in the table.
You can express this logic with the following WHERE clause:
WHERE ColumnName = ParameterValue
ParameterValue is a SQL variable containing a search parameter. In a web application, the user typically supplies this parameter using an HTML form.
This database query could be expressed fully in SQL as follows:
SELECT FIRSTNAME, LASTNAME, DEPARTMENT, EMAIL
FROM EMPLOYEES
WHERE DEPARTMENT = varDept
This SQL statement finds all the records in the employee table with a DEPARTMENT value equal to the value contained in the varDept variable. For example, if the user specifies Operations as the department name, the SQL statement might generate the following recordset:
Filtering records based on the likeness of two values
You can filter records in a database based on the likeness of a parameter’s value with a record column’s value.
Using likeness instead of equality gives users more flexibility when specifying the value of search parameters. For example, search words don’t need to be case sensitive. If the user enters ohio and the table column contains the value Ohio, the match is made.
Also, likeness lets you use wildcard characters so users can perform alphabetical and partial-word searches. For example, if the user enters m and the table column contains the values Morgan, Macy, and Michelson, then you can use a wildcard character in the SQL statement so that all three matches are made.
The standard wildcard character is the percentage sign (%):
...WHERE LastName LIKE 'Mc%'
Suppose you decide to let users search the database by last names. The following logic is required to build the search results recordset:
- Check a record in the database table.
- If the value in the last name column of the record contains a value like the value submitted by the user, then include that record in the results recordset.
- Check the next record in the table.
You can express this logic with the following WHERE clause:
WHERE ColumnName LIKE ParameterValue
ParameterValue is a SQL variable containing a search parameter. In a web application, the user typically supplies this parameter using an HTML form.
This database query could be expressed fully in SQL as follows:
SELECT FIRSTNAME, LASTNAME, DEPARTMENT, EMAIL
FROM EMPLOYEES
WHERE DEPARTMENT LIKE 'varLastName'
If you want to give users the ability to perform partial-word searches, combine the variable with a wildcard character. The SQL wildcard character to use in this case is the percentage sign (%). Here’s an example:
...WHERE LASTNAME LIKE 'varLastName%'
For example, if the user types s as the search parameter, all records with last names starting with the letter s are included in the recordset, as in the following example:
If the user specifies sm as the search parameter, then only those records with last names that start with the letters sm are included in the recordset:
Filtering records based on a range of values
You can filter records in a database based on whether a record column’s value falls within the range of two parameter values.
Suppose you decide to let users search the database by a date range. The following logic is required to build the search results recordset:
- Check a record in the database table.
- If the value in the date column of the record falls between the two date values submitted by the user, then include that record in the results recordset.
- Check the next record in the table.
You can express this logic with the following WHERE clause:
WHERE ColumnName BETWEEN ParameterValue1 AND ParameterValue2
ParameterValue1 and ParameterValue2 are SQL variables containing search parameters. In a web application, the user typically supplies these parameters using an HTML form.
Here’s how this type of database query can be expressed in SQL:
SELECT FIRSTNAME, LASTNAME, DEPARTMENT, STARTDATE
FROM EMPLOYEES
WHERE STARTDATE BETWEEN 'varStartRange' AND 'varEndRange'
If you use MySQL, make sure the format of the date stored in the parameters is YYYY-MM-DD. Instead of using scripting to produce the correct format, you can use the MySQL function called DATE_FORMAT. For more information, see the MySQL documentation.
If you use Microsoft Access, the date range is expressed as follows:
...WHERE STARTDATE BETWEEN #varStartRange# AND #varEndRange#
For example, if the user enters 7/1/99 and 12/31/99 as the range parameters (reformatted as 1999-07-01 and 1999-12-31 for MySQL), all employees starting in the second half of 1999 are included in the recordset, as in the following example:
Filtering records based on a combination of search conditions
This section describe how to include records in the search results recordset based on a combination of search conditions. You combine search conditions in SQL using the AND, OR, and NOT logical operators.
If you want all the conditions to be true for a record to be included in the recordset, use the AND operator as follows:
...WHERE LASTNAME LIKE 'varLastName' AND DEPARTMENT LIKE 'varDept'
If you want any one of the conditions to be true for a record to be included in the recordset, use the OR operator as follows:
...WHERE LASTNAME LIKE 'varLastName' OR DEPARTMENT LIKE 'varDept'
If you want one condition to be true but not another, use the NOT operator as follows:
...WHERE DEPARTMENT LIKE 'varDept' AND NOT COUNTRY LIKE 'varCountry'
You can use parentheses to group search conditions:
...WHERE (DEPARTMENT LIKE 'varDept' AND STARTDATE < 'varStart')
OR STARTDATE BETWEEN 'varStartRange' AND 'varEndRange'
Sorting the records in a recordset
Use the ORDER BY clause to sort the records in your recordset. For example, suppose you want to sort the records in the recordset by customer earnings, from the lowest to the highest. In SQL, order the records as follows:
SELECT LastName, FirstName, Earnings FROM Customers
ORDER BY Earnings
By default, the ORDER BY clause sorts records in ascending order (1, 2, 3... or A, B, C...). If you want to sort them in descending order, from the highest earnings to the lowest, use the DESC keyword as follows:
...ORDER BY Earnings DESC
Joining tables
You can use a single SELECT statement to retrieve data from more than one table in the database. The statement joins the tables and returns a single recordset containing selected data from each table.
For example, a company database might contain one table with personal data about employees and another table with data about the company’s departments. If you want to build an employee directory that displays an employee’s name, phone number, and department, you must retrieve information from the two tables simultaneously.
To do this, create a join specifying all the tables to include and how the tables are related to each other. Here’s an example:
SELECT FIRSTNAME, LASTNAME, PHONE, DEPTNAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPT_ID = DEPARTMENTS.DEPT_ID
Note: Use dot notation to identify the columns more precisely. For example, EMPLOYEES.DEPT_ID refers to the DEPT_ID column in the EMPLOYEES table.
The first line specifies the columns to retrieve. The first three columns—FIRSTNAME, LASTNAME, PHONE—exist in the EMPLOYEES table, while the fourth column—DEPTNAME—exists only in the DEPARTMENTS table.
The second line specifies the two tables from which to retrieve data, EMPLOYEES and DEPARTMENTS.
The final line specifies the records to join and retrieve from the two tables. Each table has a column called DEPT_ID. (In the DEPARTMENTS table, the column is the primary key. For more information, see "Defining relationships between the tables" on page 988.) The WHERE clause compares the value of DEPT_ID in one table to the value of DEPT_ID in the other table. When a match is found, all the fields of the record in the EMPLOYEES table are joined with all the fields of the record in the DEPARTMENTS table. Next, the combined data is filtered to create a new record made up of a FIRSTNAME, LASTNAME, PHONE, and DEPTNAME column. Finally, the new record is added to the recordset.
Using slightly different join syntax may be preferable in some database systems. For example, the following SQL statement uses the SQL keywords INNER JOIN…ON to achieve the same results as the previous example:
SELECT FIRSTNAME, LASTNAME, PHONE, DEPTNAME
FROM EMPLOYEES INNER JOIN DEPARTMENTS
ON EMPLOYEES.DEPT_ID = DEPARTMENTS.DEPT_ID
Consult your database system’s documentation to determine which join syntax you should use.
More Like This
- Using Subversion with Adobe Dreamweaver CS5 – Part 2: Configuring Dreamweaver to use Subversion
- Using Subversion with Dreamweaver CS5 – Part 3: Configuring Apache with Subversion support
- Beginner's guide to databases
- Managing multiple subscriptions in PHP
- Building a subscribe/unsubscribe app in PHP with Dreamweaver CS3
- XSL overview
- Which server-side technology should I choose?
- Creating master and detail ColdFusion pages
- Adding database-driven shipping charge functionality to an online store
- Using Subversion with Dreamweaver CS5 – Part 1: Introducing Subversion
Tutorials and samples |
| 04/23/2012 | Resolution/Compatibility/liquid layout |
|---|---|
| 04/20/2012 | using local/testing server with cs5 inserting images look fine in the split screen but do not show |
| 04/18/2012 | Ap Div help |
| 04/23/2012 | Updating |
