EDIT: I tried to simplify it before but I think I'll just lay it all out there as it may clear up some confusion as to what I am trying to achieve.
I am using two mySQL tables to create a javascript chart showing a bar graph and a line graph on the same chart. The line graph showing attendance and the bar graph showing what brewing processes was done for that date. Table A:
id | date | attended
1 |2015-01-14| 3
2 |2015-01-20| 4
3 |2015-01-31| 2
4 |2015-02-21| 3
5 |2015-02-25| 4
6 |2015-03-16| 4
Table B:
id | name | brewdate | bottledate
1 | Honey Ale | 2015-01-14 | 2015-02-21
2 | IPA | 2015-01-14 | 2015-01-31
3 | Porter | 2015-01-20 | 2015-02-25
4 | Brown Ale | 2015-02-21 | 2015-03-16
Using the code below I am able to get arrays to use with zingchart:
<?php
$sql = "SELECT * DATE_FORMAT(DATE(date), '%m/%d') AS bpdate
FROM TableA ORDER BY date";
$data = $conn->query($sql);
?>
<script
//GET array of attendance for line chart
var present=[<?php
mysqli_data_seek($data, 0);
while($info=mysqli_fetch_array($data)) {
echo $info['present'].",";
}
?>];
//Get dates for x-axis of chart
var dates=[<?php
mysqli_data_seek($data, 0);
while($info=mysqli_fetch_array($data))
echo '"'.$info['bpdate'].'",';
?>];
<?php
$sql = "SELECT COUNT(TableB.brewdate) AS brewed
FROM TableB
RIGHT JOIN TableA
ON TableB.brewdate=TableA.date
GROUP BY TableA.date ORDER BY TableA.date";
$events = $conn->query($sql);
?>
//Get number of beers brewed per date
var brewed=[<?php
while($info=mysqli_fetch_array($events)) {
echo $info['brewed'];
}
?>];
<?php
$sql = "SELECT TableB.*
FROM TableB
RIGHT JOIN TableA
ON TableB.date=TableA.date
ORDER BY TableA.date";
$names = $conn->query($sql);
?>
//Get the name of each beer that was brewed.
var brewednames=[<?php
while($info=mysqli_fetch_array($names)) {
echo '"' . $info['name'] . ',"';
}
?>];
</script>
Which gives me this:
var present=[3,4,2,3,4,4];
var dates=["01/14","01/20","01/31","2/21","02/25","03/16"];
var brewednames=["Honey Ale","IPA","Porter","","Brown Ale","",""];
The problem I am having is with variable brewednames. It is correctly giving me the list of the beers with blanks filling in the dates where no beers were brewed, but it lists beers that have the same date in different array elements and I want them to show in the same one. Like so:
var brewednames=["Honey Ale IPA","Porter","","Brown Ale","",""];
The reasoning behind this is that this array is shown when a user hovers over the corresponding bar on chart. So rather than showing the number of beers brewed for that date I want it to give the names of all the beers brewed on that date.
I found this article which was able to help me group each beer that was brewed on the same date in the same array element but now they are not ordered according to date as all the blanks have been moved to the beginning of the array:
var brewednames=[<?php
$info = array();
while($row=$names->fetch_assoc()) {
$date = $row['date'];
$name = $row['name'];
$info[$date][] = $name;
}
foreach ($info as $date => $values) {
echo '"';
foreach($values as $value) {
echo $value . ' ';
}
echo '",';
}
?>];
Which gives me this:
var namegroup=[" ","Honey Ale IPA","Porter","Brown Ale"]
Any help would be much appreciated. Also I am sure that my code is not perfect as I am somewhat teaching myself mySQL and PHP so if there are any suggestions for cleaner/better code feel free to weigh in. Thanks.
Aucun commentaire:
Enregistrer un commentaire