Getting the Information You Need

Have you ever had anyone scream at you “I NEED THE SALES FIGURES FOR THE NORTHERN GROUP ON OUR WEB SITE! TODAY!!” Ever had those days? You can put these figures on a web site. But, why not have the web site do it for you with dynamically driven web pages. To be able to access this type of information you need a database. But, more than that, you need to be able to query the content in the database. To do this on a Web page you must be learn how to leverage the Structured Query Language (SQL). Sound hard? Fortunately, Macromedia has come bounding to our rescue with tool designed for these types of crisis: Dreamweaver UltraDev. In this article you will learn how to create SQL usingDreamweaver UltraDev’s easy to use interface.

Sorting With SQL
Go to the Data Binding Panel and double click on the rsCompass Recordset. This opens the Recordset window. Let’s begin by arranging all of the names in Alphabetical order by last name. Select the “Sort” drop down and choose “LASTNAME”. The default sort value is “Ascending.” Select the Test button to view the results. You see all of the information in the database is now sorted in Alphabetic order by Last name. Select OK. View your Web page from your web server. You will see all of the results displayed the same way as previewed through the Test SQL Statement window, as shown in Figure K.

Figure K: The sorted results from your SQL Query

Filtering With SQL
Now, what if you want only the results from a specific area? One of the fields in the Employee Table is “Department”. Using this field we can sort the number of people down to a specific department.

Open the rsCompass Recordset. Choose “Department” from the Filter drop down. You are now presented with several different ways to filter the content. Choose “Entered Value” and add “Operations” as the value, as shown in Figure L.

Figure L: The Filter Control modifies what content is retrieved from the database.

Select OK. On your page will display only the employees who work in the Operations Department.

At this point, all filtering is matched with the “=” symbol. In other words, an exact match is made from the value you enter. If you select the “=” symbol you see a whole selection of symbols you can use. These allow for you to have exact matches to values, to have results that are equal to and greater and other types of sort. Table A is a breakdown of the symbols and what they mean.

Symbol	Name	Definition
=	Equals	This allows you to make an exact match with a value. For instance, you may have a Sales spreadsheet for the last five years. Entering 2000 for the year column would extract the results only for the year 2000
>	Greater Than	This allows you to find the results greater than the one you enter. Take for instance, the Sales spreadsheet for the last five years. Entering 2000 for the year column would extract the results only for the year 2001 and up.
<	Less Than	This allows you to find the results less than the one you enter. Take for instance, the Sales spreadsheet for the last five years. Entering 2000 for the year column would extract the results only for the year 1999 and down.
>=	Equal to and Greater Than	This allows you to find the results equal to and greater than the one you enter. Take for instance, the Sales spreadsheet for the last five years. Entering 2000 for the year column would extract the results only for the year 2000 and up.
<=	Equal to and less than.	This allows you to find the results equal to and less than the one you enter. Take for instance, the Sales spreadsheet for the last five years. Entering 2000 for the year column would extract the results only for the year 2000 and down.
<>	Not Equal to	This allows you to find all of the results that do not include the entered value. Take for instance, the Sales spreadsheet for the last five years. Entering 2000 for the year column would extract all of the results that are not for the year 2000.
Begins with		“Begins with” is great for creating online phone books. This command will on return results that begin with an entered value. For instance, with a phone directory if you entered a value of “A” it will return all of the names that begin with “A”.
Ends with		“Ends with” is the opposite of “Beings with”. This command will return results that end with an entered value. For instance, with a phone directory if you entered a value of “A” it will return all of the names that end with “A”.
Contains		“Contains” will search a field to find a match for you value. This is good for creating search engines. You can use SQL to search through how many fields you like to find a contained value.

Table A: A breakdown of the symbols you can use for comparing values in SQL

Using any of these symbols will allow you to generate the type of custom report needed by your customers. Experiment with the Employee table from the Compass Travel database. See what types of results you can generate.

Next: Pulling Values from an Other Page >>

Matthew David

Matthew David has been developing Flash based applications for over 6 years (that makes him very old in this business!). Examples of his work can be found at his web site www.matthewdavid.ws, or you can email him directly at mdavid@email.com.

Matthew’s most recent publications include content for Flash 5 Magic, Inside Dreamweaver 4, Flash 5: Visual FX, Web Publishing Bible and The Dreamweaver Bible. You can also see him popping up in many online magazines, such as Sitepoint.com, Windowatch.com, UDzone.com and DevX.com.

Currently, Matthew is working on two books and writing articles for Element K Journal’s Macromedia Solutions magazine. He is a available as a freelance consultant to work on web based projects.

See All Postings From Matthew David >>

Comments

Be the first to write a comment

You must me logged in to write a comment.