Message Board Web Service

Post Image
Posted on Updated

If you’ve made it this far, you are at the final stages of the project. We’ve constructed the message board from scratch, and we’ve written a program to display a message to the board. All that remains is writing a small web service that allows the Raspberry Pi 3 to read a message, and output that message to the board. I will describe creating the database, creating the web service, and writing the message to the board.


Objective

Our goal is to submit a message to a form on a website, say my website, and have the message board output that message. There are plenty of techniques to do this:

  1. Simplest: store the last message input by the user in a database and retrieve that entry upon request.
  2. A little bit more: queue the messages input by the user in a database and retrieve messages from the queue to ensure we get every message written by the users.
  3. A little bit much: create a tcp connection with the web server and the raspberry pi and update the board everytime a user submits a message.
  4. Overkill: create a tcp connection with the web server and the raspberry pi and queue the messages updating the board one message at a time.

I’m sure you can think of other techniques, but you get the idea. As for our choice, we will choose a solution that best fits our needs. Our needs are as follows: this service will be used by little users and it is not necessary that the messages are real-time. This leads us to choice number 2. We won’t need any TCP connection established between the devices because we don’t require real-time responses. Although we could go with #1 since there probably won’t be a message that is overwritten and never read from the Pi, there is a chance that this does happen. Because of this, we’ll queue the messages and pull the messages from the queue.

Prerequisites/Environment Setup

I am assuming you have an apache environment setup. This could mean you have access to a running server on the web, or you are runnning a web server on your local/private network. If you’ve never setup an apache environment, you can check out LAMP/WAMP/MAMP. It’s free software that manages the apache, MySQL, and php configuration for you based on your operating system. You could also individually setup the apache server, MySQL server, and install php. All of these components are necessary to proceed, so I put some links in the resource section to help you figure out how to get you’re environment setup.

When writing this article, I assume you have some understanding of the relationship between all the components listed above. I assume you can read php and python code and are familiar with programming in some way. I assume you have a basic understanding of MySQL and can read SQL and HTML.

Note: I do not properly clean entries that go into the MySQL database, properly establish database connections, or properly import files. I assume you have a framework to help query your database that will allow you to properly clean entries going into the database, will allow ease of establishing a database connection, and will save time writing the same cookie cutter code importing files. I’ve listed some common frameworks and CMS in the resource section.

Creating the Database

The first thing we need to do is establish a place to store the messages we receive. We’ll be using a MySQL database to store the messages, so go ahead and create a database where you want to store the messages if you don’t have one already. If you’re using phpMyAdmin, you can simpy navigate the UI to create the database. Within this database we’ll create a table called message_board_messages (or whatever you prefer) that stores the message, an ID for the message, and the date time it was created. You can use the following SQL code to create the table:

CREATE TABLE `<YOUR DATABASE NAME>`.`message_board_messages` ( 
	`mbm_uid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT , 
	`mbm_message` VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
	`mbm_dtc` DATETIME NOT NULL , 
PRIMARY KEY (`mbm_uid`)) ENGINE = InnoDB;

As you can see the table uses mbm_uid as the unique id for the table, mbm_message as the message, and mbm_dtc which will be used as the date time the message was created. We’re going to use mbm_dtc to limit the number of entries we accept per day. I’ll talk about this a little later when I describe cleaning up the code.

We’ve also limited the message length to 200 characters to prevent the storage from filling up from long messages. This table is as simple as it gets and should be sufficient for our purposes. We could do a bunch of other things here and add a user column to signify the user that input the message, add a read column to indicate if the message was read by the board, etc., but that is not necessary.

Creating the Web Service

The web service we’re creating needs to do two things: it needs to input the message from the user, and it needs to read messages from the SQL database. Technically the part where we input a message from the user isn’t exactly part of the web service since it’s performed by the user manually. I’m going to just grab bag that topic into this section, however, and consider that part of the service.

Writing to the database

The first thing we’ll need to do is create a php file and call it messageInput.php. In this file we will create an HTML form that will submit the message to our database. We will post the message from the form to the same page. Here’s my code for the form:

<?php
//code to handle POST for message goes here
?>

<form id="messageBoardForm" action="" method="post">
	<div class="input-group">
		<input class="form-control" name="messageBoardMessage" value="" placeholder="Write a Message!" title="Write a Message!" type="text">
		<span class="input-group-btn"><button class="btn btn-success" type="submit">Send</button></span>
	</div>
</form>

I’m not going to go through the entire process of making this a pretty HTML page, as the purpose is to show you the backend programming, so I suggest adding this code to an existing HTML page. This page should be placed in the directory your apache server considers the Document Root, and can be run by opening up your web browser to the file location.

Now that we have the form setup, you’ll notice if we press the “Send” button, then the page will reload. This form sends a POST request to the same page in which it is located. All that means is it fills the php $_POST superglobal with information from the form. In our case $_POST['messageBoardMessage'] will be equal to whatever the user input.

Now let’s add onto this a bit and connect to the database at the top of the file. Depending on how you setup MySQL, you’ll need to alter the code I provide:

<?php
//database parameters
$db_hostname = 'localhost';
$db_username = 'root';
$db_password = 'root';
$db          = 'message_board_database';
$db_server   = mysqli_connect($db_hostname, $db_username, $db_password, $db);

//check server connection
if (mysqli_connect_errno()){
    die("Unable to connect to MySQL: " . mysqli_connect_error());
}

echo "Connected to our database!";

mysql_close($db_server);
?>

<form id="messageBoardForm" action="" method="post">
	<div class="input-group">
		<input class="form-control" name="messageBoardMessage" value="" placeholder="Write a Message!" title="Write a Message!" type="text">
		<span class="input-group-btn"><button class="btn btn-success" type="submit">Send</button></span>
	</div>
</form>

You’ll notice the file now establishes a connection to the database, closes it, then outputs the HTML form. Not much of this is actually safe to use in production, since you are hard coding the database password in plain text. There are some techniques you could do to make this more secure like putting the login information in a separate file and including that file, but we’re not focused on the nooks and crannies of security right now. Most live web applications are built on frameworks that have this built in, so we will focus on the process only.

If you run the script and get the “Connected to our database!” message, congratulations you can proceed! If you didn’t, then check to make sure you’re database parameters are correct. Make sure you put in the proper parameters particular to your MySQL setup and that the password is correct. Sometimes it helps writing the actual IP address instead of localhost.

Now let’s move onto the actual processing of the message:

<?php

if(!empty($_POST)){

	//database parameters
	$db_hostname = 'localhost';
	$db_username = 'root';
	$db_password = 'root';
	$db          = 'message_board_database';
	$db_server   = mysqli_connect($db_hostname, $db_username, $db_password, $db);

	//check server connection
	if (mysqli_connect_errno()){
	    die("Unable to connect to MySQL: " . mysqli_connect_error());
	}

	$dtc     = date('Y-m-d h:i:s');
	$message = $_POST['messageBoardMessage'];

	$query = "INSERT INTO message_board_messages (mbm_message,mbm_dtc) VALUES ('$message','$dtc')";

	 if (!mysqli_query($db_server,$query)){
	   	echo "INSERT failed: $query<br>" . mysqli_error($db_server) . "<br><br>";
	}else{
		echo "Added new message to queue!<br>";
	}

	mysql_close($db_server);

}
?>

<form id="messageBoardForm" action="" method="post">
	<div class="input-group">
		<input class="form-control" name="messageBoardMessage" value="" placeholder="Write a Message!" title="Write a Message!" type="text">
		<span class="input-group-btn"><button class="btn btn-success" type="submit">Send</button></span>
	</div>
</form>

Right off the bat, let me tell you that we did a big no-no. We’ve taken the exact contents of what the user submitted from the form ($_POST['messageBoardMessage']) and inserted it into our database. Terrible, terrible things can happen when you do this. What if the user that submitted the messages decided they wanted to put SQL code into the text field? They could very easily do that and create a user that can connect to your database with root privileges! Very bad. There is also the concern of cross-site scripting, but I’m not going to go over that. PHP database sanitization is an entirely different beast that is usually handled by web application frameworks for you anyway. Because of this, I will not delve too far into that topic

Now we’re talking! Actual data in the database! Wonderful! Do a quick verification that everything went smoothly with the “Added new message to queue!” message appearing, and manually check the database to see if entry made it into the table. If it didn’t, then double-check the table name is correct as well as your php syntax.

Reading from the database

Let’s move on to creating a page to view messages for the board. This page needs to take in a parameter and output a certain number of messages based on that parameter. In our case, the parameter will be the offset from the start of the messages in the table. After we get this parameter, we need to filter the value, limit the output, and output in a format we can recognize from the Pi. Make a file called messageOutput.php that has the following contents:

<?php

//database parameters
$db_hostname = 'localhost';
$db_username = 'root';
$db_password = 'root';
$db          = 'message_board_database';
$db_server   = mysqli_connect($db_hostname, $db_username, $db_password, $db);

//check server connection
$status = "OK";
if (mysqli_connect_errno()){
	$status = "ERROR";
	$reason = "Unable to connect to MySQL: " . mysqli_connect_error();
}

//define variables we will use for the query and output later
$offset   = !is_null($_GET['offset']) ? intval($_GET['offset']) : 0;
$limit    = 10;
$reason   = "";
$messages = array();

if($status == "OK"){

	//grab the messages
	$query = "SELECT   mbm_message 
		FROM  message_board_messages 
		LIMIT $limit
		OFFSET $offset";
	$result = mysqli_query($db_server,$query);

	//check for error in the query
	 if (!$result){
		$status = "ERROR";
		$reason = "Database access failed: " . mysqli_error($db_server);
	}

	if($status == "OK"){

		//add the messages to our output array ($messages)
		while($row = $result->fetch_object()) {
			$messages[] = $row;
		}

	}

}

//define the new offset the user will request
$newOffset = $offset + (count($messages) < $limit ? count($messages) : $limit);

//output messages
$response = array('messages' => $messages, 'newOffset' => $newOffset, 'status' => $status, 'statusReason' => $reason);
echo json_encode($response);

mysql_close($db_server);
?>

Let’s break this down. Users retrieve messages through a GET request, filling in the URL with the offset parameter (e.g. www.mysite.com/messageOutput.php?offset=0). The script takes in the offset parameter and defines other variables we will need for the query, like $limit, which is the parameter to limit the data, and $messages, which is the parameter to hold the retrieved messages. We put a limit on the page so it can easily load any request.

After we define those variables, we move on to running the query to retrieve the messages. Once we get the messages back, we store the result in the $messages array. After this, we calculate $newOffset, which is the new offset the user will need to submit to retrieve new messages. Finally, we output the result and messages in a JSON format, so the Raspberry Pi can easily parse the result. In between all of this, we check for errors and fill in some variables with diagnostic information we will also output.

If this is the first time seeing something like this, it may be a little overwhelming, but I promise you it doesn’t take long to understand. Most PHP scripts are connecting to the database, performing a query, and returning a result (just a lot cleaner and with more protection against malicious behavior).

Updating the Board

Looks like the service is built, and all we need to do is create a way for the board to retrieve the latest messages. Since we are receiving messages to display to the board, we will write this program in Python. I will encapsulate the algorithm to retrieve the message in an object called MessageRepo. This class has one public function getMessage() to retrieve the next message in the queue. It has two private functions to help save and load data we use to makeup the queue:

import urllib2
import json
import os.path

class MessageRepo(object):

	_messageIndex  = 0
	_messageOffset = 0
	_messages      = []
	_filepath      = "message.json"

	def __init__(self):
		self.__setOffset()

	def __setOffset(self):
		# check if we created a file before with an offset, and load parameters where we left off
		if os.path.isfile(self._filepath):
			with open(self._filepath) as data_file:    
				data = json.load(data_file)
				self._messages      = data['lastMessages']
				self._messageOffset = data['lastMessageOffset']
		# create a file if it doesn't exist already
		else:
			self.__writeToSavedFile(0,['No Messages'])

	def getMessage(self):

		displayMessage = ""

		# if we have read all of our messages, check to see if there are new ones
		if self._messageIndex >= len(self._messages):

			# reset the message index
			self._messageIndex = 0

			# retrieve the new messages
			request  = urllib2.urlopen("http://localhost/messageOutput.php?offset=" + str(self._messageOffset))
			response = json.load(request)

			# set new messages if we get a valid response
			if response['status'] == 'OK' and len(response['messages']) != 0:
				self._messages = []
				for message in response['messages']:
					self._messages.append(message['mbm_message'])
			# if there are no new messages, we have displayed all messages, keep repeating the last message
			elif response['status'] == 'OK' and len(response['messages']) == 0:
				self._messages = [self._messages[len(self._messages)-1]]
			# display the error
			else:
				self._messages = [response['statusReason']]

			# setup internal parameters
			self._messageOffset = response['newIndex']
			displayMessage      = self._messages[self._messageIndex]

			# write to json file to update info
			self.__writeToSavedFile(self._messageOffset,self._messages)

		else:
			displayMessage = self._messages[self._messageIndex]

			# make sure to not show the message from the last display if we crash the program here
			self.__writeToSavedFile(self._messageOffset,self._messages[self._messageIndex:])

		self._messageIndex += 1

		return displayMessage

	def __writeToSavedFile(self,offset,lastMessages):
			messageConfig  = {'lastMessageOffset': offset, 'lastMessages': lastMessages}
			fh = open(self._filepath, 'w')
			fh.write(json.dumps(messageConfig))
			fh.close()

The first thing we do when this class is initialized is set the offset. The offset is used to retrieve the proper message from the queue and is loaded from a file (if we ever retrieved a message before). After the class is initialized, the getMessage() function can be called to retreive the latest message from the queue. Calling this function increments the index used to retrieve messages from our local message array. If we reach the end of the array, we retrieve more messages from the database and set the message array appropriately. The last message repeats if there are no new messages.

As for drawing the message we receive from the database, download the project, modify animateMessageBoard.py to import the new class we created, and loop through the messages. And that’s it! Run the script and Wah-la! The board is retrieving and displaying messages from the web service!

Cleaning up the Code

There are some improvements that need to be made to the database to make the architecture self-maintainable. We need to limit the number of entries in the database in one day. This will ensure the database does not grow an unreasonable amount from someone spamming a form. To do this, all we have to do is check the database for the number of entries that exist in the database for that day. If this number is over a certain threshold, say 1000 entries, then the form will not be able to submit the message. This, of course, should notify the user, so the user knows the message has not been submitted. We could also add a cron job to delete entries after each day. This will only leave entries for a single day to be submitted, and is the reason we added the mbm_dtc column to our message_board_messages table.

Resources

The following links will help you setup your local environment for web development:

The following are common web development frameworks for web applications:

The following are common Content Management Systems (CMS) for websites:

Comment Below

Your email address will not be published.

*This field is required.
*This field is required.
*This field is required.