[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

Cfgraph, Coldfusion and MySQL getting started

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
brihol44
Asked:
brihol44
  • 2
1 Solution
 
bayesianmindCommented:
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
 
brihol44Author Commented:
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
 
bayesianmindCommented:
Isn't that what the first code you posted does? Or are you looking to have multiple months selectable and displayed?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now