Hello All,
I very new to Java, JSP, MySQL and Google Charts [about 45 days experience]. I've been using the Netbeans IDE to create JSP's for interaction with a MySQL data base as part of a Linux based LAMP server that I've constructed. I've got a database that records event attendance with four distinct groups: Adults, Children, Members and Researchers. The input JSP runs wonderfully and passes data back to the server with ease, but the problem that I've encountered is in adding a nice dynamic Google Pie Chart to the Reports pages. I've successfully retrieved the data and subtotaled the corresponding columns displaying results in a table, but I'm stuck at integrating the retrieved subtotals into the Google Pie Chart. The code for the weekly subtotal report JSP is as follows:

<%-- 
    Document   : EventViewTable
    Created on : Sep 24, 2015, 1:09:08 PM
    Author     : sysadmin
--%> <%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %> <%@ page import="java.awt.*"%> <%@ page import="java.io.*"%> <%@ page import="org.jfree.chart.*"%> <%@ page import="org.jfree.chart.entity.*"%> <%@ page import="org.jfree.data.general.*"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <link rel="stylesheet" href="css/TCHSreport.css"> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>JSP List Event Attend Records-WEEKLY</title> </head> <body> <sql:setDataSource
        var="EventsTable"
        driver="com.mysql.jdbc.Driver"
        url="jdbc:mysql://TCHSserver:3306/Events"
        user="batman" password="to the batmobile"
    /> <sql:query var="listEventAttend"   dataSource="${EventsTable}">
    SELECT 
    SUM(Adults) as AdultsTotal ,
    SUM(Children) as ChildrenTotal, 
    SUM(Members) as MembersTotal, 
    SUM(Researchers) as ResearchersTotal,
    (SUM(Adults)+SUM(Children)+SUM(Members)+SUM(Researchers)) as GrandTotal
    FROM EventAttend WHERE YEAR(DateTime) = YEAR(CURDATE()) AND WEEK(DateTime) = WEEK(CURDATE())
    </sql:query> <center><caption><h1>TCHS Event Attendance</h1></caption></center> <center><caption><h2>Calendar Week Subtotals  </h2></caption></center> <center><caption><h5>Report Print Date:  </h5></caption></center> <script language="javascript">
 var today = new Date();
 document.write(today);
 </script> <div align="center"> <div style="height:120px; overflow:auto"> <table border="1" cellpadding="5"> <tr> <th>TOTAL Adults</th> <th>TOTAL Children</th> <th>TOTAL Members</th> <th>TOTAL Researchers</th> <th>GRAND TOTAL</th> </tr> <c:forEach var="user" items="${listEventAttend.rows}"> <tr> <td><c:out value="${user.AdultsTotal}" /></td> <td><c:out value="${user.ChildrenTotal}" /></td> <td><c:out value="${user.MembersTotal}" /></td> <td><c:out value="${user.ResearchersTotal}" /></td> <td><c:out value="${user.GrandTotal}" /></td> </tr> </c:forEach> </table> </div> <div> <script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Group', 'Number Attending'],
          ['Adults',     110],
          ['Children',      110],
          ['Members', 110],
          ['Researchers', 110]

        ]);

        var options = {
          title: 'TCHS Museum Event Attendance',
          is3D: true,
        };

        var chart = new google.visualization.PieChart(document.getElementById('piechart_3d'));
        chart.draw(data, options);
      }
    </script> </head> <body> <div id="piechart_3d" style="width: 900px; height: 500px;"></div> </div> </body> </html>

Once again, any help is greatly appreciated. THANKYOU.

-ED

seperate concernc ..
java will be in servers side making all the requests (e.g. in the db) don't have such requests in JSP , JSP is meant to be what the title says "Java Server Pages" (it is translated to a provider back to the server)

seperate js
js can be an OOP language .. is there any reason to have all in one page ?

use JSP as a tempate and do the work in Java side

@jkon,
as I've stated, I'm very new to this and I do understand that using a different method would have been a better choice. Sorry about that.

I've implemented the coding all in one page to remain modular. There will be a daily-weekly-monthly-yearly report done with ongoing dynamic data, so I tried to just get a framework together that could be reused with little modification. The data retrieval for the table with subtotals and grandtotal works perfectly too.

For example, the MySQL query SELECT/FROM/WHERE so that I can change as little as possible to implement the other forms. My inexperience in this area is evident, but to refine my question.....

Is there any way to use the returned numerical MySQL query data [from lines 14-17] in the numerc input for the Google Pie Chart [lines 28-32]?

Am I just being hopeful in the assumptionthat the returned query data is already present and the syntax required by the Google Chart callout is what needs to be changed?

I've been through the Google Developer Forums and feel that I may have to actually reformat the database query syntax and restate it within the Chart callout.

Once again, thanks much in advance for any input on this....
-ED

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.