< Case study: British Birds of Prey
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.
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.
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:
- How many species (birds of prey) are in Britain & Ireland?
- Types of birds of prey (owls, falcons, eagles and etc)?
- Known locations?
- Resident or migratory species?
- 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.
I formulated the data into a readable format for when developing the identifying tool.
- Time/Date (Given by phone/browser - no interaction needed)
- Select body type of the bird of prey (Only two options - Owls or Others)
- Select country (The person has to select which country)
- Select region (Same as country)
- Show results based on person’s selections
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.
// Define the seasons to match with database
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.
$country_option = get('country_option');
This will form the basis for the SQL query which would then provide the results into a page for the user to look at.
// Actual SQL query for identification
This will enquire the MySQL database to look for the following:
- Which selected body type?
- Is this bird seen in this location?
- Is this bird seen in this region?
- 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.
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:
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.