Developing a REST-powered AJAX table control with the Slim Microframework and AngularJS (1)

Developing an REST-powered AJAX table control with the Slim Microframework and AngularJS

Welcome to a hands-on tutorial on writing reusable user interface components with AngularJS. In this tutorial, we’ll write a table control that asynchronously loads data from a server, display it, and allows the user to sort the data by clicking table headers. The sorting is done client-side. We’ll show how to write a REST-server in PHP, how to define custom HTML elements in AngularJS, and how to setup a controller for the elements. To top it off, we’ll write CSS for our table in such a way that it can be dropped into a user interface in a snap, and scales fluidly with the space allotted to it.

The first part of this tutorial introduces AngularJS, and takes you through the use of the Slim PHP Microframework and the Idiorm and Paris ORM libraries in order to write a RESTful web service, which we’ll need for our table control to talk to.

What is AngularJS?

AngularJS is a nifty JavaScript library offering you well-tested code that allows you to:

  • Extend the HTML vocabulary for your application. That is, add new HTML elements;
  • Bind data to your user interface, automatically wiring up update events as necessary;
  • Validate forms;
  • Communicate with a server through AJAX;
  • Perform localization.

What we’ll create

We’ll use AngularJS to create a new HTML tag, <mytable>. This tag will allow us to declaratively insert an AJAX powered table. In other words, you simply add a <mytable> element to your HTML to get a dynamic table, without needing to write any JavaScript to write up events, send AJAX requests, etc. All that is taken care of behind the scenes. This can make for very rapid web application development (which is precisely the point of AngularJS).

What we’ll get is something like this:

AngularJS AJAX table example

AngularJS AJAX table

Our table will fill the screen space available to it, both horizontally and vertically. This means you can create a <div> container somewhere on your page with a set width and height (or even a flexible with and height) and put the table in there – it will scale automatically. Incidentally, this has nothing to do with AngularJS but rather with writing crafty CSS, which is all part of the game.

It will be possible for the user to click the table column headers and sort the data in the table automatically.

Database Access

While we’re exploring AngularJS, let’s take the opportunity to look at how our table will access data from a database. We’ll need to offer and endpoint for our table’s AJAX requests to connect to. Let’s do this nicely, and build a REST interface (more on this below). We’ll also use the Idiorm and Paris PHP libraries to write simple, readable and robust database access code.

Setting things up

Let’s get some requirements out of the way first. We’ll need a MySQL database for our table control to access, and some data in it. For this tutorial, we’ll use a database called wine, containing a single table called wine with the following schema:

CREATE TABLE `wine` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;

You can create this database with MySQL Workbench, or directly in the MySQL console. Fill it with some data so that we’ll have something to test with.

'1', 'Brampton Sauvignon Blanc'
'2', 'Neil Ellis Groenekloof Sauvignon Blanc'
'3', 'Bouchard Finlayson Chardonnay "Kaaimansgat"'
'4', 'Fairview "Oom Pagel" Sémillon'
'6', 'Flagstone Noon Gun'
'7', 'Imbizo Cape White'
'8', 'Ken Forrester Scholtzenhof Chenin Blanc'
'9', 'Kanonkop Estate "Paul Sauer"'
'10', 'Le Riche Cabernet Sauvignon'
'11', 'Vergelegen'
'12', 'Veenwouden Merlot'
'13', 'De Trafford Pinot Noir'
'14', 'De Wetshof Pinot Noir'
'15', 'Avontuur Estate Pinot Noir'
'16', 'Bouchard Finlayson "Galpin's Peak" Pinot Noir'

Other AngularJS tutorials seem to use wine lists as well, so why not follow suit.

About REST

When you are building a web application, bets are ten to one that you’ll need to to access a local or remote database at some point. Your web app will be bursting with JavaScript, and while JavaScript is good for many things, it can’t access databases – for security reasons. The JavaScript in your app can be altered by savvy visitors, and they could end up accessing much more of your database than you want them to.

In order to talk to a database, you’ll need to have a back-end written in some flavor of server-side language. PHP’s a good candidate; so is Ruby. The back-end code runs on the (database) server, and cannot be seen or altered by your web app’s users.

Your JavaScript now needs to communicate with the server-side code that controls the database in order to read or write bits of data. The part of your back-end that forms an endpoint that your JavaScript code can talk to is a web service, which can be as closed (secured, private access only) to as open (public, everyone can access this service) as you’d like. JavaScript accesses your web service through an URL, like for example:

http://www.myhost.com/webservice/get-wine.php?id=6

This would cause the web service to read the data for wine #6 from the database, and send it back to the browser. The data can be sent in any format you’d like, but I’d recommend you use JSON, because JSON is a snap to parse at the JavaScript receiving end. More so than XML which requires you to jump through some fiery hoops to parse.

So along these lines, you’d create other URLs like:

http://www.myhost.com/webservice/get-wines.php
http://www.myhost.com/webservice/get-wine.php?id=[id]
http://www.myhost.com/webservice/create-wine.php?name=[name]
http://www.myhost.com/webservice/update-wine.php?id=[id]&name=[name]
http://www.myhost.com/webservice/delete-wine.php?id=[id]

In other words, a CRUD interface: CReate, Update and Delete (and various getters). All of these URLs are accessed through GET or POST requests (this becomes interesting in the next paragraphs).

Moving to REST

So on to REST. REST stands for Representational State Transfer, and apparently there are many ways to explain what it is. Actually, it’s an academic concept and I’ve no desire to join any friendly flame war, so rather than mince words, we’ll just do.

Let’s put it like this: REST uses HTTP verbs to indicate actions to be performed against a database.

  • When you send an HTTP GET request, a REST web service (a “restful” web service) will get some data from a database and return it.
  • When you send an HTTP POST request, a REST web service will create a new record in a database.
  • When you send an HTTP PUT request, a REST web service will update a record in a database.
  • When you send an HTTP DELETE request, a REST web service will delete a record in a database.

You’re likely familiar with GET and POST requests; your browser can send PUT and DELETE requests as well although they’re much less used on the web.

So there it is – REST puts the semantics of HTTP requests to good use. Rather than have many URLs for your web service (like in the example above), you’ll have just one: wine. The HTTP verb used to access it determines what the web service will do to the database.

While we’re at it, it’s also customary to introduce URL rewriting at this point. Rather than accessing

http://www.myhost.com/webservice/wine.php?id=6

using a GET request, it’s much prettier to be able to write:

http://www.myhost.com/webservice/wine/6

URL rewriting (offered by both the IIS and Apache web servers) make this possible, without requiring changes to your code.

Writing a RESTful webservice

Let’s put the theory in practice. Before we can turn our attention to AngularJS, we’ll need a web service set up for database access, so let’s stick to REST for a while longer.

Writing the PHP code to access a MySQL (or any other) database is straightforward, but error prone without the use of various libraries that make your life easier. You could, technically, do this:

// Connect to database server
$hd = mysql_connect("localhost", "username", "password") or die ("Unable to connect");
// Select database
mysql_select_db ("wine", $hd) or die ("Unable to select database");
// Execute sample query
$res = mysql_query("SELECT * FROM wine", $hd) or die ("Unable to run query");
// Loop through rows
while ($row = mysql_fetch_assoc($res))
{
  $id = $row["id"];
  $name = stripslashes($row["name"]);
  // Do something with the data
}
mysql_close($hd);

I’m going to beg you not to do this. There are less painful ways. Here’s some reasons to convince you.

  • The mysql_xxx functions are deprecated.
  • You’re better off using PDO functions, as they are safer and allow query parameterization.
  • You don’t actually have to write the queries yourself, since you can use object relational mapping.

The last point is the most important point. You don’t have the manually craft SQL queries anymore (unless you need some very particularly crafted queries). Object Relational Mapping (ORM) can work for you. C# and Java developers have been using it for years (the Hibernate library being a case in point), and there are mapping libraries for PHP, too.

Idiorm and Paris

Get the Idiorm and Paris libraries here. Take a moment to study that page; it illustrates the power of ORM in PHP nicely. Note that you just need the idiorm.php and paris.php files; the rest is fluff. Now let’s look at this code:

require_once 'idiorm.php';
require_once 'paris.php';
// Create model for Wine table
class Wine extends Model {}
// Configure database access
ORM::configure("mysql:host=localhost;dbname=wine");
ORM::configure("username", "username");
ORM::configure("password", "password");
// Get all wines
$wines = Model::factory("wine")->find_many();
// Do something with the data.

No queries. No stripslashes. No mess. We’re not actually using parameters here, but trust me, you don’t have to do anything arcane to use them.

Just to whet your appetite a bit more, here’s some more examples.

Getting wine #6 from the database (told you it was easy to insert a parameter!):

$wine = Model::factory("wine")->find_one(6);

Creating a new wine:

$wine = Model::factory("wine")->create();
$wine->name = "Fleur du Cap Merlot";
$wine->save();

If that doesn’t make you happy, I don’t know what will. So cast off your old ways and start using ORM. There’s more PHP ORM libraries out there, and you can always roll your own.

Using the Slim PHP microframework

With the ORM bit out of the way, it’s time to set about implementing the actual REST web service. We’ve put together enough tools do to this now, but we can still make life a little bit easier by using a framework to do the grunt work for us. Rather than writing PHP code that determines whether a request was a GET, POST, PUT or DELETE request, and fishing the request arguments from the HTTP header, we can use the Slim PHP microsframework to do this for us.

Get the Slim framework here.

Slim is a very small framework that allows you to specify routes for HTTP request, along the lines of “If this is a PUT request for a wine with ID x, then do this. If this is a GET request for a wine with ID y, then do this.”

Setting up Slim is easy:

// Register Slim autoloader:
require "Slim/Slim.php";
\Slim\Slim::registerAutoloader();
 
// Instantiate Slim application:
$app = new \Slim\Slim();
 
// (Define the routes here.)
 
// Run the Slim application:
$app->run();

Let’s define a route for retrieving a wine by ID.

$app->get("/wine/:id", function($id) use ($app)
{
  $app->response()->header("Content-Type", "application/json");
  $wine = Model::factory("wine")->find_one($id);
  echo json_encode($wine);
});

This route is for URLs of the form

http://www.myhost.com/webservice/wine/6

We use Slim to send out an HTTP header indicating that JSON content is forthcoming. Then we use Paris to grab wine #6 from the database, and send it back to the browser in JSON format – all that in surprisingly little code.

However, we’ll need to do a little more to make this robust.

$app->get("/wine/:id", function($id) use ($app)
{
  $app->response()->header("Content-Type", "application/json");
  $wine = Model::factory("wine")->find_one($id);
  if($wine != NULL)
  {
    $arr = $wine->as_array();
    $arr["name"] = utf8_encode($arr["name"]);
    echo json_encode($arr);
  }
  else
  {
    echo json_encode(array(
      "status"  => false,
      "message" => "Wine ID {$id}  does not exist."
    ));
  }
});

This code adds two important things:

  • Wine names can contain diacritics. In order for them not to be mangled when they get sent to the browser, we encode them as UTF8.
  • If wine #6 cannot be found, our REST web service must return an error message, encoded in JSON.

Let’s write another route. This one is for /wines, which retrieves all wines:

$app->get("/wine", function() use($app)
{
  $app->response()->header("Content-Type", "application/json");
  $wines = Model::factory("wine")
    ->order_by_asc("name")
    ->find_many();
  $wines = array_map(function($a) {
    $arr = $a->as_array();
    $arr["name"] = utf8_encode($arr["name"]);
    return $arr; }, $wines);
  echo json_encode($wines);
});

Here’s a route for POSTing a new wine:

$app->post("/wine", function() use($app)
{
  $app->response()->header("Content-Type", "application/json");
  $data = $app->request()->post();
  $wine = Model::factory("wine")->create();
  $wine->name = $data["name"];
  $wine->save();
  echo json_encode(array("id" => $wine->id));
});

You can now write additional routes for PUT and DELETE – I’ll leave that as an exercise.

Summary

Great – it’s all come together. Using the Slim Microframework, along with the Idiorm and Paris ORM libraries, we’ve written a RESTful web service, which allows the following requests:

  • GET http://www.myhost.com/webservice/wine (No ID – get all wines)
  • GET http://www.myhost.com/webservice/wine (with an ID)
  • POST http://www.myhost.com/webservice/wine (with name in POST data)
  • PUT http://www.myhost.com/webservice/wine (with ID and name in HTTP data)
  • DELETE http://www.myhost.com/webservice/wine (with ID in HTTP data)

Having done all this, we can now focus on the front-end of our web application, and actually look at AngularJS. Please keep reading in part 2 of this tutorial!

 


2 Comments


Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">