converting Mysql to Json on the fly

[martin]

I have seen references to this but no solutions that I could find so
here is a working example using
the PHP 5 function "json_encode".

this is working well for Exhibit but I'm still having problems when
loading a timeline, I get an error
"TypeError: this._timeline is undefined" .
anyway I thought I would share what I have so far

cut the below and save as "my_phpfile.php":
======
<?php
$mysql = mysql_connect('localhost', 'root', 'password');
mysql_select_db('mydatabase');
$query = 'select * from mytable';
$res = mysql_query($query);

// iterate over every row
while ($row = mysql_fetch_assoc($res)) {
// for every field in the result..
for ($i=0; $i < mysql_num_fields($res); $i++) {
$info = mysql_fetch_field($res, $i);
$type = $info->type;

// cast for real
if ($type == 'real')
$row[$info->name] = doubleval($row[$info->name]);
// cast for int
if ($type == 'int')
$row[$info->name] = intval($row[$info->name]);
}

$rows[] = $row;
}

// display as JSON all rows together as one big array
echo '{"items": ';
echo json_encode($rows);
echo '}';

mysql_close($mysql);
?>

======
after you have some data in your mysql database
place the above php file in a folder on your website and test it by
loading it in your browser and you should see the json data, once you
have confirmed it works then modify your html page (that reside in the
same folder) to :

<link href="my_phpfile.php" type="application/json" rel="exhibit/
data" />

so have fun! that's all there is to it :)


If anyone has any ideas on the Timeline error I would be pleased to
hear about them.
you can see the presidents example here:
http://courtenay.widget-it.com/presidents/index.html

you can see the the json encoded data here:
http://courtenay.widget-it.com/presidents/bip.php

you can download the mysql backup of presidents here:
http://courtenay.widget-it.com/presidents/presidents.sql

[martin]

Here is a working example of Exhibit:
using the above procedure:
http://courtenay.widget-it.com/crime/

[mleden]

Hi Martin,

Thanks very much for sharing and very nice Exhibit, by the way.

A couple of minor issues that I'm seeing:
1. Google map key error ("This web site needs a different Google Maps
API key. A new key can be generated at http://code.google.com/apis/maps/.")
when entering the site.
2. One data point cannot be plotted.

Do you happen to know if there are any performance gains in using this
procedure versus crafting the JSON from MySQL in the PHP code? I
basically iterate through the database result-set as follows:
while($thisrow=mysql_fetch_row($result))
{
echo ",";
echo "{
'id':\"".trim($thisrow[2]).".".trim($thisrow[0])."\",
'label':\"".trim($thisrow[1])."\",
'type':\"".trim($thisrow[2])."\",
'service_server_name':\"".trim($thisrow[3])."\",
'service_full_name':\"".trim($thisrow[4])."\"
}";
$i++;
}

Thx,
-Mark

[martin]

Hi Mark,
Thanks for the feedback, I fixed the little problems with the crime
map.
I didn't do any performance testing on using the PHP built-in
json_encode function compared to the type of thing your doing, I just
decided to keep it small and reuse what I could.
I did try a Mysql2Json class library from:
http://lehardy.mirrors.phpclasses.org/package/4369-PHP-Generate-JSON-representation-from-MySQL-queries.html
and by changing the root string to 'Items' in MYSQL2JSON.class.php it
also produced quite usable Json data.
cheers

On Jun 10, 10:19 am, mleden <mle...@yahoo.com> wrote:
> Hi Martin,
>
> Thanks very much for sharing and very nice Exhibit, by the way.
>
> A couple of minor issues that I'm seeing:
> 1. Google map key error ("This web site needs a different Google Maps

> API key. A new key can be generated athttp://code.google.com/apis/maps/.")

> when entering the site.
> 2. One data point cannot be plotted.
>
> Do you happen to know if there are any performance gains in using this
> procedure versus crafting the JSON from MySQL in the PHP code?  I
> basically iterate through the database result-set as follows:
> while($thisrow=mysql_fetch_row($result))
> {
>         echo ",";
>         echo "{
>                 'id':\"".trim($thisrow[2]).".".trim($thisrow[0])."\",
>                 'label':\"".trim($thisrow[1])."\",
>                 'type':\"".trim($thisrow[2])."\",
>                 'service_server_name':\"".trim($thisrow[3])."\",
>                 'service_full_name':\"".trim($thisrow[4])."\"
>         }";
>         $i++;
>
> }
>
> Thx,
> -Mark
>
> On Jun 9, 10:41 pm, martin <mwcourte...@yahoo.com> wrote:
>
>
>
> > Here is a working example of Exhibit:

> > using the above procedure:http://courtenay.widget-it.com/crime/- Hide quoted text -
>
> - Show quoted text -

[martin]

I figured out the timeline error I was having, I just had a mistake in
the data.
so it's all working now.
grab the files as above and look at the source of the index.html to
recreate what you need.

[Zulkifli Said]

i cant acces this link

you can see the presidents example here: 
http://courtenay.widget-it.com/presidents/index.html 

you can see the the json encoded data here: 
http://courtenay.widget-it.com/presidents/bip.php 

you can download the mysql backup of presidents here: 
http://courtenay.widget-it.com/presidents/presidents.sql 

can u give me a new link?