Google Chart using dynamic data

[Arron Dobbins]

Hi all, im using Google Charts to process graphs based on data in a database which is working fine.

However now i need to get data from a database (Users from a specific area) and then get data from a different table (How many submissions one user from that area has made) and then process that into a graph.

Currently this posts the data for the first user in the graph with no issue, however it doesn't repeat the line i have highlighted in red, which i believe is the issue.

Is this possible? 

[code]

      <?  $rs1_settings = mysql_query("select * from users WHERE `area`='$area' ORDER BY  `full_name` ASC"); ?>

        <?php while ($row1_settings = mysql_fetch_array($rs1_settings)) {

$user_id = $row1_settings['id'];

$user_name = $row1_settings['full_name']; 

$id = mysql_query("select count(*) as total_all from issues WHERE `created_by_id` = '$user_id' AND `completed` = '0'");

list($uid) = mysql_fetch_row($id);

?>

          <!--Load the AJAX API-->

          <script type="text/javascript" src="https://www.google.com/jsapi"></script>

    <script type="text/javascript">

      // Load the Visualization API and the piechart package.

      google.load('visualization', '1.0', {'packages':['corechart']});

      // Set a callback to run when the Google Visualization API is loaded.

      google.setOnLoadCallback(drawChart);

      // Callback that creates and populates a data table,

      // instantiates the pie chart, passes in the data and

      // draws it.

      function drawChart() {

        // Create the data table.

        var data = new google.visualization.DataTable();

        data.addColumn('string', 'Channel');

        data.addColumn('number', 'Issues');

        data.addRows([

          ['<? echo $user_name; ?>', <?php echo $uid; ?>],

 

        ]);

        // Set chart options

        var options = {'title':'Compliance issues by Channel:',  'width':500,

                       'height':400};

        // Instantiate and draw our chart, passing in some options.

        var chart = new google.visualization.ColumnChart(document.getElementById('chart_channel'));

        chart.draw(data, options);

      }

    </script>

 <? } ?>

    <!--Div that will hold the pie chart-->

    <div id="chart_channel"></div>

[/code]

[asgallant]

You can simplify this quite a bit by using a table join in SQL.  Try this instead:

<?php

// query for full name and count

$rs1_settings = mysql_query("

SELECT

u.id,

u.full_name,

count(t.*) AS count

FROM users AS u

LEFT JOIN total_all AS t

ON (u.id = t.created_by_id)

WHERE

u.area = '$area'

AND t.completed = 0

ORDER BY full_name ASC

");

$rows = array();

// loop over the results of the query and input data into data structure

while ($row1_settings = mysql_fetch_array($rs1_settings)) {

$user_id = $row1_settings['id'];

$user_name = $row1_settings['full_name'];

$count = $row1_settings['count'];

// input data into data structure

// typecast count as integer so it doesn't get interpreted as a string

$rows[] = array($user_name, (int) $count);

}

// format data structure for output to javascript

$data = json_encode($rows);

?>

<!--Load the AJAX API-->

<script type="text/javascript" src="https://www.google.com/jsapi"></script>

<script type="text/javascript">

// Callback that creates and populates a data table,

// instantiates the pie chart, passes in the data and

// draws it.

function drawChart() {

// Create the data table.

var data = new google.visualization.DataTable();

data.addColumn('string', 'Channel');

data.addColumn('number', 'Issues');

data.addRows(<?php echo $data; ?>);

// Set chart options

var options = {

title: 'Compliance issues by Channel:',

width: 500,

height: 400

};

// Instantiate and draw our chart, passing in some options.

var chart = new google.visualization.ColumnChart(document.getElementById('chart_channel'));

chart.draw(data, options);

}

// Load the Visualization API and the piechart package.

google.load('visualization', '1.0', {'packages':['corechart']});

// Set a callback to run when the Google Visualization API is loaded.

google.setOnLoadCallback(drawChart);

</script>

<!--Div that will hold the pie chart-->

<div id="chart_channel"></div>

[Arron Dobbins]

Hi,
Thanks for your input, this works and displays all the users in that area who match the criteria, however all the data for the count is contained in a separate table. I have slightly modified the code, however this returns a value of 3 for every user. and doesn't repeat like the full name does.

Again many thanks for your help. Code is attached below. 

<?php

// query for full name and count

$rs1_settings = mysql_query("select * from users WHERE `user_hub`='$user_hub' AND user_level < '5' ORDER BY  `full_name` ASC"); ?>

        <?php while ($row1_settings = mysql_fetch_array($rs1_settings)) {

$user_id = $row1_settings['id'];

$user_name = $row1_settings['full_name']; 

$user_count = mysql_query("select count(*) as total_all from users WHERE `user_hub` = '$user_hub' AND user_level < '5' ORDER BY `created_by` ASC");

{

$id = mysql_query("select count(*) as total_all from issues WHERE `created_by_id` = '$user_id' AND `completed` = '0'");

list($count) = mysql_fetch_row($id);

}

//$result = str_repeat($row, $count); 

 

$rows = array();

// loop over the results of the query and input data into data structure

while ($row1_settings = mysql_fetch_array($rs1_settings)) {

$user_id = $row1_settings['id'];

$user_name = $row1_settings['full_name'];

// input data into data structure

// typecast count as integer so it doesn't get interpreted as a string

$rows[] = array($user_name, (int) $count);

}

// format data structure for output to javascript

$data = json_encode($rows);

}

?>

<!--Load the AJAX API-->

<script type="text/javascript" src="https://www.google.com/jsapi"></script>

<script type="text/javascript">

// Callback that creates and populates a data table,

// instantiates the pie chart, passes in the data and

// draws it.

function drawChart() {

// Create the data table.

var data = new google.visualization.DataTable();

data.addColumn('string', 'Channel');

data.addColumn('number', 'Issues');

data.addRows(<?php echo $data; ?>);

// Set chart options

var options = {

title: 'Compliance issues by User:',

width: 500,

height: 400

};

// Instantiate and draw our chart, passing in some options.

var chart = new google.visualization.ColumnChart(document.getElementById('chart_channel'));

chart.draw(data, options);

}

// Load the Visualization API and the piechart package.

google.load('visualization', '1.0', {'packages':['corechart']});

// Set a callback to run when the Google Visualization API is loaded.

google.setOnLoadCallback(drawChart);

</script>

    <!--Div that will hold the pie chart-->

    <div id="chart_channel"></div>    

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/wAVzMDXKeqY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Arron Dobbins]

I have now fixed this.

Thank you. For reference i have attached my code. 

      <?php

// query for full name and count

$rs1_settings = mysql_query("select * from users WHERE `user_hub`='$user_hub' AND user_level < '5' ORDER BY  `full_name` ASC"); ?>

        <?php while ($row1_settings = mysql_fetch_array($rs1_settings)) {

$user_id = $row1_settings['id'];

$user_name = $row1_settings['full_name']; 

//$result = str_repeat($row, $count); 

 

$rows = array();

// loop over the results of the query and input data into data structure

while ($row1_settings = mysql_fetch_array($rs1_settings)) {

$user_id = $row1_settings['id'];

$user_name = $row1_settings['full_name'];

$id = mysql_query("select count(*) as total_all from compliance_issues WHERE `created_by_id` = '$user_id' AND `completed` = '0' ORDER BY `created_by` ASC");

list($count) = mysql_fetch_row($id);

// input data into data structure

// typecast count as integer so it doesn't get interpreted as a string

$rows[] = array($user_name, (int) $count);

// format data structure for output to javascript

$data = json_encode($rows);

}

}

?>

On Mon, Jun 17, 2013 at 5:23 PM, asgallant <drew_g...@abtassoc.com> wrote:

--