Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Before diving into the PHP side of this project, let's take a look at how the raw data will be stored in a database. I'm using MySQL and phpMyAdmin, but you can use the database of your choice. Instead of going through the database setup step by step, I'll focus on the decisions I made and why. By the way, if you need help with creating database tables and user accounts in MySQL, take a look a my course, Up and Running with phpMyAdmin in the lynda.com online training library.
I decided to create a separate database called pendoreille. So let's take a look at that by clicking the pendoreille link on the left here. And it contains two tables. The first table, environmental data, contains the measurements from 2001 to early 2014. The second one, serialized data, only goes up to May 2010 and if I zoom in a little and if we look at the size over here on the right hand side, you can see why I abandoned the serialized data table.
Although it contains less data, it's nearly four times the size of the other table. It's 58.5 megabytes, as opposed to 17 and a half megabytes. So, how did I manage to cram a lot more data into a much smaller space. Well, let's first take a look at the structure of the serialized_data table. It contains four columns. The first one, date_recorded, is a date data type and the other three are all blobs, binary large objects.
I chose blob as the data type because my first instinct had been to gather the data into arrays. And to use the PHP serialize function to store them in the database. Serialized data needs to be stored as a blob. When retrieving the data from the database I used unserialized to convert them back into arrays. And that worked very well, but it bloated the database far too much. After all, the text files that contain all of the historical data up to May 2010 are only half the size, about 29 megabytes.
It's not the data type that caused the bloat but the extra information that's stored with a serialized array. So, let's take a look at the other table, environmental data. This also contains four columns. The first one, data recorded, is data data type. The other three are text data types. So, this table uses text, rather than blob. And if we browse the contents of the table. And then scroll down. You can see that the raw data is stored as comma-separated strings.
All of the white space has been stripped out to make the data as compact as possible. And with some dates, the value is null because there was no data available for that particular date. So, let's just go over the table structure again. Date recorded uses the date data type and it's the table's primary key but it's not auto indexed. The air_temp, bar_press and wind_speed are all text data types and the default value is null because some dates don't have any records.
You don't need the serialized data table, I've got that purely here to show you the comparative size. I also set up two user accounts for the pendoreille database. So, let's just take a look at the user accounts, go to Users, and here are the two accounts, there's pend admin and pend user. Pend Admin is used to insert and update the data, so has all privileges. Pend User will be used for the web service, so it's restricted to the select privilege. That will avoid the danger of data being corrupted by a malicious attacker.
If you want to replicate this setup in your own MySQL database, you can use pendoreille.sql in the Exercise Files for this video. Just load the homepage of phpMyAdmin, then click the Import tab at the top center, and then choose File. You'll need to select pendoreille.sql, and then, when you've selected that, go down to the bottom and click Go. That will create the database called pendoreille, set up the structure of the environmental data table, and create the two user accounts with the ultra secret password lynda all in lower case.
When you got the database organized, the next step is to gather the historical data from the pendoreille website
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 101019 Viewers
61 Video lessons · 87812 Viewers
71 Video lessons · 71686 Viewers
56 Video lessons · 103575 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.