This course will be retired on June 1, 2025.
Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Using parts of the Sakila Data to build out a listing of DVD titles we will learn how to communicate using PHP Data Objects or PDO for short. PDO uses object oriented methodology such as classes, objects, methods, and properties to read and write to the database which makes interfacing to databases very clean and efficient.
Important correction:
Type sqlite3 database.db to open SQLite and the database that is discussed in this video. The .open command is not available in the version of SQLite available in Workspaces.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
Now that you know the basics of the PHP
language,
0:01
it's time for us to start talking about
how to integrate with databases.
0:07
For this course, we're going to use a
sample database that is structured to
0:11
fit the construct of a movie rental store.
0:14
This sample database is called the Sakila
Database.
0:17
Using parts of the Sakila data,
0:20
we'll learn how to communicate using PHP
data objects, or PDO for short.
0:22
PDO uses object-oriented methodology, such
as classes, objects, methods,
0:26
and properties to read and write to the
database,
0:32
which makes interfacing to databases very
clean and efficient.
0:35
The table structure that we'll be working
with is large and
0:39
covers not just films, but also actors in
those films,
0:41
film categories, staff, customers,
addresses, and even inventory.
0:45
It's a great example database to use for
0:50
testing and experimentation as you learn
your way around databases.
0:52
But for us, our main focus will be in the
film table.
0:56
Let's take a look at what is inside our
film table.
1:00
If you open up the example workspace, next
to this video,
1:04
you'll see that it starts off with three
files, a database.db file,
1:07
an index.php file and a style sheet or
style.css.
1:12
Our database file is local to the
workspace, so it's great for you to be
1:17
able to play with and experiment and try
out different things with the database.
1:20
It's also SQLite instead of MySQL so it's
very light weight.
1:24
We'll be able to use most of the same
query commands, especially for
1:28
this particular lesson.
1:31
So, if we wanna take a look at the
database, we will here in a second through
1:32
the console, but let's take a look at our
index file and our style sheet.
1:35
Well, really just the index file.
1:39
You'll see it's just a standard HTML file,
with the extension of .php.
1:41
There's actually no PHP code in it at the
moment.
1:46
It is giving us a title, or a header
saying the Sakila Sample Database.
1:49
Then a list of films by title, and you'll
see film one through four.
1:54
If you go over and
1:58
hit Preview, you can see the title starts
off with Sakila Sample Database,
1:59
Films by Title and then, Films One through
Four in an ordered list.
2:04
These are just examples.
2:09
We'll replace that with the code from our
database by the end of this.
2:10
So let's say we wanna take a look at our
database.
2:14
I'm gonna go ahead and open up the console
by clicking on View and
2:17
show console if it's not already visible.
2:21
I don't really need the index file.
2:23
I'm gonna make this a little bit bigger.
2:25
Okay.
2:27
So to go in here, our first command will
be to open up the SQLite console.
2:28
The way we're gonna do that is by typing
the command sqlite and
2:33
then the number 3 for the version here.
2:38
Hit Enter, that's our console.
2:41
And then, we can always type .help, and
2:43
that will give us a list of commands that
we can use.
2:47
We're gonna use a command called
.databases,
2:50
to show what databases are currently
existing in the console.
2:52
I'm gonna hit Ctrl+L to clear the screen,
and then I'm gonna hit .databases.
2:56
Hit Enter, and you'll see that it says,
name is main, and
3:05
then there's no file currently attached.
3:09
So, we're gonna wanna open and
3:11
attach that particular database file
that's in our workspace.
3:12
The way we're gonna do that so we can use
the console, is to type the command .open
3:16
and then the file name, which I can just
type, D-A-T, and hit Tab to complete it.
3:21
Hit Enter, and
3:28
now when we run up through the history
twice, we can see .databases again.
3:29
Hit Enter and you'll see it's actually
linking main to a file.
3:33
Gonna clear my screen again, and then type
a new command .tables.
3:37
So, .t-a-b-l-e-s.
3:41
And then hit Enter, and you will see that
we have several tables.
3:44
Now these tables are going to match up
with what is in
3:47
the Sakila database example on their
website.
3:50
Let's use their website to look at the
structure a little bit more.
3:53
I'm gonna go over, start a new tab, and
then go ahead and
3:57
search for Sakila database.
4:01
All right.
Our first result would be
4:04
the MySQL Sakila Database.
4:05
And then we're gonna look at Structure,
Tables, and then The film Table.
4:09
So you'll see here the film table,
4:18
if you scroll down, all of the columns
that are available to you.
4:20
So those columns that are available are
film ID, title, description, release year,
4:24
so on, so forth.
4:30
If we go back to our index.php in the
preview,
4:31
you'll see that here it'll list the films
one, two, three, and four.
4:37
So those would be the titles.
4:41
So that's really what we need.
4:43
And then we're gonna wanna be able to
click on any four of these, and that then
4:45
link off to a new page that shows the
information to just one of the films.
4:50
You can always reference the Sakila Sample
Database website, and
4:55
I'll put the link in the notes.
4:59
Okay, I don't need this anymore, I'm gonna
click that.
5:01
I'm also gonna go ahead and close this.
5:04
And now that gives us a quick base of what
our database structure looks like,
5:06
what tables we're gonna look at.
5:11
We're really just gonna concentrate on
just the film table, and
5:13
then all of the columns that we're gonna
be able to pull information from.
5:17
Our next step is to connect to the
database using PHP.
5:20
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up