- Building Basic Charts
- Dressing Up Your Charts
- Building Dynamic Charts with CFQUERY
- Building Dynamic Applications with Clickable Charts
This chapter is from the book
This chapter is from the book
This chapter is from the book
Building Dynamic Charts with CFQUERY
Coders, by and large, are lazy people. (Not you, just those other coders). In the previous section, you learned how to build informative graphs by manually entering plot points with the <cfchartdata> tag. While this method gets the job done, it's problematic for a few reasons.
First, building graphs manually probably is not your job. If you work for a large company, someone else may have the responsibility for providing Web content. Even if you're an independent Web developer, your clients probably provide much of their own content. Why spend time entering data when you could be coding new features?
Second, building graphs manually is tedious. You typed only 20 values in the Census example, but what if you wanted to graph 100—or 1,000—points of data?
Third, graphs built manually are not dynamic. Census numbers are relatively static: They change once every ten years. Assume that your company sells widgets, thousands of widgets in different colors, shapes, and sizes. What if your boss tells you to track widget sales daily—by color? You'll quickly find you need to automate the graphing process.
Finally, a static graph provides no opportunity for the user to interact with the graph. Remember, useful interaction is one of the best ways to attract visitors to your Web site and keep them coming back. If you track many pieces of data, you can't assume that your visitors are equally interested in each piece.
Fortunately, you can easily address all these issues by using <cfquery> to provide data to your chart.
Know Your SQL
To become a proficient ColdFusion developer, you need to understand many different technologies: interfaces, application servers, databases, and more. But if you were to pick only one skill to set yourself apart from other developers, choose SQL.
Knowing how to write efficient queries will speed up your application significantly. It also will help you provide better data to your end users. If this book whets your appetite for learning more SQL, check out Ben Forta's Teach Yourself SQL in 10 Minutes, Second Edition, Indianapolis: Sams, 2001. It's an easy and affordable introduction to many helpful SQL techniques.
In this section, you learn how to build charts on the fly using data from your database. You also learn how to let users click on your charts to get more information. But first, you briefly go back to SQL school to learn about the GROUP BY clause. Along the way, you're going to learn about a new Cold-Fusion tag charmingly named <cfdump>.
Assume that you have a list of all the National Parks in the United States. Assume also that each park is listed both by region and by state. In other words, Arches National Park is in both Utah state and the Rocky Mountain Region. (Actually, you don't have to make these assumptions. The table tblParks in the exampleapps data source is set up this way). As you look through your list, you may decide you'd like to group Arches National Park with other parks in Utah. You could ask, "How many parks are in Utah versus Alaska?"
Enter the GROUP BY clause. When you group a piece of data with similar pieces of data, you can count those pieces as one unit and compare that unit to other groups. This concept is probably easiest to understand by working through an example. Once you understand how powerful the GROUP BY clause can be, you'll wonder how you ever lived without it.
Queries by Hand
In this chapter, you can decide whether to type your queries by hand or to use the help of the query builder. It's a good idea to become proficient at both methods. You might find that you like creating queries by hand so much that you do so for most of your recordsets.
Either way, Dreamweaver is so powerful that it will recognize the queries you write by hand and place them in the list of server behaviors.
To display your data using <cfdump>:
Create a new ColdFusion file, title it, and save it.
On the first line of your new page, type this code:
<cfquery name="q_ParkList" data source="exampleapps"> SELECT PARKNAME, REGION, STATE FROM tblParks ORDER BY PARKNAME </cfquery>
Inside the document's <body> tags, insert this code:
<cfdump var="#q_ParkList#"
label="Ungrouped List of US National Parks">Figure 13.21 shows the complete code listing for the page.
Figure
13.21 Output the contents of a variable with <cfdump>.Save the page and preview it in your browser.
You will see a list of over three hundred parks presented in a table (Figure 13.22). How did that happen?
Figure
13.22 The contents of q_ParkList is displayed via <cfdump>.
<cfdump> outputs the content of any variable in a neatly formatted table. It's very useful for debugging your application. After you've looked at the data in the list of parks, you can delete the recordset q_ParkList if you like; you won't use it again on this page.
)
Figure
13.21 )
Figure
13.22 Tips
Don't <cfdump> on your users. You should use <cfdump> only during application development and debugging.
<cfdump> does not require a closing tag.
To use the GROUP BY clause to group data:
To create a new recordset named q_ParksByState with the data source exampleapps, type this code at the top of your document:
<cfquery name="q_ParksByState" datasource="exampleapps"> SELECT STATE, COUNT(*) AS state_count FROM tblParks WHERE STATE IS NOT NULL GROUP BY STATE ORDER BY STATE; </cfquery>
Replace your old <cfdump> tag with this code:
<cfdump var="#q_ParksByState#" label="US Parks Per State">
Your code should look like the code in Figure 13.23.
Figure
13.23 The changed code outputs the results of a grouped query.Save your page and view it in a browser. You will see a list of States, accompanied by the number of parks in each state (Figure 13.24).
Figure
13.24 This chart shows the dumped results for q_ParksByState.
Let's examine the SQL line by line to see how you got this result.
SELECT STATE, COUNT(*) AS state_count
The SQL function count() returns the number of rows in a column. In the line above, you have told the database to give a list of states and a second column with a count called state_count.
FROM tblParks WHERE STATE IS NOT NULL
You already know about the FROM and WHERE clauses. You can filter out records that don't have any data using NOT NULL. This bit of SQL, in English, means "Don't retrieve records unless they have a value in the state column."
GROUP BY STATE ORDER BY STATE;
This phrase tells the query to organize the data by the STATE field and then to place the data in alphabetical order based on the state.
While the table produced by <cfdump> is informative, it's hard to get a sense of how each region relates to the others. In other words, this table is a good candidate for <cfchart>.
)
Figure
13.23 )
Figure
13.24 To create a chart using <cfquery>:
Open the page you created in the last section if it isn't already open. After the query q_ParksByState, type a new query as follows:
<cfquery name="q_MyState" datasource="exampleapps"> SELECT STATE, COUNT(*) AS my_count FROM tblParks WHERE STATE = 'AK' GROUP BY STATE; </cfquery>
This query is almost identical to q_ParksByState. In this case, however, you've limited your results to the state of Alaska.
Replace your existing <cfdump> tag with the following code and preview the page in your browser.
<hr size="1" noshade> <cfdump var="#q_MyState#" label="Number of US Parks in Alaska"><br />
This code simply dumps the contents of your new query.
Just below the q_MyState query, type this code:
<cfquery name="q_OtherStates" data- source="exampleapps"> SELECT COUNT(*) AS other_count FROM tblParks WHERE STATE <> 'AK' </cfquery>
This query simply counts the number of parks that are in all states other than Alaska and returns the resulting number as the variable other_count. Your block of queries should look like Figure 13.25.
Figure
13.25 Your page's query block should look like this.Underneath your <cfdump> tag, add another one:
<cfdump var="#q_OtherStates#"
label="Number of US Parks outside Alaska">The body of your page should look like Figure 13.26.
Figure
13.26 Your page's body should look like this. Save the page and preview it in a browser. You will see two tables, one showing the number of parks in Alaska, the other showing the number of parks in all states other than Alaska (Figure 13.27). You will use this data to build your dynamic chart.
Figure
13.27 These two tables show the number of parks inside and outside
Alaska.Above the <hr> tag, type this code:
<cfchart showborder="yes" show3d="yes"
chartwidth="600" chartheight="400"
pieslicestyle="sliced">
</cfchart>You've already seen most of this code in action with static charts. The pieslicestyle attribute sets the appearance of the pie chart you are going to create next.
Within the <cfchart> container tags, create a new chart series:
<cfchartseries type="pie" query="q_MyState"
itemcolumn="STATE" valuecolumn="my_count">
</cfchartseries>You've already seen the type attribute; this time, you've specified that you want to create a pie chart.
Table 13.1 identifies the three new attributes you've used in this tag in order to create a dynamic chart: That's a lot of information to take in all at once.
Preview your page in a browser (Figure 13.28).
Figure
13.28 This graph shows that the parks in Alaska make up 100 percent of the
parks in Alaska. Hmm...You'll see a pie chart showing the number of parks in Alaska.
But what's wrong with that chart? You already know the number of parks in Alaska by looking at the <cfdump> table. Your chart simply repeats the information in that table. Let's add one more piece of data to it.
Table 13.1 <cfchartseries> Attributes Used to Create a Dynamic Chart
Attribute
Tells Chart
Example from this Chart
Query
Name of ColdFusion query from which to get data.
q_MyState
Itemcolumn
Name of a column in the query specified in the query attribute; contains the item label for a data point to graph.
STATE
Valuecolumn
Name of a column in the query specified in the query attribute; contains data values to graph.
query column my_count
In between the opening and closing <cfchartseries> tags, add this code:
<cfchartdata item="Other States"
value="#q_OtherStates.other_count#">Remember the recordset q_OtherStates that you created earlier? You've just told ColdFusion to add an item to your chart with the value of that query, and to label it Other States. Your <cfchart> tag, in its entirety, should look like the code in Figure 13.29.
Figure
13.29 This code is a <cfchart> tag with dynamic chart series and
chart data.Save your page and preview it in a browser (Figure 13.30).
Figure
13.30 This graph shows that the parks in Alaska make up 5 percent of all
the parks in the United States.Now that's a useful chart!
)
Figure
13.25 )
Figure
13.26 )
Figure
13.27)
Figure
13.28 )
Figure
13.29 )
Figure
13.30 Tips
You cannot combine a pie chart with any other type of chart.
As a rule of thumb, consider using a pie chart when you want to show the relative quantity of an item or group of items expressed as a number or percentage.
Don't use a pie chart when you need to show a quantity that changes over time.