Google charts column graph from MySql database

Question

The real question is how do I populate a Google charts datatable form a MySql query when a date is used for the first column?

I am trying to create a column chart using the Google Charts API. The problem is that the data is time series data. There are many dates represented. The way that this is done to create the data table is to assign column 0 to the date and each subsequent column represents the data being measured. That information is derived from the documentation. I just can't figure out how to get the data to assign to the subsequent columns. I have researched this extensively now and there are some answers that are close, but I can't make them work for this. Here is the code that is currently generating my chart data table:

$invArr = array('cols' => array(

                          array('label' => 'date', 'type' => 'date'),
                          array('label' => 'SKU', 'type' => 'number')),
          'rows' => array());

while($row = mysql_fetch_row($query)) {
   $invArr['rows'][] = array('c' => array(array('v' => $row[0]), array('v' => $row[1])));
}

I understand from the Google charts documentation that column 0 is always the date for continuous data. Each subsequent column is the data that is being measured, in this case ths SKU numbers. I need to have the first array in each row represent the date and the subsequent arrays represent the qtyoh data. I don't know how to do that.

I am 100% unclear what you're asking here (and considering the lack of comments/answers, I'm guessing I'm not alone in that). Could you please try to explain what the issue is? You want dates but show nothing with dates, and just show a separate array of values... — jmac
Thank you very much for responding. I was asking how to do this. That is why I don't have an example. I just figured this out about 5 minutes ago. I was surprised how few examples I could find about this. I will post the answer shortly. — attentionjay
I edited my question a bit to add some clarity to the actual question. I have an answer, but it is clunky and could probably be simplified greatly. — attentionjay

attentionjay attentionjay · Accepted Answer · 2013-07-06T04:53:01

I researched this for several days and I was finally able to solve the problem by piecing together answers to many different questions.

I started by querying the distinct dates and placing them into an array. The way this is done is very important because the date is a string when json encoded. It is important to parse it out and pass it in the Date(year,month,day) format exactly. It is also important that later on you use a variable to represent the date.

$dateArray = array();

while($row = mysql_fetch_row($queryDates)) {

    $dtArray = explode('-',$row[0]);
    $day = (int) $dtArray[1];
    $month = ((int) $dtArray[0])-1;
    $year = (int) $dtArray[2];
    $dateArray[] = "Date($year, $month, $day)";

}

I then set up the columns for the table by looping through a query of the SKU's.

$invArr = array(
    'cols' => array(
        array('label' => 'Date', 'type' => 'date')
        )
    );

 while($row = mysql_fetch_row($querySkus)) {
$invArr['cols'][] = array('label' => $row[0], 'type' => 'number');

}

I then query the quantities and place them into an array. I then loop through each value and populate the table array ($inVarr).

$quantities = array();
while($row = mysql_fetch_row($queryQty)) {
     $quantities[] = (int) $row[0];
}

$qtyCounter = 0; 
for($i=0;$i<count($dateArray);$i++) {

   $invArr['rows'][] = array(
        'c' => array(
                array('v' => $dateArray[$i]),
                array('v' => $quantities[$qtyCounter]),
                array('v' => $quantities[$qtyCounter+1]),
                array('v' => $quantities[$qtyCounter+2])
                )
              );
   $qtyCounter=$qtyCounter+3;

}

I can then just echo the json encoded array which will return the data for the data table.

echo json_encode($invArr);

I believe that this is a bit clunky, but it works.

Google charts column graph from MySql database

1 Answers