为柱形图创建动态数据表（未知的列和行）

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 />
";
    }

    $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!

Answer 1

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 />
";
    }
    $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);