MACROMEDIA COLDFUSION 5-DEVELOPING Manuale Utente Scaricare il pdf (Pagina 28)

ColdFusion MX 7 Getting Started Experience Tutorial Page 28 of 47

Learning Point: More about SELECT and WHERE clauses

The ARTISTID is not necessary in the SELECT statement just because you use it in the WHERE clause. You only need to

SELECT fields that you want to display. Generally, the ARTISTID is only used by the database to locate information, while

humans use the artists’ names to identify them. Since the tutorial does not display the ARTISTID on the page, you can

leave it out of the SELECT statement.

It is often necessary to join more than two tables in a query. In this case, you not only need to assign the correct artist to

the artwork, but you also need to assign the correct media type to the artwork.

Figure 45. Art and Media

table data

In this figure, look at the row in the ART table with an ARTID value of 8. The MEDIAID associated with that row is number

2. To find the human-readable version of that MEDIAID, look at the MEDIA table. You can see that the MEDIAID primary

key that has a value of 2 is associated with the MEDIATYPE Sculpture.

8. Add another join statement to the WHERE clause to ensure that the correct MEDIATTYPE is always associated with the

correct art piece. Also add the MEDIATYPE to the SELECT statement and the MEDIA table to the FROM statement.

Your code now looks like this:

SELECT FIRSTNAME, LASTNAME, ARTNAME, DESCRIPTION, PRICE, LARGEIMAGE, ISSOLD, MEDIATYPE

FROM ARTISTS, ART, MEDIA

WHERE ARTISTS.ARTISTID = ART.ARTISTID

AND ART.MEDIAID = MEDIA.MEDIAID

Learning Point: Extending the SELECT and WHERE clauses

As with the ARTISTID join, you do not need to add the MEDIAID to the SELECT statement, because you will not be

printing those numbers. However, you do want to print the MEDIATYPE, and therefore you need to add that field to the

SELECT statement and the MEDIA table to the FROM statement.

When you have more than one join condition in the WHERE clause, connect them with AND statements.

9. Browse the page index.cfm page in a browser window by either clicking the Preview/Debug in browser icon or

pressing the F12 key. If you still have the browser window open, click the refresh button in the browser or use the

Windows keyboard shortcut Ctrl+R to refresh the display.

1 2 ... 23 24 25 26 27 28 29 30 31 32 33 ... 46 47

Nessun commento

MACROMEDIA COLDFUSION 5-DEVELOPING Manuale Utente Pagina 28