Compare Two MySQL Database Structures Using mysqldump and diff

To compare two mysql databases in linux/osx you can use a very simple script that calls the “mysqldump” and “diff” commands to output the difference in structure of two databases to an output file. In this case I was comparing my local database with another at IP address 1.2.3.4. You will be prompted for passwords but to avoid this you can add “-pYOURPASSWORD” (less secure):

mysqldump --no-data --skip-comments --skip-extended-insert --host=127.0.0.1 -u root -p DATABASENAME>file1.sql
mysqldump --no-data --skip-comments --skip-extended-insert --host=1.2.3.4 -u root -p DATABASENAME>file2.sql
diff file1.sql file2.sql > diffoutput.txt
rm -f file1.sql
rm -f file2.sql

Use a Spring InitBinder to Resolve Type Mismatch and Bind Exceptions in POST from Spring Framework MVC Forms to Controller Actions

As a follow up to the previous article on binding entities and their children to form objects in Spring Framework it’s important to know how to submit the values of form objects in a standard form back to the controller. In the previous example we had a “Parent” entity, with a dropdown “select” on the form where you could choose one of the available “Children” objects. We also had a “Description” textbox that allowed the user to type in a suitable description.

The actual form, a very simple interface with both these elements and a submit button looks like:

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form" %>

<c:url var="saveParentUrl" value="/parent/save" />
<form:form modelAttribute="parent" method="POST" action="${saveParentUrl}">
Description:  <form:input path="description"/>
Child:  <form:select path="child" id="child" items="${children}" itemValue="id" itemLabel="name"/>
<input type="submit" value="Save Parent" />
</form:form>

In this example, when the user clicks on the “Save Parent” button the form will be posted to the “parent” controller action “saveParent” at “/parent/save” where we can save the updated “Parent” object.

Now the problem is that the POST header only contains text strings, not a full description of the actual “Child” object we have chosen for “Parent”. When you try to save the “Parent” object in the controller action the result is an exception,  “org.springframework.validation.BeanPropertyBindingResult”. The error message itself tells you exactly what is happening:

default message [Failed to convert property value of type ‘java.lang.String[]’ to required type ‘models.Child’ for property ‘Child’; nested exception is java.lang.IllegalStateException: Cannot convert value of type ] to required type [models.Child] for property ‘Child’: no matching editors or conversion strategy found]

The message tells you that Spring can’t automatically convert from the POST string value of the “child” select option (actually “id” in our case) to an actual “Child” object. We need to convert this string to a “Child” object in order to save the updated “Parent” entity. This is easily achieved using an “InitBinder” method within the controller class and a “@Validated” annotation on the “save” action’s “Parent” “@ModelAttribute” argument.

The code for the controller action looks like the following. Note that in this example I am using a “parentService” to pull the protocol to be updated from a “Parent” stored in session. It is likely that you will have something different or even pass in the id as part of the POST:

// ParentController.java
@RequestMapping(value = "/save", method=RequestMethod.GET)
public ModelAndView saveParent (@Validated @ModelAttribute("parent") Parent formparent, HttpServletRequest request )
{
    ...
    // find parent to update in database using parent service
    Parent parent = parentService.findById(sessionparent.getId());

    // set parent description and Child based on user input and save
    parent.setDescription(formparent.getDescription());
    parent.setChild(formparent.getChild());
    parentService.saveParent(parent);
    ...
}

The “formparent” is the “Parent” object passed in POST and mapped by Spring using the “@ModelAttribute” annotation. The “InitBinder” required to map the “Child” id passed in as part of POST to an actual “Child” object is:

@InitBinder
protected void initBinder(HttpServletRequest request, ServletRequestDataBinder binder) throws Exception {
    binder.registerCustomEditor(Child.class, "child", new PropertyEditorSupport() {
    @Override
    public void setAsText(String text) {
        Child ch = childService.findById(Long.parseLong(text));
        setValue(ch);
    }
    });
}

What this does is register a binder for the “child” object when it is passed from the form to Spring that converts the value to an actual object. By overriding the “SetAsText” function I use a “childService” service to retrieve the correct “Child” entity based on the text value passed in POST. Spring can now understand and convert the POST values to actual objects and the “Parent” entity can be safely modified by the “saveParent” controller action mapped to “/parent/save”. More information and some alternative methods are available at Develop and Conquer and Empire5.

Binding Entities and Their Children to Java JSP Form Elements in Spring Framework MVC

Binding objects to JSP form elements is actually quite easy in Spring Framework using the “modelAttribute” and “path” tags. Our scenario was the use of two Hibernate POJOs comprised of a parent entity “Parent” and a single “Child” entity which needed to be displayed on screen with appropriate form elements (“input” and “select”). The entities were annotated for use as part of a larger application but the basic structure was:

// Parent.java
public class Parent{
  private Int id
  private String description
  private Child child
  ...
}

// Child.java
public class Child{
  private Int id
  private String name
  ...
}

To display these objects on screen as part of a form in Spring Framework MVC we used the following “ParentController” controller with a “showparent” action. The action uses a “parentService.getParent(id)” method (not shown) to retrieve a single parent entity based on “id” and attach it to the form using a Map “model” and a returned “ModelAndView”. A “childService” is used to get the list of every possible child entity (also not shown) and this is also added to the “ModelAndView”:

// ParentController.java
@RequestMapping(value = "/showparent", method=RequestMethod.GET)
public ModelAndView protocolList(HttpServletRequest request)
{
	...
	Map<String, Object> model = new HashMap<String, Object>();
	model.put ( "children", childService.getAll());
	model.put ( "parent", parentService.getParent(id));
	return new ModelAndView ( "showparent", model );
}

The form itself is very simple and uses Spring binding to automatically create and populate input fields:

<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form" %>
<form:form modelAttribute="parent" >
Description:  <form:input path="description"/>
Child:  <form:select path="child" id="child" items="${children}" itemValue="id" itemLabel="name"/>
</form:form>

Spring Framework uses the “modelAttribute” and “path” tags to determine what is set on the form. In this case the forms “modelAttribute” is “parent” which means that the elements in the form are properties of the “parent” entity. The “description” is shown as the contents of a simple textbox input using “form:input”. The interesting part is the “form:select” which automatically populates the select with option objects based on the set of “child” objects in “children” and selects the correct “child” option for the “parent” based on the “itemValue”, “id” in this case.

Submitting changes to “parent” entities is covered in my more recent post.

Use PHP_Compat to use more recent PHP functions in old versions of PHP

As stated in my previous post, we needed to stick to PHP 5.2 rather than upgrade to a more recent version. One of the many downsides of this other than for security reasons is that you miss out on a large number of more useful functions.

In our case we missed the “str_getcsv()” function when reading in a .CSV file line by line. A really quick way to get around this, with or without using PEAR, was to download PHP_Compat, which includes a huge number of compatible functions from later versions of PHP.

If you have PEAR installed you can simply run the following to enable the functions:

pear install PHP_Compat

But if you are like us, running a version of Linux without PEAR installed by default you can just download the library directly. Once downloaded, just place in your web application directory (or elsewhere depending on configuration) and use a require_once() command in each PHP script for each function that you need, e.g.:

require_once(“PHP_Compat-1.6.0a3/Compat/Function/str_getcsv.php”);

If you are on shared hosting or cannot get a more recent version of PHP, this library is a lifesaver.

Upgrade PHP from 5.1.6 to 5.2.17 on CentOS

The default install of PHP on our CentOS 5.5 box was 5.1.6, which is very out of date (we are currently using PHP 5.3 elsewhere while we figure out how to get around some very serious problems with 5.4). Unfortunately, we needed to upgrade to PHP 5.2 and no further as 5.3 meant upgrading MySQL and potentially breaking compatibility with our web application.

It used to be that you could add the CentOS testing repositories and just update PHP but as PHP 5.2 is depreciated this option is no longer available. The solution is to use the Atomic repositories which can be added to your CentOS install by typing:

wget -q -O – http://www.atomicorp.com/installers/atomic | sh

This will add a new repository file “/etc/yum.repos.d/atomic.repo” which means we can use their packages as well as those from CentOS. Now we need to make sure that we don’t upgrade our PHP beyond 5.2 so we add a single line to “/etc/yum.conf” under the [main] section:

exclude=php-*5.3*

The exclusion means we will include packages from all repositories other than anything that matches “php-*5.3*” so PHP 5.3 won’t be installed as part of an upgrade.

Now just upgrade PHP and restart Apache:

yum update php

service httpd restart

You can check which PHP version you have using:

php -v

Now obviously you want to use a more recent version of PHP than 5.2 but in the rare case where you have to, the previous commands make things very easy.

Easily add multiple lightbox style popup videos to your page using Flowplayer and jQuery Tools Overlay

Adding a video to your page is easy enough by embedding directly or by using something like flashembed to embed Flash. What we needed to do was have multiple links on a page that would each open specific videos, whilst still keep the same generic JavaScript somewhere in a separate page template file. The accompanying CSS also had to stay in a separate CSS file and also needed to be generic. Our videos were all in .FLV format and needed to pop over the content in a nice overlay so as not to disturb the flow of the page.

Blacktrash have a nice demo of the kind of functionality we needed but it wasn’t quite generic enough to manage our very different sized videos. The code below, based on this example using Flowplayer and jQuery Tools Overlay, is simple enough that you can easily add multiple videos to a page. The size of the video player is determined by the html5 style attributes “data-width” and “data-height” on each “a” link which are picked up by jQuery and used to resize the player. If you do not specify these then the default is used, as given by the CSS.


<html>
<head>
<!-- include the flowplayer, jQuery and jQuery Tools libraries -->
<script src="http://releases.flowplayer.org/js/flowplayer-3.2.11.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7/jquery.js" type="text/javascript" charset="utf-8"></script>
<script src="http://cdn.jquerytools.org/1.2.6/all/jquery.tools.min.js" type="text/javascript" charset="utf-8"></script>

<!-- set up the flowplayer to play videos in an overlay using jQuery Tools Overlay -->
<script type="text/javascript">
  $(function () {
    var player;
    $("a[rel]").overlay({
      mask: {
        color: '#000',
        opacity: 0.2
      },
      onLoad: function () {
		// create player object and load in the href of the link clicked as the source of the video
	    player = $f("player", "http://releases.flowplayer.org/swf/flowplayer-3.2.15.swf",this.getTrigger().attr("href"));
		player.load();
		// set the height and width based on data-height and data-width
		$("#player").height(this.getTrigger().attr("data-height"));
		$("#player").width(this.getTrigger().attr("data-width"));
      },
      onClose: function () {
        player.unload();
      }
    });
  });
</script>

<!-- set up the CSS to hide the overlay by default and set up default height and width for the player -->
<style>
#overlay {
      display: none;
      padding: 40px;
    }
    .close {
      background:url(http://flowplayer.org/media/img/overlay/close.png) no-repeat;
      position:absolute;
      top:2px;
      right:5px;
      display:block;
      width:35px;
      height:35px;
      cursor:pointer;
    }
    #player {
      display: block;
      width: 600px;
      height: 300px;
      margin: 0;
    }
    #player *:focus {
      outline-style: none;
    }
</style>
</head>
<body>

<!-- each link contains a reference to the video to be played in flowplayer, the JavaScript above ensures they are played in an overlay -->
<a rel="#overlay" href="http://www.sitename.com/video/video1.flv">Video 1</a>
<a rel="#overlay" href="http://www.sitename.com/video/video800x600.flv" data-height="600" data-width="800">800x600 Video</a>

<!-- the overlay <div> itself is empty apart from a &amp;nbsp; and hidden using CSS to make sure it isn't shown on screen -->
<div id="overlay"><a class="close"></a><div id="player">&amp;nbsp;</div></div>

</body>
</html>

You can do a lot more with flowplayer and overlays so this is just a starting point but the approach is generic enough that any of the web team can add nice popup videos to a page just by adding a simple “a” link.

Easily connect and use PHP with SharePoint lists using cURL, RSS and NTLM authentication

Connecting to SharePoint from PHP is actually not that difficult if you have the cURL extension installed on your web server. In the case of my XAMMP windows development server I just made sure the following line in php.ini (c:xammpphpphp.ini in my case) was uncommented before restarting Apache:

extension=php_curl.dll

In Ubuntu/Linux you can usually just install the packages for cURL and after restarting Apache it will become available. Just type the following on the command line:

sudo apt-get install curl libcurl3 libcurl3-dev php5-curl

Then restart Apache

sudo /etc/init.d/apache2 restart

Now the following code comes from both Norbert Krupa’s comment on David’s IT Blog and a question about parsing HTML on StackOverflow. The important thing to note is that I needed to use cURL to authenticate my domain user when connecting to my secure SharePoint Services 3.0 test site. Apparently you can get away without using cURL on sites that don’t need authentication but the same cURL code listed below can be used with a blank username and password for the same effect.

The goal of this listing is to connect to SharePoint using a domain user (can also be a local user if SharePoint is set up that way) and retrieve the contents of a SharePoint list. The trick is to supply the RSS feed url, which allows PHP to parse the RSS feed and neatly list the contents of a SharePoint list. An advantage of using RSS feeds of SharePoint lists is that they are secured using the same method as the list itself and require no extra configuration on the SharePoint side of things. You can also set the RSS feed to only show a set number of items or days, which is useful for regularly updated lists.

// generic function to get the contents of an HTML block
function get_inner_html( $node ) {
    $innerHTML= '';
    $children = $node->childNodes;
    foreach ($children as $child) {
        $innerHTML .= $child->ownerDocument->saveXML( $child );
    }
    return $innerHTML;
}

// username and password to use
$usr = 'DOMAINUSERNAME';
$pwd = 'PASSWORD';
// URL to fetch, this is the address of the RSS feed (go into a list and click "Actions" -> "View RSS Feed" to get the url)
$url = "http://www.sharepointsite.com/_layouts/listfeed.aspx?List=%7BCED7CDDC-49C0-4C46-BDE6-CFC2BA993C84%7D";
//Initialize a cURL session
$curl = curl_init();
//Return the transfer as a string of the return value of curl_exec() instead of outputting it out directly
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
//Set URL to fetch
curl_setopt($curl, CURLOPT_URL, $url);
//Force HTTP version 1.1
curl_setopt($curl, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
//Use NTLM for HTTP authentication
curl_setopt($curl, CURLOPT_HTTPAUTH, CURLAUTH_NTLM);
//Username:password to use for the connection
curl_setopt($curl, CURLOPT_USERPWD, $usr . ':' . $pwd);
//Stop cURL from verifying the peer’s certification
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
//Execute cURL session
$result = curl_exec($curl);
//Close cURL session
curl_close($curl);

$xml = simplexml_load_string($result);

// display results on screen
foreach($xml->channel->item as $Item){
    echo "<br/>($Item->title)";
    $doc = new DOMDocument();
    $doc->loadHTML($Item->description);
    $ellies = $doc->getElementsByTagName('div');
    foreach ($ellies as $one_el) {
        if ($ih = get_inner_html($one_el))
        {
            echo ", $ih";
        }
    }
}

The SharePoint RSS feed is a little interesting as the “$Item->title” object is the main column in the list but the rest of the list is encapsulated in <div> within “$Item->description”, hence the requirement to parse the html.

For a SharePoint list with 3 columns the output will look something like:

(Item 1 Title) , Other Column A: xxxx, Other Column B: yyyy
(Item 2 Title) , Other Column A: zzzz, Other Column B: kkkk

Now the potential for this is great as it allows us to securely synchronise SharePoint lists with external databases, use SharePoint for authenticating PHP applications etc . We are going to be using this for automatically pulling users from a SharePoint list to populate a separate PHP application, whilst keeping user-identifiable data locked away on SharePoint.

Create Certificates and Use SSL with MySQL and the ADOdb Database Abstraction Library for PHP

This post is mainly for my own records and is the combination of 3 tutorials from KrWiki at waterlovinghead, Carl’s Whine Rack and Mad Irish. All 3 are almost 100% what I was looking for.

I needed to set up a secure database connection between our live web server and database server, both running Ubuntu Server. The 2 machines are completely separate and have their own IP address. The procedure for setting up an SSL connection between the two is similar to setting up a standard SSL certificate in Apache. First you create the certificates and sign them, then you configure the client (web server 192.168.0.2) and server (database server 192.168.0.1) to use them, finally you create a special MySQL user that uses SSL and connect using ADOdb. You don’t need to use ADOdb but it makes things a lot easier (see my previous posts).

It’s very important that your MySQL server has SSL enabled (most do by default). You can test this by connecting to your server and typing the SQL command:

SHOW VARIABLES LIKE '%ssl%'

Which should show “have_ssl” as either “DISABLED” or “YES”. If it says “NO” you will need to enable SSL (which might mean recompiling MySQL).

Creating the certificates

You need to create certificates for both the server (our database server) and the client (our web server). There are always a few ways of doing this described in various tutorials but this way is the easiest.

First create a directory somewhere that you will store your certificates in, e.g. “/var/ssl_certificates/” then go into the directory and type the following:

openssl genrsa 2048 > ca-key.pem

Which will generate a 2048 bit RSA private key for the Certificate Authority (CA). Now you need to create a Certificate Authority (CA) certificate, which will be used later to sign our server and client certificates. It is possible to use an external CA but in this case we are self signing, which makes things easier.

openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

You will be asked a few details: country name, state or province name, locality name, organisation name, organisational unit name, common name and email address. Set the common name to the name of your server e.g. 192.168.0.1. The ca-cert.pem file created is our CA certificate.

Now you can create the server certificate request:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

This will ask for the same details as the CA certificate but will also ask you to put in a challenge password and optional company name. In our case, we are not going to be using the challenge password as we would have to enter it every time the SQL server started up.

Now sign the request using our CA certificate and key and generate the server certificate:

openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Now we need to create a client certificate request. We need to create one for each client, but in this case we only need to create one for the web server:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem

You will be asked the same questions as for the server certificate request. The difference this time is that you need to put the client hostname in e.g. 192.168.0.2. Now sign this request and generate the client certificate:

openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

So now we should have several files in our directory. These are:

  • ca-key.pem – The certificate authority (CA) key
  • ca-cert.pem – The certificate authority (CA) certificate
  • server-key.pem – The server key
  • server-req.pem – The server certificate request
  • server-cert.pem – The server certificate
  • client-key.pem – The client key
  • client-req.pem – The client certificate request
  • client-cert.pem – The client certificate

So now we have the required certificates for both the server and the client we can set up both.

Setting up the MySQL server and client to allow SSL

All you need to do to set up the MySQL server to accept SSL requests is edit the MySQL my.cnf configuration file (usually in either /etc/my.cnf or /etc/mysql/my.cnf). Add the following lines in the [mysqld] section to point MySQL to the required files (in our case in /var/ssl_certificates/):

[mysqld]
ssl-ca=/var/ssl_certificates/ca-cert.pem
ssl-cert=/var/ssl_certificates/server-cert.pem
ssl-key=/var/ssl_certificates/server-key.pem

Now restart the MySQL service to check it works by typing:

/etc/rc.d/init.d/mysqld restart

If everything is working fine it should just start up like normal.

Now to set up the client (our web server) we need to copy the 3 required client files ca-cert.pem, client-cert.pem & client-key.pem to a directory on the client (e.g. /var/ssl_certificates/ again). Now you edit the my.cnf on the client (usually in either /etc/my.cnf or /etc/mysql/my.cnf) and add the following lines in the [client] section, which you may need to add yourself:

[client]
ssl-ca=/var/ssl_certificates/ca-cert.pem
ssl-cert=/var/ssl_certificates/client-cert.pem
ssl-key=/var/ssl_certificates/client-key.pem

So now the client and server are set up to use the SSL certificates you created for all SSL connections between the two. Now we set up a user for the database who is forced to use SSL.

Setting up a user to use SSL only

It’s easiest to set up the user on the command line using the “mysql” command on the database server. So log in as your root user (or equivalent) on the database server and create a test database “test_ssl”:

create database test_ssl;

Now create a test user “test_user” with password “testpass” with the correct client hostname “192.168.0.2″ and force them to use SSL when logging in:

grant all privileges on test_ssl.* to 'test_user'@'192.168.0.2' identified by 'testpass' require ssl;
flush privileges;

Now we can create a test table “test” and insert some data so we can query it to test everything is working from PHP:

create table test (test_id int auto_increment primary key, test_data varchar(255));
insert into test set test_data='foo';

Now we go back to ADOdb and PHP to test this all works.

Connect to the database over SSL using ADOdb

Simply create a .php file on your web server with the following code:

<?php
include('adodb5/adodb.inc.php'); // database libraries (must point to where your ADOdb libraries are kept)

$dsn = 'mysqli://test_user:testpass@192.168.0.1/test_ssl?clientflags=2048';
$dbh = NewADOConnection($dsn);

$arr = $dbh->GetAll("SELECT * from test");
print_r($arr);
?>

This should connect to our database using the test_ssl user and using SSL and print out the following on your web browser:

Array ( [0] => Array ( [0] => 1 [test_id] => 1 [1] => foo [test_data] => foo ) )

Now the great thing about this is that by using ADOdb we don’t need to worry about any kind of special connection string. The only thing that is different between an SSL connection and a standard connection is that SSL ues the “mysqli” command in the data source name rather than “mysql”, the rest of the code stays the same.

NOTE: It’s worth double checking that everything is working by executing "show status like 'ssl_cipher'" as a SQL query in the PHP above:

<?php
include('adodb5/adodb.inc.php'); // database libraries (must point to where your ADOdb libraries are kept)

$dsn = 'mysqli://test_user:testpass@192.168.0.1/test_ssl?clientflags=2048';
$dbh = NewADOConnection($dsn);

$arr = $dbh->GetAll("show status like 'ssl_cipher'");
print_r($arr->fields);
?>

which gives:

Array ( [0] => Ssl_cipher [Variable_name] => Ssl_cipher [1] => DHE-RSA-AES256-SHA [Value] => DHE-RSA-AES256-SHA )

Easily retrieve HTML content from websites using C# and HTML Agility Pack (HtmlAgilityPack)

I needed to retrieve a chunk of content (a product price) within a certain DIV (<div class=”price”>) on a website for a large number of search terms. The quickest way of doing this was to use the HtmlAgilityPack library for .NET to call the website’s search page (with custom search terms) and then pull out the content I needed using the SelectNodes() method. SelectNodes() uses XPATH to search across the document, which sounds tricky but is easy when you know some basic rules. XPATH is described well on w3schools if you need to know the syntax. You will need to download the Html Agility Pack .DLL and make a reference to it in your code.

The following is a simplified version of my code with a file reader/writer to read in a text file containing search terms line by line and output the result to another text file. The exact code will change depending on the website you are using, especially the XPATH in SelectNodes():

using System;
using System.IO;
using HtmlAgilityPack;

namespace webget
{  class Program
  {
    static void Main(string[] args)
    {
      string row = "", line = "";
      StreamReader infile = new StreamReader("input.csv");
      StreamWriter outfile = new StreamWriter("output.csv");

      // Create the Html Agility Pack object
      HtmlWeb hw = new HtmlWeb();

      while ((line = infile.ReadLine()) != null)
      {
        // load the website and store in a Htmldocument object
        HtmlDocument doc = hw.Load("http://www.website.com/search.php?keywords=" + line.Trim());

        try
        {
          // loop through every DIV with class "price" on the website and extract the content
          foreach (HtmlNode link in doc.DocumentNode.SelectNodes("//div[@class='price']"))
          {
            // clean up the content by removing any standard text items and the £ symbol
            string price = link.InnerText.Replace("£", "").Replace("r", "").Replace("t", "").Replace("n", "").Trim();
            line += price + ",";
          }
        }
        catch
        {
          line += ",ERROR";
        }

        Console.WriteLine(line);
        outfile.WriteLine(line);
      }

     infile.Close();
     outfile.Close();
   }
  }
}

Combine JQuery, JSON and Zend Framework MVC actions to update page items dynamically using AJAX

In order to populate and dynamically update a table on one of our Zend framework based pages without refreshing the page we had to use a combination of JQuery, JSON and standard Zend views/forms. The JQuery is part of the view and is inserted in <script> blocks while a standard Zend form is used to populate the page with dropdown menus, text boxes etc. JSON is used to handle passing data between the client side JQuery and the server side Zend action.

The following is a simple example, which passes JSON stored by JQuery in a hidden field to a Zend action on the server to update a database. Where possible the code has been simplified (so no long winded database connection etc  etc). As getting Zend framework up and running can be a bit difficult from scratch, this is not covered here. The Zend Framework Quickstart is useful although personally I think it could be clearer for first time users. Although I use modules in Zend to split different sections of my web application into self contained areas, for this example I will keep it as simple as possible and not use modules. To keep things even more simple I am not going to describe any validation or POST handling of the Zend action, we are only concerned with JQuery/Zend integration.

The example action we are going to call is the LIST action in the VISIT controller, which will show a list of visits and allow us to add a new one using a simple one textbox interface. JQuery will be used to pull all the required table data from the server and handle the visual client side aspect of adding a new visit.

The controller VisitController.php will have a very simple LIST action that looks like the following:

public function listAction()
{
  // set the view title to "Visit List"
  $this->view->title = "Visit List";
  // create a new Form_ListVisit() form object and add it to the view
  $newVisitForm = new Form_ListVisit();
  $this->view->form = $listVisitForm;
}

All this action does is set the view title, generate a Form object from a Zend form Form_ListVisit() and set the view form to this object (thereby placing the form on the page). The action will not do anything without a corresponding view script and Zend form, which both need to be created as separate files in the correct location in your file structure (more than likely /views/scripts/visit/list.phtml and /forms/ListVisit.php). Again, use of views and Zend forms is standard Zend Framework practice and is beyond the scope of this simple example.

For the view, currently without any JQuery, it will also be very simple:

<?php
  echo "<h1>".$this->title."</h1>";
  echo $this->form;
  echo "<div class='statusmessage'></div>";
?>

All this does is place the title and the form on the page that is displayed to the user, both the title and the form are set in the controller (see above). In a moment the JQuery will be placed in a <script> tag above this <?php ?> section, although it is a good idea to refactor your code to keep the view as simple as possible by having the JQuery code in a separate .js file. The “statusmessage” div will be used by JQuery to place success/error message on screen when we update the database.

The ListVisit.php Zend form file is more complicated and in the real world can take a while to write, including all the objects, decorators, subforms, validation options etc. In this case we keep it very simple and just add a single hidden field that we will use to store a single JSON string and a textbox to handle user input:

class Form_ListVisit extends ZendX_JQuery_Form
{
  public function init()
  { 
    // add the hidden field used to store our JSON string 
    $hiddenjson = $this->createElement('hidden','hiddenjson');
    $this->addElement($hiddenjson);
    // add the textbox used to handle user input
    $inputfield = $this->createElement('text', 'inputfield', array('Label' => 'Enter new data here'));
    $this->addElement($inputfield);
  }
}

So on loading the NEW action on the VISIT controller we end up with a blank page with only a title that says “Visit List” and a single textbox. The hidden field placed on the page by the Zend form is there but is empty, there is no table on screen and no other links. In this example we are going to add everything else we need, such as the table of visits, link to add new visit and link to save to the database, using JQuery. The communication between the client side and the server is handled using JQuery’s .post method and another Zend action which is set to take in JSON and only return a JSON string.

JQuery is used to generate a simple single column table and add two links to the page. The first link “Add New Visit” will add whatever is in the textbox to our very simple single column table. The second link “Save to Database” will update the database with the values of our table by calling the SAVEDATA action on the VISIT controller. The SAVEDATA action takes in a JSON string, does something to the database and returns a JSON string to the client. The possibilities that result from passing JSON between client and server using this JQuery method are amazing and make it trivial to create AJAX interfaces with JQuery and Zend.

IMPORTANT: I had to include the well known json2.js script by Douglas Crockford in order to get my JSON encoding and decoding working correctly in all browsers. This script saved a lot of headaches and is only 17k (and can probably be minified).

In the view, in the <script></script> at the top we first need to add in the JQuery that will be loaded on document ready (standard JQuery practice):

$(document).ready(function(){
  // create the two links to "Add New Visit" and "Save to Database" and place before statusmessage div
  $( "div.statusmessage" ).before("<a href='#' id='addvisitbutton'>Add New Visit</a>&nbsp;&nbsp;<a href='#' id='savedatabutton'>Save to Database</a>");
  // create the empty simple one column table to store our visits and place it after the statusmessage div
  $('div.statusmessage').after("<table id='visittable'><tbody><tr><th>Visit Name</th></tr></tbody></table>");
  // handle "Add New Visit" button click
  $( "#addvisitbutton" ).click(function() {
    addvisit();
  });
  // handle "Save to Database" button click
  $( "#savedatabutton" ).click(function() {
    savedata();
  });
}

Note that the click handling functions that have been added to the 2 links call either addvisit() or savedata(). These 2 functions are described above and outside the JQuery $(document).ready() as follows:

function addvisit(){
  // get a JSON object from the value of the hidden data field (will be empty initally)
  var jparsed = JSON.parse($("#hiddenjson").val());
  // create a new row to add to the JSON data based on the contents of the text field
  var row = [$("#coughscore").val()];
  // add the row to the end of the table
  jparsed.push(row);
  // set the hidden value to a JSON string representing the contents of the JSON object
  $("#hiddenjson").val(JSON.stringify(jparsed));
  // remove all rows from the visittable table of class "datarow", all new data rows will be of this class
  $("#visittable tbody tr.datarow").remove();
  // iterate through the JSON object  and append the data rows to the visittable table
  for(var i=0;i<jparsed.length;i++)
  {
    rowstring += "<tr class='datarow'>";
    var obj = jparsed[i];
    for(var j=0;j<obj.length;j++)
    {
      rowstring += "<td>" + obj[j] + "</td>";
    }
    rowstring += "</tr>";
  }
  $("#visittable tbody").append(rowstring);
}

When a row is added to the table the hidden field is updated to contain a JSON string describing the whole table. It is this JSON string that is passed to the SAVEDATA action of the VISIT controller by the savedata() function’s .post(), where it is converted to an object by some simple PHP code and then stored in the database:

function savedata(){
  // get the host name and correct controller url using a little Javascript/PHP and the built in Zend url() function
  var host = 'http://' + window.location.hostname + '<?php echo $this->url(array('controller'=>'visit'),"",true); ?>';
  // get the JSON string stored in the hidden field
  var jsondata = $("#hiddenjson").val();
  // use JQuery's .post() function to post to the Zend SAVEDATA action on the VISIT controller
  $.post(host+"/savedata",{
    // send the value of the jsondata variable (the JSON string) in the POST as "jsondata"
    jsondata: jsondata
  },
  // handle the returning JSON data from the SAVEDATA action
  function(data)
  {
    // convert the returning JSON data to an object using .eval()
    obj = window.eval(data);
    // set the value of the statusmessage div to the 'notes' field in the JSON returned by the SAVEDATA action
    // (this is either "Succcess" or "Failure" in this example)
    $('.statusmessage').html(obj['notes']);
  },
    "json");
  }
}

The SAVEDATA action is a standard Zend action on the VISIT controller with the addition of a couple of important lines of code to avoid rendering a view:

public function savedataAction()
{
  // disable the Zend layout
  $this->_helper->layout->disableLayout();
  // suppress auto-rendering and displaying a view
  $this->_helper->viewRenderer->setNoRender();
  // retrieve the jsondata string from the POST variables
  $jsondata = $_POST['jsondata'];
  // create an object to store the success or failure message strings and return them to JQuery.
  $data['notes'] = "";
  // decode the JSON string and turn into an object
  if($dec = Zend_Json::decode($jsondata))
  {
    // do important things with the converted JSON object, e..g write to database etc..
    //
    // set the return message to Success
    $data['notes'] = "Success";
  }
  else
  {
    // conversion from JSON string to PHP object failed, so return Error
    $data['notes'] = "Error";
  }
  // encode the returning object into a JSON string
  $json = Zend_Json::encode($data);
  // echo the JSON string back to JQuery, where it is handled according to the definition in the .post() function
  echo $json;
}

So now you can pass JSON objects between the client and the server using JQuery and json2.js. In this example JQuery is used to populate a table client-side and then pass the data for storage in the database on the server.

Alternatively you can use a Zend action and a JavaScript function to pull data from the server and display it as a table. This is really useful for displaying tabular data and is just a modification of the method above. The JavaScript function retrievedata() to pull data from the server using a new RETRIEVEDATA action and JQuery’s .post() function and add it to our visittable table is as follows:

function retrievedata(){
  // get the host name and correct controller url using a little Javascript/PHP and the built in Zend url() function
  var host = 'http://' + window.location.hostname + '<?php echo $this->url(array('controller'=>'visit'),"",true); ?>';
  // get the JSON string stored in the hidden field
  var jsondata = $("#hiddenjson").val();
  // use JQuery's .post() function to post to the Zend RETRIEVEDATA action on the VISIT controller
// note that we don't actually post any data this time, we only retrieve data
  $.post(host+"/savedata",{
  },
  // handle the returning JSON data from the RETRIEVEDATA action
  function(data)
  {
    // clean up and convert the returned JSON to a string
    // (this step required as sometimes Zend JSON is not always "correct")
    var json = JSON.stringify(data);
    // convert JSON string to object
    var jparsed = JSON.parse(json);
    // create a blank string ready to store a single html row of the table
    var rowstring = "";
    // iterate through the JSON object adding the html for a row of the table each time
    for(var i=0;i<jparsed.length;i++)
    {
      rowstring += "<tr class='datarow'>";
      var obj = jparsed[i];
      // iterate through each column of the row, adding the html for a table cell each time
      for(var j=0;j<obj.length;j++)
      {
        rowstring += "<td>" + obj[j] + "</td>";
      }
      rowstring += "</tr>";
    }
    // append the newly formed rowstring to the table
    $("#visittable tbody").append(rowstring);
    // set the hidden field to the JSON string
    $("#hiddenjson").val(json);

  },
    "json");
  }
}

The RETRIEVEDATA action (simplified to remove database queries) looks like:

public function retrievedataAction()
{
  $this->_helper->layout->disableLayout();
  $this->_helper->viewRenderer->setNoRender();
  // get array of objects from database using ORM
  // alternatively, use standard SQL connection etc etc
  // e.g. $arr = SomeORMMethod("visits");
  $output = array();
  foreach($arr as $key => $value)
  {
    $row = array();
    $row[0] = $value->visitName;
    $row[1] = $value->visitDate;
    $output[] = $row;
  }
  $json = Zend_Json::encode($output);
  echo $json;
}

You may have noticed that all of this can be done without Zend at all. If you read the JQuery .post() documentation it becomes a bit clearer, especially if you end up not using JSON at all. I know there are other methods out there to do this, but since I’m heavily involved with Zend at the moment I thought it would make sense to try and explain this example, as I used it in my own code. Also note, there is no security at all on this example; you will need to manage sessions, use encrypted data etc to make this secure.

The basic framework of JQuery/JSON/Zend means you can easily create rich MVC apps with AJAX and the flexibility of JQuery means that you don’t even have to use Zend at all! You can apply the same basically identical approach with Microsoft MVC, CakePHP or even with your own frameworks, all you need to do is modify the actions.