1
00:00:01,860 --> 00:00:08,120
Hi I'm Alena, a PHP teacher at Treehouse.

2
00:00:08,120 --> 00:00:12,740
I'm going to be teaching you how to
connect a basic PHP website to a database.

3
00:00:12,740 --> 00:00:15,980
This will allow you to
dynamically update your web pages

4
00:00:15,980 --> 00:00:17,730
with constantly changing information.

5
00:00:18,830 --> 00:00:21,460
You already know the basics of PHP but

6
00:00:21,460 --> 00:00:27,100
building a PHP driven page is just
the first step in mastering PHP.

7
00:00:27,100 --> 00:00:31,480
The true power of PHP shines when
you connect it to a data source,

8
00:00:31,480 --> 00:00:33,060
like an API or a database.

9
00:00:33,060 --> 00:00:37,150
To introduce you to databases,
we'll start with an example project,

10
00:00:37,150 --> 00:00:39,780
a personal media library.

11
00:00:39,780 --> 00:00:42,280
Currently the application
is pretty simple and

12
00:00:42,280 --> 00:00:45,630
it stores its data in
an associative array.

13
00:00:45,630 --> 00:00:49,750
An associative array can become
extremely hard to maintain.

14
00:00:49,750 --> 00:00:54,220
Since it's just text, we could easily
miss adding a piece of information.

15
00:00:54,220 --> 00:00:56,990
We could also mistype the name of a field,

16
00:00:56,990 --> 00:00:59,370
which would stop our site
from using it at all.

17
00:01:00,710 --> 00:01:05,150
We could handle these issues,
by writing our own interface with PHP.

18
00:01:05,150 --> 00:01:09,420
However, one of the first things you
should learn about programming is,

19
00:01:09,420 --> 00:01:11,230
don't reinvent the wheel.

20
00:01:11,230 --> 00:01:16,670
Use the right tool for the job,
a database is designed to store data.

21
00:01:16,670 --> 00:01:21,190
If you need to store data, like our media
items, a database is a good choice.

22
00:01:22,410 --> 00:01:24,570
Another important thing to consider,

23
00:01:24,570 --> 00:01:28,080
anytime we want to use the data
that stored in an array.

24
00:01:28,080 --> 00:01:33,360
We need to include the entire array,
even if we only need one item.

25
00:01:33,360 --> 00:01:35,528
With twelve items,
this is not really an issue.

26
00:01:35,528 --> 00:01:40,430
But with 1,200 items,
things can really start to slow down.

27
00:01:40,430 --> 00:01:45,110
A database can also give us more advanced
sorting and search capabilities.

28
00:01:46,330 --> 00:01:51,120
We'll refactor that application to use
a database to store the media items.

29
00:01:51,120 --> 00:01:54,900
This will make our application easier and
faster to scale.

30
00:01:54,900 --> 00:01:58,575
It also makes it easier and
faster to add functionality.

31
00:01:58,575 --> 00:02:02,600
We'll be adding search and
pagination functionality to this project.

32
00:02:02,600 --> 00:02:04,020
Using this library data,

33
00:02:04,020 --> 00:02:09,190
we'll learn how to communicate using
php's data objects or pto for short.

34
00:02:10,640 --> 00:02:13,310
Pto uses object oriented methodology,

35
00:02:13,310 --> 00:02:16,670
which makes interacting with
databases very efficient.

36
00:02:16,670 --> 00:02:18,920
We'll be covering everything
you need to know, so

37
00:02:18,920 --> 00:02:22,090
don't be afraid if you don't
understand object oriented programming.

38
00:02:23,780 --> 00:02:25,600
We'll be using relational databases for

39
00:02:25,600 --> 00:02:30,330
this project because it fits well with
the way our data relates to each other.

40
00:02:30,330 --> 00:02:32,650
To learn more about relational databases,

41
00:02:32,650 --> 00:02:36,080
make sure you check the teacher's
notes for more great courses.

42
00:02:36,080 --> 00:02:38,950
For now, let's take a look at
the way our data is structured.

43
00:02:40,080 --> 00:02:44,620
The table structure that we'll be working
with has a media table, a genre table and

44
00:02:44,620 --> 00:02:45,940
a people table.

45
00:02:45,940 --> 00:02:48,970
There is only one genre
assigned to each item, so

46
00:02:48,970 --> 00:02:52,890
we can link directly from the media
table to the genre's table.

47
00:02:52,890 --> 00:02:56,810
On the other hand, there are multiple
people assigned to each item.

48
00:02:56,810 --> 00:03:01,600
So, we link the media and the people
table together, using another table.

49
00:03:01,600 --> 00:03:05,640
That table also tells us which
role that person performs.

50
00:03:05,640 --> 00:03:10,830
For example, the movie Office Space
has a director, Mike Judge and

51
00:03:10,830 --> 00:03:15,010
stars Ron Livingston,
Jennifer Aniston, and David Herman.

52
00:03:16,220 --> 00:03:20,180
This simple structure will allow you
to practice with relational data,

53
00:03:20,180 --> 00:03:21,780
as you learn your way around a database.

54
00:03:22,800 --> 00:03:26,200
For reference, I've also included
a chart in the teacher's notes

55
00:03:26,200 --> 00:03:28,890
that describes the database structure.

56
00:03:28,890 --> 00:03:31,500
Our main focus will be
in the media table and

57
00:03:31,500 --> 00:03:34,760
we'll be pulling supporting
information from the other tables.

58
00:03:34,760 --> 00:03:35,910
Let's take a look at our project.

59
00:03:37,130 --> 00:03:40,630
If you open the workspace attached to
this video, you'll see all the files for

60
00:03:40,630 --> 00:03:41,905
our personal media library.

61
00:03:41,905 --> 00:03:48,036
You'll also see a database file within
the includes folder, named database.db.

62
00:03:48,036 --> 00:03:51,420
Our database file is local
to the workspace, so

63
00:03:51,420 --> 00:03:54,240
it's really great for
you to be able to play with.

64
00:03:54,240 --> 00:03:57,840
Don't be afraid to experiment and try
out different things with the database.

65
00:03:57,840 --> 00:04:00,170
You can always replace
the file with a fresh copy.

66
00:04:01,600 --> 00:04:04,390
In workspaces we'll be using SQlite.

67
00:04:04,390 --> 00:04:07,440
However, most of the query
commands will be the same,

68
00:04:07,440 --> 00:04:10,070
no matter which relational
database you use.

69
00:04:10,070 --> 00:04:12,730
Where SQlite differs,
I'll highlight those differences and

70
00:04:12,730 --> 00:04:14,720
add them to the teacher's notes.

71
00:04:14,720 --> 00:04:18,430
You're also welcome to download the
project files and install them locally.

72
00:04:18,430 --> 00:04:21,740
Within the download,
I've also included a dot sequel file for

73
00:04:21,740 --> 00:04:25,340
importing the data into another database,
such as my sequel.

74
00:04:25,340 --> 00:04:27,380
Check the teachers notes,
if you need more instruction.

75
00:04:28,470 --> 00:04:29,130
When we go over and

76
00:04:29,130 --> 00:04:32,250
hit preview, we can see how
the application works in the browser.

77
00:04:33,450 --> 00:04:34,150
On the home page,

78
00:04:34,150 --> 00:04:37,600
we are showing four random media items,
each time the page is loaded.

79
00:04:38,890 --> 00:04:44,150
We also have the links at the top to
view the library categories, books,

80
00:04:44,150 --> 00:04:45,470
movies and music.

81
00:04:46,530 --> 00:04:50,090
From the category pages we can
also link to the full catalog,

82
00:04:50,090 --> 00:04:52,830
there is also a link to this suggest page.

83
00:04:52,830 --> 00:04:56,890
Which includes a form for
sending a suggestion for a new media item.

84
00:04:56,890 --> 00:05:00,060
After connecting to the database,
we'll add a couple new features.

85
00:05:00,060 --> 00:05:01,800
So, let's take a look at
the finished site as well.

86
00:05:02,810 --> 00:05:05,110
First of all we'll be
adding a search feature.

87
00:05:05,110 --> 00:05:09,310
Can you imagine browsing the internet
without the ability to search?

88
00:05:09,310 --> 00:05:12,688
The internet is full of information
on almost anything you can think of.

89
00:05:12,688 --> 00:05:16,300
But how would we possibly find it,
without the ability to search?

90
00:05:17,450 --> 00:05:21,440
In this course we'll be introducing you to
some of the basic components of search.

91
00:05:21,440 --> 00:05:24,970
By adding the ability to
search the media library.

92
00:05:24,970 --> 00:05:28,950
Another useful item as our
catalog grows is pagination.

93
00:05:28,950 --> 00:05:33,920
Listing a million items on a single page
would make for a really slow loading page.

94
00:05:33,920 --> 00:05:37,760
Can you imagine trying to shop on Amazon
if every single item was on one page?

95
00:05:38,870 --> 00:05:42,360
Pagination allows us to split
the items across multiple pages and

96
00:05:42,360 --> 00:05:44,610
navigate between those pages.

97
00:05:44,610 --> 00:05:50,210
It's kind of like the old adage, how do
you eat an elephant, one bite at a time.

98
00:05:50,210 --> 00:05:54,000
Pagination allows us to give our
users the data in bite sized chunks.

99
00:05:55,220 --> 00:05:58,940
Finally, we'll use our database to
simplify our form drop downs as well.

100
00:06:00,300 --> 00:06:06,070
All this code- Will be
simplified into this function.

101
00:06:07,900 --> 00:06:11,510
Currently all the data about
the items used in our application

102
00:06:11,510 --> 00:06:13,360
is stored in an array.

103
00:06:13,360 --> 00:06:18,130
Moving the data to a database will give
us more flexibility for organization.

104
00:06:18,130 --> 00:06:21,030
And allows us to select
only part of the data

105
00:06:21,030 --> 00:06:24,440
without having to loop through
each item in the array.

106
00:06:24,440 --> 00:06:27,280
This becomes even more important
as our data set grows.

107
00:06:28,530 --> 00:06:31,720
Our first step is to look
at the php data object

108
00:06:31,720 --> 00:06:33,720
to set up the connection to the database.

109
00:06:33,720 --> 00:06:34,580
So let's get started