Skip to main content
M
Mi-ul-vmijiI
Known Participant
June 17, 2008
Question

MySQL and CFchart

  • June 17, 2008
  • 5 replies
  • 863 views
This is mainly a mysql problem but I may have more questions about cfchart shortly so this way I can keep things in one place. I've simplified some of what is below for ease of reading and clarity.

I have a web form for customer feedback where customers leave a rating (N/A, Poor, Good, Excellent) and I'm trying to create pie charts to summarise the comments.

I have a database with 5 fields - commentID, Service, Value, Information, Overall
I can produce a pie chart for each field using the query

SELECT service, count(service) as countService FROM feedback GROUP BY service
and repeat of this query for value, information and overall

I've then got 4 pie charts, one for each query. So far so good.

What I would like to do now is to create a "total" graph (total is the working name so it won't be confused with the earlier overall). Is there any way to query the database to gather all of the fields into one field which can then be grouped and counted.

E.g. (this doesn't work but probably (hopefully) explains it better than the waffle above)

SELECT service as total, value as total, overall as total, information as total, count(total) FROM feedback GROUP BY total


Thanks
Michael
    This topic has been closed for replies.

    5 replies

    M
    Mi-ul-vmijiIAuthor
    Known Participant
    June 18, 2008
    Unfortunately the usually helpful error template is only showing "error on line"

    Your code worked locally and gave teh same error on the uni server
    -
    -__cfSearching__-
    Inspiring
    June 18, 2008
    Well, the code does run on MX7 windows. It sounds like it is a problem with cfchart on that server. Without the detailed error message, there is not much else I can suggest other than to take it up with your helpdesk.
    M
    Mi-ul-vmijiIAuthor
    Known Participant
    June 18, 2008
    Thanks for your help. If I can get a better error message or the help desk are any use then I may be back

    Cheers
    Michael
    M
    Mi-ul-vmijiIAuthor
    Known Participant
    June 18, 2008
    Running the page without any graphs works, but as soon as I add any of them it brings up the useless error.

    How do I get the stack trace - I suspect it won't be available to me
    -
    -__cfSearching__-
    Inspiring
    June 18, 2008
    If it is available, it is usually at the bottom of the error message. Example

    coldfusion.compiler.ParseException: Invalid CFML construct found on line 2 at column 66.
    at coldfusion.compiler.cfml40.generateParseException(cfml40.java:6691)
    at coldfusion.compiler.cfml40.jj_consume_token(cfml40.java:6569)
    at coldfusion.compiler.cfml40.FunctionParameters(cfml40.java:3381)
    ....

    Can you generate any charts at all? For example, does this code work?

    <cfset q = queryNew("")>
    <cfset queryAddColumn(q, "rating", listToArray("5,3,2"))>
    <cfset queryAddColumn(q, "category", listToArray("NA,Good,Poor"))>

    <cfchart format="flash" show3d="true" chartwidth="500" chartheight="500" pieslicestyle="sliced" title="Service">
    <cfchartseries type="pie" query="q" valueColumn="rating" itemColumn="category" />
    </cfchart>
    -
    -__cfSearching__-
    Inspiring
    June 18, 2008
    So is the error caused by cfquery or the cfchart code?

    What about the stack trace?
    M
    Mi-ul-vmijiIAuthor
    Known Participant
    June 17, 2008
    Hi

    Thanks thats just what I was after.
    Michael
    M
    Mi-ul-vmijiIAuthor
    Known Participant
    June 18, 2008
    Ok, I've got this all working now on my local machine (running WAMP and CF developer) however when I upload to the university server I get an error:

    "The error occurred on line " and no further information

    query code:
    SELECT service, COUNT(service) as cService FROM feedback GROUP BY service

    display code (page works without this section)
    <cfchart format="flash" show3d="true" chartwidth="500" chartheight="500" pieslicestyle="sliced" title="Service">
    <cfchartseries type="pie" query="feedbackService" valueColumn="cService" itemColumn="service" />
    </cfchart>



    I've emailed the helpdesk to see if it's a restriced command (the list says it isn't but...) but I was hoping someone here may have a suggestion

    Thanks
    Michael
    -
    -__cfSearching__-
    Inspiring
    June 17, 2008
    > I have a web form for customer feedback where customers
    > leave a rating (N/A, Poor, Good, Excellent)

    So the columns Service, Value, Information, Overall all have the same values: ie N/A, Poor, Good, Excellent? If that is the case, some sort of UNION might do the trick.

    SELECT grp.Total, SUM(grp.CountTotal) AS CountTotal
    FROM (
    SELECT Service AS Total, COUNT(Service) AS CountTotal
    FROM Feedback
    GROUP BY Service
    UNION ALL
    SELECT Value AS Total, COUNT(Value) AS CountTotal
    FROM Feedback
    GROUP BY Value
    UNION ALL
    SELECT Information AS Total, COUNT(Value) AS CountTotal
    FROM Feedback
    GROUP BY Information
    UNION ALL
    SELECT Overall AS Total, COUNT(Value) AS CountTotal
    FROM Feedback
    GROUP BY Overall
    ) AS grp
    GROUP BY grp.Total
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices