< Case study: British Birds of Prey

I realised that since I took the website down recently to improve it with JavaScript instead of PHP & MySQL. There wasn’t any technical explanation of how I developed the project on line in the first place.

This case study will explain in more detail how I developed the British Birds of Prey website project. The identifying tool is an integral component of the website. I built the project around the identifying tool.

Why British Birds of Prey?

As a child, I grew up around animals, and it was part of the family life. I always had a transient interest in birds. This project served two purposes. The first one was to fulfil the criteria for an university web thesis project. The second one was to improve my own birding knowledge.

Answering questions and collecting data

Before I could devise how to develop the identifying tool, I needed to find the right questions. To collect the data required me to find out the commonality between all species. I had narrowed it down to the following:

  1. How many species (birds of prey) are in Britain & Ireland?
  2. Types of birds of prey (owls, falcons, eagles and etc)?
  3. Known locations?
  4. Resident or migratory species?
  5. If migratory, when are they here?

After quite a bit of research, I had arranged a spreadsheet which would answer all the questions. I also chose to narrow down the type of birds of prey to either Owls or Others. The reasoning behind narrowing it down, is to make it easier for people to identify. People will know what an Owl looks like but they may not for a falcon or an eagle.

A spreadsheet covering where British birds of prey can be spotted in UK & Ireland all seasons
I formulated the data into a readable format for when developing the identifying tool.

Using PHP

With progressive enhancement in mind, I chose PHP as a main focus for the identify tool. In case if JavaScript got turned off, this would break the tool if I went down this route. I simplified the steps for a person to identify what they have just seen while outside. The steps are:

  1. Time/Date (Given by phone/browser - no interaction needed)
  2. Select body type of the bird of prey (Only two options - Owls or Others)
  3. Select country (The person has to select which country)
  4. Select region (Same as country)
  5. Show results based on person’s selections

Seasons

As there are migratory species which are not in Britain or Ireland all year around. As an example, it would filter out the Osprey if the selection option was winter. The Osprey is only in Scotland during the summer. See the PHP code below for the seasons.

1
2
3
4
5
6
7
8
9
10
11
// Define the seasons to match with database
$month=DATE("m");
if ($month>="03" && $month <="05") {
$season_option = "spring";
} elseif ($month>="06" && $month<="08") {
$season_option = "summer";
} elseif ($month>="09" && $month<="11") {
$season_option = "Autumn";
} else {
$season_option = "winter";
}

Body type, country and region options

This part is quite simple. The person can select the option for the body type, country and region. The PHP will pull that into a SQL query alongside with the selection of seasons.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$country_option = get('country_option');
$bodytype_option = get('bodytype_option');

// Switch based on country for SQL query
switch ($country_option) {
case 'England':
$region_option = get('region_option_england');
break;
case 'Wales':
$region_option = get('region_option_wales');
break;
case 'Ireland':
$region_option = get('region_option_ireland');
break;
case 'Scotland':
$region_option = get('region_option_scotland');
break;
}

All options selected

This will form the basis for the SQL query which would then provide the results into a page for the user to look at.

1
2
3
4
5
6
7
8
9
// Actual SQL query for identification
$sql_query = "SELECT * from birdOfPrey as bop, region as r, country as c, birdLink as l, seasons as s";

$sql_query = $sql_query . " where bop.birdID = l.birdID and l.regionID = r.regionID and l.countryID = c.countryID and s.seasonID = l.seasonID";

$sql_query = $sql_query ." and s.season = '".$season_option."' and bop.family = '". $bodytype_option ."' and c.country = '".$country_option."' and r.region = '".$region_option."';";

// Collect query details
$result = mysqli_query($link, $sql_query);

This will enquire the MySQL database to look for the following:

  1. Which selected body type?
  2. Is this bird seen in this location?
  3. Is this bird seen in this region?
  4. Is this bird seen during this season of the year?

When the SQL query reaches the end of filtering out of the non-matching ones. It then will display a list of matching results into a results page for the person to browse through.

Database design

For the SQL query to work, I had to design a database where there would be 5 tables. Each table would have an unique ID in each row that got inserted. For example with the birdOfPrey table, the unique ID that I entered for a row would have a number. I would enter the bird species in alphabetical order. The Barn Owl is the first entry with an ID of 1. In that row, it will have details such as common name, latin name, latin family name, page URL and image URL.

You can see the diagram of an entity relationship within the database below:

A database entity relationship diagram showing the birdLink between tables

With each table having their own ID, it allowed me to create combinations into a SQL query. This allowed me to add them all to the birdLink which would assist in with filtering the results.

Conclusion

As you may have seen how the basics of the identifying tool works. I am currently exploring ways of improving this project by using only JavaScript. I’m looking at making this into a progressive web app which would be more useful and to work offline.