Feet Have Been

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.

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

Getting started with Limonade-php

Your first step is to simply download Limonade-php from GitHub. Make sure you grab the latest version (v0.5.1 at the time of writing this) and extract it. You’ll want to go into the lib directory and copy limonade.php and the limonade directory. The directory contains some utilities for limonade-php, but is not necessary. Infact, once your application is complete properly, I’d suggest you delete this folder and leave just the limonade.php file behind.

The standard first project

When it comes to programming, your first project is always Hello, World. So let’s not break that tradition. Create a new directory called “hello_world” and in it create a directory called “lib”. You can paste in the file and directory you copied earlier. Now let’s create a file called “index.php” and open it up in an editor of your choice.

Step 1: Setup

1 2 3 4 5 

<?php 

include('lib/limonade.php'); 

?>

Simple enough, but if you visit that page in your browser, you’ll see that it doesn’t really do anything. So we’re going to add a bit more code.

Step 2:

First we’re going to say that whenever a user visits that page using a “GET” header, we’re going to display the text “Hello, World!”. In a nutshell, GET is one of the four recognized header types for REST implementations. GET is for retrieving information about a resource. For more information about REST, check out this post and combine it with this

1 2 3 4 5 6 7 8 9 10 11 

<?php 

include('lib/limonade.php'); 

dispatch_get('/', function() {
    return "Hello, World!";
});

run();

?>

Did you just pass that function a function as an argument?

Why yes. Yes I did. It’s a new feature of PHP 5.3 that allows for anonymous functions. You’ll see the same thing happen in JavaScript all the time, so if you work with that, you should find it familiar. Essentially an anonymous function is just a function without a name. There is no way to call it without assigning it to a variable. So, by passing it to dispatch_get as an argument, it is assigned to a variable within limonade-php.

Finally, on line 9 we add the run(); command. This tells limonade-php to start.

Now if you visit index.php in your browser, you’ll see the text “Hello, World!”

Step 3: Profit

Now that you’ve gotten a taste of how easy it is to build something using limonade-php let me just say, that it really is that easy to build just about anything. Because of limonade-php’s adherence to RESTful principles it becomes ridiculously easy to build an API or a dynamic website, or just about anything in between. It even integrates very well with other libraries and components. It allows for rapid prototyping (I created this ([source])(https://github.com/AngeloR/Lemontrac) in only a few hours – including the time it took to learn limonade-php) and it has become an important part of my php toolbox.

Now what?

Well, it’s all well and good to display “Hello, World!” but it’s kind of useless to everyone who wants to build something useful. It hardly scratches the surface of what is possible with limonade-php. So, to properly show you what you can do with it, let’s build a very simple todo application. But with a twist. First we’re going to build a todo application API. Then we’re going to build a website that consumes this API using JavaScript and jQuery.

Stay tuned for part 2!