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>
On Mon, Jun 17, 2013 at 5:23 PM, asgallant <[email protected]>wrote:
> 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>
>
> On Sunday, June 16, 2013 2:22:12 PM UTC-4, Arron Dobbins wrote:
>>
>> 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 <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]
>>
> --
> 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
> [email protected].
> To post to this group, send email to
> [email protected].
> Visit this group at
> http://groups.google.com/group/google-visualization-api.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.
