Combining 3 different APIs (Rapleaf, ToneAPI and my own Murrion APIs) I wanted to see if they could be of any use in helping identify spam that comes through forms on websites such as Contact Forms.

I built a simple form that could be on any website, asking a user for a name, email address, subject and a message to send.

You can try out a demo here. The form doesn’t submit the information anywhere other than back to the user on the screen to see the results.

• The Name field is passed to the Rapleaf Utilities API to determine if the name is Male, Female or Unknown
• The Subject and Content message are sent to the ToneAPI to try and determine the positive or negative tone of the content.
• The Email address is passed to the Rapleaf API to see if it can return any information about the user.
• The IP address (which the user isn’t asked for but is determined automatically) is sent to an API of my own which uses the Maxmind location database to convert an IP address to a geographical location.
• The Browser agent (Ie the web browser programme the user is using to view the page, such as Internet Explorer) is sent to another API of my own which can tell if a browser agent is a normal user, a web bot, a mobile device or an Unknown browser agent.

Combining these results could allow a website to filter or block the information coming through their form in any number of ways.

For example if the Name wasn’t Male or Female, if the tone of the Subject and/or Content message couldn’t be determined, if the Location couldn’t be determined and the browser agent type was unknown, then that message could be labelled as spam and not delivered to the users inbox.

If the tone of the Subject or Message as overly negative it could be sent directly to the complaints department in an organisation.

Its probably not going to solve all of the worlds spam but it was an interesting experiment nonetheless. At the very least, these APIs can provide additional information to incoming information from a website.

Earlier today I created a simple app to test out the ToneAPI from Lymbix. I’m very interested in sentiment analysis  and think tools such as ToneAPI are very powerful.

Its easy enough to use tools such as Seesmic to watch out for mentions of your brand name on Twitter but I think its even more useful if you monitor the tone of the messages too. If the tone of a message is negative, perhaps one member of staff might be better at responding to it than another. Likewise, you could monitor the tone of messages about your brand over time to see if you’re doing any better or worse.

The app I built allows you to search Twitter showing the most recent 100 mentions of your search term. Using the ToneAPI, any Tweets that are positive are marked in Green and any Tweets that are negative are marked in Red. Try it out, search for something on twitter and see peoples moods.

I’m only using a single piece of information that is returned by the API. Some further refinement could be made by taking into account some of the other variables that are available. More than showing just positive or negative its possible to determine if the user is expressing gratitude, grief or even loathing.

Sample Response from ToneAPI

[ignored_terms] => “”
[affection_friendliness] => 0.47
[enjoyment_elation] => 2.2
[amusement_excitement] => 2.05
[contentment_gratitude] => 2.54
[sadness_grief] => -0.25
[anger_loathing] => -0.42
[fear_uneasiness] => -0.34
[humiliation_shame] => -0.26
[dominant_emotion] => contentment_gratitude
[average_intensity] => 3.26
[article_sentiment] =>
[sentiment] => Positive
[score] => 0.85
[coverage] => 36
[intense_sentence] =>
[sentence] => “”
[dominant_emotion] => contentment_gratitude
[intensity] => 0.32
[clarity] => 61.52

After building this, I got to thinking that maybe social media APIs could be used to help prevent spam.. (another blog post)

I’m writing this mainly as a guide to myself in case I need to do this again in the future. I had to do this last year too, it was a headache each time to find an approach that worked.

My goal is to copy the tables and data from a Microsoft SQL Server database hosted with Blacknight.com to a local mySQL database.

The process is a bit long winded as it is currently not possible to connect directly to a MSSQL database hosted with Blacknight using a 3rd party database manager.

The following software is used in the following steps.

1. Microsoft SQL Server Express 2008 R2 (
2. SQLyog (A 30-day free trial is available)

It is assumed that you already have a mySQL database up and running on your local machine to import the information in to.

Step 1.

The first step is to export a backup of the current Microsoft SQL Server database. Blacknight offer a control panel called ‘myLittleBackup’ to allow me to log in and create 1 backup of the database which I can then download. The result is a .bak file downloaded to my desktop.

Step 2.

To begin working with the .bak file, I need to download and install ‘Microsoft SQL Server Express’. The current version I used is SQL Server 2008 R2 Express, 64 Bit. It is free to download and use and is approximately 247 MBs.

Step 3:

Once SQL Server express is downloaded an installed, open up ‘Microsoft SQL Server Management Studio’. (Installing SQL Server  could easily have a tutorial of its own but for the sake of this tutorial, using the default options should be fine and worked for me).

You will be asked to connect to a server. There should be a database there already in the form  <Computer-Name>/SQLEXPRESS. Click Connect to continue.

Step 4:

You will see a list on the left with a folder called ‘Databases’. Right-click on ‘Databases’ and click ‘New Database’.

Give your database a name, I’ve called mine ’sample’, then click OK. ‘Sample’ will appear within the Databases folder on the right.

Right click on ‘Sample’, choose ‘Tasks’ -> ‘Restore’ then ‘Database’. A window will appear with the title ‘Restore Database’. Your Sample database will already be selected on the right. Below this there is a section called ‘Source for Restore’

Choose ‘From Device’ and click on ‘…’ (browse) on the right.

A window will pop up called ‘Specify Backup’. Choose ‘File’ and click ‘Add’ on the right.

Locate the .bak file you downloaded from myLittlebackup earlier and press OK twice to go back to the ‘Restore Database’ window. Make sure you tick the ‘Restore’ box next to the name of the database that has just appeared in the box.

Before clicking ‘OK’, Click on ‘Options’ on the top left of the window, tick the box which says ‘Overwrite the existing database (WITH REPLACE)’.

Now press OK and your database will be imported. You will be able to expand the ‘Sample’ database to see the tables.

Step 5:

Leaving the SQL Server Management Studio, go to Control Panel in Windows. Select ‘Administrative Tools’, click on ‘Data Sources’.

In ‘User DSN’ click ‘Add’ to add a new ODBC source.

Click on ‘SQL Server Native Client 10.0′. Give it a name (eg ’sample_odbc’) and choose a server. It should be the same as the database to connect to in step 4: <computer-name>/SQLEXPRESS. Click ‘Next’

The following screen asks about verifying the login ID. You can leave this screen alone and click ‘Next’.

The next screen is ‘Create a new database source to sql server’. Ensure ‘Change the default database’ is set to the name of your database from step 4. In this case ‘Sample’.

The next screen can be ignored and click ‘Finish’

A window will appear allowing you to test the database source. If there is a problem, repeat the actions in this step 5.

Click Ok to finish the set up of an ODBC source.

Step 6:

Next, open up SQLyog.

When SQLyog opens, connect to your existing mySQL database, probably ‘localhost’

If you haven’t created a mySQL database, create one now by right clicking in the New Connection window on the left and clicking ‘Create database’. Give the database a name eg: ‘New’. This database will appear on the left.

Right click on the database and select ‘Import’ and then ‘Import External Data’.

In the window that opens choose ‘Any ODBC Source’. Below this, select your ODBC connection (created in step 5) called ’sample_odbc’ from the list next to ‘System/User DSN’. Click Next to continue.

The next step is asking where to import the next information to. Ensure your database ‘new’ is selected at the bottom of the window and press Next.

Ensure ‘Copy table(s) from the data source’ is selected in the next window and click Next.

The next window will show a list of tables that can be imported. In my case I know the names of the tables I need so I select them manually. You might want to select the tables manually as there is more information listed in this window than we need to import.

Click ‘Next’ when you have selected your tables. The Wizard will begin copying the table structure and database from the MSSQL database over to your MS SQL database. It could take several minutes. The ‘Next’ button will become available when it is finished.

When the ‘Next’ button highlights, click it, then the ‘Finish’ button.

You have now successfully copied the tables and data from a MSSQL database .bak file in to a mySQL database.

On the left, you can select the ‘new’ database and see its newly imported tables and data.

I wanted to write about and share a small script I wrote that might be of use to other Codeigniter developers.

This script will list all of the controllers and models in a Codeigniter application and the function names within those files. Its a handy reference for a larger application, to see whats already developed at a glance.

The script is a single PHP file. To use it, place it in your webserver root and pass an application name to it via the query string.

For example:

http://localhost/list_app_functions.php?app=mynewapplication

This assumes that there is a folder called ‘mynewapplication’ with a codeigniter system folder inside. It will look in the application/controllers folder and the application/models folder. The files and functions are added to an array and displayed in a table

The output of the file is a simple HTML file listing the file names on the left and the function names within the file on the right.

You can download the script here. Place it in your root and run it by using:

http://localhost/list_app_functions.php?app=mynewapplication

Where ‘mynewapplication’ is the name of your codeigniter application.

Introducing Fueltweet, a new Twitter application you can talk to, it’ll tell you the best Petrol or Diesel price in your area.

If you want to know the cheapest spot to fill up in Bishopstown forexample, tweet “@petroltweet Bishopstown” or “@dieseltweet Bishopstown” depending on wether you are looking for Petrol or Diesel prices. It will respond to you a few seconds later with the most up to date and best price it knows in that area.

Read the rest of this entry »