• If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Todos 06-Building with PHP

Page history last edited by jjquimora 13 years, 8 months ago

 

Before You Start

 

This tutorial will walk you through writing your own, very basic REST server in PHP.  If you don't want to do that and just need a good PHP REST script, I recommend you check out http://phprestsql.sourceforge.net/download.html

About PHP

PHP is a widely used, general scripting language that is especially well suited for server-side web development.  PHP was created way back in the mid-to-late 1990's as an open source alternative to the proprietary server-side scripting languages that existed at the time.  It was the first widely used open source, server-side scripting language and is still popular today.  Most web hosting companies provide PHP support.

 

PHP right out of the box cannot be used to create RESTful web services that look and feel like services built with RoR (Ruby on Rails: http://rubyonrails.org/) or Merb.  This is because PHP relies on client browsers making requests to specific PHP script files (e.g. http://mydomain.com/myphpscript.php) to trigger their execution.  In contrast, RoR uses the URL to specify object type and id (e.g. /my_object/1234) that is being requested, be it a web page or a data object.  So the first challenge we face when using PHP is translating a RoR-like URL (e.g. /tasks/1) into a PHP friendly URL (e.g. /tasks.php?guid=1).  Thankfully, this is easily accomplished with Apache's mod_rewrite module (http://httpd.apache.org/docs/1.3/mod/mod_rewrite.html).

Before You Get Started

Before this will work, you must have Apache, Apache's mod_rewrite module, and PHP installed and running on your server.  To get all of those installed and running, please read any of the many existing tutorials out there on the web; RTFGoogle.

 

You should probably be running PHP 5.x.  But it isn't required.  Our REST script is so simple that I think it could be written in very old PHP versions and still work.

Configuring Apache's mod_rewite

Once you are sure that your Apache server is running and you have the mod_rewrite module set up, the first step is to go to your web root folder and create an .htaccess file:

 $ cd /your/web/root
 $ touch .htaccess

Now that you have an .htaccess file, edit it with your favorite editor (eh...vim, isn't it everybody's favorite?) and make it contain the following:

 

RewriteEngine On
RewriteBase /

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ /index.php?url=\$1

 

What this .htaccess file does is tell Apache to turn on the mod_rewrite module for requests into this directory and rewrite the URL's relative to the server root.  The lines that begin with "RewriteCond" tell mod_rewrite to not rewrite the URL if the URL refers to an existing file or directory on your server.  This is important if you are storing data files (e.g. images, mp3s, SproutCore app files, etc) on the same web server that you are making REST calls to.

 

The last line in the .htaccess file is where the fun happens.  The RewriteRule declaration takes a regular expression to match against the URL sent by the client and if the regular expression matches, rewrite it to the pattern given after the regular expression.  In this case we are matching all URL's and rewriting the URL to be a request to /index.php and with a GET parameter called "url" that contains the URL that was sent from the client.

 

Here's how it works:

 

 

So now, we can just write a single PHP file called index.php, put it in your web root and it will get called for every request sent to your sever by your sproutcore application.  Inside the PHP script, we can access the GET variable called "url" to get the original URL the client used when making the request and thus parse it to figure out which object the SproutCore app wants manipulate.

The Magical index.php File

The index.php file has a few things that it must do to correctly handle the REST calls coming from Sproutcore:

  • Check the HTTP method (e.g. GET, PUT, POST, DELETE) of the REST call and act accordingly.
  • Parse the "url" get parameter passed to the script via mod_rewrite to know how to handle the HTTP method.
  • Construct and execute an appropriate query to service the REST call.
  • Construct and send the correct response for the REST call.

 

Let's take each of these in turn and build a simple index.php that can handle the REST calls for the Todo sproutcore application.  I should note that what we are building is very simple, minimal PHP for handling sproutcore requests.  There are many ways to improve on our design that would make it more flexible and easier to maintain. 

Checking the HTTP Method

When a PHP script is executed in response to an HTTP request from a client, the PHP interpreter sets up some global variables for you that contain information about the request.  The first piece of information that we are concerned with is the HTTP method of the request (e.g. GET, PUT, POST, or DELETE).

 

The PHP interpreter puts the method in a global map called $_SERVER under the key 'REQUEST_METHOD'.  So the first bit of our index.php script will get the request method and switch behavior based on what it is.

 

// get the request method
$method = $_SERVER['REQUEST_METHOD'];

// handle the method
switch($method) {
    case 'GET':
        // handle GET
        break;
    case 'POST':
        // handle POST
        break;
    case 'PUT':
        // handle PUT
        break;
    case 'DELETE':
        // handle DELETE
        break;
}

 

As you can see this is just some stub code for handling the different methods.  We'll come back to this code later on, but first we have to look at how to part URLs and to get the payload data if there is any.

Parsing the URL

The RESTful URL pattern used by SproutCore to access records on the backend follows the standards set by Ruby on Rails. Here are the basic CRUD (Create, Read, Update, Delete) operations and their URLs:

 

Create

POST /tasks

 

Read All

GET /tasks

 

Read a Specific Record

GET /tasks/<id>

 

Update a Specific Record

PUT /tasks/<id>

 

Delete a Specific Record

DELETE /tasks/<id>

 

It is important to note that the <id> is an identifier for a specific record.  Usually, this identifier is the unique database index for the record but it could just as easily be a GUID or a unique string.  It is easiest to use an numerical index in your database table and let your database automatically generate the identifier for each new record created.

 

Another important detail is which requests send data to the server, which requests receive data from the server and which requests do both. 

 

When a record is created with a POST request, all of the data for the record must be sent to the server so that it can store it.  In the simplest case, the server stores the data in  a new database record, the database assigns it an identifier, and the the server reports the identifier back to the client.

 

When the client retrieves all records from the server, the request does not send any data to the server. The GET request to the /tasks URL is enough information for the server to know what the client wants.  When the client wants to get the data for a specific record, again, the GET request and the URL containing the identifier for the record is all the server needs to retrieve the data being requested.  In both cases the server returns JSON encoded representations of the requested data.

 

When the client updates a record, not only must it send a PUT to the URL containing the identifier of the record to be updated, but it must also send the new data to be stored in the record.  The server does not send any data back since the client already has the updated information.

 

When the client deletes a record, there is no data sent from the client or the server.  The DELETE request and the URL containing the identifier is all that is needed.

 

So now that we know what each of these URLs and methods mean, how do we parse them so that our script has all of the data it needs to carry out the request?  Simple, remember the .htaccess file we created earlier on?  It translated the URL sent from the client into a request for our index.php file with a parameter called "url" that contained the original URL.  So that is where we start.  Open up your index.php file and add the following above the code for parsing the method that we wrote earlier.

 

// get the original url in a nice format with a leading /
$url = (key_exists('url', $_GET) ? $_GET['url'] : '');

// now that we have the url, break up the pieces
$parts = explode('/', $url);

// get the record type
$record_type = $parts[0];

// get the record ID if it exists
$record_id = (isset($parts[1]) ? $parts[1] : FALSE);

 

Now, if the request URL was in the form /tasks/<id>, $record_type contains "tasks" and $record_id contains the identifier for the record.  If the URL was just /tasks, then $record_id is set to FALSE. Indeed, you can sell antiques for sale in online mall.

Getting the Request Payload (If There is Any)

When the sproutcore client makes POST and PUT requests the server, it sends JSON encoded data that is to be used when creating a new record or updating an existing record respectively. The PHP script must translate the JSON encoded data into a PHP object that can be send to the database to store.  Since this only happens on two of the four possible HTTP methods, we are going to create a function to handle the payload translation.  So again, open up your index.php file and somewhere above the URL parsing code, add the following.

 

// define the function for translating the payload
function getPayload() {

    // define the holder for our data
    $payload = FALSE;

    // make sure there is payload data
    if(isset($_SERVER['CONTENT_LENGTH']) && 
       $_SERVER['CONTENT_LENGTH'] > 0) {
        $payload = '';
        $httpContent = fopen('php://input', 'r');
        while($data = fread($httpContent, 1024)) {
            $payload .= $data;
        }
        fclose($httpContent);
    }

    // check to make sure there was payload and we read it in
    if(!$payload)
        return FALSE;

    // translate the JSON into an associative array
    $obj = json_decode($payload);
    return $obj;
}

 

What this function does is grab the HTTP request payload and translate the JSON into a PHP variable. The json_decode function translates the JSON into an object of type stdObject. That object then contains all of the data from the JSON.

 

In the todo list tutorial, all of the data payload is sent in a map with a single member called "content" that contains either a single record or an array of records. The JSON for a single record looks like this (note that the guid member and order member may not always be there, depending on the circumstances of the request):

 

{
    "content": {
        "guid": "/tasks/123",
        "description": "task description of some sort",
        "order": 1,
        "isDone": false
    }
}

 

So the call to json_decode will translate the JSON  into an object with a single member called "content" which will contain, in this case, an object with members called "guid", "title", "order", and "isDone".

 

So now we have all of the parts for pulling the data out of requests, parsing the url, and getting the method.  Now we need to add the code for executing the requests.

A Handy Error Reporting Function

To simplify our code I use a function for handling the setting of error response headers:

 

function setError($code, $debug='') {
    switch($code) {
        case 400:
            header('HTTP/1.1 400 Bad Request');
            break;
        case 500:
            header('HTTP/1.1 500 Internal Server Error');
            break;
    }

    if($debug != '') {
        header('X-Reason: ' . $debug);
    }
}

 

This function just sets the correct HTTP error code header and an optional header for relaying a textual error message back from the server.

 

Utility Function for Preventing SQL Injection

Whenever you are taking data from a URL or the payload of an HTTP request and jamming it into a SQL query to run, you have to be mindful of SQL injection attacks.  Fortunately with PHP, it is rather easy to avoid.  PHP provides us with two functions for cleaning up incoming data: stripslashes() and mysql_real_escape_string().  According to the documentation, we have to be careful how we use them however.  Below is the code for a utility function I use to condition inputs before putting the data into any SQL queries:

 

function cleanValue($value) {
    if(get_magic_quotes_gpc()) {
        $value = stripslashes($value);
    }

    if(!is_numeric($value)) {
        $value = mysql_real_escape_string($value);
    }
    return $value;
}

 

This function checks to see if the magic_quotes_gpc setting is enabled.  If it is, we have to use stripslashes() to strip out the escaping slashes added by that module.  Then if the value isn't numeric, we use mysql_real_escape_string() to clean it up.

Handling Create

When the client wants to create a new record, it will send a POST request to the /tasks url with a JSON payload of a single record.  Open up your index.php file and up at the top add the following code:

 

// handle a POST request
function handlePOST($type, $payload) {

    // check the payload to make sure that it only has a single object
    if(is_array($payload->content)) {
        // we have an array so set up an error response and exit
        setError(400, 'Received an array of records when expecting just one');
        return;
    }

    // get the record
    $record = $payload->content;

    // validate the record to make sure we have all the data
    if(!isset($record->description) || !isset($record->isDone)) {
        // we got bad data so set up an error response and exit
        setError(400, 'Malformed payload object');
        return;
    }

    // open a connection to the database
    $mysql = mysql_connect();
    if(!$mysql) {
        // we couldn't connect to the db, set up a 500 error
        // response and exit
        setError(500, 'Could not connect to database');
        return;
    }

    // select the "mydb" database to use
    mysql_select_db('mydb', $mysql);

    // clean up the inputs to prevent SQL injection
    $table = cleanValue($type);
    $description = cleanValue($record->description);
    $isDone = $record->isDone ? 1 : 0; // A hack to safely convert a Bool to a Int for MySQL

    // build the query to create the new record
    $sql = "INSERT INTO `$table` " .
           "(`description`,`isDone`) VALUES " .
           « (‘$description’, ‘$isDone')";

    // run the query
    mysql_query($sql, $mysql);

    // get the identifier assigned to the new record
    $id = mysql_insert_id($mysql);

    // build the success response
    header('HTTP/1.1 204 No Content');

    // set the location of the new record
    header("Location: http://$_SERVER['HTTP_HOST']/$type/$id");
    
    // close the mysql connection
    mysql_close($mysql);
}

 

There is a lot going on in this function, so let's take it line by line. The first few lines check to see if the payload data has a member called "content". Since we decided that we were going to send all records in a map called "content", this must be there or we error out. If it doesn't exist, we send a generic 400 error with a custom header called "X-Reason" that contains the reason for the error. Adding your own headers to the response like that is tremendously useful when you are trying to debug when something doesn't work.

 

The next line after the first if block gets a reference to the content.  In the case of a POST request, the content must not be an array since we only handle creating one record at a time. So after we get the reference we check to see if it is an array. If it is, we again error out with a 400.

 

We then try to connect to our database.  In this tutorial, I'm assuming that you are using MySQL and that you have set up your php.ini file to contain your MySQL server, username, and password.  (I know this is the old, nasty, procedural way of accessing MySQL and that there are much nicer and faster ways to do this, but I'm trying to keep this simple without any externel dependencies like ADO or getting into OO PHP.)  If the we can't connect to MySQL, we error out with a 500.

 

After we have a connection to the database, we select our database, in this case I'm assuming that you have a database on your server called "mydb". Change this to meet your local setup. The next step is to build the insert query with the data from the client. We execute the query, get the identifier that was assigned to the new record in the database and then we build the 204 response with the URL pointing to the new record.

 

And that's it for handling POST requests!  This looks really complicated but trust me, this is a piece of cake.

Handling Read One/All

Handling the GET request is the same for both getting all records and getting one record. The only difference is that a GET for all doesn't have a record identifier and the resulting "content" map in the JSON object is an array rather than a single object. Here's the code for handling both cases:

 

// handle a GET request
function handleGET($type, $id) {

    // connect to the database
    $mysql = mysql_connect();
    
    if(!$mysql) {
        // we couldn't connect to the db, set up a 500 error 
        // response and exit
        header('HTTP/1.1 500 Internal Server Error');
        header('X-Reason: Could not connect to database');
        return;
    }

    // select the database to use
    mysql_select_db("mydb", $mysql);

    // build the query
    $sql = "SELECT * FROM `$type`";

    // if this is a query for a single record, add the where clause
    if($id)
        $sql .= " WHERE guid=‘$id'";

    // run the query
    $res=mysql_query($sql, $mysql);

    $output = FALSE;
    if($id) {
        $obj = mysql_fetch_object($res)
        $obj->isDone = $obj->isDone == 1;
        $output->content = $obj;
    } else {
        $output->content = array();
        while($obj = mysql_fetch_object($res)) {
            $obj->isDone = $obj->isDone == 1;
            array_push($output->content, $obj);
        }
    }
    
    // output the response header
    header("HTTP/1.1 200 OK");

    // output the JSON encoded output
    echo stripslashes(json_encode($output));

    // close the database connection
    mysql_close($mysql);
}
 

 

 

Handling Update

Handling a PUT request to update a specific record is almost idential to handling a POST to create one.  The only significant change is the SQL query changes from INSERT to UPDATE and the response changes from 204 to 200.  Here's the code:

 

// handle a PUT request
function handlePUT($type, $id, $payload) {
    // check to make sure we have everything
    if(!$type || !$id || !$payload) {
        // we don't have everythin we need
        header('HTTP/1.1 400 Bad Request');
        header('X-Reason: Missing type, id, or payload');
        return;
    }

    // check the payload to make sure that it only has a single object
    if(is_array($payload->content)) {
        // we have an array so set up an error response and exit
        header('HTTP/1.1 400 Bad Request');
        header('X-Reason: Malformed Payload');
        return;
    }

    // get the record
    $record = $payload->content;

    // validate the record to make sure we have all the data
    if(!isset($record->description) || !isset($record->isDone)) {
        // we got bad data so set up an error response and exit
        header('HTTP/1.1 400 Bad Request');
        header('X-Reason: Received an array of records when ' .
               'expecting just one');
        return;
    }

    // open a connection to the database
    $mysql = mysql_connect();
    if(!$mysql) {
        // we couldn't connect to the db, set up a 500 error 
        // response and exit
        header('HTTP/1.1 500 Internal Server Error');
        header('X-Reason: Could not connect to database');
        return;
    }

    // select the database to use
    mysql_select_db("mydb", $mysql);

    // clean up the inputs to prevent SQL injection
    $table = cleanValue($type);
    $description = cleanValue($record->description);
    $isDone = $record->isDone ? 1 : 0; // A hack to safely convert a Bool to a Int for MySQL

    // build the query to create the new record
    $sql = "UPDATE `$table` SET " .
           " description='$description', isDone='$isDone' WHERE " .
           "  guid=‘$id'";
    // run the query
    mysql_query($sql, $mysql);
    // build the success response
    header('HTTP/1.1 200 OK');
            
    // close the database connection
    mysql_close($mysql);
}

 

So this handles updating a record in the database with new values.  The id specifies which record to update.  One thing to note, this does not do any error checking.  It will not catch the case where the record associated with the given id no longer exists in the database.  I leave that up to the reader to figure out...RTFGoogle.

Handling Delete

Hanlding delete requests is pretty straight forward too.  It is a special case of update.  Here's the code:

 

// handle a DELETE request
function handleDELETE($type, $id) {
    // check to make sure we have everything
    if(!$type || !$id) {
        // we don't have everythin we need
        header('HTTP/1.1 400 Bad Request');
        header('X-Reason: Missing type or id');
        return;
    }
 
    // open a connection to the database
    $mysql = mysql_connect();
    if(!$mysql) {
        // we couldn't connect to the db, set up a 500 error 
        // response and exit
        header('HTTP/1.1 500 Internal Server Error');
        header('X-Reason: Could not connect to database');
        return;
    }

    // select the database to use
    mysql_select_db("mydb", $mysql);

    // build the query to create the new record
    $sql = "DELETE FROM `$type` WHERE guid=‘$id'";

    // run the query
    mysql_query($sql, $mysql);

    // build the success response
    header('HTTP/1.1 200 OK');
 
    // close the database connection
    mysql_close($mysql);
}

 

This code checks the inputs, connects to the database and then executes a SQL query to delete the record from the database associated with the given id.

Improvements

Immediately there are some obvious improvments that can be made to this system.  Most notably, you could switch from having a single index.php script in your web root to having a PHP script in the root of each application.  You must move your .htaccess file to the folder where you want the script and you have to change the RewriteBase to match the folder path.  This will allow you some separation of objects in your URL space.  If you had two applications, one called "todos" and the other called "todonts" you could have /todos/task/1 and /todonts/task/1 be the URLs for the respective applications.

 

You may also want to rename it from index.php to something like data.php or rest.php.  Just be sure to update the RewriteRule line to rewrite your URL to call your new script rather than index.php.

 

There are many, many things you can do to URL's with mod_rewrite.  Once again RTFGoogle.

 

Like I said above, the PHP code could be completely rewritten to be object oriented and to use better database connection mechanisms to gain speed and make the code easier to maintain.  This tutorial shows you how to hack together a quick and dirty solution just to get something up and running.  SproutCore is all about rapid development afterall.

Authentication and Security

One thing I completely left out was authentication and security.  The PHP script that we wrote makes no attempt to authenticate the client to protect your data from malicious users.  Heck, I didn't even escape the SQL to prevent SQL injection attacks.  If you use this code in a production environment, you are stupid.  There are many ways to make PHP and REST services more secure...You know what I'm about to say...RTFGoogle.

 

It looks like the dominant players in the REST service authentication and security realm are OAuth (http://oauth.net/), Amazon's S3 authentication (http://docs.amazonwebservices.com/AmazonS3/2006-03-01/index.html?RESTAuthentication.html) and using HTTP Basic Authentication over SSL.

 

Both OAuth and Amazon's S3 system work but they put a bigger burden on the client.  Personally, I have always just bought a cheap SSL cert from GoDaddy (http://www.godaddy.com) and put my applications in web folders that can only be accessed by HTTP over SSL (i.e. HTTPS) and require the user to authenticate using HTTP basic authentication.  This is a no brainer method and it works.  There are scalability issues related to caching of data, but if you run into those limits, then your web site is much more popular than mine have ever been and chances are you have the resources to hire somebody who really knows this stuff.

 

Using HTTP over SSL works well with SproutCore.  SproutCore doesn't have to know anything beyond how to handle HTTP basic authentication.  The SSL part is transparent to SproutCore since the browser handles all of the SSL stuff for us.  So if you're wrestling with this stuff, save yourself some heartache and pony up the $20/year for your own SSL cert and have your hosting company's support staff help you set it up on your server.

 

Now, if only somebody would write a tutorial on creating a login view using SproutCore....hrm ;-)

The Complete Script

<?php

// Escape special meaning character for MySQL
// Must be used AFTER a session was opened
function cleanValue($value) {
    if(get_magic_quotes_gpc()) {
        $value = stripslashes($value);
    }

    if(!is_numeric($value)) {
        $value = mysql_real_escape_string($value);
    }
    return $value;
}

// handle a POST request
function handlePOST($type, $payload) {

    // check the payload to make sure that it only has a single object
    if(is_array($payload->content)) {
        // we have an array so set up an error response and exit
        header('HTTP/1.1 400 Bad Request');
        header('X-Reason: Malformed Payload');
        return;
    }

    // get the record
    $record = $payload->content;

    // validate the record to make sure we have all the data
    if(!isset($record->description) || !isset($record->isDone)) {
        // we got bad data so set up an error response and exit
        header('HTTP/1.1 400 Bad Request');
        header('X-Reason: Received an array of records when ' .
               'expecting just one');
        return;
    }

    // open a connection to the database
    $mysql = mysql_connect();
    if(!$mysql) {
        // we couldn't connect to the db, set up a 500 error 
        // response and exit
        header('HTTP/1.1 500 Internal Server Error');
        header('X-Reason: Could not connect to database');
        return;
    }

    // select the database to use
    mysql_select_db("mydb", $mysql);

    // clean up the inputs to prevent SQL injection
    $table = cleanValue($type);
    $description = cleanValue($record->description);
    $isDone = $record->isDone ? 1 : 0; // A hack to safely convert a Bool to a Int for MySQL

    // build the query to create the new record

    $sql = "INSERT INTO `$table` " .
            " (`description`,`isDone`) VALUES " .
            " ('$description’, '$isDone')";
    // run the query
    mysql_query($sql, $mysql);
    // get the identifier assigned to the new record
    $id = mysql_insert_id($mysql);
    // build the success response
    header('HTTP/1.1 204 No Content');
    // set the location of the new record
    header("Location: http://$_SERVER['HTTP_HOST']/$type/$id");

    // close the database connection
    mysql_close($mysql);
}

// handle a GET request
function handleGET($type, $id) {

    // connect to the database
    $mysql = mysql_connect();
    
    if(!$mysql) {
        // we couldn't connect to the db, set up a 500 error 
        // response and exit
        header('HTTP/1.1 500 Internal Server Error');
        header('X-Reason: Could not connect to database');
        return;
    }

    // select the database to use
    mysql_select_db("mydb", $mysql);

    // build the query
    $sql = "SELECT * FROM `$type`";

    // if this is a query for a single record, add the where clause
    if($id)
        $sql .= " WHERE guid='$id'";

    // run the query
    $res=mysql_query($sql, $mysql);

    $output = FALSE;
    if($id) {
        $obj = mysql_fetch_object($res);
        $obj->isDone = $obj->isDone == 1;
        $output->content = $obj;
    } else {
        $output->content = array();
        while($obj = mysql_fetch_object($res)) {
            $obj->isDone = $obj->isDone == 1;
            array_push($output->content, $obj);
        }
    }
    
    // output the response header
    header("HTTP/1.1 200 OK");

    // output the JSON encoded output
    echo stripslashes(json_encode($output));

    // close the database connection
    mysql_close($mysql);
}

// handle a PUT request
function handlePUT($type, $id, $payload) {

    // check to make sure we have everything
    if(!$type || !$id || !$payload) {
        // we don't have everythin we need
        header('HTTP/1.1 400 Bad Request');
        header('X-Reason: Missing type, id, or payload');
        return;
    }

    // check the payload to make sure that it only has a single object
    if(is_array($payload->content)) {
        // we have an array so set up an error response and exit

        header('HTTP/1.1 400 Bad Request');
        header('X-Reason: Malformed Payload');
        return;
    }

    // get the record
    $record = $payload->content;

    // validate the record to make sure we have all the data
    if(!isset($record->description) || !isset($record->isDone)) {
        // we got bad data so set up an error response and exit
        header('HTTP/1.1 400 Bad Request');
        header('X-Reason: Received an array of records when ' .
               'expecting just one');
        return;
    }

    // open a connection to the database
    $mysql = mysql_connect();
    if(!$mysql) {
        // we couldn't connect to the db, set up a 500 error 
        // response and exit
        header('HTTP/1.1 500 Internal Server Error');
        header('X-Reason: Could not connect to database');
        return;
    }

    // select the database to use
    mysql_select_db("mydb", $mysql);

    // clean up the inputs to prevent SQL injection
    $table = cleanValue($type);
    $description = cleanValue($record->description);
    $isDone = $record->isDone ? 1 : 0; // A hack to safely convert a Bool to a Int for MySQL

    // build the query to create the new record
    $sql = "UPDATE `$table` SET " .
           "description='$description', isDone='$isDone' WHERE " .
           "guid='$id'";
    // run the query
    mysql_query($sql, $mysql);
    // build the success response
    header('HTTP/1.1 200 OK');
            
    // close the database connection
    mysql_close($mysql);
}

// handle a DELETE request
function handleDELETE($type, $id) {

    // check to make sure we have everything
    if(!$type || !$id) {
        // we don't have everythin we need
        header('HTTP/1.1 400 Bad Request');
        header('X-Reason: Missing type or id');
        return;
    }

    // open a connection to the database
    $mysql = mysql_connect();
    if(!$mysql) {
        // we couldn't connect to the db, set up a 500 error 
        // response and exit
        header('HTTP/1.1 500 Internal Server Error');
        header('X-Reason: Could not connect to database');
        return;
    }

    // select the database to use
    mysql_select_db("mydb", $mysql);

    // build the query to create the new record
    $sql = "DELETE FROM `$type` WHERE id=`$id`";

    // run the query
    mysql_query($sql, $mysql);

    // build the success response
    header('HTTP/1.1 200 OK'); 
    // close the database connection

    mysql_close($mysql);
}

// define the function for translating the payload
function getPayload() {

    // define the holder for our data
    $payload = FALSE;

    // make sure there is payload data
    if(isset($_SERVER['CONTENT_LENGTH']) && 
       $_SERVER['CONTENT_LENGTH'] > 0) {
        $payload = '';
        $httpContent = fopen('php://input', 'r');
        while($data = fread($httpContent, 1024)) {
            $payload .= $data;
        }
        fclose($httpContent);
    }

    // check to make sure there was payload and we read it in
    if(!$payload)
        return FALSE;

    // translate the JSON into an associative array
    $obj = json_decode($payload);

    return $obj;
}

// get the original url in a nice format with a leading /
$url = (array_key_exists('url', $_GET) ? '/' . $_GET['url'] : '/');

// now that we have the url, break up the pieces
$parts = explode('/', $url);

// get the record type
$record_type = $parts[0];

// get the record ID if it exists
$record_id = (isset($parts[1]) ? $parts[1] : FALSE);

// get the request method
$method = $_SERVER['REQUEST_METHOD'];

// handle the method
switch($method) {
    case 'GET':                             // handle GET
        
        // process the GET request
        handleGET($record_type, $record_id);
        
        break;
    case 'POST':                            // handle POST

        // get the payload
        $payload = getPayload();

        // if we have payload, process it
        if($payload) {
            handlePOST($record_type, $payload);
        }

        break;
    case 'PUT':                             // handle PUT
        // get the payload
        $payload = getPayload();

        // if we have payload and an id, process the request
        if($payload && $record_id) {
            handlePUT($record_type, $record_id, $payload);
        }

        break;
    case 'DELETE':                          // handle DELETE
        
        if($record_id) {
            handleDELETE($record_type, $record_id);
        }

        break;
}

?>


 

 

NOTE:  I haven't tested this out with SproutCore on a live Apache+PHP+MySQL server.  This is all derived from personal experience and other scripts I have written to write JSON web services.  If you get a chance to test this out, post a comment on any corrections and/or correct the wiki page.

 

Thanks!

Comments (15)

Christoffer Davidsson said

at 4:58 am on Aug 26, 2009

I just got this to work for fetching all tasks, sort of.
It seems the isDone proprty is not loaded properly. A have a "tasks" table in my mySql-database with fields guid(int), description(mediumtext), isDone(mediumtext).
One entry has isDone set to "false", and the other is "true". They both end up as checked in my application though.

Also, there should be a note about all servers not having JSON running. I used MAMP locally and had to inlude JSON and do some modifications in the code.


// get the original url in a nice format with a leading /
$url = (array_key_exists('url', $_GET) ? '/' . $_GET['url'] : '/');

The nice leading "/" screwed things up a bit as the url was exploded with a leading empty element.

Pedro Luz said

at 8:08 am on Sep 2, 2009

changed an misspell (error) in the handleUpdate() here:

$sql = "UPDATE `$type` SET " .
"title=`$record->title`, isDone=`$record->title` WHERE " .
"id=`$id`";

to:

$sql = "UPDATE `$type` SET " .
"title=`$record->title`, isDone=`$record->isDone` WHERE " .
"id=`$id`";

Pedro Luz said

at 8:10 am on Sep 2, 2009

ups.. the changed was made in the handlePUT() ... not handleUpdate()

Pedro Luz said

at 8:18 am on Sep 2, 2009

changed a misspell in the handleDELETE()

header('X-Reason: Missing type, id, or payload');

to

header('X-Reason: Missing type or id');

Maurits Lamers said

at 6:22 am on Sep 13, 2009

Wooky, did you look at OrionDB? http://github.com/mauritslamers/orion-db
Does all the same things, but generates everything from the DB, so no manual SQL typing needed...

Frédéric GRASSET said

at 4:13 am on Nov 28, 2009

I’ve got some issues that i’ve finally fixed, so here are my comment:
- SQL syntax are wrong here: i need to replace the backquote on the value field by a quote (in the mySQL 5.0 doc backquote are used to indicate a column name), i.e:

// Original code
$sql = "INSERT INTO `$table` " .
"(`description`,`isDone`) VALUES " .
"(`$description`, `$isDone`)";

// New code
$sql = "INSERT INTO `$table` " .
"(`description`,`isDone`) VALUES " .
"('$description', '$isDone’) »;


- It seems that the SproutCore Store used in tutorial 7 don’t follow the protocol as explained here: when sending a single record the store send directly in the body, not in a flied name ‘content’, i.e:
Thus as a quick hack I’ve changed this line in the create handler:
original:
// get the record
$record = $payload->content;

Fixed:
// get the record
$record = $payload;

But I think it’s better to directly use the $record variable instead of a useless $content one

The same apply to the update handler.

Please can someone can confirm about this issue, thus I can eventually fix in the tutorial too

- Another strange behavior in the create handler is that when the header set the Location property then the error code is set to 302 (don’t know if it’s done by PHP or by Apache on my Mac server); As I want to keep the 204 return code I changed the property by x-reason, ie :
original
// set the location of the new record
header("Location: http://$_SERVER['HTTP_HOST']/$type/$id");

fixed:
// set the location of the new record
header("X-Reason: /$type/$id");

I’ve also removed the URL part that is not the record identifier as it’s mat be problematic if we use some proxy and that it seems to be useless (please someone can confirm so It can be fixed in the tutorial?)

Sajith said

at 6:33 am on Mar 30, 2010


How do i parse the header for X-Reason and get the url value? The code sample explains how to get the url from the 'Location' keyword in the header. Similarly do you have code sample to get the url after making this change?

Frédéric GRASSET said

at 4:41 am on Nov 28, 2009

I’ also fix the name of the columns used in this tutorial that doesn’t match with those defined in the model as it simpler to understand

As I’ve some issue with boolean value in relation with MySQL I explicitly cast them into Int.

Nick Watkins said

at 12:43 pm on Nov 28, 2009

In the cleanValue method, you need to return $value otherwise '$table = cleanValue($type);' is NULL!

Giancarlo Nuzzolo said

at 9:13 am on Apr 2, 2010

Hi,
i've been starting with the new experience of sproutcore for few weeks and probably mine is a newbie question, but I've spent the latest two weeks in facing the following problem in hooking data between sproutcore and apache+php+mysql:
my local server (running on osx server 10.6) is provided with apache and php 5.3 support. I've enabled both php5 and mod_rewrite modules in apache but following the tutorial I get always bad results.
In details, the built-in proxy provided with sproutcore works perfectly (it redirects requests to my local server or to the demo server according to what I set up), but it seems apache does not reach the "magical" index.php listed here.
In particular I get those logs, when trying to use my own local server:
GET 301 /tasks -> http://10.0.1.10:80/tasks
location: http://10.0.1.10/tasks/
expires: Fri, 02 Apr 2010 15:49:48 GMT
content-type: text/html; charset=iso-8859-1
server: Apache/2.2.13 (Unix) PHP/5.3.0
date: Fri, 02 Apr 2010 15:48:48 GMT
content-length: 314
cache-control: max-age=60
and no tasks details are loaded in the web application, even if the location is correct (if I call it directly my browser prints the expected json message)
Instead, when I set up the proxy to get todos.demo.sproutcore.com, I receive everything correctly:
~ PROXY: GET 200 /tasks -> http://todos.demo.sproutcore.com:80/tasks
x-varnish: 869605674
via: 1.1 varnish
content-type: application/json
date: Fri, 02 Apr 2010 16:05:41 GMT
server: nginx/0.6.39
content-encoding: gzip
age: 0
any ideas on it depends on? if needed I can paste here my .htaccess file (set up as described here and co-resident with index.php into webroot/tasks) and httpd.conf (I've set AllowOverwrite field to All instead of the default None).
Thank you in advance to everybody for this great framework.
Best wishes for a nice Easter.
Giancarlo from Italy.

Christophe Sautot said

at 1:10 am on May 18, 2010

Lots of information here. Thank you for writing this post.

Have you ever used a PHP framework though, such as CodeIgniter? It would simplify a lot of your code and really clean things up. It also helps you organize your code in a file structure that makes it easier to know where things are; such as putting all your database logic in model classes in a models folder.

HecHu said

at 12:16 pm on Jun 3, 2010

Hello, i have some problems with POST and PUT method, don't do it any thinks, the GET and DELETE method works fine, i don't have my index.php in the root server it is in the folder tasks (root-->tasks-->index.php), i do it this changes in my code:

My .htaccess
RewriteEngine On
RewriteBase /tasks

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.+)$ index.php?url=$1

The createRecord function
createRecord: function(store, storeKey) {
if (SC.kindOf(store.recordTypeFor(storeKey), Todo.Task)) {
SC.Request.postUrl('/tasks/tasks/').json()
.notify(this, "didCreateTask", store, storeKey)
.send(store.readDataHash(storeKey));
return YES;
} else return NO;
}

The problem is: the Request takes 1 minute and the response is empty, what is missing ??? or why don't works PUT and POST.

Thanks !!!!!!!

HecHu said

at 7:56 am on Jun 10, 2010

The solution for now is start the server this: sc-server with --filesystem=false

Daniel said

at 7:54 am on Jul 9, 2010

I get this error:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /Library/WebServer/Documents/tasks.php on line 58

What do I do?

Lazar Alexandru said

at 1:22 am on Aug 23, 2010

Hi all,
Need help!!!!
I am using sproutcore with PHP on a WAMP server over windows , the both servers are installed on the same machine. I have a problem with AJAX request over the POST and PUT requests , is giving me some redirection response 302 .
Thanks in advance

You don't have permission to comment on this page.