Recent Comments
  • 7281This month:
  • 377Today:
  • 12Currently online:

LeaseWeb CDN

Posts Tagged ‘php’

MySQL-CRUD-API now has transforms!

Last week I created a new GitHub project called “MySQL-CRUD-API” and it allows you to quickly setup a simple REST API with CRUD functionality by just adding a single “api.php” file to your project and configuring it’s database connection. Today I show how the relational support of this project works.

Supported table relations

There are three types of table relations supported:

  • BelongsTo
  • HasMany
  • HasAndBelongsToMany

Blog example

When you use the “list” command of the API it allows you to specify multiple tables. If these tables have relations (foreign keys) the output will be filtered in such a way that only relevant records are returned. This is what the API outputs when you list posts and comments filtered on a specific post:

    "posts": {
        "columns": [
        "records": [
                "blog started"
    "comments": {
        "relations": {
            "post_id": ""
        "columns": [
        "records": [

Not so useful right? You would probably like to see something like this:

    "posts": [
            "id": "1",
            "comments": [
                    "id": "1",
                    "post_id": "1",
                    "message": "great"
                    "id": "2",
                    "post_id": "1",
                    "message": "fantastic"
            "user_id": "1",
            "category_id": "1",
            "content": "blog started"

That is exactly what the function “mysql_crud_api_transform()” does. You run this function on the client after receiving the API response. This is beneficial as it uses the CPU and RAM of the API consumer instead of that of the API server. This transformation function is implemented in PHP and JavaScript, so that you can make spiders and users with browsers equally happy!

function mysql_crud_api_transform(&$tables) {
	$getobjs = function(&$tables,$table_name,$where_index=false,$match_value=false) use (&$getobjs) {
		$objects = array();
		foreach($tables[$table_name]['records'] as $record) {
			if ($where_index===false || $record[$where_index]==$match_value) {
				$object = array();
				foreach ($tables[$table_name]['columns'] as $index=>$column) {
					$object[$column] = $record[$index];
					foreach ($tables as $relation=>$reltable) {
						foreach ($reltable['relations'] as $key=>$target) {
							if ($target == "$table_name.$column") {
								$columnidx = array_flip($reltable['columns']);
								$object[$relation] = $getobjs($tables,$relation,$columnidx[$key],$record[$index]);
				$objects[] = $object;
		return $objects;
	$tree = array();
	foreach ($tables as $name=>$table) {
		if (!isset($table['relations'])) {
			$tree[$name] = $getobjs($tables,$name);
	return $tree;

And the JavaScript version:

function mysql_crud_api_transform(tables) {
	var array_flip = function (trans) {
		var key, tmp_ar = {};
		for (key in trans) {
			tmp_ar[trans[key]] = key;
		return tmp_ar;
	var get_objects = function (tables,table_name,where_index,match_value) {
		var objects = [];
		for (var record in tables[table_name]['records']) {
			record = tables[table_name]['records'][record];
			if (!where_index || record[where_index]==match_value) {
				var object = {};
				for (var index in tables[table_name]['columns']) {
					var column = tables[table_name]['columns'][index];
					object[column] = record[index];
					for (var relation in tables) {
						var reltable = tables[relation];
						for (var key in reltable['relations']) {
							var target = reltable['relations'][key];
							if (target == table_name+'.'+column) {
								column_indices = array_flip(reltable['columns']);
								object[relation] = get_objects(tables,relation,column_indices[key],record[index]);
		return objects;
	tree = {};
	for (var name in tables) {
		var table = tables[name];
		if (!table['relations']) {
			tree[name] = get_objects(tables,name);
	return tree;

Check out all the source code on my GitHub account:

Simple PHP REST API script for MySQL

With single page applications (or SPAs) becoming very popular very quickly we see a rising need to add APIs for everything. Most companies take a first step by creating a simple data-driven API. I wrote a PHP script that generates a simple and fast REST API from your MySQL tables with full CRUD support. Even pagination and filtering is supported! It is only 450 lines of code, not exactly rocket science, but it may be useful when you need to whip up a Minimum Viable Product (or MVP).


  • Authentication or authorization is not included
  • Validation on API input is not included
  • Only a single database is supported


  • Single PHP file, easy to deploy.
  • Very little code, easy to adapt and maintain
  • Streaming data, low memory footprint
  • Condensed JSON: first row contains field names
  • Blacklist support for tables (and columns, todo)
  • JSONP support for cross-domain requests
  • Combined requests with support for multiple table names
  • Pagination, sorting and search support
  • Relation detection and filtering on foreign keys
  • Relation “transforms” for PHP and JavaScript


This is a single-file application. In the bottom of the file you find the configuration:

$api = new MySQL_CRUD_API(
	"localhost",                        // hostname
	"user",                             // username
	"pass",                             // password
	"db",                               // database
	false,                              // whitelist
	array("users"=>"crudl")             // blacklist

Example output

When you request the URL http://localhost/api.php/cate* you will be matching a single table (“categories”) in the configured database. The (formatted) output would be something like this:

    "categories": {
        "columns": [
        "records": [
                "Web development"

As you can see the column names are only at the start of the object and the table name is used as a key on the object allowing for multiple table matches when using a wildcard (star) in the URL.

Get it on Github!

If you want to get it check out my Github page for this little project:

Contributions, forks and additions are more than welcome.

PHP asset proxy increases website availability


Don’t you hate it when your site does not work, because you linked jQuery from “” and that site is suffering connection problems? This may also happen with stylesheets or with font files. To counter this problem (but not lose the convenience of remote loaded assets) I created  an “asset proxy” in PHP. It will cache the assets in a cache folder on your web server, so that you do not have to worry about downtime of other services. You can configure how often the cache should be refreshed. When the external source is not available during a refresh the stale cache files will be used and there is no downtime at all!


Install asset-proxy.php in your webroot. Then replace all references in your HTML from:




Make sure you edit the list of allowed hostnames in the header of the PHP file and that you set an appropriate refresh time (in seconds). If the assets are not available upon refresh the stale files are served.

// hostnames for which "GET" requests can be proxied over "HTTP" (no ssl)
$hostnames = array(

// maximum age of a file before being refreshed
$refresh_age = 24*3600;

// directory where the cache resides (should exist and not be served)
$cache_dir = '/tmp/cache';

// strip the leading "/proxy.php/" from the URL
$url = substr($_SERVER['REQUEST_URI'], strlen($_SERVER['SCRIPT_NAME'].'/'));

// if there is no URL specified show bad request error
if(!$url || !strpos($url,'/')){
	header('Bad Request', true, 400);

// get the hostname which should be the first segment (until the first slash)
$hostname = substr($url, 0, strpos($url, '/'));

// if the hostname is not in the list of allowed hostnames show forbidden error
if (!in_array($hostname, $hostnames)) {
	header('Forbidden', true, 403);

// calculate the cached filename and check whether it already exists
$filename = $cache_dir.'/'.md5($url);
$file_exists = file_exists($filename);

// get the file age if the file exists
if ($file_exists) {
	$file_age = time()-filemtime($filename);

// if cache exists and is fresh, let's read the file, else retrieve it with cURL
if ($file_exists && $file_age<$refresh_age) {
	$result = file_get_contents($filename);
} else {
	// set some headers on the cURL call to pretend we are a user
	$sent_headers = array();
	foreach (array('User-Agent','Accept','Accept-Language','Referer') as $header) {
		$key = 'HTTP_'.strtoupper(str_replace('-','_',$header));
		if (isset($_SERVER[$key])) {
			$sent_headers[] = $header.': '.$_SERVER[$key];

	// make sure we do net get chunked, deflated or gzipped content
	$sent_headers[] = 'Accept-Encoding: ';
	$sent_headers[] = 'Cache-Control: max-age=0';
	$sent_headers[] = 'Connection: keep-alive';

	// initialize cURL with the URL, our headers and set headers retrieval on
	$curl = curl_init('http://'.$url);
	curl_setopt_array($curl, array(
			CURLOPT_HEADER => true,
			CURLOPT_HTTPHEADER => $sent_headers

	// execute cURL call and get status code
	$result = curl_exec($curl);
	$status = curl_getinfo($curl, CURLINFO_HTTP_CODE);

	if ($status == 200) {
		// file was successfully retrieved
		if (file_put_contents($filename, $result)===false) {
			// show error on unsuccessful write
			header('Internal Server Error', true, 500);
	} else if ($file_exists) {
		// serve stale
		$result = file_get_contents($filename);
		// reset refresh timer


// split the message in raw headers and body
if (strpos($result,"\r\n\r\n")!==false) {
	list($raw_headers,$body) = explode("\r\n\r\n", $result, 2);
} else {
	list($raw_headers,$body) = array($result,'');

// convert raw headers into an array
$raw_headers = explode("\n", $raw_headers);

// parse raw headers into received headers
$received_headers = array();
foreach ($raw_headers as $h) {
	$h = explode(':', $h, 2);
	if (isset($h[1])) {
		$received_headers[$h[0]] = trim($h[1]);

// set certain headers for the output
$headers = array('Content-Type','Content-Encoding','Cache-Control','ETag','Last-Modified','Vary');
foreach ($headers as $header) {
	if (isset($received_headers[$header])) {
		header($header.': '.$received_headers[$header]);

// replace the absolute URL's in the output
foreach ($hostnames as $hostname) {
	$body = preg_replace('/(https?:)?\/\/'.str_replace('.','\.',$hostname).'\//',
		$_SERVER['SCRIPT_NAME'].'/'.$hostname.'/', $body);

// set the new content length properly
header('Content-Length: '.strlen($body));

// echo the contents of the body
echo $body;

Best thing since sliced bread.. ;-) And only 128 lines of PHP code! Source code is on Github:


AmsterdamPHP talks Docker at LeaseWeb

amsterdam_phpNext Thursday (December 18th) Robin Speekenbrink will be talking at LeaseWeb about Docker and virtualization in his talk titled “Meet the Phockers”.

Docker is an open platform for developers and sysadmins to build, ship, and run distributed applications. Consisting of Docker Engine, a portable, lightweight runtime and packaging tool, and Docker Hub, a cloud service for sharing applications and automating workflows, Docker enables apps to be quickly assembled from components and eliminates the friction between development, QA, and production environments. As a result, IT can ship faster and run the same app, unchanged, on laptops, data center VMs, and any cloud.-

At LeaseWeb we are very proud to be hosting this event. It was quickly “sold out” even though we had 100 seats available. One of the reasons the talk is so popular is that LeaseWeb has about 40 software developers that are all very interested in Docker technology themselves. So from yesterday another 25 seats are available. On the meetup site you can sign up and reserve a seat!

The schedule

19:00 - 19:30: Welcome Drinks 
19:30 - 20:30: Talk 
20:30 - 20:45: Raffle 
20:45 - 23:00: Social, drinks and Pizza

The location

You can easily find the location of LeaseWeb as it is near the IKEA in Amsterdam and it is situated in the corner of the A2 and A9 highways. To get directions and find the exact location you can use Google Maps. Or enter the following address in your satnav:

LeaseWeb Netherlands B.V.
Luttenbergweg 8
1101 EC Amsterdam
tel +31 20 316 288

Free as in free beer…

We have ordered lots of large pizza’s and we have juice, soda, draft beer and wine in our canteen. So please visit us, meet the nice people at LeaseWeb and enjoy our beautiful building and free parking.

Meetup link:

10 reasons why PHP is better than Python

“There are only two kinds of languages: the ones people complain about and the ones nobody uses” – Bjarne Stroustrup,

People wonder: Did he really say that? Yes, he did. If you wonder who Bjarne Stroustrup is: He created the C++ programming language. Many people believe that Python is “pretty cool” and PHP is “really bad”. But as we all know, there is truth in the saying:

It’s a poor carpenter who blames his tools.

I believe both good and bad software can be written in any language. And I should probably also quote Joel Spolsky who calls “language wars” a “fruitless debate”. But nevertheless, if you program in PHP and run into one of these Python “evangelists”, then the following list may come in handy.

10 reasons why PHP is better than Python

  1. Python hosting, hard to find and expensive, while cheap PHP hosting is everywhere.
  2. Python cannot be mixed with HTML (needs a template library), while PHP can.
  3. Python has no proper encapsulation (private keyword), while PHP has.
  4. Python is hardly used in the real world, while something as big as Facebook is built on PHP.
  5. Python has a great community, but it is not comparable to PHP’s.
  6. Python has some books and tutorials, but PHP has way more of them.
  7. Python does not have the live docs (famous forum-like reference manual) like PHP has.
  8. Python does not have a steep learning curve, but PHP is still easier to learn.
  9. Python indentation for code blocks is prone to errors, while PHP uses curly braces.
  10. Python lexical scoping is a mess (‘global’ and ‘nonlocal’ keywords fix this), while PHP behaves as expected.

To wrap up

PHP has come a long way. Today it is a mature language that executes fairly speedy. Agreed that it has some quirky naming of it’s built-in functions, but hey.. that’s the price you pay for backwards compatibility.

Hint: Make sure to also check out this page on that is a good reference when comparing Python to PHP.