Mysql and PHP help Obama become President of the United States

We have our answer to my earlier question about technology used by Obama and McCain.  One small step for LAMP, one giant leap for LAMP-kind.

read more...

First impressions on Google App Engine

From the Google site:

Google App Engine lets you run your web applications on Google's infrastructure. App Engine applications are easy to build, easy to maintain, and easy to scale as your traffic and data storage needs grow. With App Engine, there are no servers to maintain: You just upload your application, and it's ready to serve your users.

You can serve your app using a free domain name on the appspot.com domain, or use Google Apps to serve it from your own domain. You can share your application with the world, or limit access to members of your organization.

Without actually firing up the SDK yet, here are my initial thoughts:

1. Do we trust Google? With our data? With our users? I love the idea of the Google App Engine (GAE), a scalable web app system where you only have to worry about coding your business logic and structuring your models. This is kind of like Amazon’s EC2 on steroids. No sysadmin stuff like with EC2, just coding. However I do not love that Google is already in the web app space themselves and is now marketing a web app space hosting platform. You have to trust them a little bit more then I’m comfortable with. They've already got your users and your data and your code. Don't they conceivably control your business at that point? Maybe I'm being paranoid here.Plus, there’s the lock-in factor. If I develop my app using MySQL as a backend, I know I could with only some minor pain change that backend to Oracle. It’s all SQL at some point. How would you swap out of using the Google DataStore API? It’s a proprietary system with no published standards.
2. GAE is currently only in Python. No PHP love? I realize a lot of Google runs on Python and C++ and that’s what it is in their wheelhouse. But I still consider Python to be a fringe language. If GAE were opened up to support PHP and C#, it would blast off in popularity. Then again, a Python only crowd is a good beta test before the crush of PHP devs comes in.
3. The DataStore API (ie, BigTable) is difficult to wrap my head around. After spending so many years carefully crafting db tables and relationships to get the most bang for my buck, I now have to throw out a lot of that hard-earned knowledge. I need to think in terms of objects (or columns) instead of rows.There are still relationships and keys to keep track of but not in the traditional ways of SQL. It seems like much more of your data fetching is done in code rather then in SQL. The shift in thinking reminds me of my own move to Object-Oriented Programming. I whined like a baby during that phase of my programming development (well, at least I did on the inside).

Hopefully I’ll get some more time to play with this stuff soon and report back.

read more...

Delphi for PHP and Mysql development

On my short list of things to check out soon are Delphi for PHP and Scibit's MyComponents for Mysql.  It looks like Delphi gives you a "visual (RAD) framework for PHP" and MyComponents gives you the glue to Mysql.  It's an interesting concept but I can't quite get my head around doing visual development in PHP (cuz that's what html and css designers are for).

read more...

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

read more...

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(); 
    } 
  } 
}

read more...

The SqlXML class -- PHP sql query results in XML via object

For a personal project that I’m working on in PHP4, I’m using XSLT to display a bunch of pages. Most times, I don’t use the XSL transforms for the whole page, usually just tables of data that are returned from a query. The first thing that you need to do an XSL transform is a nice chunk of XML data. It would be pretty handy if when we queried the database, it returned our results as XML that we could use for the transform. More robust database systems like Oracle can do this right out of the box, but Mysql needs some help from PHP. I created a nice little object to take care of this for me.

The object does a few things. There are two methods in the object, query and queryPaged. The query method just does a straight query to the db and returns the results as XML. The queryPaged method also does a query to the db and returns the results as XML. But this method takes on a few extra parameters so that it can return page navigation information in the XML also. This way, you can set up paged data tables with navigation (page 1, page 2, etc.) instead of just glomming out hundreds of rows of data.

First things first – The query method

First off, we need to include our db connection stuff and our SqlXML object file. Then we can create our sql. Then instantiate the SqlXML object and pass it our sql. Here's that code:

// start db connection 
require_once('db.php'); // left to the reader to do 
require_once('SqlXML.php'); // full code to this program below 

$sql = "SELECT * FROM customers LIMIT 10"; 

$sqlxml =& new SqlXML(); 
$xml = $sqlxml->query($sql); 

Pretty simple right? If you want to see the results, you can always just echo out a textarea:

echo '' . $xml . '';

Your results will obviously depend on your query, but an excerpt of mine look something like this:

<?xml version="1.0"?> 

  
    
      1
      Harry
      harry@test.com 
    
    
      2
      Tom
      tom@test.com 
    
 

So the method creates an XML “row” element for each row returned. Each field in the row returned gets its own “column” element. Each “column” element has a “name” attribute which tells you which field that data is for.

Keep in mind here that $xml is a string and not an xml object at this point. All the domxml stuff is handled inside the SqlXML object so we don’t have to fuss with it outside the object.

So how does this thing work? Well, first, it executes the query. If something didn’t work in the query, it just returns this as a string, not as XML.

function query($query) 
{ 
  $xml = ''; 
  $result = mysql_query($query); 
  if (!$result) 
  { 
    return 'Query failed: ' . mysql_error(); 
  }

Then it records the number of rows in the data.

$totalRows = mysql_num_rows($result);

The method then starts to build the xml tree by setting a result-set element at the root with an attribute of total-rows. The method then goes on to build the rest of the xml tree, row by row. I should note here that you do need the domxml extensions for PHP installed. All the XML creation in the object is done with domxml methods, no lame XML-as-string solutions here. Here’s the code snip of that area of the code:

$rowCount = 1; 
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
{ 
  $row = $root->append_child($dom->create_element("row")); 
  $row->set_attribute("number", $rowCount); 

  foreach ($line as $colKey => $colValue) 
  { 
    $col = $row->append_child($dom->create_element("column")); 
    $col->set_attribute("name", strtoupper($colKey)); 
    $col->append_child($dom->create_text_node($colValue)); 
  } 
  $rowCount++; 
} 

Here, the while loop creates the row elements in our resulting xml and the foreach loop creates the column elements.

Once you have your xml in that $xml variable, you can bring it in to your XSL transform (article coming soon...). Here is the full code for using the query method of the object.

// start db connection 
require_once('db.php'); // left to the reader to do 
require_once('SqlXML.php'); // full code to this program below 

$sql = "SELECT * FROM customers LIMIT 10"; 

$sqlxml =& new SqlXML(); 
$xml = $sqlxml->query($sql); 
echo '' . $xml . ''; 

// then take that $xml string and transform with the appropriate XSL 

Now what if you wind up with a lot of data from your query? For me, if I'm displaying any more than about 20 rows of data, I'm going to paginate it (place 20 rows on each page with some page to page naviation). We can use the queryPaged method of the SqlXML object for this. But I'll save that for another article.

Here's the complete SqlXML.php class.

class SqlXML 
{ 
  var $resultsPerPage = 0; 
  var $curPage = 0; 
  var $sort_by; 
  var $sort_dir; 

  function SqlXML() 
  { 

  } 

  function query($query) 
  { 
    $xml = ''; 
    $result = mysql_query($query); 
    if (!$result) 
    { 
      return 'Query failed: ' . mysql_error(); 
    }
 
    $totalRows = mysql_num_rows($result); 
    $dom = domxml_new_doc("1.0"); 
    
    $root = $dom->append_child($dom->create_element('result-set')); 
    $root->set_attribute('total-rows',$totalRows); 

    $rowCount = 1; 
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
    { 
      $row = $root->append_child($dom->create_element("row")); 
      $row->set_attribute("number", $rowCount); 

      foreach ($line as $colKey => $colValue) 
      { 
        $col = $row->append_child($dom->create_element("column")); 
        $col->set_attribute("name", strtoupper($colKey)); 
        $col->append_child($dom->create_text_node($colValue)); 
      } 
      $rowCount++; 
    } 

    $xml = $dom->dump_mem(); 

    // Free resultset 
    mysql_free_result($result); 
    return $xml; 
  } 

  function queryPaged($queryFields, $queryTail, $scriptPath = '') 
  { 
    $recCount = 0; 
    $totalPages = 0; 
    $xml = ''; 

    // get the total count without getting field data 
    $queryCt = "SELECT COUNT(*) " . $queryTail; 
    $resultCt = mysql_query($queryCt); 

    if (!$resultCt) 
    { 
      return 'Query failed: ' . mysql_error(); 
    } 

    // calc total pages 
    $row = mysql_fetch_row($resultCt); 
    $recCount = $row[0]; 

    if ($recCount > 0) 
    { 
      $query = "SELECT " . $queryFields . $queryTail; 
      if ($this->resultsPerPage != 0) 
      { 
        $recStart = ($this->curPage * $this->resultsPerPage) - $this->resultsPerPage; 

        // add limit to query 
        $query .= " LIMIT $recStart, $this->resultsPerPage"; 
      } 

      mysql_free_result($resultCt); 
      $result = mysql_query($query); 

      if (!$result) 
      { 
        return 'Query failed: ' . mysql_error(); 
      } 

      // calc total pages 
      $recCount2 = mysql_num_rows($result); 
      if (!$recCount2) 
      { 
        return 'Query failed : ' . mysql_error(); 
      } 

      $totalPages = floor($recCount / $this->resultsPerPage); 
      if ($recCount % $this->resultsPerPage > 0 ) 
      { 
        $totalPages++; 
      } 
    } 

    // create xml 
    $dom = domxml_new_doc("1.0"); 
    $rs = $dom->append_child($dom->create_element('result-set')); 
    $rs->set_attribute('total-rows',$recCount); 
    $rs->set_attribute('results-per-page', $this->resultsPerPage); 
    $rs->set_attribute('total-pages', $totalPages); 
    $rs->set_attribute('current-page', $this->curPage); 
    $rs->set_attribute('script-path', $scriptPath); 

    if (isset($this->sort_by) && isset($this->sort_dir) && $this->sort_by != '' && $this->sort_dir != '') 
    { 
      $rs->set_attribute('sort-by', $this->sort_by); 
      $rs->set_attribute('sort-dir', $this->sort_dir); 
    } 

    $nav = $rs->append_child($dom->create_element('nav')); 

    // create XML to set up paging 
    for ($i = 1; $i < = $totalPages; $i++) 
    { 
      $page = $nav->append_child($dom->create_element('page')); 
      $page->set_attribute('num', $i); 
      if ($i == $this->curPage) 
      { 
        $page->set_attribute('current-page', 'true'); 
      } 
    } 

    $rows = $rs->append_child($dom->create_element('rows')); 

    if ($recCount > 0) 
    { 
      $rowCount = 1; 
      while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
      { 
        $row = $rows->append_child($dom->create_element("row")); 
        $row->set_attribute("number", $rowCount); 

        foreach ($line as $colKey => $colValue) 
        { 
          $col = $row->append_child($dom->create_element("column")); 
          $col->set_attribute("name", strtoupper($colKey)); 
          $col->append_child($dom->create_text_node($colValue)); 
        } 
        $rowCount++; 
      } 
    } 

    if (is_resource($result)) 
    { 
      mysql_free_result($result); 
    } 
    $xml = $dom->dump_mem(); 

    return $xml; 
  } 
} 

read more...