• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 4. Using SQL > Defining Your Records

Defining Your Records

Now that we’ve touched on the basics of the most common SQL commands, let’s look at how you can use SQL in Dreamweaver.

When you display information from a database, you might not want to show every field in a record. Your internal product catalog, for example, might contain your cost, in addition to the price you are charging consumers. You probably wouldn’t want to display that information to just anyone shopping online.

You can control the fields you display by generating a recordset containing only those fields you want to include (For more on recordsets, see The Data Bindings Panel section in Chapter 2). As you might have guessed, behind the scenes, Dreamweaver uses our old friend the SELECT statement to generate the recordset.

Creating a recordset

You were introduced to the recordset dialog box in Chapter 2. Here, we’ll be using the same dialog box, but we’ll be delving a little deeper into it. First, let’s create a recordset that contains an entire table.

To create a recordset that contains the entire table:
1.
Create a recordset by following the steps in To create a recordset, in the Data Bindings Panel section of Chapter 2.

2.
In the Recordset dialog box, select your database connection from the Connection drop-down menu.

3.
In the Table drop-down menu, a list of the tables in the active database will appear. By default, the All Columns radio button will be selected (Figure 4.8).

Figure 4.8. All the columns in your table are selected by default.


4.
Click the Test button to see a preview of your recordset (which in this case is equivalent to the whole table) (Figure 4.9).

Figure 4.9. After you click the Test button (Figure 4.7), a preview of your recordset will appear in this dialog box.


This may seem pretty straightforward, and only tangentially related to SQL, but we’re about to show you the man behind the curtain. What Dreamweaver is doing behind the scenes as a result of the information you plugged in is to create a SQL command. Fortunately, Dreamweaver lets you see the SQL it generates.

To see the SQL behind creating a recordset:
1.
Follow Steps 1-3 on the previous page to create a recordset, choose your database connection, and specify a table.

2.
In the Recordset dialog, box, click the Advanced button.

The dialog box will switch to advanced mode (Figure 4.10).

Figure 4.10. The Recordset dialog box has an Advanced mode setting...


In the SQL text box, you can see the SQL command that shows the whole database (Figure 4.11).

Figure 4.11. ...which shows the SQL statement behind your query.


Selecting the appropriate fields

You often want to display only some of the fields in a record, or in a group of records. You already know how to do that with SQL (at least if you read the beginning of this Chapter); now we’ll see how that fits in with Dreamweaver.

To create a recordset without all the fields:
1.
Follow Steps 1-3, on the previous page under To create a recordset that contains the entire table, to create a recordset, choose your database connection, and specify a table.

2.
In the Recordset dialog box, click the Selected Columns radio button.

The list of fields will not be grayed out any more (Figure 4.12).

Figure 4.12. Clicking the Selected Columns radio button activates the field list.


3.
Click on the fields in the list box to select the fields that you want the recordset to include.

You can Shift+click to select a range or Ctrl+click (Command+click on the Mac) to select multiple fields (Figure 4.13).

Figure 4.13. You can Shift+click (Command+click on the Mac) to select multiple fields.


4.
Click the Test button to show your recordset.

Again, all well and good, but where’s the SQL?

To see the SQL:
1.
Follow steps 1-3 in the previous exercise to select the fields you want to include.

2.
Click the Advanced button in the Recordset dialog box to switch to Advanced mode.

You’ll see the SELECT statement with the appropriate fields specified (Figure 4.14).

Figure 4.14. The Advanced mode shows the SQL for selecting only some fields.


Tip

  • You can type a SQL statement directly into the SQL box in Advanced mode. If you do, you don’t have to include the semicolon at the end of the statement.


  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint