Handy AJAX data structures using JSON, XML, ADODB, PHP, and Mysql

I'm working on a project where I thought a little AJAX magic would help the user interface a bit, so I've been adding that functionality piece by piece. For this web site, we've previously standardized on using the ADODB PHP database abstraction library for all our queries.

The particular circumstances of the XMLHttpRequest callbacks usually required a few small rows of data to be returned. So, the challenge became how to use the ADODB record sets to create structures that were nice and portable for use with AJAX. Most of the examples of JSON code shows only the properties of one object being displayed. But to return multiple rows, you basically need an object with an object (ie, rows within a dataset)
There's currently quite a debate raging on whether to return XML or JSON for manipulating via JavaScript. I use both approaches depending on the situation but find that I prefer JSON the most. Here are functions for ADODB that will return both XML and JSON as well as examples of usage. These files can be copied from below or downloaded at:

http://www.boringguys.com/example_code/adodb/toxml.inc.txt
http://www.borignguys.com/example_code/adodb/tojson.inc.txt

Just remember to rename them to .php !!

Here's that same code:

toxml.inc.php

/** 
* Creates XML from the ADODB record set 
* 
* @param     object         $rs         - record set object 
* @param     bool        $moveFirst    - determines whether recordset is returned to first record 
* @return     string        $xml        - resulting xml 
* @version V1.0  10 June 2006  (c) 2006 Rich Zygler ( http://www.boringguys.com/ ). All rights reserved. 
* 
*    Released under both BSD license and Lesser GPL library license. You can choose which license 
*    you prefer. 
*/ 

function rs2xml($rs, $moveFirst = false) 
{ 
  if (!$rs) 
  { 
    printf(ADODB_BAD_RS,'rs2xml'); 
    return false; 
  } 

  $xml = ''; 
  $totalRows = 0; 
  $totalRows = $rs->numrows(); 

  $domxml = new DOMDocument('1.0', 'utf-8'); 
  $root = $domxml->appendChild($domxml->createElement('rows')); 
  $root->setAttribute('total-rows', $totalRows); 

  $row_count = 1; 
  while($line = $rs->fetchRow()) 
  { 
    $row = $root->appendChild($domxml->createElement('row')); 

    foreach ($line as $col_key => $col_val) 
    { 
      $col = $row->appendChild($domxml->createElement('column')); 
      $col->setAttribute('name', strtolower($col_key)); 
      $col->appendChild($domxml->createTextNode($col_val)); 
    } 
    $row_count++; 
  } 
  $domxml->formatOutput = true; 
  $xml = $domxml->saveXML(); 
  $domxml = null; 

  if ($moveFirst) 
  { 
    $rs->MoveFirst(); 
  } 
  return $xml; 
} 

tojson.inc.php

/** 
* Creates JSON ( http://www.json.org/ ) from the ADODB record set 
* 
* @param     object         $rs         - record set object 
* @param     bool        $moveFirst    - determines whether recordset is returned to first record 
* @return     string        $output        - resulting json string 
* @version V1.0  10 June 2006  (c) 2006 Rich Zygler ( http://www.boringguys.com/ ). All rights reserved. 
* 
*    Released under both BSD license and Lesser GPL library license. You can choose which license 
*    you prefer. 

Example output from query  "SELECT Name, Continent From Country LIMIT 10;" 

{"rows":[ 
{"row":{"Name":"Afghanistan","Continent":"Asia"}}, 
{"row":{"Name":"Netherlands","Continent":"Europe"}}, 
{"row":{"Name":"Netherlands Antilles","Continent":"North America"}}, 
{"row":{"Name":"Albania","Continent":"Europe"}}, 
{"row":{"Name":"Algeria","Continent":"Africa"}}, 
{"row":{"Name":"American Samoa","Continent":"Oceania"}}, 
{"row":{"Name":"Andorra","Continent":"Europe"}}, 
{"row":{"Name":"Angola","Continent":"Africa"}}, 
{"row":{"Name":"Anguilla","Continent":"North America"}}, 
{"row":{"Name":"Antigua and Barbuda","Continent":"North America"}} 
]} 

*/ 

function rs2json($rs, $moveFirst = false) 
{ 
  if (!$rs) 
  { 
    printf(ADODB_BAD_RS,'rs2json'); 
    return false; 
  } 

  $output = ''; 
  $rowOutput = ''; 

  $output .= '{"rows":'; 
  $totalRows = $rs->numrows(); 

  if($totalRows > 0) 
  { 
    $output .= '['; 
    $rowCounter = 1; 
    while ($row = $rs->fetchRow()) 
    { 
      $rowOutput .= '{"row":{'; 
      $cols = count($row); 
      $colCounter = 1; 
      
      foreach ($row as $key => $val) 
      { 
        $rowOutput .= '"' . $key . '":'; 
        $rowOutput .= '"' . $val . '"'; 

        if ($colCounter != $cols) 
        { 
          $rowOutput .= ','; 
        } 
        $colCounter++; 
      } 

      $rowOutput .= '}}'; 

      if ($rowCounter != $totalRows) 
      { 
        $rowOutput .= ','; 
      } 
      $rowCounter++; 
    } 
    $output .= $rowOutput . ']'; 
  } 
  else 
  { 
    $output .= '"row"'; 
  } 

  $output .= '}'; 

  if ($moveFirst) 
  { 
    $rs->MoveFirst(); 
  } 
  return $output; 
} 

Example Usage

Place both of the files into your existing ADODB directory. If you don't already have the "world" sample database from mysql set up, please go and get it and install it ( http://dev.mysql.com/doc/ ). Make sure that the toxml and tojson files from above are placed in your adodb/ dir. Then use the following sample pages. Make sure to change your db settings in the file to what's appropriate. You will need PHP5 for the XML example. But you could easily mock up a version of that in PHP4, or you could always use this.

To test it out just do something like this for xml


or this for json