Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Setting up the database

From: Code Clinic: PHP

Video: Setting up the database

Before diving into the PHP side of this project, let's take a And it contains two tables.

Setting up the database

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

Show transcript

This video is part of

Image for Code Clinic: PHP
Code Clinic: PHP

28 video lessons · 2308 viewers

David Powers
Author

 

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Code Clinic: PHP.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.