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

I have the result of a query on a WordPress site, the query results are: -

  H COUNT (*) 0 7 1 5 2 2  

The query is something like this: -

  $ sql = 'SELECT HOUR (`post_data `) Like H, COUNT (*) '. Veterans like 'FROM `wp_7tv8g5_posts`' 'where date (` post_data`) = date (now ()) and `gu /' \ /% / questions /% \ '. 'Group by H';  

The Google Chart API requires data in the following format: -

& amp; Cht = lc & amp; Chxt = x, y & amp; 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 and; Chco = FF9900, FF00000000FF "alt =" sample chart "/>

or something like that.

I have an important question on how to read through the result of the query and

Any thoughts?

Cheers

Jonathan

"Post-text" itemprop = "text">

Here's an example (using PHP5, you need to customize the type of graph, color, etc.):

  Lt ;? Php require_once ('/ include config.inc'); // MYSQL_ * constants $ db = defines new mysqli (MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB); if ($ db- & g T; connect_error} {die ('connect failed:'. $ Db-> connect_up);} if (! $ Db- & gt; set_cutset ("utf8")) {die ('Error loading character set utf8:' $ Db-> Error);} $ Sql ​​= 'SELECT HOUR (`Post_data`) AH,' (FROM` wp_7tv8g5_posts` '' '' where 'date' ('post_date' ) = DATE (now ()) \ '% / question /% \' like '.' And 'guid' 'Group by H'; $ Url = 'http://chart.apis.google.com/chart ? '; $ Point_size = 3; $ Data = array ('cht' = & gt; 's',' chxt '=>' x, y ',' chs' = & gt; '700x300', 'chd' = & gt; 't: ',' Chxr '= & gt;' ',' chds' = & gt; '', 'chxl' => '0: |') ;; $ Chxl = array (); $ Chd = array ('x' => array (), 'y' => array ()); If ($ stmt = $ db-> Ready ($ sql)) {/ * Execute Query * / if ($ stmt-> Execute) === true) {/ * Bind result variable * / $ stmt - & gt; Bind_Stilt ($ H, $ C); / * Fetch value * / while ($ stmt-> Fatch ()) {array_push ($ chd ['x'], $ h); Array_push ($ chd ['y'], $ c); }} And  an error); } / * Free Result * / $ stmt- & gt; Free_Schand (); / * Close details * / $ stmt- & gt; Close (); } And (die ('stmt can not be prepared:'. $ Db-> error);} $ db-> off (); foreign currency (range (0, 24) $ H } {Array_push ($ chxl, sprintf ('% 02d', $ h). 'H');} $ data ['chxl']. = Implode ($ chxl, '|'); $ data ['chd'] . = Intestine ($ chd ['x'], ','). '|'. Implode ($ chd ['y'], ','). '|'. $ Point_size; $ data ['chxr'] . = Sprintf ('0,0,24 | 1,0,% d, 1', maximum ($ chd ['y'])); $ data ['chds']. = Sprintf (' 0,24,0 , $ D ', Max ($ chd [' y '])) $ Url. = Http_build_query ($ data,' ',' amp; amp; '); // http_build_query is PHP5 only? & Gt; & lt; Img src = '& lt ;? php echo $ url;? & Gt;' / '& gt; Pre & gt; & lt ;? php echo urldecode ($ url);? & Gt; & lt; / east & Gt;  

Output image:

Output URL:

  http: / /chart.apis.google. Com / chart? Cht = s and chxt = x, y and chs = 700x300 and 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 & amp; chxr = 0,0,24 | 1 , 0,5,1 & amp; chds = 0, 24,0,5 & amp; chxl = 0: | 00h | 01h | 02h | 03h | 04h | 05h | 06h | 07h | 08h | 09h | 10h | 11h | 12h | 13h | 14h | 15h | 16h | 17h | 18h | 1 9h 20h | 21h | 22h | 23h | 24h