Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cfgraph, Coldfusion and MySQL getting started

Posted on 2007-11-14
3
Medium Priority
?
591 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 2000 total points
ID: 20284750
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
ID: 20291270
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
ID: 20292005
Isn't that what the first code you posted does? Or are you looking to have multiple months selectable and displayed?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

972 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