Solved

Cfgraph, Coldfusion and MySQL getting started

Posted on 2007-11-14
3
535 Views
Last Modified: 2008-02-01
Hi,

I need to display the hits, and visits of our site in a bar chart using Coldfusion. An example of the SQL data is like so....

daily_id, date, hits, visits
1, 11/13/2007, 20000, 400000
2. 11/12/2007, 20000, 400000

but I would like to have a drop down menu for months and show a bar chart of the daily hits throughout the month. This is what I've got started...

<cfquery name="get_general_stats" datasource="#dsn#">
        SELECT *, COUNT(visits) as "visits"
        FROM stats_daily
        ????
         WHERE month of the date =  '#form.date#'
</cfquery>


<CFCHART
             backgroundcolor="F2ECEC"
               CHARTWIDTH="950"
               CHARTHEIGHT="180"
         ROTATED="no"
               showlegend="yes"
              showmarkers="yes"
             SHOW3D="yes"
         XOFFSET=".01"
         YOFFSET=".02"
               FONTSIZE="10"
         FONTBOLD="Yes"
               DATABACKGROUNDCOLOR="DDDDDD"
             SCALETO="100"
         GRIDLINES="5"
         SHOWXGRIDLINES="no"
         SHOWYGRIDLINES="yes">
            
            <CFCHARTSERIES
           TYPE="bar"
         QUERY="query_name"
           ITEMCOLUMN="date"
           VALUECOLUMN="visits"
           SERIESLABEL="Visits"
           SERIESCOLOR="F94A4A">
             
            </cfchart>

Thanks,

B
0
Comment
Question by:brihol44
  • 2
3 Comments
 
LVL 3

Accepted Solution

by:
bayesianmind earned 500 total points
Comment Utility
Have you tried this? Count(field) counts the number of records that match a query, which is probably not what you wanted.

<cfquery name="get_general_stats" datasource="#dsn#">
        SELECT *
        FROM stats_daily
        ????
         WHERE month of the date =  '#form.date#'
</cfquery>

That should populate the required fields and get you going.
0
 

Author Comment

by:brihol44
Comment Utility
Nice! Thank you, I got it! However how would I loop over all the daily values in bar chart? So if I selected the month of October 2007 I would get all 31 days for the month with a bar value representing each day.

I get the value and the bar chart with the correct value with adding the two variables below. I just need some hints to figure out out to display all the days for each month/year selected.



<cfset month = "11">

	<cfset day = "08">
 

	<cfquery name="get_visits" datasource="#dsn#">

	  SELECT *

	  FROM stats_daily

	  WHERE EXTRACT(MONTH FROM date) = '#month#' AND

	  EXTRACT(DAY FROM date) = '#day#' 

	</cfquery>
 

    <CFCHART

		 backgroundcolor="F2ECEC"

  		 CHARTWIDTH="950"

  		 CHARTHEIGHT="180"

         ROTATED="no"

  		 showlegend="yes"

 		 showmarkers="yes"

		 SHOW3D="yes"

         XOFFSET=".01"

         YOFFSET=".02"

  		 FONTSIZE="10"

         FONTBOLD="Yes"

  		 DATABACKGROUNDCOLOR="DDDDDD"

		 SCALETO="100"

         GRIDLINES="5"

         SHOWXGRIDLINES="no"

         SHOWYGRIDLINES="yes"> 

		

		<CFCHARTSERIES 

    	 TYPE="bar" 

         QUERY="get_visits"

    	 ITEMCOLUMN="Visits"

    	 VALUECOLUMN="visits"

    	 SERIESLABEL="Visits"

    	 SERIESCOLOR="F94A4A">

		 

		</cfchart>

Open in new window

0
 
LVL 3

Expert Comment

by:bayesianmind
Comment Utility
Isn't that what the first code you posted does? Or are you looking to have multiple months selectable and displayed?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now