Times Tables

My times tables site is now back online at times-tables.willis-owen.co.uk. Rote learning is no longer fashionable but it still remains an effective tool to get kids to memorise times tables instead of trying to work out the sum in their heads each time.

Although this site is simple, it gives competitive kids a sense of achievement and there is nothing to distract them from the task at hand.

For parents there is no marketing / advertising and no agenda.

Times Tables screenshot

Technical Details

It has been updated to use the superb PHP Fat-Free Framework v3.5 which is now installable using Composer.

Why would MySQL crash when WordPress attacked?

WordPress brute force attacks are increasingly common at the time I write this – September 2014. Recently a server I look after was coming under attack and then after a few minutes the site would display ‘Error establishing a Database Connection’. error-establishing-database-connectionWhen I logged into the server the MySQL service wasn’t running.

My first thoughts were that something in the attack was causing MySQL to crash, There was no information in the MySQL error.log to indicate why though so I spent time looking at plugins to help mitigate a brute force attacks, which tend to focus on the WordPress wp-login.php and xmlrpc.php files:

Disable XML-RPC will disable WordPress XML-RPC functionality meaning that nothing can be done to your content, however this does nothing to prevent initial database queries so MySQL can still be affected when running this, and it does nothing about wp-login.php either.

BruteProtect – I’ve seen comments that it will protect xmlrpc but I’ve seen it in action during an attack and as of version 2.2.2 it did nothing to stop it.

NinjaFirewall – there are a lot of configuration options but this one does the job. It sits in front of WordPress and so it can intercept the attack before all the database activity starts up. This worked great when I used it during an attack.

However you may host multiple WordPress sites on a single server and it could be tedious having to install this and configure it for each site, plus there is going to be duplication of what the code is doing and Apache still has to handle all the incoming requests.

It may be better to stop any attacks at the firewall because that’s the single point of entry to your server. This approach still requires a plugin – WP fail2ban but this one will log attacks to your systems auth.log which can be picked up by fail2ban and automatically added for a temporary period to your firewall rules. It is more complicated than the other plugins to install but once a malicious IP has been blocked it can’t affect any of the sites on your server will keep the system load much lower.

After getting the fail2ban solution in place the database was brought down yet again. I ended up looking in kern.log and at the time of the attack was the line ‘apache2 invoked oom-killer’ – that revealed the problem… Apache was using so many processes that it was running out of memory and as this particular server runs off an SSD the swap file is disabled. As there was nowhere else to get memory from it started killing off other processes, the biggest of these was MySQL.

What a relief to find the cause – MySQL wasn’t crashing at all. The solution was just to reduce the MaxClients value in apache2.conf – the default value of 150 is way too big for a modest sized server. If each process takes 40 MB then 150 processes require 6 GB of RAM. Getting a realistic value for this requires a little load testing plus the use of some tools to see how the memory usage increases as Apache has to handle more requests.

How to stop splitting PHP strings in NetBeans 8

In Java and JavaScript strings cannot span multiple lines. NetBeans 8 will helpfully change the code you are writing so the a single multi-line string will become many single line strings joined together. However if you are using NetBeans for PHP this can become a little annoying especially if you are writing long SQL statements.

If you want to switch this off like I did, go into Tools / Options / Editor / Code Completion, select PHP as the language then scroll down to the very bottom and deselect ‘Use String Auto-Concatenation after Typed Break’.

NetBeans 8 Auto-Concatenation settings

Blog Tutorial with Fat-Free Framework v3

This article is adapted from my first one originally written 30/09/2011 for v2
This version was updated 18/02/13 for v3.05

Version 3 is better with even less typing required to access the power of this simple framework.

A while ago now I read an excellent tutorial on ‘Rapid Application Prototyping in PHP Using a Micro Framework‘, this used the Slim micro framework but one thing that bothered me was it required 5 packages before development could start.
These were: Slim (the micro framework), Slim Extras (additional resources for Slim), Twig (template engine), Idiorm (object-relational mapper) and Paris (Active Record implementation).
It struck me that if you need an extra 4 packages alongside Slim for a basic blog then maybe it is a little too skinny and I set out to find a micro framework that could do the same thing without these dependencies.

I found the Fat-Free Framework. It is condensed into a single 50KB file and has a host of features, you can find out about these on the web site so I won’t repeat it here. Instead I’ve reproduced the Slim tutorial to create a simple blog site, but using Fat-Free Framework instead.
You will need PHP 5.3 on your server, I used Ubuntu 12.04 for the tutorial as that version of PHP is easily installed.

Step 1: Setup

Download Fat-Free Framework (F3) from the website.
F3 works just as happily in the site root as from a subdirectory – I’ll assume you’re using an empty website site to do this tutorial.
Unzip the contents of the F3 download and copy the contents into your website root.
The folder contents should look like this:
folder contents

Notice how much simpler the starting site is compared with Slim + extra packages.

Move up one level in the directory heirarchy and set the permissions:

sudo chgrp -R www-data blog
sudo chmod -R 775 blog
cd blog
sudo chmod -R 777 tmp

If using Apache, mod_rewrite will need to be running.

You can browse this site right away and get the F3 start page.
Fat-Free Framework start page

(Once the site has been visited, F3 will create additional folders cache and temp – you don’t need to worry about these).

Step 2: Bootstrapping

As everything we need is already part of F3 you don’t need to do anything here!

You can however tidy up the site to remove the current home page and add a database connection.
Edit index.php and remove the two route functions – these are just used to display the welcome page and the documentation. It should look like this once they have been removed:

$f3=require('lib/base.php');

$f3->set('DEBUG',3);
$f3->set('UI','ui/');

$f3->run();

To setup a database connection add the following between the set and run commands:

$f3->set('DB',
  new DB\SQL(
    'mysql:host=localhost;port=3306;dbname=YourDatabaseName',
    'YourUserName',
    'YourPassword'
  )
);

All the User Interface files will go in the ui directory, you can delete welcome.htm however the contents of the css folder may be useful as they provide some basic styling to make your pages look a little nicer.

Step 3: Routing

Similar to Slim you need to tell F3 the route request method (GET, POST, PUT etc), the URI to respond to and how to respond.
Example home page route:

$f3->route('GET /',
  function ($f3) {
  //do something
  }
);

And if you want to use a route with parameters you would use something like this:

$f3->route('GET /view/@id',
  function ($f3) {
    $id = $f3->get('PARAMS.id');
  }
);

This tells F3 to expect a URI parameter and assigns it to a PHP variable in the anonymous function.

Now for our blog we need basic administration routes like this (the code will come later):

// Admin Home
$f3->route('GET /admin',
  function ($f3) {
  }
);

//Admin Add
$f3->route('GET /admin/add',
  function($f3) {
  }
);

//Admin Edit 
$f3->route('GET /admin/edit/@id',
  function($f3) {
  }
);

//Admin Add and Edit both deal with Form Posts
//don't use a lambda function here
$f3->route('POST /admin/edit/@id','edit');
$f3->route('POST /admin/add','edit');
function edit($f3) {
}

//Admin Delete
$f3->route('GET /admin/delete/@id',
  function($f3) {
  }
);

Notice that we’re using the same function to process Add and Edit form posts so it isn’t anonymous but the function name is passed as a parameter to the route command.

Step 4: Models

The ORMs in Fat-Free Framework do all the hard work for you – no directories, files or code required here.
Are you beginning to see how much simpler this is compared with Slim?

Here’s the SQL that will set you up with the 2 tables necessary for this tutorial:

CREATE DATABASE `blog` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `blog`;

CREATE TABLE IF NOT EXISTS `article` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `title` varchar(128) NOT NULL,
  `summary` varchar(128) NOT NULL,
  `content` text NOT NULL,
  `author` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `article` (`id`, `timestamp`, `title`, `summary`, `content`, `author`) VALUES
(1, '2011-07-28 02:03:14', 'Hello World!', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut ', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.', 'Mr White'),
(2, '2011-07-28 02:03:14', 'More Hello World!', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut ', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.', 'Mr Green');

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`, `name`, `password`) VALUES
  ('1', 'admin', 'password');

Step 5: Application Front End

Like the Slim tutorial we’re going to keep this simple.
F3 has a set of object-relational mappers (ORMs) to make it easy and fast to work with data.
Instantiate a data mapper object that interacts with the users table, call the find method to return a simple array of results, finally the set command is used which will pass the variable between MVC components. F3 calls this a framework variable.

$article=new DB\SQL\Mapper($f3->get('DB'),'article');
$articles=$article->find();
$f3->set('articles',$articles);

You could condense the final two lines together like $f3->set('articles',$article->find()); but I’m keeping this verbose to aid readability and help you figure out how it is all working.

To use templating you need a base layout file in the ui folder called layout.html, feel free to link in the contents of /ui/css if you want it to look nicer

<!DOCTYPE html>
<html>
  <head>
    <title>{{ @html_title }}</title>
    <meta charset='utf8' />
  </head>
  <body>
    <include href="{{ @content }}" />
  </body>
</html>

The F3 template engine uses {{ @name }} to write out the value of a framework variable.
The include tag will embed the contents of a file at the position where the directive is stated – in the example above we’re using a framework variable as the URL so that the content is dynamic.

Now lets create the first of those content files with the view for the homepage, called blog_home.html

<h1>Blog Titles</h1>
<repeat group="{{ @articles }}" value="{{ @item }}">
  <p><a href="view/{{ @item['id'] }}">{{ trim(@item['title']) }}</a> by {{ @item['author'] }}</p>
  <p>{{ @item['summary'] }}</p>
</repeat>

The repeat tag will loop through an array, setting item to the current array element. Within the loop item contains the array of data retrived from the database table and this is accessed using the column name as the array key.

Now that the view is in place we can complete the code in index.php to display it. Set the framework variable to tell the template which view to include, then tell F3 to serve the template.

$f3->set('content','blog_home.html');
  echo Template::instance()->render('layout.html');

Serving the template also converts it to PHP code the first time it’s used and this optimised version is used thereafter which is great for performance.
The full code for the home page function is:

//home page
$f3->route('GET /',
  function ($f3) {
    $f3->set('html_title','Home Page');
    $article=new DB\SQL\Mapper($f3->get('DB'),'article');
    $f3->set('list',$article->find());
    $f3->set('content','blog_home.html');
    echo Template::instance()->render('layout.htm');
  }
);

Now for the detail view, in index.php we need to get an Mapper object, search for the id then send the data to the view/template. In this case we could have assigned each value individually e.g. $f3->set('title',$article->title); but it is quicker to put all the values in the POST framework variable with the copyTo command.

$f3->route('GET /view/@id',
  function ($f3) {
    $id = $f3->get('PARAMS.id');
    //create Mapper object and search for id
    $article=new DB\SQL\Mapper($f3->get('DB'),'article');
    $article->load(array('id=?', $id));
    //set framework variables
    $f3->set('html_title',$article->title);
    $article->copyTo('POST');
    //serve up the view
    $f3->set('content','blog_detail.html');
    echo Template::instance()->render('layout.html');
  }
);

And the view file itself called blog_detail.html accesses the individual data items from the POST framework variable:

<h1>{{ @POST.title }}</h1>
Published: {{ @POST.timestamp }} by {{ @POST.author }}
{{@POST.content}}
<a href="/">Back to Homepage</a>

Step 6: Application Back End

The admin home page just needs to list the blog articles and provide links, the code is similar to that for the homepage.

$f3->route('GET /admin',
  function ($f3) {
    $f3->set('html_title','My Blog Administration');
    $article=new DB\SQL\Mapper($f3->get('DB'),'article');
    $list=$article->find();
    $f3->set('list',$list);
    $f3->set('content','admin_home.html');
    echo template::instance()->render('layout.html'); 
  }
);

The view called admin_home.html and contains a table of the results. It looks like this:

<h1>My Blog Administration</h1>
<p><a href='admin/add'>Add Article</a></p>
<table>
  <thead>
    <tr>
      <th>Title</th>
      <th>Date</th>
      <th>Author</th>
      <th colspan='2'>Actions</th>
    </tr>
  </thead>
  <tbody>
  <repeat group="{{ @list }}" value="{{ @item }}">
    <tr>
      <td>{{ @item.title }}</td>
      <td>{{ @item.timestamp }}</td>
      <td>{{ @item.author }}</td>
      <td><a href="admin/edit/{{ @item.id }}">Edit</a></td>
      <td><a href="admin/delete/{{ @item.id }}">Delete</a></td>
    </tr>
  </repeat>
  </tbody>
</table>

The output of this look like:
Admin Home Page
Now a form to add and edit articles, called admin_edit.html

<h1>Edit</h1>
<form name="blog" method="post" action="{{ @BASE }}{{ @PARAMS.0 }}" >
  <label for='title'>Title: </label><br /><input type="text" name="title" id="title" value="{{ isset(@POST.title)?htmlspecialchars(@POST.title):'' }}" size="60"/><br />
  <label for='author'>Author: </label><br /><input type="text" name="author" id="author" value="{{ isset(@POST.author)?htmlspecialchars(@POST.author):'' }}" size="60"/><br />
  <label for='summary'>Summary: </label><br /><textarea name="summary" id="summary" cols="60" rows="10">{{ isset(@POST.summary)?htmlspecialchars(@POST.summary):'' }}</textarea><br />
  <label for='content'>Content: </label><br /><textarea name="content" id="content" cols="60" rows="10">{{ isset(@POST.content)?htmlspecialchars(@POST.content):'' }}</textarea><br />
  <input type="submit" value="Submit"/>
</form>

I’ve kept this basic, apologies for the lack of styling but that’s not what this tutorial is about.

The lines containing isset are using the ternary operator to check that the template variable exists before trying to access it.

Now for the logic with the routes, add & edit both use the same view remember.

$f3->route('GET /admin/add',
  function($f3) {
    $f3->set('html_title','My Blog Create');
    $f3->set('content','admin_edit.html');
    echo template::instance()->render('layout.html');
  }
);

$f3->route('GET /admin/edit/@id',
  function($f3) {
    $f3->set('html_title','My Blog Edit');
    $id = $f3->get('PARAMS.id');
    $article=new DB\SQL\Mapper($f3->get('DB'),'article');
    $article->load(array('id=?',$id));
    $article->copyTo('POST');
    $f3->set('content','admin_edit.html');
    echo template::instance()->render('layout.html');
  }
);

Now we assigned a function for the POST routes and here’s the content:

$f3->route('POST /admin/edit/@id','edit');
$f3->route('POST /admin/add','edit');
function edit($f3) {
  $id = $f3->get('PARAMS.id');
  //create an article object
  $article=new DB\SQL\Mapper($f3->get('DB'),'article');
  //if we don't load it first Mapper will do an insert instead of update when we use save command
  if ($id) $article->load(array('id=?',$id));
  //overwrite with values just submitted
  $article->copyFrom('POST');
  //create a timestamp in MySQL format
  $article->timestamp=date("Y-m-d H:i:s");
  $article->save();
  // Return to admin home page, new blog entry should now be there
  $f3->reroute('/admin');
}
);

And to delete a record

//Admin Delete
$f3->route('GET /admin/delete/@id',
  function($f3) {
    $id = $f3->get('PARAMS.id');
    $article=new DB\SQL\Mapper($f3->get('DB'),'article');
    $article->load(array('id=?',$id));
    $article->erase();
    $f3->reroute('/admin');
  }
);

There’s a lot less coding required here than with Slim + extras.

Step 7: Using Middleware

This isn’t relevant to using the Fat-Free Framework.
Basic authentication using a database is built in.
The following lines are added to the admin homepage route, they will cause a username/password prompt to appear. If the details are in the user table of the database a session variable is set which allows access to the other admin screens. If unsuccessful they get an error page.

//assign a mapper to the user table
$user=new DB\SQL\Mapper($f3->get('DB'),'user');
//tell Auth what database fields to use
$auth=new \Auth($user,
  array('id'=>'name','pw'=>'password'));
if ($auth->basic()) {
  //store the name they logged in with
  $f3->set('SESSION.user', 'SERVER.PHP_AUTH_USER');
} else {
  $f3->error(401);
}
//the following will display an HTTP 401 Unauthorized error page if unsuccessful
$auth->basic();

In the other admin routes, add this line at the start of the function to deny access if they haven’t authenticated

if (!$f3->get('SESSION.user')) $f3->error(401);

That’s it (if you didn’t spot it in the SQL, my example uses name: admin and password: password for the login credentials).

You could instead choose to redirect back to the homepage with:

if (!$f3->get('SESSION.user')) $f3->reroute('/');

Step 8: Summary

So here is another example of how to quickly get a prototype running using a PHP micro framework.
Is it any faster than with Slim? I’d like to think that it is, there’s less code, less complexity and you aren’t dependent on other packages which may end up changing in some way or not being maintained.
I’ve used F3 to create a times tables application for my son and found it was fun to build and made me more productive – I’ve now put it online at times-tables.willis-owen.co.uk for anyone to use.

Licencing
Slim is released under the MIT Public License and you are free to use, modify and even sell it.

F3 is released on the GNU Public License (GPL v3). You are allowed to use it your business or for commercial gain – but if you make any money you should consider making a donation to the project.

You can download the files used from here Fat-Free-Blog.zip.
And access the files online via my Bitbucket repository.

I’ve done another tutorial on creating a near identical blog application to this but using CakePHP: Blog Tutorial with CakePHP Framework which may be interesting to compare with differences with using a micro-framework.

jQuery Autocomplete widget for CakePHP 2.2

A JavaScript autocomplete element that links through to your database isn’t built into CakePHP any more – here is a simple solution to add to your application using a jQuery plugin.
Why a plugin? jQuery doesn’t include this functionality, it is provided by jQuery UI but including that library may be a heavyweight solution for your application.

It should end up looking like the image below – as you type, relevant options from your database will appear in the list beneath.

CakePHP autocomplete widget

You can download the jQuery autocomplete plugin from https://github.com/dyve/jquery-autocomplete
Extract the files from the archive and move the .css and .js files from the src folder into the css and js folders of the CakePHP webroot directory.

Link to the CSS and JavaScript in your default layout:

echo $this->Html->css('jquery.autocomplete');
echo $this->Html->script('jquery.autocomplete.min.js');

Add a small form to your View, in this example there is a model called company and we want to use autocomplete on the name field.

Form->create('Company', array('type' => 'post', 'action' => 'find'));
echo $this->Form->input('name');
echo $this->Form->submit();
echo $this->Form->end();
?>

Add this script block to the bottom of the document which activates the plugin and tells it where to submit AJAX requests to:


Modify jQuery.autocomplete.css to look nice with CakePHP default CSS:

.acResults ul li {
	margin: 0px;
	padding: 2px 5px;
	cursor: pointer;
	display: block;
	font: menu;
	overflow: hidden;
	color: #333;
}

Now back to some CakePHP – Your Controller needs a new action which the AJAX requests will be sending data to:

public function find() {
  $this->Company->recursive = -1;
  if ($this->request->is('ajax')) {
    $this->autoRender = false;
    $results = $this->Company->find('all', array(
      'fields' => array('Company.name'),
      //remove the leading '%' if you want to restrict the matches more
      'conditions' => array('Company.name LIKE ' => '%' . $this->request->query['q'] . '%')
    ));
    foreach($results as $result) {
      echo $result['Company']['name'] . "\n";
    }
    
  } else {
  	//if the form wasn't submitted with JavaScript
    //set a session variable with the search term in and redirect to index page
    $this->Session->write('companyName',$this->request->data['Company']['name']);
    $this->redirect(array('action' => 'index'));
  }
}

And finally add some code to the index action in the index action, which handles what happens if the form was submitted via a button press rather than AJAX.

//if there's a session with some data in, add a filter to the search conditions
if ($this->Session->check('companyName')) {
  $name = $this->Session->read('companyName');
  if ($name) {
    $this->paginate['conditions'][] = array('Company.name LIKE' => '%' . $name . '%');
    $this->request->data['Company']['name'] = $name;
  }
}

That’s it.

To to make submit buttons appear alongside form elements
here is a simple bit of jQuery to improve the appearance

$(document).ready(function(){
  $('div.input, div.submit').css({
    'float' : 'left',
    width : '200px',
    clear : 'none'
  });
  $('div.submit').css('margin-top', '14px');
  $('div.input.text').css('margin-top', '-4px');
});

Thanks to the answer on this question from StackOverflow for some inspiration
http://stackoverflow.com/questions/6071828/autocomplete-search-form-cakephp