Dynamic data table creation for for column chart (columns and rows unknown)

Question

I'm looking for a loop that retrieves the data from the MySQL result set with an unknown amount of columns and rows and puts it in GViz format. With the following code I can read the data dynamically, but I can't get it into the format that Google Visualization API desires:

$cols = array();
$row = array();
$i = 0;
while ($i < mysql_num_fields($result)) {
$meta = mysql_fetch_field($result, $i);
    if (!$meta) {
    echo "No information available<br />\n";
    }

    $cols[]= "{id: '".$i."', label: '".$meta->name."', type: '".$meta->type."'}";
    $i++;
    }

while($r = mysql_fetch_assoc($result)) {
$row[]= $r;
}

$jsonTable = json_encode($cols);
$jsonTable2 = json_encode($row);
$Table3 = "{cols: {$jsonTable}, rows: $jsonTable2}";

echo $jsonTable;
     echo '<br>';

echo $jsonTable2;
     echo '<br>';

echo $Table3;
     echo '<br>';

The JS error I get from the browser in debug-mode is:

Error: Invalid type, {[id= '0', label= 'mon', type= 'string']}, for column "0".

I have looked at the data parameter on googles documentation, but their data is hard coded as always. And this SO-page does not generate the cols and rows dynamically.

I'm glad for any help on getting the data parameter right. Thanks!

Do you know what the data types will be ahead of time? Is `$meta->name` supposed to be the column name? — asgallant, Aug 05 '14 at 00:43
Yes, the first column has to be set as string and the following columns as numbers. — ErrorBusy, Aug 05 '14 at 07:11
@asgallant: yes, table name is supposed to be the column name. — ErrorBusy, Aug 05 '14 at 12:41

score 1 · Accepted Answer · answered Aug 05 '14 at 13:02

The cause of the error you see is that your JSON string construction is wrong. Don't try to build them yourself, it is much easier to build arrays in PHP and allow the json_encode function to take care of the hard parts for you.

You can parse your data from MySQL into a DataTable like this:

$data = array(
    'cols' => array(),
    'rows' => array()
);
for ($i = 0; $i < mysql_num_fields($result), $i++) {
    $meta = mysql_fetch_field($result, $i);
    if (!$meta) {
        echo "No information available<br />\n";
    }
    $data['cols'][] = array(
        'type' => ($i == 0) ? 'string' : 'number',
        'label' => $meta->name,
        'id' => $i
    );
}
while($r = mysql_fetch_assoc($result)) {
    $temp = array();
    foreach ($data['cols'] as $col) {
        $temp[] = array('v' => $r[$col['label']]);
    }
    $data['rows'][] = array('c' => $temp);
}
echo json_encode($data, JSON_NUMERIC_CHECK);

Dynamic data table creation for for column chart (columns and rows unknown)

1 Answers1