how to convert query result to formatted string for google chart API

I have the results of a query on a wordpress site. The query results are::-

h   COUNT( * )
0   7
1   5
2   2

The query is something like thi开发者_Go百科s this:-

$sql = 'SELECT HOUR( `post_date` ) AS h, COUNT( * )'
        . ' FROM `wp_7tv8g5_posts`'
        . ' WHERE DATE( `post_date` ) = DATE( NOW( ) ) AND `guid`like \'%/questions/%\''
        . ' GROUP BY h'; 

The google chart APi needs the data in the following format::-

&cht=lc &chxt=x,y &chxl=0:|00:00|01:00|02:00|03:00|04:00|05:00|06:00|07:00|08:00|09:00|10:00|11:00|12:00|13:00|14:00|15:00|16:00|17:00|18:00|19:00|20:00|21:00|22:00|23:00 &chco=FF9900,FF0000,0000FF" alt="Sample chart" />

or something similar.

The key question I have is how to read the through the quesy result and construct the feed / format that google chart api is expecting.

Any ideas?

Cheers

Jonathan

Here's an example (PHP5, using mysqli connector, you'll need to adapt the type of graph, colors, etc):

<?php

    require_once('includes/config.inc'); // defines MYSQL_* constants

    $db = new mysqli(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB);

    if ($db->connect_error) {
        die('Connect failed: ' . $db->connect_error);
    }

    if (!$db->set_charset("utf8")) {
        die('Error loading character set utf8: ' . $db->error);
    }

    $sql = 'SELECT HOUR( `post_date` ) AS h, COUNT( * ) AS c'
         . ' FROM `wp_7tv8g5_posts`'
         . ' WHERE DATE( `post_date` ) = DATE( NOW( ) ) '
         . ' AND `guid` LIKE \'%/questions/%\''
         . ' GROUP BY h';
    $url = 'http://chart.apis.google.com/chart?';
    $point_size = 3;
    $data = array(
        'cht' => 's',
        'chxt' => 'x,y',
        'chs' => '700x300',
        'chd' => 't:',
        'chxr' => '',
        'chds' => '',
        'chxl' => '0:|'
    );
    $chxl = array();
    $chd = array(
        'x'=> array(), 
        'y' => array()
    );

    if ($stmt = $db->prepare($sql)) {
        /* execute query */
        if ($stmt->execute() === true) {
            /* bind result variables */
            $stmt->bind_result($h, $c);

            /* fetch values */
            while ($stmt->fetch()) {
                array_push($chd['x'], $h);
                array_push($chd['y'], $c);
            }
        } else {
            die('stmt error: ' . $stmt->error);
        }

        /* free result */
        $stmt->free_result();

        /* close statement */
        $stmt->close();
    } else {
        die('Cannot prepare stmt: ' . $db->error);
    }

    $db->close();

    foreach (range(0, 24) as $h) {
        array_push($chxl, sprintf('%02d', $h) . 'h');
    }

    $data['chxl'] .= implode($chxl, '|');
    $data['chd']  .= implode($chd['x'], ',') . '|' . implode($chd['y'], ',') . '|' . $point_size;
    $data['chxr'] .= sprintf('0,0,24|1,0,%d,1', max($chd['y']));
    $data['chds'] .= sprintf('0,24,0,%d', max($chd['y']));
    $url .= http_build_query($data, '', '&amp;'); // http_build_query is PHP5 only
?>

<img src='<?php echo $url; ?>' />
<pre><?php echo urldecode($url); ?></pre>

Output image:

Output URL:

http://chart.apis.google.com/chart?cht=s
&chxt=x,y&chs=700x300&chd=t:0,6,7,10,11,12,13,14,15,16,17,18,19,20,21,22|
2,1,1,1,2,1,5,3,2,3,4,1,1,1,1,1|3&chxr=0,0,24|1,0,5,1
&chds=0,24,0,5&chxl=0:|00h|01h|02h|03h|04h|05h|06h|07h|08h|09h|10h|11h|
12h|13h|14h|15h|16h|17h|18h|19h|20h|21h|22h|23h|24h