8.18.2006

VS2005 Errors when creating .aspx pages that retrun XML or JSON

I kept getting this on a page that did nothing but reference a code behind.

Validation(): Element 'html' occurs too few times.

This is because VS2005 is performing HTML validation by default. Yuk. You can turn this off by going to:
  • Tools --> Options --> Text Editor --> HTML --> Validation
  • Uncheck the "Show Errors" checkbox

Voila!

8.16.2006

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

Bug in CareerBuilder.com saved job search

This stuff drives me nuts.
  • Go to careerbuilder.com. Set up an account if you don't already have one
  • Add a job search, save it as an agent (or whatever they call it)
  • Now, when you go to your "my careerbuilder" you'll see a list of local jobs and your saved searches below that.
  • Click edit on your job search. Change the mileage (include jobs within: X miles)
  • Click "Save Edits." Watch in amazement as your mileage change is NOT saved

The only way to accurately save this change to your searches is to delete your current search and add another with the proper mileage constraints.

8.02.2006

How to get a C# class to talk to Mysql in under 5 minutes

I had to access a Mysql database at work today using C# and .NET. Well, I didn't have to... but I wanted to... and I had some down time. I'm mainly a PHP guy, but C# is similar enough to C/C++ that I figured out what I needed to do in a few minutes. I think it's always important to step out of your safe little world every now and again to try something radically different. It keeps your problem solving skills fresh.

Here's the rundown for those interested.
  • Download Mysql Connector/Net package http://dev.mysql.com/downloads/connector/net/1.0.html
  • While you're there, if you don't have the Mysql sample db "world" set up on your server, you should get that too at http://dev.mysql.com/doc/ (I'm assuming you know how to set this upin Mysql.
  • Install it on the Windows server you are using (and on your dev machine too)
  • Open up Visual Studio (2k5 if you got it)
  • Open up yer C# project
  • In the References of your project, Add a reference to the Mysql Connector. You'll probably need to go to the "browse" tab and go find it. The default install puts a Mysql.Data.dll in C:\Program Files\MySQL\MySQL Connector Net 1.0.7\bin\.NET 2.0
  • For my sample below, you'll also need to add the System and System.Data .NET references
  • Then plop this test code into a C# project. This example is just a stand-alone console app to get you going. There's plenty of documentation at http://www.mysql.org/doc/refman/4.1/en/connector-net.html to keep you motivated.
using MySql.Data.MySqlClient; 

class mysql 
{ 
  static void Main() 
  { 
    MySqlConnection conn; 
    string myConnectionString; 

    myConnectionString = "server=localhost;user id=test; pwd=test;database=world;"; 

    try 
    { 
      conn = new MySqlConnection(); 
      conn.ConnectionString = myConnectionString; 
      conn.Open(); 

      string sql = "SELECT Name, Continent FROM Country Limit 10"; 

      MySqlCommand cmd = new MySqlCommand(); 
      MySqlDataReader results; 
      cmd.CommandText = sql; 
      cmd.Connection = conn; 
      results = cmd.ExecuteReader(); 

      while (results.Read()) 
      { 
        //string f1 = results.GetString(0); 
        //int f2 = results.GetInt32(1); 
        System.Console.WriteLine(results.GetString(0) + ", " + results.GetString(1)); 
      } 

      results.Close(); 
      System.Console.ReadLine(); 
    } 
    catch (MySqlException ex) 
    { 
      System.Console.WriteLine("error" + ex.Message); 
      System.Console.ReadLine(); 
    } 
  } 
}