Database Basics for ColdFusion Developers
This chapter is from the book
This chapter is from the book
This chapter is from the book
In this Chapter:
- A Look at Relational Databases
- Introduction to SQL
- To Quote or Not to Quote?
- Project I: Creating an Access Database File
- Setting Up an ODBC Connection
- Project II: Retrieving Data
- SQL Builder
- New Functions
- SELECT
- FROM
- INSERT
- ORDER BY
- WHERE
- DISTINCT
- INNER JOIN
- ASC/DESC
- <CFOUTPUT>
- <CFSET>
- <CFQUERY>
- Recap
Let's face it . . . nowadays, just about every major site is using some sort of database on the backend to power their site in one way or another. Even smaller sites have started incorporating databases into their websites due to the amazing flexibility it offers. Not to mention the somewhat short learning curve involved in understanding the basics of what is involved in actually using them. Today, with applications such as ColdFusion, ASP, PHP, and countless others, integrating database functionality into a website is becoming increasingly easier.
But in order to effectively work with databases you need to have some sort of idea of how they actually work. And with a chapter called "Database Basics" I'm sure you are counting on learning exactly that.
There are several types of database applications out on the market, ranging in price from under a hundred dollars to tens of thousands of dollars. The application that most people have available to them—or that doesn't cost an arm and a leg—is Microsoft's Access. Others that you can use if you are going to be doing some heavy-duty database work that needs to span multiple servers are Microsoft SQL server, Oracle, or Sybase. While these cost much more than Access, they offer a lot more in functionality, scalability, and security. They are also designed to handle a lot more simultaneous requests than Microsoft Access.
For the examples in this book we will be using Microsoft Access. You will find the data sources (also known as DSNs which we will be explaining in the next few pages) and all the examples from this book on the Essential ColdFusion 4.5 for Web Professionals accompanying website which is located at http://www.phptr.com/essential/coldfusion45/.
If you do not have a copy of Microsoft Access you can still use the data files to run with ColdFusion and do all the fun stuff like adding, deleting, and editing your data.
A Look at Relational Databases
What is a relational database? In simplest of terms it can be defined as a series of tables that have common fields linking related information. It's a way to relate different information that has a common bond. For instance, a simple online store application may have three tables:
- Customer table
Where all the personal information about the customers is stored. - Products table
Lists information about the products. - Category table
Contains the various categories the products will fall under.
The Customer table will be a stand-alone table meaning that it will not relate to the other two tables. Only the Products and Category tables will be related. The relationship between these two tables could look something like this:
FIGURE 1–1 Relationship of the category field to the Category table
){kind=link}
You'll notice the Products and Category tables have primary keys (shown in bold) associated with them. A primary key is used to uniquely identify a row in a table. For a relational database to function properly, primary keys cannot be duplicated. The relationship shown above demonstrated a "one to one" relationship. In the Products table the sku field is the primary key, which has the data type of AutoNumber. This means that every time you add a new product into the Products table the sku will AutoNumber itself in increments of one.
TABLE 1–1 Common Data Types Found in Access
|
Text |
Holds text up to 255 characters. |
|
Memo |
Holds text up to 65,535 characters. |
|
Number |
Holds numerical characters only. Good to use for mathematical calculations. |
|
Date/Time |
Holds date and time values. |
|
Currency |
Holds currency (numeric) values. |
|
Autonumber |
Incremented value by 1 when a new record is added. Can also be set to randomly autoincrement. |
|
Yes/No |
Contains a value of 0 or 1. Used for 'NO/YES', 'OFF/ON', 'TRUE/FALSE'. |
The categoryID field in the Category table is the primary key for that table. The category field in the Products table holds the value of the categoryID in the Category table and therefore uses the categoryID in the Category table. This might sound a little confusing at first, so take a look at Figure 1-2 and you'll see how this is working.
){kind=link}
FIGURE 1–2 The values of the category field of the Products table are related to the values of the categoryID field in the Category table.
Through the use of SQL you can now interact with the data in your tables.