Publishers of technology books, eBooks, and videos for creative people

Home > Articles > Web Design & Development > Adobe ColdFusion

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>:

  1. Create a new ColdFusion file, title it, and save it.

  2. 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>
  3. 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.21Figure 13.21 Output the contents of a variable with <cfdump>.

  4. 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.22Figure 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.

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:

  1. 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>
  2. 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.23Figure 13.23 The changed code outputs the results of a grouped query.

  3. 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.24Figure 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>.

To create a chart using <cfquery>:

  1. 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.

  2. 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.

  3. 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.25Figure 13.25 Your page's query block should look like this.

  4. 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.26Figure 13.26 Your page's body should look like this.


  5. 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.27Figure 13.27 These two tables show the number of parks inside and outside Alaska.

  6. 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.

  7. 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.

  8. Preview your page in a browser (Figure 13.28).

    Figure 13.28Figure 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


  9. 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.29Figure 13.29 This code is a <cfchart> tag with dynamic chart series and chart data.

  10. Save your page and preview it in a browser (Figure 13.30).

    Figure 13.30Figure 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!

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.

Peachpit Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from Peachpit and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about Peachpit products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites; develop new products and services; conduct educational research; and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email ask@peachpit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by Adobe Press. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.peachpit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020