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
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
Comments
Post a Comment