<?php
$CountrySQL = "SELECT DISTINCT Country FROM report_adherence_daily";
$CountryResult = mysql_query($CountrySQL);
$Country_num_rows = mysql_num_rows($CountryResult);
if(mysql_num_rows($CountryResult) > 0) { ?>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.setOnLoadCallback(drawAnnotations);
function drawAnnotations() {
var data = new google.visualization.DataTable();
<?php
$htmlOutput = array();
$CountryResult = mysql_query($CountrySQL) or die (mysql_error());
$colsToDisplay = mysql_num_rows($CountryResult)+1;
echo "data.addColumn('string', '<nobr>Week Commencing:');";
while($row=mysql_fetch_assoc($CountryResult)) {
echo "data.addColumn('number', '{$row['Country']}');";
}
$colsDifference = count($htmlOutput) % $colsToDisplay;
if($colsDifference) {
while($colsDifference < $colsToDisplay) {
$htmlOutput[] = '';
$colsDifference++; } }
$htmlOutput = array_chunk($htmlOutput, $colsToDisplay);
foreach($htmlOutput as $currRow) {
print implode('', $currRow);
}
// Let's find out which Dates there are....
$DateSQL = "SELECT DISTINCT Date FROM report_adherence_daily WHERE Date >= '".$Date."' AND Date <= '".$DateTo."' ORDER BY Date";
$result = mysql_query($DateSQL) or die (mysql_error());
while($row=mysql_fetch_assoc($result)) {
$Datez[] = array("Date"=>$row["Date"]);
}
// Now sort out the unique Departments and make an array out of them
$array = array_unique($Datez, SORT_REGULAR);
// Loop through the above array so it makes one query for each separate Department.
foreach($array as $item) {
$AddAnother = "";
$DateSQL = "SELECT Country, Date, format(((sum(Duration)/sum(Scheduled))*100),2) AS Percentage FROM report_adherence_daily WHERE Country = '".$row['Country']."' Date '".$item['Date']."' GROUP BY Country, Date";
$DateResult = mysql_query($DateSQL);
$Date_num_rows = mysql_num_rows($DateResult);
if(mysql_num_rows($DateResult) > 0) {
if($Country_num_rows > $Date_num_rows) {
$ValuesToAdd = ($Country_num_rows-$Date_num_rows);
for ($i = 0; $i < $ValuesToAdd; $i++) {
$AddAnother = $AddAnother."0,";
}
} else { $AddAnother = ""; }
?>
data.addRows([
<?php
$htmlOutput = array();
$DateResult = mysql_query($DateSQL) or die (mysql_error());
$colsToDisplay = mysql_num_rows($DateResult)+1;
while($row=mysql_fetch_assoc($DateResult)) {
if(!empty($row["Percentage"])) {
$Percentage = $row["Percentage"];
} else {
$Percentage = "";
}
$htmlOutput[] = "{$Percentage},";
}
$colsDifference = count($htmlOutput) % $colsToDisplay;
if($colsDifference) {
while($colsDifference < $colsToDisplay) {
$htmlOutput[] = '';
$colsDifference++; } }
$htmlOutput = array_chunk($htmlOutput, $colsToDisplay);
foreach($htmlOutput as $currRow) {
print "['".$item['Date']."', " . implode(' ', $currRow) . " ".$AddAnother." ], ]);";
}
?>
<?php } } ?>
var options = {
curveType: 'function',
legend: { position: 'right' },
lineWidth: 3,
series: {9: {type: 'line', color: 'grey',lineWidth: 5}},
chartArea : { left: 60, top:30, right:200, height:300,width: 1300, backgroundColor: { stroke: '#000', strokeWidth: 2, fill:'#fff' } },
vAxis: { viewWindowMode:'explicit',format:'#,####', viewWindow: { min:0,max:100 }, textStyle:{color: '#000',fontSize: '12'} },
backgroundColor: { fill:'#ebf1de', stroke: '#000', strokeWidth: 2, },
};
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);
}
</script>
<div id="curve_chart" style="width: 800px; height: 400px"></div>
</center>
<?php } ?>
I have just been introduce to HTML/PHP do to my knowledge of VBA. I have inherited some code and have made some amendments which is at the start of this post. The data produced originally looked like `
google.setOnLoadCallback(drawAnnotations);
function drawAnnotations() {
var data = new google.visualization.DataTable();
data.addColumn('string', '<nobr>Week Commencing:');data.addColumn('number', '2016-07-04');data.addColumn('number', '2016-07-05');data.addColumn('number', '2016-07-06');data.addColumn('number', '2016-07-07');data.addColumn('number', '2016-07-08');data.addColumn('number', '2016-07-09');
data.addRows([['France', 55.93, 68.16, 64.77, 67.71, 54.83, 97.59, ], ]);data.addRows([['Germany', 66.61, 64.25, 71.90, 74.68, 78.02, 87.33, ], ]);data.addRows([['Italy', 62.81, 61.68, 62.01, 62.22, 58.69, 60.23, ], ]);data.addRows([['Netherlands', 71.61, 60.32, 62.00, 63.98, 57.41, 0, ], ]);data.addRows([['Nordic', 59.46, 65.68, 64.60, 67.24, 63.88, 0, ], ]);data.addRows([['Spain', 63.27, 68.87, 67.67, 69.12, 70.31, 0, ], ]);data.addRows([['UK', 65.30, 68.44, 70.26, 76.11, 74.18, 93.18, ], ]);
var options = {
curveType: 'function',
legend: { position: 'right' },
lineWidth: 3,
series: {9: {type: 'line', color: 'grey',lineWidth: 5}},
chartArea : { left: 60, top:30, right:200, height:300,width: 1300, backgroundColor: { stroke: '#000', strokeWidth: 2, fill:'#fff' } },
vAxis: { viewWindowMode:'explicit',format:'#,####', viewWindow: { min:0,max:100 }, textStyle:{color: '#000',fontSize: '12'} },
backgroundColor: { fill:'#ebf1de', stroke: '#000', strokeWidth: 2, },
};
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);
}
I need the country and Date to swap places.
I have managed in the code posted above to move the country for the Addcolumn part.
data.addColumn('string', '<nobr>Week Commencing:');data.addColumn('number', 'Italy');data.addColumn('number', 'Spain');data.addColumn('number', 'Germany');data.addColumn('number', 'Nordic');data.addColumn('number', 'France');data.addColumn('number', 'Netherlands');data.addColumn('number', 'UK');
but when it comes to the date and percentage data, I cannot work out what is going on
<b>Warning</b>: mysql_num_rows() expects parameter 1 to be resource, boolean given in <b>/home/servicee/public_html/reporting/Paul/MySite/freddie2.php</b> on line <b>339</b><br />
Apologies if this in not in the correct format but as I advised, I am new at this
