a simple php code that will parse any db query into JSON

php db query result to JSON

/*

=================================================
===================== INFO ======================
=================================================

Here is my useful mysql to json code:

mysql tip:
select "|"tour 
is same as
select "|" as tour


"|"tour >>> marks begining of new object (and end of previous once) named tour
"-"password  >>> will not include column named password 


For query like this:

$query=select "|"tour,t.id,t.title,"|"destination,d.id,d.name from tour t, destination d
$table=array("");
$more_rows=array(1); 


It will return/echo following json:
[
  {tour:{id:XXX,title:XXX},destination:{id:XXX,name:XXX}},
  {tour:{id:XXX,title:XXX},destination:{id:XXX,name:XXX}},
  ...
]
You see object tour with id and tittle, that is from select "|"tour,t.id,t.title 
then comes destination with id and name ,"|"destination,d.id,d.name


If $more_rows was array(0); 
It will not wrap results into [ ]
$more_rows can't be based on mysql result because you need same json output every time.
For above query it would return, this
Which is not legit json, only do this if are sure you will get only one row
{tour:{id:XXX,title:XXX},destination:{id:XXX,title:XXX}},
{tour:{id:XXX,title:XXX},destination:{id:XXX,title:XXX}},


If $table was array("whatever"), again make sure you either name all or none
It will return: 
{
  whatever:[
    {tour:{id:XXX,title:XXX},destination:{id:XXX,title:XXX}},
    {tour:{id:XXX,title:XXX},destination:{id:XXX,title:XXX}},
    ...
  ]
}


It is mutli query ready, that is why $table and $more_rows is an array, each array item is for one db query.


=================================================
=================== EXAMPLES ====================
=================================================

Here are some examples:


$query=''

.' select'
.' *' 
.' from'
.' product'
.' order by id asc'
.';'

.' select'
.' f.id,f.mail,u.name' 
.' from'
.' firm f'
.' left join user u on u.mail=f.mail'
.' order by u.name is null,u.name,f.mail asc'
.';'

.' select'
.' f.id"firm",c.price,c.amount,c.product '
.' from'
.' firm f'
.' join contract c on f.id=c.firm'
.';'

;

$table=array("product","firm","contract");
$more_rows=array(true,true,true);
    






$query=''

.'select'

.' u.*,"-"password' // select everything from user except password
.',f.id firm'

.' from'

.' user u'
.' left join firm f on f.mail=u.mail'
.' where u.id='.get('user')  
.';'

;

$table=array("");
$more_rows=array(0);









$query=

'select'

.' f.mail'
.',u.id user,u.name,u.phone,u.street,u.town,u.ico,u.dic'

.' from'

.' firm f'
.' left join user u on f.mail=u.mail'
.' where f.id='.get('id')  
.';' 
 
 
.'select'

.' c.price,c.amount'
.',p.name'

.' from'

.' firm f'
.' join contract c on f.id=c.firm and c.firm='.get('id') 
.' join product p on p.id=c.product' 
 .';'

;    

$table=array("firm","contracts");
$more_rows=array(0,1);







=================================================
======================= CODE ====================
=================================================


db_XXX functions are almost equivalent to //http://www.php.net/manual/en/book.mysqli.php


*/





$table=array("");
$more_rows=array(1); 


$index=0;
if($table[0]||!$more_rows[0]) echo '{';
if(db_multi_query($query)) {  
  $l0=0;  
  do {   
    if($table[$index]) {
      if($l0) echo ',';     
      echo '"'.$table[$index].'":';
    }
    $l0=1;  
    $fields=db_fetch_fields();
    $l1=0;     
    if($more_rows[$index]) echo '[';
    while($row=db_fetch_row()) {
      if($l1) echo ','; $l1=1;    
      if($table[$index]||$more_rows[$index]) echo '{';        
      $l2=0;
      $l3=0;
      $dont=array();
      //$i=0;     
      foreach($row as $i=>$v) {
        $k=$fields[$i]->name;
        //$i++;
        if($v=='-') {
          $dont[]=$k;
        } else if($v=='|') {
          if($l2) echo '},';
          else if($l3) echo ',';
          echo '"'.$k.'":{';
          $l2=1;
          $l3=0;
        } else {
          if(!in_array($k,$dont)) {
            if($l3) echo ','; $l3=1;
            //echo '"'.$k.'":"'.str_replace('"','\"',$v).'"';
            if(is_null($v)) $v='null';
            else if($v=='') $v='""';
            else if($v[0]!='['&&$v[0]!="{"&&!is_numeric($v)) $v='"'.$v.'"';                   
            echo '"'.$k.'":'.$v;
          }
        }
      }
      if($l2) echo '}'; 
      if($table[$index]||$more_rows[$index]) echo '}';       
    }
    if($more_rows[$index]) echo ']';
    $index++;
  } while (db_next_result());
}
if($table[0]||!$more_rows[0]) echo '}';