FrontlineSMS – Saving to a database

For our Major Studio Red Cross project, I continued with setting up the FrontlineSMS system, and we are now successfully saving the received messages to our database using HTTP commands calling to a PHP file on our server.

I put together a quick tutorial on how to set-up FrontlineSMS to save the information you receive to a database, so here we go:

1. Set-up your database and table.
I created a mySQL database, and then made the following table using phpMyAdmin:

`id` int(50) NOT NULL AUTO_INCREMENT, PRIMARY KEY
`name` varchar(50) DEFAULT NULL
`number` varchar(20) DEFAULT NULL
`keyword` varchar(20) DEFAULT NULL
`content` varchar(100) DEFAULT NULL
`date` varchar(20) DEFAULT NULL
`cur_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

These table values all relate back to information that FrontlineSMS can send. For instance, ‘name’ will be the name of the contact you receive a message from. If you don’t have the particular person in your contacts, Frontline will send the phone number associated with the message received. The ‘content’ element is the message content Frontline receives, and it stores this information without the ‘Keyword’ included in the message content.

2. Place your PHP files on a server where you can access it via a URL.
I created two sample files (click here) that you can use and load with your own information. One, called index.php, is the PHP file that you’re going to call within FrontlineSMS, and the other, called results.php, is a simple HTML page that displays your table information.

The index.php file sets up a few variables which allows the file to access your database table, so you’ll need to update phrases like “your_db_name” with your actual info. This index file will access your database and then save the information it receives from Frontline. The results.php will also need to be updated with your information.

3. Within Frontline, create a Keyword and then set-up the External HTTP command within the “Advanced Settings”.
For my example, I used the word “Test”, but you can use any word that you wish to be the trigger to send information to your database. Frontline provides a few reference buttons that will automatically drop in the appropriate code for the information you would like, but the important part is that you first provide the location of your PHP file, and then set up the matching variables that are in your PHP file.

For this example, I am including a generic website address, so my HTTP command will look a little something like this:

I included this text in a comment at the top of the index.php file.

And that’s basically it. There is a lot more that you can do, but as a general overview of how we setup Frontline to save to our database, this is the basic functionality.

A special thanks to Gayatri at Eureka Child (India) for all of the posts on the FrontlineSMS Forum. I wouldn’t have been able to set this up without your insightful posts.