Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Getting to Know the Project

6:35 with Alena Holligan

Building a php-driven page is just the first step in mastering PHP. The true power of PHP shines when you connect it to a data source like an API or a Database. In this course, we’ll refactor an application to use a database to store the information. This will make our application easier and faster to scale. It will also make it easier and faster to add functionality like the search and pagination we’ll also be adding in this project.

Teacher's Notes
Video Transcript
Downloads

Learn More About Relational Databases

https://teamtreehouse.com/library/database-foundations

https://teamtreehouse.com/library/sql-basics

Database Diagram

Using the Database Locally

Included in the download is a .sql file for importing the data into another database such as MySQL. Once you have this file, you can choose one of the following options for loading the database into your local dev environment

CAREFUL: These steps will replace all tables in any existing database named "database"!

1. Import Database into MySQL using phpMyAdmin

If you are using MAMP, you can find phpMyAdmin by starting your servers and going to http://localhost/phpMyAdmin or http://localhost:8888/phpMyAdmin

Then choose the import tab and select the downloaded database.sql file.

2. Importing Database into MySQL using Command Line

Step One:

Open a new terminal (Mac) or command line (Windows) window

Step Two:

[PATH_TO_MYSQL] -u [USERNAME] -p < [PATH_TO_SQL_FILE] Hit the Enter Key

Example Mac with MAMP: /applications/MAMP/library/bin/mysql -u root -p < /Downloads/database.sql

Example Windows with MAMP: c:/MAMP/bin/mysql/bin/mysql -u root -p < c:/Downloads/database.sql

Quick Tip: Don’t forget that you can simply drag the file into the terminal window and it will enter the location of the file for you.

Step Three:

You should be prompted with the following line:

Enter password:

Type your password, keep in mind that the letters will not appear, but they are there Hit the enter key.

Quick Tip: By default, the MySQL password for MAMP is "root".

Step Four:

Check if you database was successfully imported Navigate to phpMyAdmin in a browser

http://localhost/phpMyAdmin OR http://localhost:8888/phpMyAdmin

Hi I'm Alena, a PHP teacher at Treehouse. 0:01

I'm going to be teaching you how to connect a basic PHP website to a database. 0:08

This will allow you to dynamically update your web pages 0:12

with constantly changing information. 0:15

You already know the basics of PHP but 0:18

building a PHP driven page is just the first step in mastering PHP. 0:21

The true power of PHP shines when you connect it to a data source, 0:27

like an API or a database. 0:31

To introduce you to databases, we'll start with an example project, 0:33

a personal media library. 0:37

Currently the application is pretty simple and 0:39

it stores its data in an associative array. 0:42

An associative array can become extremely hard to maintain. 0:45

Since it's just text, we could easily miss adding a piece of information. 0:49

We could also mistype the name of a field, 0:54

which would stop our site from using it at all. 0:56

We could handle these issues, by writing our own interface with PHP. 1:00

However, one of the first things you should learn about programming is, 1:05

don't reinvent the wheel. 1:09

Use the right tool for the job, a database is designed to store data. 1:11

If you need to store data, like our media items, a database is a good choice. 1:16

Another important thing to consider, 1:22

anytime we want to use the data that stored in an array. 1:24

We need to include the entire array, even if we only need one item. 1:28

With twelve items, this is not really an issue. 1:33

But with 1,200 items, things can really start to slow down. 1:35

A database can also give us more advanced sorting and search capabilities. 1:40

We'll refactor that application to use a database to store the media items. 1:46

This will make our application easier and faster to scale. 1:51

It also makes it easier and faster to add functionality. 1:54

We'll be adding search and pagination functionality to this project. 1:58

Using this library data, 2:02

we'll learn how to communicate using php's data objects or pto for short. 2:04

Pto uses object oriented methodology, 2:10

which makes interacting with databases very efficient. 2:13

We'll be covering everything you need to know, so 2:16

don't be afraid if you don't understand object oriented programming. 2:18

We'll be using relational databases for 2:23

this project because it fits well with the way our data relates to each other. 2:25

To learn more about relational databases, 2:30

make sure you check the teacher's notes for more great courses. 2:32

For now, let's take a look at the way our data is structured. 2:36

The table structure that we'll be working with has a media table, a genre table and 2:40

a people table. 2:44

There is only one genre assigned to each item, so 2:45

we can link directly from the media table to the genre's table. 2:48

On the other hand, there are multiple people assigned to each item. 2:52

So, we link the media and the people table together, using another table. 2:56

That table also tells us which role that person performs. 3:01

For example, the movie Office Space has a director, Mike Judge and 3:05

stars Ron Livingston, Jennifer Aniston, and David Herman. 3:10

This simple structure will allow you to practice with relational data, 3:16

as you learn your way around a database. 3:20

For reference, I've also included a chart in the teacher's notes 3:22

that describes the database structure. 3:26

Our main focus will be in the media table and 3:28

we'll be pulling supporting information from the other tables. 3:31

Let's take a look at our project. 3:34

If you open the workspace attached to this video, you'll see all the files for 3:37

our personal media library. 3:40

You'll also see a database file within the includes folder, named database.db. 3:41

Our database file is local to the workspace, so 3:48

it's really great for you to be able to play with. 3:51

Don't be afraid to experiment and try out different things with the database. 3:54

You can always replace the file with a fresh copy. 3:57

In workspaces we'll be using SQlite. 4:01

However, most of the query commands will be the same, 4:04

no matter which relational database you use. 4:07

Where SQlite differs, I'll highlight those differences and 4:10

add them to the teacher's notes. 4:12

You're also welcome to download the project files and install them locally. 4:14

Within the download, I've also included a dot sequel file for 4:18

importing the data into another database, such as my sequel. 4:21

Check the teachers notes, if you need more instruction. 4:25

When we go over and 4:28

hit preview, we can see how the application works in the browser. 4:29

On the home page, 4:33

we are showing four random media items, each time the page is loaded. 4:34

We also have the links at the top to view the library categories, books, 4:38

movies and music. 4:44

From the category pages we can also link to the full catalog, 4:46

there is also a link to this suggest page. 4:50

Which includes a form for sending a suggestion for a new media item. 4:52

After connecting to the database, we'll add a couple new features. 4:56

So, let's take a look at the finished site as well. 5:00

First of all we'll be adding a search feature. 5:02

Can you imagine browsing the internet without the ability to search? 5:05

The internet is full of information on almost anything you can think of. 5:09

But how would we possibly find it, without the ability to search? 5:12

In this course we'll be introducing you to some of the basic components of search. 5:17

By adding the ability to search the media library. 5:21

Another useful item as our catalog grows is pagination. 5:24

Listing a million items on a single page would make for a really slow loading page. 5:28

Can you imagine trying to shop on Amazon if every single item was on one page? 5:33

Pagination allows us to split the items across multiple pages and 5:38

navigate between those pages. 5:42

It's kind of like the old adage, how do you eat an elephant, one bite at a time. 5:44

Pagination allows us to give our users the data in bite sized chunks. 5:50

Finally, we'll use our database to simplify our form drop downs as well. 5:55

All this code- Will be simplified into this function. 6:00

Currently all the data about the items used in our application 6:07

is stored in an array. 6:11

Moving the data to a database will give us more flexibility for organization. 6:13

And allows us to select only part of the data 6:18

without having to loop through each item in the array. 6:21

This becomes even more important as our data set grows. 6:24

Our first step is to look at the php data object 6:28

to set up the connection to the database. 6:31

So let's get started 6:33

You need to sign up for Treehouse in order to download course files.

Sign up