The Web Services and Database design.

The first stage that I will be completing is the back end server stuff, my main reason for starting here is that I want to be able to test that in practice the end to end technology stack actually works (Android/Java->JSON->PHP/Apache->MySQL Database, and the reverse) to send and receive data to/from an Android device (actually an emulator initially) and from/to a remote MySQL database.

It would have been entirely feasible to start from the Android/Java side without a real data source, all routines that require a data source or sink would be coded to interfaces and behind the interfaces we would have dummy data provider implementations which, when the server side implementation was running, could be swapped out for the real implementation (in fact I will be doing this anyway to facilitate easier testing).

The Database

The design of the database is purposefully quite simple, I won’t explain too much as the diagram below should describe it pretty well.

Essentially we have types of animals, a table of missing animals of those types and a table of sightings of those types. I have made location it’s own table as a location may well be a valid entity in it’s own right.

Database schema

Database schema

I built this schema using MySQL Workbench then Forward Engineered it into my MySQL server instance.

I have also exported the SQL to create a database with this schema which is available online (see further down this post).

Web Services

From analysis of our specification it has been decided that these are the database web services that we must provide:

  • Retrieve all animal types.
  • Add a new sighting.
  • Retrieve all sightings.
  • Confirm a sighting (modify sighting record).
  • Retrieve missing animal data.
  • Confirm a capture (modify missing record).

Each of these services will be implemented as a php script.

Accessing MySQL from PHP

In order to use MySQL from PHP we need to ensure that the mysqli extension is installed and enabled for PHP.

Using the package manager install php5-mysql.

It may be necessary to add the line

extension = /usr/lib/php5/20121212/

to your php.ini, I had to do this for my cli version however the extension was already enabled for my apache2 version.

Obtaining the Source Code

In general I won’t be discussing the code in detail on these pages, I may draw attention to specific parts of the code but the code will all be available both as a zipped download for the project at a specific state or as a publicly available repository on Bitbucket.

The recommended way to stay up to date is to clone the repository locally and update the repository as the project progresses.

Create a clone of the repository with mercurial.

In the directory that you want to clone the repository into execute the command:

~ $ hg clone

to clone the repository.
Update to changeset with tag ‘Page – Database Design and Querying with PHP’.

Or download code from the point of the mercurial tag here -> Database Design and Querying with PHP.

There are only two files of interest at this point, the SQL create script and a PHP script to retrieve all entries from the ‘AnimalTypes’ table.

Create and populate the database

In a web browser open the page http://localhost/phpmyadmin/ (phpmyadmin must be installed).

Go to the ‘Databases’ tab and create a new database with the name ‘EscapedAnimalsDb’ (TODO change the script to create the database as well).

Go to the ‘Import’ tab and import the SQL create script ./sql/EscapedAnimalsDb.sql

In phpmyadmin add some entries into the AnimalTypes table (TODO add a populate script to pre-populate the db).

Retrieving data via PHP

In Eclipse create a new php project similar to below.


Ensure that the PHP executable used to run the PHP and the debugger are set up.

Go to Window->Preferences->PHP

Under PHP Executables add something like this:


When we come to publishing our files to the web server we’ll want debugging as well so also under the PHP preferences go to PHP Servers and add en entry something like this:


Now right click on the RetrieveAnimalTypes.php file in the PHP Explorer, select Debug As -> PHP CLI Application.

The script should now start executing in a debug session.

If you take look at the source you’ll see that all this script does is open a database connection using some hardcoded credentials (this isn’t a security risk as none of this gets passed over the network, it’s all server side which we hope is secure), executes an SQL select query to select all  rows from the AnimalTypes table, encodes the result as a JSON array and echoes to output.

If we run it all the way through we should see the contents of our AnimalTypes table output encoded as JSON array.

The PHP to retrieve data from the AnimalSightings and MissingAnimals table are very similar and differ only in the fact that they booth reference the AnimalTypes table, In order to get the name of the animal type we just need a simple INNER JOIN. As these 3 scripts differ only by the SQL string it would be a good idea to extract the common code into it’s own class to simplify modifying the code at a later date.

Writing to the Database with PHP

The only other thing that we require our web service to do is to write data back to the database.

We need to be slightly more careful here as we will e sending String data back to the server to construct SQL to add data, we need to be sure not to leave ourselves vulnerable to SQL injection attack.

Testing the scripts

The scripts that just read data are pretty easy to verify as long as we have something in our database. As the output of our scripts is posted using echo commands we can see the output in our console if we run using the CLI.

Checking the scripts that write to the data base is slightly more tricky as we need to get some input data from somewhere.

In a deployed environment the webapps would be situated on a server and the input would be provided by http POST.

We can use the PHP Curl library to generate an http POST request to our PHP scripts with the required input data.

To get this to work I found it necessary to place the PHP scripts in a directory under my apache web server. What I actually did, to avoid the necessity of having two copies of each script was to create a symlink under the web server to my project directory.

Install php5-curl from package manager for test page.

Next page: Setting up the Android Development Environment

Back to: Setting up the dev/test environment – Part two


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: