json encode mysql results

I have a script running on a server every 3 seconds exporting query results to an html file using the mysql  --html command line option
#!/bin/bash
while [ 1 ]; do
   mysql myfoobarDB --host=xxx.xxx.xxx.xxx --user=XXX --password=XXXXXX --skip-column-names --html --exec="
    SELECT *
    FROM foobar
    WHERE x = x
  " > foo_data.temp

Open in new window

a crontab kicks this off and the html from this file is used to display various realtime statistics on our intranet.

Currently the end result of this method looks pretty horrible with the screen refreshing and blanking the screen out with a javascript timer. I'm looking to redo the page with the timer into something that uses ajax to smoothly refresh the data hence the need for json formatting to make coding this process easier. My question, is there a way to json encode instead of using the --html command line option? If not please post example code in perl or a bash script that will json encode the query results to a file.
DalexanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA Engineer IIICommented:
If it's simple, you could concatenate the braces, quotes, and colons you need to make valid JSON.

If it's complicated, you could use the --xml option to produce an XML file.
Then use the perl XML2JSON module to convert the XML to JSON.

Here's a simple example from the cpan site.
http://search.cpan.org/~ken/XML-XML2JSON-0.06/lib/XML/XML2JSON.pm
        use XML::XML2JSON;
        my $XML = '<test><element foo="bar"/></test>';
        my $XML2JSON = XML::XML2JSON->new();
        my $JSON = $XML2JSON->convert($XML);
        print $JSON;

Open in new window


The output is
{"test":{"element":{"@foo":"bar"}},"@encoding":"UTF-8","@version":"1.0"}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DalexanAuthor Commented:
I tried to incorporate this code but I'm getting an error "Bareword found where operator expected"

 #!/bin/bash
use XML::XML2JSON;
while [ 1 ]; do
   mysql myfoobarDB --host=xxx.xxx.xxx.xxx --user=XXX --password=XXXXXX --skip-column-names --html --exec="
    SELECT *
    FROM foobar
    WHERE x = x
  " > $XML
my $XML2JSON = XML::XML2JSON->new();
my $JSON = $XML2JSON->convert($XML);

> foo_data.temp
0
Mark BullockQA Engineer IIICommented:
You have perl code in your bash script. The code I wrote was all perl.
0
DalexanAuthor Commented:
Thanks, Below is the code I'm working with now.

#!/usr/bin/perl

use DBIx::JSON;
use strict;


my $driver = "mysql";
my $database = "xxxxx";
my $dsn = "dbname=$database;host=xxx.xxx.xxx.xxx;port=3306";
my $userid = "xxx";
my $password = "xxx";

my $sql = "SELECT xidname AS Campaign FROM foobar WHERE x = x";

my $obj = DBIx::JSON->new($dsn, "mysql", $userid, $password);
 $obj->do_select($sql, "Campaign", 1);
$obj->err && die $obj->errstr;
open STDOUT, '>', "foo.out";
print $obj->get_json;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Scripting Languages

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.