Feet Have Been

Last night I published my own river at http://river.xangelo.ca. I’m hoping that soon you’ll be able to subscribe to it via the RSS with eventual support for rss cloud. Maybe I’ll even throw in support for JSON. That way, you can run your own river if you want.

Building was actually a lot simpler than I initially thought. I ended up powering the entire system using limonade-php and SimplePie. For the front end I used Baseline.css, jQuery and a little JavaScript module-based system that I’ve been working on.

The system is simple, a GET run on /river will return the last.. 6 hours worth of river in JSON and a timestamp. Just keep GET‘ing that /river with a query string of last_checked = timestamp and you’ll get a JSON update.

So yes, have fun with river.. I’ll be mucking about with the update frequency over the next few days.

Before I begin I just want to note that this document is a work in progress. It requires version 5.3+ of php as it utilizies anonymous functions and it requires the use of limonade-php v0.5.1

Get caught up

If you haven’t yet, check out Part 1 of this tutorial to get setup. I’m going to assume you did.

Step 1: Setup

1 2 3 4 5 

<?php 

include('lib/limonade.php'); 

?>

Now, rename index.php to api.php

Step 2: Database!

Now we get to setup our database. Since each of us have different ways of doing this (I switch between command line and sqlbuddy) I’m providing the SQL code that will create our database. If you want you can re-create it using your favourite SQL manager.. or you can just copy and paste the SQL code and execute it. It’s nothing too complicated, just a single table called “todo” in a database named “lemondoo”. Each row in this table will have

1. a “todo_id” which is an auto incremented primary key (int)
2. a “todo_title” which is the title of this todo item (varchar(100))
3. a “todo_text” which is the text of the todo item (text)
4. a “completed” flag that is either 0 (not completed) or 1 (completed) (tinyint)

1 2 3 4 5 6 7 8 9 10 11 

CREATE DATABASE `lemondoo` DEFAULT CHARSET utf8;

USE `lemondoo`;

CREATE TABLE `todo` (
   `todo_id` int(11) not null auto_increment,
   `todo_title` varchar(100),
   `todo_text` text,
   `completed` tinyint(4) default '0',
   PRIMARY KEY (`todo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

At this point, I would go ahead and enter a couple sets of data into our new table. Make sure that you set the completed field to 0.

Step 3: Design our API

API design, as far as I am concerned, should be an entire topic of study in itself. Adding REST principles makes it a little easier, but still it is something that should be thought about carefully. Below I’ve outlined the REST header, the associated URL and the function that it will call. Notice that we can have two different headers assigned to the same url and each can map to their own function call. What we’re going to do is define this route for limonade-php so that it knows what to do depending on what URL we try to access. Note below that when I say :id it means that if you access /anything it will call the appropriate method and also assign “anything” to the variable “id”. So if you had /:yes it would assign “anything” to the variable “yes”

When working on the web it often helps to have some kind of database abstraction present. For my Limonade-php projects, I normally end up utilizing a single function that currently ties in to a mysql database. The connection happens before you call the method, but if you pass in a connection resource it will use that resource for the sql statement.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 

<?php
/**
 * A quick little function to interact with a MySQL database.
 *
 * When working with Limonade-php a full-fledged MySQL wrapper seems like
 * overkill. This method instead accepts any mysql statement and if it works
 * returns either the result or the number of rows affected. If neither worked,
 * then it returns false
 *
 * @param   string      $sql    the sql statement you want to execute
 * @param   resource    $c      mysql connect link identifier, if multi-connect
 *                              otheriwse, you can leave it blank
 * @return  MIXED       array   the result set if the sql statement was a SELECT
 *                      integer if the sql statement was INSERT|UPDATE|DELETE
 *                      bool    if anything went wrong with executing your statement
 *
 *
 * [update|insert|delete]
 * if(db('update mytable set myrow = 4 where someotherrow = 3') !== false) {
 *  // worked!
 * }
 *
 * [select]
 * $res = db('select * from mytable');
 */
function db($sql,$c = null) {
    $res = false;
    $q = ($c === null)?@mysql_query($sql):@mysql_query($sql,$c);
    if($q) {
        if(strpos(strtolower($sql),'select') === 0) {
            $res = array();
            while($r = mysql_fetch_assoc($q)) {
                $res[] = $r;
            }
        }
        else {
            $res = ($c === null)?mysql_affected_rows():mysql_affected_rows($c);
        }
    }
    return $res;
}
?>

Line by line explanation:

1-25: Documentation

26: Function declaration, accepts an sql statement and an optional connection resource.

27: We preset $res to false This is so that we can get rid of a bunch of if-statements 28: If there is no connection resource, just execute the sql statement. If there is, use it. (@ surpresses errors)

29: Check to see if our query worked, if it didn’t, we just return res which we preset.

30: Checks to see if we tried to execute a ‘SELECT’ statement.

31: Change $res into an array. Our results will be a nested array since our statement worked!

32-34: Loop through our results and assign them to $res.

36-38: If the sql statement was NOT a ‘SELECT’, return the number of affected rows. If a user passed in a connection resource, use that.

40: return $res, which could either be false, array() or int depending on if the query failed, was a select statement, affected the rows.