I'm trying to build a line chart with mulitple lines using a very neat product. The concept is simple; partners (Dispatched_TPM), months (Calendar_Month) and values (Completed). The number of partners could vary month to month and the dates simple grow as more data is added so this cannot be hard-coded. Here is what I have so far....
Code: Select all
$query = "SELECT Dispatched_TPM, Calendar_Month, " . // row 0
"COUNT(Dispatched_TPM) AS NumDispatches, " . //total number of dispatches row 1
"SUM(IF(STATUSTEXT = 'Completed',1,0)) AS Completed, " . //completed status row 2
"SUM(IF(STATUSTEXT = 'Cancel',1,0)) AS Cancel, " . //Cancel status row 3
"partners.shortname " . //row 4
"FROM dispatches INNER JOIN partners ON (partner_name = Dispatched_TPM AND GEO = '$CUST_ADX_GEO')" .
"WHERE CUST_ADX_GEO = '$CUST_ADX_GEO' " .
"GROUP BY Dispatched_TPM, STR_TO_DATE(Calendar_Month, '%d-%M-%y') ";
"ORDER BY STR_TO_DATE(Calendar_Month, '%d-%M-%y') ";
$results = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
$row = mysql_fetch_assoc($results);
if ($results) {
$labels = array();
$data = array();
while ($row = mysql_fetch_assoc($results)) {
$labels[] = $row["Dispatched_TPM"];
$data[] = $row["Completed"];
}
// Now you can aggregate all the data into one string
$data_string = "[" . join(", ", $data) . "]";
$labels_string = "['" . join("', '", $labels) . "']";
} else {
print('MySQL query failed with error: ' . mysql_error());
}
echo $data_string;
?>
[text][2, 6, 2, 3, 4, 8, 5, 4, 8, 123, 112, 86, 67, 60, 477, 507, 487, 448, 520, 60, 54, 42, 56, 52, 11, 8, 14, 12, 12, 151, 148, 134, 141, 172, 10, 5, 6, 5, 7, 393, 421, 390, 411, 504][/text]
Now this works fine but I need to break the array into smaller arrays, 1 for each partner each with 4 values (1 for each month) like this...
[text][2, 2, 6, 2]
[4, 8, 5, 4]
[123, 112, 86, 67]
[477, 507, 487, 448]
[60, 54, 42, 56]
[11, 8, 14, 12]
[151, 148, 134, 141]
[10, 5, 6, 5]
[393,421,390,411]
[/text]
I can then concatanate the arrays to create my final string variable to look like this -
[text]
[[2, 2, 6, 2], [4, 8, 5, 4], [123, 112, 86, 67], [477, 507, 487, 448], [60, 54, 42, 56], [11, 8, 14, 12], [151, 148, 134, 141], [10, 5, 6, 5], [393,421,390,411]]
[/text]
I also need to create 2 seperate arrays, 1 for the date (Calendar_Month) and 1 for the Partner (Dispatched_Partner)
Any help here would be most welcome... many thanks in advance...