1
00:00:00,000 --> 00:00:02,000
[Week 8, Continued]

2
00:00:02,000 --> 00:00:04,000
[David J. Malan] [Harvard University]

3
00:00:04,000 --> 00:00:08,000
[This is CS50.] [CS50.TV]

4
00:00:08,000 --> 00:00:13,000
>> This is CS50, so this is the end of Week 8 here.

5
00:00:13,000 --> 00:00:16,000
We of course had a bit of a hurricane earlier this week,

6
00:00:16,000 --> 00:00:19,000
so right now it is really just you and me in this lecture hall,

7
00:00:19,000 --> 00:00:22,000
but today we continue our conversation about PHP

8
00:00:22,000 --> 00:00:25,000
and about web programming more generally, and we also introduce the idea

9
00:00:25,000 --> 00:00:29,000
of databases, particularly one called MySQL, which is quite popular these days,

10
00:00:29,000 --> 00:00:34,000
in large part because of its scalability as well as because its being free and open source.

11
00:00:34,000 --> 00:00:37,000
>> But first, a look at where we left off last time.

12
00:00:37,000 --> 00:00:40,000
Recall that we were looking at the several Frosh IMs examples,

13
00:00:40,000 --> 00:00:44,000
and this was the hideous form that I came up with some 15+ years ago

14
00:00:44,000 --> 00:00:49,000
in order to have students on campus register for freshman intramural sports 

15
00:00:49,000 --> 00:00:52,000
without actually having to trek anymore across the yard to Wigglesworth 

16
00:00:52,000 --> 00:00:56,000
to slide a physical piece of paper underneath some proctor's door.

17
00:00:56,000 --> 00:00:59,000
Instead we moved everything online, but to do that we needed to make use

18
00:00:59,000 --> 00:01:03,000
of a few technologies, so one, we needed HTML, hypertext markup language,

19
00:01:03,000 --> 00:01:07,000
which again is this markup language with which you make web pages structurally.

20
00:01:07,000 --> 00:01:10,000
>> Using a bit of CSS these days, cascading style sheets,

21
00:01:10,000 --> 00:01:16,000
whereby we use stylizations of the web page using a slightly different syntax,

22
00:01:16,000 --> 00:01:19,000
whereas the HTML was all about the structure thereof.

23
00:01:19,000 --> 00:01:21,000
We also need to introduce a web programming language.

24
00:01:21,000 --> 00:01:25,000
In this case, we'll use PHP, and PHP is going to allow us

25
00:01:25,000 --> 00:01:28,000
to dynamically output content as well as do programmatic things like

26
00:01:28,000 --> 00:01:33,000
sending emails, as was the case on the note we left last week.

27
00:01:33,000 --> 00:01:35,000
>> Recall that the code for this was in 2 parts.

28
00:01:35,000 --> 00:01:38,000
One, we had froshims3.php, 

29
00:01:38,000 --> 00:01:42,000
and this was largely markup with an HTML form inside of it,

30
00:01:42,000 --> 00:01:45,000
a tiny bit of CSS up here in the style attributes

31
00:01:45,000 --> 00:01:48,000
so that the form itself would be centered on the page, but beyond that

32
00:01:48,000 --> 00:01:51,000
we had some representative form inputs, a text field, a checkbox,

33
00:01:51,000 --> 00:01:55,000
some radio buttons, a select menu, and a submit button.

34
00:01:55,000 --> 00:02:01,000
And via this form, we submitted to a file that was apparently called register3.php,

35
00:02:01,000 --> 00:02:04,000
which itself looked a little something like this.

36
00:02:04,000 --> 00:02:08,000
Now, most of the code in register3.php, recall, was all about email.

37
00:02:08,000 --> 00:02:11,000
It did a little bit of validation of the form that was submitted to make sure

38
00:02:11,000 --> 00:02:14,000
that the fields were actually provided that were expected.

39
00:02:14,000 --> 00:02:18,000
Then we called some PHP functions using slightly new syntax,

40
00:02:18,000 --> 00:02:20,000
even though it's borrowed from C.

41
00:02:20,000 --> 00:02:24,000
>> This arrow operator allows us to make use of something called object-oriented programming.

42
00:02:24,000 --> 00:02:27,000
We won't go into that in any detail here, but know for now

43
00:02:27,000 --> 00:02:31,000
it's a way of having functions associated with objects,

44
00:02:31,000 --> 00:02:34,000
which are a special type of structure, as we saw in C.

45
00:02:34,000 --> 00:02:37,000
But for now, just take on faith that this is the correct syntax to use

46
00:02:37,000 --> 00:02:41,000
when using a library like this PHPMailer library.

47
00:02:41,000 --> 00:02:44,000
And then by the end of this file we had dynamically generated an email

48
00:02:44,000 --> 00:02:47,000
that got sent to my jharvard@cs50.net account

49
00:02:47,000 --> 00:02:50,000
from my jharvard@cs50.net account,

50
00:02:50,000 --> 00:02:54,000
and we informed the user accordingly that they had been registered for this sport.

51
00:02:54,000 --> 00:02:57,000
That is pretty much what the Frosh IMs site did all those years ago

52
00:02:57,000 --> 00:03:00,000
when I implemented it, granted, in a different language,

53
00:03:00,000 --> 00:03:02,000
but it shows you perhaps the power that you have 

54
00:03:02,000 --> 00:03:05,000
now that you can express yourself not only programmatically 

55
00:03:05,000 --> 00:03:08,000
at a low level in a language like C but at a much higher level

56
00:03:08,000 --> 00:03:11,000
with these very real world applications like email to actually solve 

57
00:03:11,000 --> 00:03:13,000
some real world problems.

58
00:03:13,000 --> 00:03:16,000
>> Now, of course, even though I use this script to generate some

59
00:03:16,000 --> 00:03:20,000
emails dynamically from jharvard@cs50.net, which is indeed an account

60
00:03:20,000 --> 00:03:23,000
that I have access to, do be quite careful to send

61
00:03:23,000 --> 00:03:26,000
mail only from accounts that are actually your own, 

62
00:03:26,000 --> 00:03:30,000
lest things get you in a bit of hot water in life.

63
00:03:30,000 --> 00:03:35,000
With that said, let's now transition to solving a different problem altogether,

64
00:03:35,000 --> 00:03:37,000
that of retaining states.

65
00:03:37,000 --> 00:03:39,000
Now, what does this actually mean?

66
00:03:39,000 --> 00:03:42,000
HTTP, this hypertext transfer protocol, 

67
00:03:42,000 --> 00:03:45,000
is actually a stateless protocol, and what this means is that

68
00:03:45,000 --> 00:03:48,000
when you pull up something like Google.com and then hit enter

69
00:03:48,000 --> 00:03:51,000
usually your browser has some kind of spinning icon that then

70
00:03:51,000 --> 00:03:54,000
results in some web page being downloaded,

71
00:03:54,000 --> 00:03:57,000
and then that little icon stops spinning, and that indeed suggests

72
00:03:57,000 --> 00:04:02,000
that HTTP has completed some kind of connection to the server and that's it.

73
00:04:02,000 --> 00:04:05,000
HTTP is stateless in the sense that it doesn't maintain

74
00:04:05,000 --> 00:04:08,000
a persistent connection to the server in the same way Skype does

75
00:04:08,000 --> 00:04:11,000
or Gchat does because with HTTP

76
00:04:11,000 --> 00:04:15,000
the assumption is that once you've fetched a web page that's it.

77
00:04:15,000 --> 00:04:18,000
>> Now, in reality these days on sites like Facebook and Google Maps

78
00:04:18,000 --> 00:04:21,000
and Twitter and the like there's a lot more dynamism  whereby 

79
00:04:21,000 --> 00:04:25,000
even after that icon stops spinning you can in fact get more updates

80
00:04:25,000 --> 00:04:29,000
from the server, more tweets, more status updates on Facebook and the like.

81
00:04:29,000 --> 00:04:33,000
But even that is using a technique that we'll talk about in a week or two

82
00:04:33,000 --> 00:04:36,000
known as Ajax using a language called JavaScript,

83
00:04:36,000 --> 00:04:38,000
but at the end of the day, HTTP is still stateless.

84
00:04:38,000 --> 00:04:42,000
And yet if you want to somehow remember things about a user

85
00:04:42,000 --> 00:04:44,000
even after they've disconnected from your server

86
00:04:44,000 --> 00:04:47,000
PHP does afford you a means of doing this

87
00:04:47,000 --> 00:04:52,000
because, as we saw last time, PHP has a number of superglobals, 

88
00:04:52,000 --> 00:04:55,000
and a superglobal is, again, a special global variable

89
00:04:55,000 --> 00:04:59,000
that's handed to you by the web server and by PHP itself.

90
00:04:59,000 --> 00:05:02,000
>> You don't have to do anything to put values in it, 

91
00:05:02,000 --> 00:05:05,000
and among the superglobals we've seen thus far are get and post,

92
00:05:05,000 --> 00:05:08,000
which is where form fields are put automatically for you,

93
00:05:08,000 --> 00:05:11,000
as well as a couple of others that we haven't seen yet.

94
00:05:11,000 --> 00:05:17,000
Inside of $_server are some special variables related to the server itself.

95
00:05:17,000 --> 00:05:22,000
What's the IP address, what protocol, HTTP or HTTPS did you use,

96
00:05:22,000 --> 00:05:25,000
what request method did you use and the like, so there's some interesting, 

97
00:05:25,000 --> 00:05:29,000
juicy details about the server, and in fact, the user in there as well.

98
00:05:29,000 --> 00:05:33,000
There's $_cookie, which is where these things called cookies are stored.

99
00:05:33,000 --> 00:05:36,000
We won't spend time on cookies themselves today,

100
00:05:36,000 --> 00:05:40,000
but know for now that a cookie is just a small piece of information

101
00:05:40,000 --> 00:05:43,000
that a web server can plant on a web browser

102
00:05:43,000 --> 00:05:46,000
and in turn its RAM or its computer's hard drive 

103
00:05:46,000 --> 00:05:49,000
to store information about a user, for instance, their user name

104
00:05:49,000 --> 00:05:52,000
so that they don't have to type it every time they log in or some

105
00:05:52,000 --> 00:05:55,000
unique number or identifier for that user 

106
00:05:55,000 --> 00:05:58,000
so that you don't have to pester them with the same kinds of questions about

107
00:05:58,000 --> 00:06:00,000
preferences in the future, but most of interest

108
00:06:00,000 --> 00:06:02,000
right now is $_session.

109
00:06:02,000 --> 00:06:07,000
>> This superglobal, which, like the others, is handed to you automatically by PHP

110
00:06:07,000 --> 00:06:10,000
when you're writing PHP-based websites

111
00:06:10,000 --> 00:06:13,000
can store anything you want, strings, integers,

112
00:06:13,000 --> 00:06:16,000
floating points, values, arrays, objects, 

113
00:06:16,000 --> 00:06:20,000
really anything that you want, and it allows you to store it in such a way

114
00:06:20,000 --> 00:06:23,000
that even if the user visits you now and then

115
00:06:23,000 --> 00:06:26,000
comes back a minute from now or 5 minutes from now because 

116
00:06:26,000 --> 00:06:28,000
they take their time before clicking some other link

117
00:06:28,000 --> 00:06:32,000
PHP will ensure that whatever you put in that session superglobal

118
00:06:32,000 --> 00:06:37,000
a minute or 5 minutes ago will still be there when the user returns.

119
00:06:37,000 --> 00:06:40,000
And underneath the hood this superglobal is implemented by way of

120
00:06:40,000 --> 00:06:44,000
those things called cookies, but for now, it's just an abstraction

121
00:06:44,000 --> 00:06:47,000
whereby it's sort of the programmatic equivalent of a shopping cart.

122
00:06:47,000 --> 00:06:50,000
Whatever you, the programmer, put in that 

123
00:06:50,000 --> 00:06:53,000
superglobal associative array will be there some number of minutes later

124
00:06:53,000 --> 00:06:59,000
until you delete it or until the user quits his or her browser altogether.

125
00:06:59,000 --> 00:07:02,000
>> Let's take a look at an example of how this thing is actually used.

126
00:07:02,000 --> 00:07:07,000
In counter.php among today's pieces of code

127
00:07:07,000 --> 00:07:09,000
we have the following line.

128
00:07:09,000 --> 00:07:13,000
At the start of this file we have a bunch of blue comments, which are uninteresting for now.

129
00:07:13,000 --> 00:07:15,000
But in line 13 we have a new line, 

130
00:07:15,000 --> 00:07:18,000
session_start, and that actually does exactly what it says.

131
00:07:18,000 --> 00:07:20,000
It starts sessions.

132
00:07:20,000 --> 00:07:25,000
It enables you to use that big superglobal $_session, and it's as simple as that.

133
00:07:25,000 --> 00:07:30,000
Now, if we proceed to look at line 16, let's try to figure out what this web page is going to do.

134
00:07:30,000 --> 00:07:35,000
If (isset ($_SESSION ["counter"]) then go ahead

135
00:07:35,000 --> 00:07:39,000
and store in the counter variable, lowercase counter,

136
00:07:39,000 --> 00:07:42,000
$_SESSION ["counter"].

137
00:07:42,000 --> 00:07:45,000
This seems to be declaring a local variable called counter

138
00:07:45,000 --> 00:07:48,000
inside of which it's putting a copy of whatever is inside of the superglobal

139
00:07:48,000 --> 00:07:52,000
called session at the location "counter."

140
00:07:52,000 --> 00:07:56,000
Else, apparently, this little local variable counter, is initialized to 0.

141
00:07:56,000 --> 00:08:01,000
>> But then a few lines later in 26 notice that the session's copy of counter,

142
00:08:01,000 --> 00:08:06,000
its key, has a new value assigned which is its current value plus 1.

143
00:08:06,000 --> 00:08:09,000
In short, this file seems to be updating

144
00:08:09,000 --> 00:08:13,000
a counter that's stored inside of the session superglobal

145
00:08:13,000 --> 00:08:16,000
by incrementing it by 1, but it first retains a copy of the previous value

146
00:08:16,000 --> 00:08:20,000
by storing it in a local variable called $counter,

147
00:08:20,000 --> 00:08:22,000
and then down here let's see what remains.

148
00:08:22,000 --> 00:08:24,000
It turns out it's pretty much just HTML.

149
00:08:24,000 --> 00:08:29,000
At the bottom of this page we see in line 37 that I have visited this site

150
00:08:29,000 --> 00:08:33,000
counter number of times, so there's a couple interesting features here.

151
00:08:33,000 --> 00:08:36,000
One, this is clearly a variable, but it doesn't suffice to just put 

152
00:08:36,000 --> 00:08:39,000
$counter in the body of your HTML because of course

153
00:08:39,000 --> 00:08:43,000
if it's just there among your HTML PHP is going to assume that's just HTML.

154
00:08:43,000 --> 00:08:48,000
You literally want $counter to be printed on the screen.

155
00:08:48,000 --> 00:08:51,000
>> But instead by dropping into PHP mode

156
00:08:51,000 --> 00:08:55,000
with this piece of syntax we can dynamically insert a value here 

157
00:08:55,000 --> 00:08:58,000
very similar in spirit to what we did last time with

158
00:08:58,000 --> 00:09:00,000
inserting values into strings.

159
00:09:00,000 --> 00:09:04,000
In fact, this is just a shorthand notation for saying something like this literally,

160
00:09:04,000 --> 00:09:12,000
print ($counter) or even something like printf (%s, counter),

161
00:09:12,000 --> 00:09:14,000
or even, as you may have seen online or in textbooks,

162
00:09:14,000 --> 00:09:17,000
there's a function in PHP called echo 

163
00:09:17,000 --> 00:09:20,000
which does the same thing, and all of those are just longer winded ways

164
00:09:20,000 --> 00:09:25,000
of saying <?=.

165
00:09:25,000 --> 00:09:28,000
In this one case you do not need to put

166
00:09:28,000 --> 00:09:30,000
the word PHP after the question mark.

167
00:09:30,000 --> 00:09:34,000
This is shorthand notation for, again, what we just saw a moment ago

168
00:09:34,000 --> 00:09:37,000
which is echoing some value.

169
00:09:37,000 --> 00:09:39,000
>> Let's see what the end result of this actually is.

170
00:09:39,000 --> 00:09:43,000
Let me go over into our counter.php file,

171
00:09:43,000 --> 00:09:47,000
and we'll see that David just made a mistake by playing with the code there.

172
00:09:47,000 --> 00:09:50,000
Let's go fix whatever he screwed up,

173
00:09:50,000 --> 00:09:54,000
and the error seems to be there, gone, on line 37.

174
00:09:54,000 --> 00:09:59,000
According to the top of this page I have visited this site 0 times.

175
00:09:59,000 --> 00:10:02,000
Well, let's go ahead now, and at the top of the browser click on the

176
00:10:02,000 --> 00:10:05,000
reload icon, and I click reload,

177
00:10:05,000 --> 00:10:12,000
and now I've visited the site 1 time, 2, 3, 4, 5, 6, 7, 8.

178
00:10:12,000 --> 00:10:16,000
And indeed, if we look at the source of this page the actual source code is changing,

179
00:10:16,000 --> 00:10:19,000
and notice the complete absence of any PHP, and that's because 

180
00:10:19,000 --> 00:10:23,000
PHP code is evaluated or interpreted server side,

181
00:10:23,000 --> 00:10:27,000
and so that means that the output of the PHP script is what's ultimately sent to the browser,

182
00:10:27,000 --> 00:10:31,000
which in this case is some raw HTML and some raw text.

183
00:10:31,000 --> 00:10:33,000
What's going on here?

184
00:10:33,000 --> 00:10:37,000
>> Well, with relatively few lines of code I am able to store

185
00:10:37,000 --> 00:10:40,000
persistently over the course of several seconds, or if we waited long enough,

186
00:10:40,000 --> 00:10:44,000
minutes, even hours, some value in a way that makes HTTP

187
00:10:44,000 --> 00:10:47,000
seem stateful as though we've retained 

188
00:10:47,000 --> 00:10:51,000
this connection to the server, and it's just remembering what I told it last time,

189
00:10:51,000 --> 00:10:55,000
but in reality there's a whole bunch of complexity going on underneath the hood

190
00:10:55,000 --> 00:10:59,000
involving cookies that's enabling PHP to give me this illusion 

191
00:10:59,000 --> 00:11:02,000
of this shopping cart-like feature.

192
00:11:02,000 --> 00:11:05,000
For now, a trivial example where we're just storing an integer,

193
00:11:05,000 --> 00:11:08,000
but that feature will come back to be of great value

194
00:11:08,000 --> 00:11:10,000
when we start talking about more complex projects, 

195
00:11:10,000 --> 00:11:12,000
among them problem set 7.

196
00:11:12,000 --> 00:11:16,000
This is your very last problem set in CS50.

197
00:11:16,000 --> 00:11:19,000
I know, it's so sad, but what you'll find is that we're going to conclude

198
00:11:19,000 --> 00:11:22,000
this part of the semester by actually transitioning 

199
00:11:22,000 --> 00:11:25,000
from the context of C certainly to the context of PHP

200
00:11:25,000 --> 00:11:27,000
but while using some of the very same fundamentals 

201
00:11:27,000 --> 00:11:29,000
we've talked about for some time. 

202
00:11:29,000 --> 00:11:33,000
>> The objective with pset 7 is to implement CS50 Finance,

203
00:11:33,000 --> 00:11:37,000
which is your own version of Yahoo Finance or Google Finance

204
00:11:37,000 --> 00:11:40,000
or even Etrade.com whereby you have the ability to 

205
00:11:40,000 --> 00:11:43,000
look up stock prices for given symbols, but even more than that

206
00:11:43,000 --> 00:11:48,000
you have the ability to "buy" and "sell" stocks

207
00:11:48,000 --> 00:11:51,000
that are traded on various stock exchanges because as this home page 

208
00:11:51,000 --> 00:11:55,000
here suggests, which is really the extent to which we've begun

209
00:11:55,000 --> 00:11:59,000
the problem set for you, you have a login form that's asking for a user name and a password.

210
00:11:59,000 --> 00:12:03,000
It has a submit button, but thereafter, as we'll eventually see,

211
00:12:03,000 --> 00:12:06,000
there is nothing really going on underneath the hood yet because it

212
00:12:06,000 --> 00:12:09,000
remains for you to implement the ability to register new users,

213
00:12:09,000 --> 00:12:12,000
the ability to buy stocks, to sell stocks,

214
00:12:12,000 --> 00:12:14,000
to actually look up current stock prices.

215
00:12:14,000 --> 00:12:17,000
>> And indeed, this will be as real world as possible because we did

216
00:12:17,000 --> 00:12:20,000
include a bit of code that will allow you with a single function 

217
00:12:20,000 --> 00:12:25,000
to query Yahoo Finance, which wonderfully makes available free data

218
00:12:25,000 --> 00:12:31,000
for looking up stock prices based on the stock symbol or ticker symbol,

219
00:12:31,000 --> 00:12:34,000
and you get back the current stock price of the day.

220
00:12:34,000 --> 00:12:37,000
The data you're actually seeing in this particular pset will be

221
00:12:37,000 --> 00:12:40,000
about as real world as it can get so that you're actually interfacing

222
00:12:40,000 --> 00:12:43,000
with real world stocks, real world prices,

223
00:12:43,000 --> 00:12:47,000
and we shall see how much money you can make perhaps

224
00:12:47,000 --> 00:12:51,000
over the next several days of playing with your own problem set.

225
00:12:51,000 --> 00:12:55,000
>> But let's first set the stage for how to design something that's certainly more complicated

226
00:12:55,000 --> 00:12:59,000
than counter.php, that's more complicated than any of the Frosh IMs examples thus far,

227
00:12:59,000 --> 00:13:02,000
and let's try to introduce a few paradigms here that allow us

228
00:13:02,000 --> 00:13:06,000
both for pset 7 and maybe for your final project if you do something web based

229
00:13:06,000 --> 00:13:11,000
to keep your code well organized, to keep yourself sane,

230
00:13:11,000 --> 00:13:15,000
and to take a step toward collaborating, whether in CS50's final project

231
00:13:15,000 --> 00:13:18,000
or beyond if you continue to program something in the future.

232
00:13:18,000 --> 00:13:21,000
There's this general design paradigm 

233
00:13:21,000 --> 00:13:24,000
in computer science and in software development more generally

234
00:13:24,000 --> 00:13:27,000
known as MVC, model view controller,

235
00:13:27,000 --> 00:13:30,000
and this is a silly acronym that describes a very nice idea, 

236
00:13:30,000 --> 00:13:34,000
which is the separation of different aspects of a program,

237
00:13:34,000 --> 00:13:39,000
specifically keeping separate the logic or the business logic of a website

238
00:13:39,000 --> 00:13:42,000
so that anything that involves things like 

239
00:13:42,000 --> 00:13:45,000
calling functions and querying databases and the like

240
00:13:45,000 --> 00:13:48,000
happens not among your HTML

241
00:13:48,000 --> 00:13:51,000
but rather in separate files, and indeed, there's this one file 

242
00:13:51,000 --> 00:13:54,000
typically that you have called the controller

243
00:13:54,000 --> 00:13:56,000
that's really the brains behind the operation, and we'll see an example 

244
00:13:56,000 --> 00:13:58,000
of this in just a moment.

245
00:13:58,000 --> 00:14:01,000
>> There's a model which is programming code

246
00:14:01,000 --> 00:14:05,000
that does the talking to your databases, that talks to Yahoo Finance and the like,

247
00:14:05,000 --> 00:14:08,000
and then there's the V in MVC, the views,

248
00:14:08,000 --> 00:14:11,000
all of the stuff that's related to aesthetics, the files that actually contain

249
00:14:11,000 --> 00:14:14,000
your HTML, maybe your CSS and the like.

250
00:14:14,000 --> 00:14:17,000
The idea here, as this picture suggests, is that the controller

251
00:14:17,000 --> 00:14:21,000
is the file, as we'll soon see and as you'll particularly see in pset 7,

252
00:14:21,000 --> 00:14:24,000
that the world talks to via their web browsers.

253
00:14:24,000 --> 00:14:27,000
That is the file that gets visited on the public internet,

254
00:14:27,000 --> 00:14:30,000
but the controller talks to potentially a model,

255
00:14:30,000 --> 00:14:34,000
which is one or more other files that contain code related to data,

256
00:14:34,000 --> 00:14:37,000
code related to databases and the like, and then it talks to 

257
00:14:37,000 --> 00:14:40,000
the controller one or more other files known as views,

258
00:14:40,000 --> 00:14:43,000
which are the aesthetics of a web page, templates of sorts,

259
00:14:43,000 --> 00:14:47,000
that might take some data as input, but at the end of the day

260
00:14:47,000 --> 00:14:50,000
the only logic inside of a view should be the rendering of that data,

261
00:14:50,000 --> 00:14:53,000
iterating over a loop and actually spitting out some 

262
00:14:53,000 --> 00:14:56,000
HTML-based rendition thereof or even something like a PDF.

263
00:14:56,000 --> 00:14:59,000
>> What's nice about MVC is that you can have different views

264
00:14:59,000 --> 00:15:02,000
based on the type of device, based on the type of file format that you actually 

265
00:15:02,000 --> 00:15:04,000
want to show to the user.

266
00:15:04,000 --> 00:15:10,000
Let's take a look at a few progressively more complex and well designed examples

267
00:15:10,000 --> 00:15:13,000
by starting first with version 0 here.

268
00:15:13,000 --> 00:15:16,000
Let me go ahead and open up in our MVC directory today

269
00:15:16,000 --> 00:15:21,000
a file called index.php in directory 0.

270
00:15:21,000 --> 00:15:26,000
Notice this is a super simple and very underwhelming website

271
00:15:26,000 --> 00:15:29,000
that's sort of version 0 of a homepage for CS50,

272
00:15:29,000 --> 00:15:32,000
and notice how we have a link to Lectures, we have a link to Syllabus, 

273
00:15:32,000 --> 00:15:35,000
and if I follow the link to Lectures notice that the URL

274
00:15:35,000 --> 00:15:39,000
up top is going to change to lectures.php.

275
00:15:39,000 --> 00:15:44,000
If I then follow the link to Week 1 notice that the URL changes to week1.php.

276
00:15:44,000 --> 00:15:46,000
There seems to be a fairly simple hierarchical structure here.

277
00:15:46,000 --> 00:15:49,000
>> Let's take a quick look underneath the hood at how this is laid out,

278
00:15:49,000 --> 00:15:53,000
and indeed, if I look at index.php it's pretty simple.

279
00:15:53,000 --> 00:15:57,000
In fact, even though I called this a PHP file there's no actual programming code.

280
00:15:57,000 --> 00:16:01,000
There's a comment that I wrote here in PHP just so the user doesn't end up seeing it. 

281
00:16:01,000 --> 00:16:05,000
Of course, as before, anything that's in between PHP tags 

282
00:16:05,000 --> 00:16:08,000
gets interpreted, even if it's a comment, and to interpret a comment

283
00:16:08,000 --> 00:16:11,000
means just to throw it away at the end of the day and not actually 

284
00:16:11,000 --> 00:16:15,000
send it to the browser, so everything in here is just aesthetics. 

285
00:16:15,000 --> 00:16:20,000
If I open up similarly lectures.php this too is just a hard coded file.

286
00:16:20,000 --> 00:16:23,000
It happens to be called something .php, 

287
00:16:23,000 --> 00:16:27,000
but it really is just .html, and week1.php, week2.php

288
00:16:27,000 --> 00:16:31,000
similarly are just markup, so there's a bunch of shortcomings of this design. 

289
00:16:31,000 --> 00:16:33,000
One, it's a huge amount of copy/paste.

290
00:16:33,000 --> 00:16:37,000
Even though the only thing that's changing among these files is the unordered list,

291
00:16:37,000 --> 00:16:41,000
the li tags, I nonetheless have doc type, HTML, head, 

292
00:16:41,000 --> 00:16:44,000
title, close body, close HTML and more

293
00:16:44,000 --> 00:16:47,000
in every single file, which means that if I ever want to restructure

294
00:16:47,000 --> 00:16:50,000
this web page or restylize it I have to go in and change

295
00:16:50,000 --> 00:16:54,000
all these files manually or with some massive find and replace.

296
00:16:54,000 --> 00:17:01,000
>> Let's take a step toward a smarter, longer thinking design in version 1 here

297
00:17:01,000 --> 00:17:04,000
whereby as per the read me that we've included so that you can play along with

298
00:17:04,000 --> 00:17:07,000
these more leisurely at home notice that we have here

299
00:17:07,000 --> 00:17:10,000
a summary of the files in version 1 of this site, 

300
00:17:10,000 --> 00:17:13,000
and it seems that I've taken it upon myself to factor out 

301
00:17:13,000 --> 00:17:17,000
some common code, header.php and footer.php.

302
00:17:17,000 --> 00:17:20,000
Well, let's take a look at what's inside the first of those.

303
00:17:20,000 --> 00:17:23,000
Header.php looks familiar, 

304
00:17:23,000 --> 00:17:26,000
but notice where does it get cut off?

305
00:17:26,000 --> 00:17:30,000
Right after line 19, so that's everything that was common

306
00:17:30,000 --> 00:17:33,000
from the files index.php, lectures.php,

307
00:17:33,000 --> 00:17:36,000
week1 and week2.php from the previous example.

308
00:17:36,000 --> 00:17:40,000
What I did was copy and cut everything that was common to all those files,

309
00:17:40,000 --> 00:17:44,000
put it in a separate header file, and similarly in footer.php

310
00:17:44,000 --> 00:17:48,000
did I apply the same principle whereby the only interesting lines

311
00:17:48,000 --> 00:17:52,000
in footer.php are these two, close body and close HTML.

312
00:17:52,000 --> 00:17:55,000
>> But what this means now is that in the new version 

313
00:17:55,000 --> 00:17:59,000
of index.php notice how much simpler it can get.

314
00:17:59,000 --> 00:18:02,000
Granted, a bit more cryptic looking, a little less intuitive

315
00:18:02,000 --> 00:18:06,000
to follow top to bottom, but my God, all of that redundancy is now gone.

316
00:18:06,000 --> 00:18:10,000
We require using a PHP function literally called require up top,

317
00:18:10,000 --> 00:18:15,000
which is very reminiscent, recall, of C's #include mechanism. 

318
00:18:15,000 --> 00:18:17,000
We require header.php at the top.

319
00:18:17,000 --> 00:18:20,000
We require footer.php at the bottom, and the only thing that is different 

320
00:18:20,000 --> 00:18:25,000
or special about this file is the content that's meant to be unique to it.

321
00:18:25,000 --> 00:18:29,000
If I then go into, say, lectures.php, the same principle applies.

322
00:18:29,000 --> 00:18:32,000
Again, some comments up top, but then I require header, require footer,

323
00:18:32,000 --> 00:18:35,000
and in between it's only the content that's actually changed.

324
00:18:35,000 --> 00:18:38,000
And if we looked into Week 1 and Week 2 we'd see

325
00:18:38,000 --> 00:18:42,000
that the same principle had been applied there.

326
00:18:42,000 --> 00:18:44,000
Well, we're not quite done there.

327
00:18:44,000 --> 00:18:48,000
>> Let's take a look at version 2, which has a similar structure,

328
00:18:48,000 --> 00:18:50,000
but notice now I've introduced something else.

329
00:18:50,000 --> 00:18:53,000
In line 10 I've introduced helpers.php,

330
00:18:53,000 --> 00:18:55,000
which apparently contains helper functions.

331
00:18:55,000 --> 00:18:58,000
A helper function is generally a relatively short function 

332
00:18:58,000 --> 00:19:01,000
that you write to help you out in various places,

333
00:19:01,000 --> 00:19:04,000
and let's see what's inside of helpers.php.

334
00:19:04,000 --> 00:19:07,000
In this case, it looks like it has 2 functions.

335
00:19:07,000 --> 00:19:10,000
Recall from the other day with our cube example

336
00:19:10,000 --> 00:19:13,000
you can define your own functions in PHP, and what I've done now is I've 

337
00:19:13,000 --> 00:19:17,000
defined functions called render footer and render header,

338
00:19:17,000 --> 00:19:21,000
the first of which takes a parameter called data,

339
00:19:21,000 --> 00:19:25,000
whose default value is an empty array, as suggested there,

340
00:19:25,000 --> 00:19:29,000
and we can actually write this even more succinctly in the latest version of PHP

341
00:19:29,000 --> 00:19:32,000
by saying open square bracket, closed square bracket.

342
00:19:32,000 --> 00:19:35,000
That means an empty array of size 0 but nonetheless an array. 

343
00:19:35,000 --> 00:19:38,000
>> This extract function is a little special in that

344
00:19:38,000 --> 00:19:41,000
what it does is it takes as its argument an associative array

345
00:19:41,000 --> 00:19:45,000
that has 0 or more key value pairs, and if you have a key of foo

346
00:19:45,000 --> 00:19:48,000
and a value of bar the extract function 

347
00:19:48,000 --> 00:19:51,000
creates a situation in which now, as of line 11,

348
00:19:51,000 --> 00:19:57,000
you have a local variable called $foo whose value is bar.

349
00:19:57,000 --> 00:19:59,000
And if you had more keys and values in the data array,

350
00:19:59,000 --> 00:20:03,000
similarly would they be extracted into the local scope

351
00:20:03,000 --> 00:20:06,000
or name space so that footer.php and 

352
00:20:06,000 --> 00:20:09,000
the same idea down here so that header.php

353
00:20:09,000 --> 00:20:12,000
have access to those variables.

354
00:20:12,000 --> 00:20:15,000
In fact, let me open up again header.php

355
00:20:15,000 --> 00:20:18,000
and draw attention to now what it looks like in this version.

356
00:20:18,000 --> 00:20:22,000
>> Rather than hard coding CS50 as the title for every single page

357
00:20:22,000 --> 00:20:24,000
notice the dynamism that's possible now.

358
00:20:24,000 --> 00:20:29,000
In line 5 I'm echoing a title variable,

359
00:20:29,000 --> 00:20:34,000
but first I'm passing that title variable to a function called htmlspecialchars.

360
00:20:34,000 --> 00:20:38,000
A stupid name for a function, long as it is, but it really does what it says.

361
00:20:38,000 --> 00:20:41,000
It ensures that any special characters

362
00:20:41,000 --> 00:20:46,000
in the string that's been passed in are properly escaped HTML.

363
00:20:46,000 --> 00:20:49,000
This is actually a way of avoiding something called a cross site scripting attack

364
00:20:49,000 --> 00:20:52,000
whereby someone can maliciously or accidentally

365
00:20:52,000 --> 00:20:55,000
inject their own HTML into your website

366
00:20:55,000 --> 00:20:59,000
by pasting into some form, for instance, 

367
00:20:59,000 --> 00:21:02,000
something that you weren't quite expecting, particularly JavaScript code,

368
00:21:02,000 --> 00:21:05,000
as we'll talk about in a week or two's time.

369
00:21:05,000 --> 00:21:08,000
>> This now header.php, it's a view

370
00:21:08,000 --> 00:21:12,000
in the sense that it allows you to view aesthetically the contents of some data set.

371
00:21:12,000 --> 00:21:14,000
But more specifically, it's a template.

372
00:21:14,000 --> 00:21:19,000
This is sort of a blueprint now of what we want the header of every page to look like,

373
00:21:19,000 --> 00:21:23,000
but there's some dynamism in that we want the title to be dynamically inserted

374
00:21:23,000 --> 00:21:26,000
based on the title variable

375
00:21:26,000 --> 00:21:30,000
that was extracted when we called, again,

376
00:21:30,000 --> 00:21:33,000
the render header function.

377
00:21:33,000 --> 00:21:36,000
Now, if we looked at render footer, there's actually not much use of that right now

378
00:21:36,000 --> 00:21:40,000
because in footer.php there's no dynamism whatsoever.

379
00:21:40,000 --> 00:21:43,000
There could be, but at the moment it's a hard coded list of 2 tags, 

380
00:21:43,000 --> 00:21:46,000
but the same idea applies, so that actually suggests why 

381
00:21:46,000 --> 00:21:49,000
did we waste time having a render header and a render footer function?

382
00:21:49,000 --> 00:21:52,000
Let me go instead now into version 3,

383
00:21:52,000 --> 00:21:56,000
and in version 3 in helpers I decided to simplify it even more.

384
00:21:56,000 --> 00:21:58,000
>> Let me have one render function.

385
00:21:58,000 --> 00:22:02,000
Let me have it take another argument, this time called template,

386
00:22:02,000 --> 00:22:05,000
which is meant to be the name of a template,

387
00:22:05,000 --> 00:22:11,000
and then I will concatenate presumptuously .php to that variable's value,

388
00:22:11,000 --> 00:22:17,000
and then if it exists foo.php, bar.php or header.php and footer.php,

389
00:22:17,000 --> 00:22:20,000
then I'm going to go ahead and extract the variable data

390
00:22:20,000 --> 00:22:23,000
and then require that path.

391
00:22:23,000 --> 00:22:29,000
In other words, to use this now, if I open up index.php

392
00:22:29,000 --> 00:22:32,000
notice that I don't call render header anymore.

393
00:22:32,000 --> 00:22:36,000
I just call render, but I pass in a quoted value of header 

394
00:22:36,000 --> 00:22:39,000
to make clear which template I actually want to load.

395
00:22:39,000 --> 00:22:41,000
>> Then over here notice what I'm doing.

396
00:22:41,000 --> 00:22:44,000
I'm passing in dynamically a key of title,

397
00:22:44,000 --> 00:22:47,000
a value of CS50, and this too, as we saw before,

398
00:22:47,000 --> 00:22:51,000
could be made more succinct in the latest version of PHP

399
00:22:51,000 --> 00:22:54,000
where I can replace the array function with square brackets,

400
00:22:54,000 --> 00:22:57,000
which I propose is even more readable and certainly 

401
00:22:57,000 --> 00:22:59,000
a little easier to type.

402
00:22:59,000 --> 00:23:02,000
And of course, with the render footer call at the bottom,

403
00:23:02,000 --> 00:23:05,000
we don't bother passing in a second argument at all, no associative array,

404
00:23:05,000 --> 00:23:07,000
because there's nothing dynamic inside of that footer.

405
00:23:07,000 --> 00:23:10,000
It's just some close tags for HTML.

406
00:23:10,000 --> 00:23:14,000
Good, we're taking steps towards really cleaning things up here,

407
00:23:14,000 --> 00:23:17,000
but let me open up 2 final examples.

408
00:23:17,000 --> 00:23:21,000
This one, number 4, notice that I've made a conscious decision now

409
00:23:21,000 --> 00:23:26,000
to improve upon the previous example by finally using some hierarchy to my files.

410
00:23:26,000 --> 00:23:29,000
>> Notice that in this summary, in this read me, I've introduced

411
00:23:29,000 --> 00:23:32,000
an includes directory and a templates directory

412
00:23:32,000 --> 00:23:35,000
whose contents are going to be the things I want to include

413
00:23:35,000 --> 00:23:38,000
and the templates that I want to render, respectively.

414
00:23:38,000 --> 00:23:42,000
This is really me being anal and trying to keep things tidy, 

415
00:23:42,000 --> 00:23:45,000
keep related files together, but the end result 

416
00:23:45,000 --> 00:23:48,000
is that we now have a slightly tidier setup, but we have to remember now

417
00:23:48,000 --> 00:23:51,000
in, for instance, index.php 

418
00:23:51,000 --> 00:23:55,000
when we require the file helpers.php

419
00:23:55,000 --> 00:24:01,000
we have to now require it via includes/helpers.php

420
00:24:01,000 --> 00:24:06,000
rather than just saying helpers.php because now it's actually in a sub directory.

421
00:24:06,000 --> 00:24:09,000
Now, as an aside, you'll see in these examples and some others

422
00:24:09,000 --> 00:24:11,000
functions like require, require once. 

423
00:24:11,000 --> 00:24:15,000
There's actually a function itself called include, and they all have slightly different behavior.

424
00:24:15,000 --> 00:24:18,000
Here I say require once to make super clear that I only want those 

425
00:24:18,000 --> 00:24:20,000
helpers included in my project once.

426
00:24:20,000 --> 00:24:24,000
But if I'm careful and if I'm actually thinking through my logic properly

427
00:24:24,000 --> 00:24:27,000
it should suffice too just to say require up top

428
00:24:27,000 --> 00:24:31,000
so long as I myself don't accidentally require that same file elsewhere.

429
00:24:31,000 --> 00:24:34,000
In fact, this is a slightly more efficient way of doing things then using 

430
00:24:34,000 --> 00:24:38,000
require once, so I'll trim it down to just require.

431
00:24:38,000 --> 00:24:40,000
>> Let's take one step further.

432
00:24:40,000 --> 00:24:46,000
This last example now, version 5, has an even cleaner folder hierarchy.

433
00:24:46,000 --> 00:24:50,000
Notice what I've done here per the read me in this final version

434
00:24:50,000 --> 00:24:54,000
is now I have my HTML directory, which I've had all this time,

435
00:24:54,000 --> 00:24:58,000
but inside of there now is only index.php, lectures.php, 

436
00:24:58,000 --> 00:25:01,000
week1.php and week2.php.

437
00:25:01,000 --> 00:25:05,000
The includes directory now lives alongside of the HTML directory,

438
00:25:05,000 --> 00:25:08,000
so at the same level as a sibling, so to speak.

439
00:25:08,000 --> 00:25:10,000
So does the templates folder. 

440
00:25:10,000 --> 00:25:14,000
The key takeaway here is I've introduced a little bit more structure,

441
00:25:14,000 --> 00:25:17,000
but the key feature now is that only the files 

442
00:25:17,000 --> 00:25:21,000
that need to be web accessible, publicly addressable 

443
00:25:21,000 --> 00:25:25,000
by a URL on the public internet are in my HTML directory.

444
00:25:25,000 --> 00:25:28,000
>> Meanwhile, other files, helpers.php, footer.php,

445
00:25:28,000 --> 00:25:32,000
header.php, that are arguably maybe more sensitive,

446
00:25:32,000 --> 00:25:35,000
maybe helpers actually has some user names and passwords or some intellectual

447
00:25:35,000 --> 00:25:39,000
property of mine, functions I really don't want the world to see, even if accidentally.

448
00:25:39,000 --> 00:25:45,000
It's good practice to keep out of the public HTML directory

449
00:25:45,000 --> 00:25:48,000
any files that do not need themselves to be public.

450
00:25:48,000 --> 00:25:51,000
All you have to do in this case when looking at, for instance,

451
00:25:51,000 --> 00:25:55,000
the HTML directory's index.php file,

452
00:25:55,000 --> 00:25:58,000
notice we just have to be a little more careful when requiring

453
00:25:58,000 --> 00:26:00,000
or require once this file.

454
00:26:00,000 --> 00:26:03,000
I need to first do .. to go to the parent directory,

455
00:26:03,000 --> 00:26:06,000
then do /includes/helpers.php

456
00:26:06,000 --> 00:26:12,000
to dive back down in to get the file that I care about.

457
00:26:12,000 --> 00:26:16,000
>> Any questions then on MVC

458
00:26:16,000 --> 00:26:20,000
or this relatively simple incarnation thereof?

459
00:26:20,000 --> 00:26:23,000
And let me make clear that we focused quite a bit on the

460
00:26:23,000 --> 00:26:27,000
V here, the views and the factoring out of these templates.

461
00:26:27,000 --> 00:26:30,000
We've not really distinguished M from C just yet.

462
00:26:30,000 --> 00:26:33,000
In fact, there really is no M here, and even our C,

463
00:26:33,000 --> 00:26:36,000
controller, isn't really doing all that much, but you'll get much more

464
00:26:36,000 --> 00:26:39,000
familiar with both of those 2 letters from MVC,

465
00:26:39,000 --> 00:26:43,000
or rather, you'll get much more familiar with the C

466
00:26:43,000 --> 00:26:49,000
in MVC for problem set 7, so there's more of that on the horizon.

467
00:26:49,000 --> 00:26:51,000
Questions?

468
00:26:51,000 --> 00:26:53,000
There's actually no one here.

469
00:26:53,000 --> 00:26:57,000
>> Okay, let's now move on to the second and final topic for today.

470
00:26:57,000 --> 00:27:00,000
That is the introduction of a database.

471
00:27:00,000 --> 00:27:03,000
Up until this point we've had a few ways of storing data.

472
00:27:03,000 --> 00:27:05,000
We've used variables.

473
00:27:05,000 --> 00:27:08,000
Back in our C file, I/O discussion we started using text files

474
00:27:08,000 --> 00:27:11,000
and using files like fprintf, and then we even started

475
00:27:11,000 --> 00:27:14,000
talking about CSV files a little bit, comma separated values, 

476
00:27:14,000 --> 00:27:17,000
so all of these allowed us to have data stored

477
00:27:17,000 --> 00:27:19,000
either non-persistently or persistently.

478
00:27:19,000 --> 00:27:23,000
But even CSVs aren't really conducive to searching

479
00:27:23,000 --> 00:27:25,000
and inserting and deleting.

480
00:27:25,000 --> 00:27:28,000
It's really just a stupid text file separated by commas

481
00:27:28,000 --> 00:27:30,000
row by row by row by row, so if you want to 

482
00:27:30,000 --> 00:27:32,000
search that file the best you can do really is linear search.

483
00:27:32,000 --> 00:27:34,000
You have to start at the top of the file, read the whole thing in, 

484
00:27:34,000 --> 00:27:36,000
and look for some value of interest.

485
00:27:36,000 --> 00:27:39,000
If you want to insert into it you have to do the same thing,

486
00:27:39,000 --> 00:27:41,000
iterating over it and inserting in a particular place,

487
00:27:41,000 --> 00:27:45,000
and in fact, you have to do all of the searching logic yourself.

488
00:27:45,000 --> 00:27:49,000
>> You can't do clever pattern matching on a CSV file unless you yourself write the code.

489
00:27:49,000 --> 00:27:51,000
You can't do filtration of a CSV file 

490
00:27:51,000 --> 00:27:53,000
unless you yourself write the code.

491
00:27:53,000 --> 00:27:56,000
Wouldn't it be nice if someone else put in all of the effort

492
00:27:56,000 --> 00:27:59,000
to actually make searching easy and insertion easy

493
00:27:59,000 --> 00:28:01,000
and deletion and updating and so forth?

494
00:28:01,000 --> 00:28:04,000
That's exactly what a database is.

495
00:28:04,000 --> 00:28:07,000
SQL, structured query language, is yet another language

496
00:28:07,000 --> 00:28:10,000
that we're introducing here today, but this too is fairly accessible, 

497
00:28:10,000 --> 00:28:13,000
and what we're really going to do is just pluck out of it some of the most salient

498
00:28:13,000 --> 00:28:16,000
characteristics so that for pset 7, and if you do something web based,

499
00:28:16,000 --> 00:28:19,000
your final project, you have the ability to express yourself

500
00:28:19,000 --> 00:28:22,000
in terms of data queries.

501
00:28:22,000 --> 00:28:25,000
You have the ability to store a little or a lot of data

502
00:28:25,000 --> 00:28:28,000
in a much more structured way that will at the end of the day

503
00:28:28,000 --> 00:28:32,000
make your life easier because with SQL you can express yourself

504
00:28:32,000 --> 00:28:35,000
much more precisely, much more methodically in order to 

505
00:28:35,000 --> 00:28:40,000
get back some subset of data from a larger corpus of data.

506
00:28:40,000 --> 00:28:45,000
>> You can think of a database, in this case, an SQL database, really like Excel

507
00:28:45,000 --> 00:28:48,000
or Numbers where it's a spreadsheet,

508
00:28:48,000 --> 00:28:50,000
or maybe multiple spreadsheets, and a spreadsheet, of course,

509
00:28:50,000 --> 00:28:53,000
has rows and columns, and that's because 

510
00:28:53,000 --> 00:28:56,000
SQL databases are relational, relational in the sense

511
00:28:56,000 --> 00:28:59,000
that they store data in terms of these tables,

512
00:28:59,000 --> 00:29:01,000
rows and columns.

513
00:29:01,000 --> 00:29:03,000
They are higher performing than something like a spreadsheet,

514
00:29:03,000 --> 00:29:05,000
and a spreadsheet is meant to be used by a human. 

515
00:29:05,000 --> 00:29:08,000
A database is meant to be used by a programmer

516
00:29:08,000 --> 00:29:12,000
writing code against it, so the incarnation of a database

517
00:29:12,000 --> 00:29:14,000
is going to be either command line.

518
00:29:14,000 --> 00:29:18,000
>> One of the most popular relational databases out there is, again, MySQL,

519
00:29:18,000 --> 00:29:22,000
which is wonderfully free, very high performing, and this is what 

520
00:29:22,000 --> 00:29:24,000
Facebook used very early on and to some extent still today

521
00:29:24,000 --> 00:29:27,000
to store a lot of its data, and we'll see in a moment

522
00:29:27,000 --> 00:29:30,000
that using relatively simple commands

523
00:29:30,000 --> 00:29:33,000
we can select data, insert data, update data,

524
00:29:33,000 --> 00:29:37,000
delete data and the like, but thankfully, there's a more user-friendly interface

525
00:29:37,000 --> 00:29:39,000
than just typing at a black and white prompt here.

526
00:29:39,000 --> 00:29:43,000
We'll use for pset 7 and beyond a free tool called phpMyAdmin.

527
00:29:43,000 --> 00:29:45,000
The name is coincidental.

528
00:29:45,000 --> 00:29:47,000
The tool happens to be implemented in PHP,

529
00:29:47,000 --> 00:29:49,000
but that's fundamentally irrelevant.

530
00:29:49,000 --> 00:29:53,000
What's useful about phpMyAdmin is that it's a web-based utility.

531
00:29:53,000 --> 00:29:55,000
We've pre-installed it in the appliance for you,

532
00:29:55,000 --> 00:29:58,000
and with it you can create tables in a database, 

533
00:29:58,000 --> 00:30:01,000
you can insert data, delete data, and generally see

534
00:30:01,000 --> 00:30:04,000
your data in a fairly user-friendly environment.

535
00:30:04,000 --> 00:30:07,000
Your users are not going to use phpMyAdmin.

536
00:30:07,000 --> 00:30:09,000
>> This is really just an administrative or developer's tool

537
00:30:09,000 --> 00:30:12,000
with which to see and poke around your data and figure out how to structure it,

538
00:30:12,000 --> 00:30:15,000
much like you yourself might use Excel or Numbers,

539
00:30:15,000 --> 00:30:19,000
but it's going to be a great way of visualizing what's going on underneath the hood

540
00:30:19,000 --> 00:30:22,000
so that you can focus on the interesting problem solving and not so much

541
00:30:22,000 --> 00:30:25,000
on the arcane commands.

542
00:30:25,000 --> 00:30:28,000
Let's take a look at an example of data that might be stored tabularly 

543
00:30:28,000 --> 00:30:30,000
in a relational database.

544
00:30:30,000 --> 00:30:32,000
Here's one such example.

545
00:30:32,000 --> 00:30:35,000
Now, unfortunately, phpMyAdmin erred on the side of throwing way too many words

546
00:30:35,000 --> 00:30:38,000
and graphics at you, but if you hone in just on the

547
00:30:38,000 --> 00:30:42,000
ID column, the user name column, and the hash column,

548
00:30:42,000 --> 00:30:45,000
this is effectively a spreadsheet, but it happens to be a snippet

549
00:30:45,000 --> 00:30:49,000
of a table inside of the appliance

550
00:30:49,000 --> 00:30:53,000
using a file that we provide you with in problem set 7.

551
00:30:53,000 --> 00:30:57,000
>> In particular, we give you a file that represents

552
00:30:57,000 --> 00:31:01,000
a user's table, so a spreadsheet containing users with 3 columns,

553
00:31:01,000 --> 00:31:05,000
one of which is a unique ID starting at 1 and being incremented thereafter.

554
00:31:05,000 --> 00:31:08,000
The second column is a user name, and those of you who did Hacker,

555
00:31:08,000 --> 00:31:12,000
the Hacker edition for pset 2, might recognize some of these user names at least.

556
00:31:12,000 --> 00:31:16,000
On the right-hand side are passwords, but they're not literal passwords.

557
00:31:16,000 --> 00:31:19,000
They're hashes thereof, so it turns out

558
00:31:19,000 --> 00:31:22,000
that storing passwords in a database is a really bad idea.

559
00:31:22,000 --> 00:31:25,000
You've all probably read at some point of some website

560
00:31:25,000 --> 00:31:28,000
or some company's database being compromised, and then you have to 

561
00:31:28,000 --> 00:31:31,000
change your password, you need to get refunds on things

562
00:31:31,000 --> 00:31:34,000
because some bad guy actually broke into your account as a result.

563
00:31:34,000 --> 00:31:38,000
>> Storing passwords in clear text, unencrypted in a database

564
00:31:38,000 --> 00:31:41,000
is completely asinine, and yet it's greatly amusing

565
00:31:41,000 --> 00:31:44,000
then to read about some very well known companies

566
00:31:44,000 --> 00:31:47,000
sometimes in the press whose databases are compromised,

567
00:31:47,000 --> 00:31:50,000
and that part is not funny, but the fact that the databases contain unencrypted

568
00:31:50,000 --> 00:31:53,000
passwords is ridiculous because literally with one line of code

569
00:31:53,000 --> 00:31:57,000
you can protect against that particular threat, and that's what we've done here.

570
00:31:57,000 --> 00:32:00,000
Even for our fake little CS50 Finance version 

571
00:32:00,000 --> 00:32:03,000
we are encrypting passwords just for good measure, and the fact that

572
00:32:03,000 --> 00:32:07,000
all of these passwords start with $1$ is just a convention. 

573
00:32:07,000 --> 00:32:10,000
That just means they're encrypted or really hashed,

574
00:32:10,000 --> 00:32:13,000
which is like a one-way encryption function 

575
00:32:13,000 --> 00:32:17,000
whereby you can't reverse its effects with something called MD5.

576
00:32:17,000 --> 00:32:21,000
>> The fact that 50 is after that means that a salt value

577
00:32:21,000 --> 00:32:24,000
of 50 was used for hashing all of these passwords except for one.

578
00:32:24,000 --> 00:32:27,000
Mine, of course, as you can see there, HA, 

579
00:32:27,000 --> 00:32:30,000
was using a different salt, so those of you who got slightly tripped up

580
00:32:30,000 --> 00:32:33,000
maybe in Hacker 2, that might have been the result of our having used

581
00:32:33,000 --> 00:32:36,000
a different hash than the others because my password is actually the same

582
00:32:36,000 --> 00:32:38,000
as some other user up there.

583
00:32:38,000 --> 00:32:41,000
In fact, if you've been waiting all these weeks to find out

584
00:32:41,000 --> 00:32:44,000
what those passwords were here were the passwords that you were challenged

585
00:32:44,000 --> 00:32:48,000
to crack in the Hacker edition of problem set 2, so none too tricky.

586
00:32:48,000 --> 00:32:50,000
In fact, malan's was the same as jharvard,

587
00:32:50,000 --> 00:32:54,000
but if we go back they looked different.

588
00:32:54,000 --> 00:32:58,000
>> Focus on jharvard in crimson because they were salted differently.

589
00:32:58,000 --> 00:33:01,000
The algorithm was perturbed in a way that the

590
00:33:01,000 --> 00:33:05,000
hash value, the encrypted value looks a little different 

591
00:33:05,000 --> 00:33:08,000
because the inputs were slightly different, but the password underneath the hood

592
00:33:08,000 --> 00:33:10,000
was still ultimately crimson.

593
00:33:10,000 --> 00:33:12,000
Now, who cares about this?

594
00:33:12,000 --> 00:33:15,000
Well, we are providing you with the sample users, the sample user names 

595
00:33:15,000 --> 00:33:18,000
and hashes of their passwords so that you actually have some

596
00:33:18,000 --> 00:33:23,000
customers for CS50 Finance when you first get off the ground with your code.

597
00:33:23,000 --> 00:33:27,000
You'll have to implement more tables inside of MySQL, inside of the database.

598
00:33:27,000 --> 00:33:30,000
You'll have to create more spreadsheets, effectively, but we decided to give you this one

599
00:33:30,000 --> 00:33:33,000
to get you started, and you'll see that the problem set specification

600
00:33:33,000 --> 00:33:37,000
walks you through the process of importing this table

601
00:33:37,000 --> 00:33:39,000
and also explaining what some of the characteristics are, 

602
00:33:39,000 --> 00:33:41,000
and you'll also see that we provide you with the code

603
00:33:41,000 --> 00:33:44,000
to handle the hashing or the encryption of these passwords,

604
00:33:44,000 --> 00:33:49,000
so you don't need to worry too much about what MD5 or the like is actually all about.

605
00:33:49,000 --> 00:33:53,000
>> So, SQL, structured query language.

606
00:33:53,000 --> 00:33:56,000
This is, quite simply, the language we're about to start using in pset 7

607
00:33:56,000 --> 00:34:01,000
and perhaps beyond to request data from some database.

608
00:34:01,000 --> 00:34:06,000
The data is, again, stored tabularly in these relational tables, columns, and rows,

609
00:34:06,000 --> 00:34:09,000
but using some relatively simple syntax like delete,

610
00:34:09,000 --> 00:34:12,000
insert, update and select can we do exactly that.

611
00:34:12,000 --> 00:34:15,000
We can delete from the database, insert,

612
00:34:15,000 --> 00:34:19,000
update data, as well as select, that is, retrieve data from the database.

613
00:34:19,000 --> 00:34:21,000
How do we go about doing this?

614
00:34:21,000 --> 00:34:23,000
Let me go ahead into the appliance.

615
00:34:23,000 --> 00:34:28,000
Let me pull up http://localhost,

616
00:34:28,000 --> 00:34:30,000
which, again, is the local appliance itself.

617
00:34:30,000 --> 00:34:32,000
That's its default nickname.

618
00:34:32,000 --> 00:34:35,000
And let me go to /phpmyadmin.

619
00:34:35,000 --> 00:34:39,000
This happens to be a special URL that the appliance is preconfigured to understand

620
00:34:39,000 --> 00:34:42,000
that immediately prompts me for a user name and password.

621
00:34:42,000 --> 00:34:46,000
>> As usual, I'm going to type jharvard and crimson,

622
00:34:46,000 --> 00:34:48,000
but realize that's the administrator account on the computer.

623
00:34:48,000 --> 00:34:53,000
It's just a coincidence that there's also a jharvard registered for CS50 Finance.

624
00:34:53,000 --> 00:34:56,000
Jharvard, crimson, enter gives me the user interface that we saw

625
00:34:56,000 --> 00:34:59,000
a glimpse of a moment ago, and it's a little overwhelming at first,

626
00:34:59,000 --> 00:35:02,000
but rest assured, you're never going to have to click most of the links in this tool.

627
00:35:02,000 --> 00:35:05,000
You'll end up using a small subset that are super helpful,

628
00:35:05,000 --> 00:35:08,000
the first of which is databases up here.

629
00:35:08,000 --> 00:35:11,000
If I go up to databases, notice that I'm prompted to create a database.

630
00:35:11,000 --> 00:35:14,000
This is like creating a new Excel file, effectively.

631
00:35:14,000 --> 00:35:18,000
I'm going to go ahead and call this lecture, and I'm just going to ignore the field there, collation.

632
00:35:18,000 --> 00:35:20,000
It has to do with the representation of data therein, 

633
00:35:20,000 --> 00:35:24,000
and I'm going to click create, and now notice as I let go of create

634
00:35:24,000 --> 00:35:27,000
on the left-hand side where it says no databases

635
00:35:27,000 --> 00:35:30,000
I should soon see the lecture database.

636
00:35:30,000 --> 00:35:32,000
>> If I now click at the left-hand side, the lecture database,

637
00:35:32,000 --> 00:35:34,000
notice my tabs change a little bit.

638
00:35:34,000 --> 00:35:38,000
I've got structure, SQL, export, import and some other things.

639
00:35:38,000 --> 00:35:40,000
Structure is pretty much blank.

640
00:35:40,000 --> 00:35:43,000
No tables found in database, as it says here,

641
00:35:43,000 --> 00:35:47,000
so let's create a table, and let's go ahead and create a table

642
00:35:47,000 --> 00:35:53,000
like students, and how many columns do we want?

643
00:35:53,000 --> 00:35:55,000
Let's keep this simple, and let's record for every student 

644
00:35:55,000 --> 00:35:58,000
an ID number, a name, and an email address.

645
00:35:58,000 --> 00:36:02,000
>> We'll keep it simple like that, so 3 columns, go.

646
00:36:02,000 --> 00:36:05,000
The form that you see here is now a little bit messy and overwhelming,

647
00:36:05,000 --> 00:36:08,000
but we just have to go through it row by row, so really quickly let's give

648
00:36:08,000 --> 00:36:14,000
the first column in this database a name of ID for the unique identifier.

649
00:36:14,000 --> 00:36:17,000
It will be an integer. I can actually ignore length and values.

650
00:36:17,000 --> 00:36:21,000
An int is going to be 32 bits no matter what you type in there, so let's leave it blank.

651
00:36:21,000 --> 00:36:24,000
Default value, I could make it null, as defined. 

652
00:36:24,000 --> 00:36:27,000
I'm going to leave that alone. Let's not worry about default values.

653
00:36:27,000 --> 00:36:29,000
Let's scroll over here to the right, attributes.

654
00:36:29,000 --> 00:36:31,000
This is interesting. 

655
00:36:31,000 --> 00:36:33,000
Let's go ahead and somewhat arbitrarily say that IDs must be unsigned. 

656
00:36:33,000 --> 00:36:35,000
Let's not waste any negative numbers. 

657
00:36:35,000 --> 00:36:37,000
>> Let's go 0 to 4 billion, give or take,

658
00:36:37,000 --> 00:36:40,000
and then let's not touch any of these fields just yet over there,

659
00:36:40,000 --> 00:36:43,000
but then let me type in name down here, 

660
00:36:43,000 --> 00:36:46,000
and then the other was email, so the catch is email

661
00:36:46,000 --> 00:36:50,000
and name, obviously not integers, so let's change these to a different field.

662
00:36:50,000 --> 00:36:53,000
It turns out varchar, variable length char, 

663
00:36:53,000 --> 00:36:56,000
is like a string in an SQL database

664
00:36:56,000 --> 00:36:59,000
but a variable length, and you actually have to tell it in advance the

665
00:36:59,000 --> 00:37:02,000
maximum length of the string, so I'm going to somewhat arbitrarily

666
00:37:02,000 --> 00:37:05,000
by convention type 255 characters.

667
00:37:05,000 --> 00:37:08,000
I could totally say 32. I could say 1,000.

668
00:37:08,000 --> 00:37:11,000
You kind of need to decide for yourself based on your demographics what is 

669
00:37:11,000 --> 00:37:14,000
the longest student's name and go with that number or a little bigger,

670
00:37:14,000 --> 00:37:17,000
but what's nice about a varchar is it's not going to waste 

671
00:37:17,000 --> 00:37:19,000
255 bytes on every student's name.

672
00:37:19,000 --> 00:37:23,000
If it's D-A-V-I-D it's not going to use a whole 255 bytes,

673
00:37:23,000 --> 00:37:26,000
but that's an upper bound, so I'll go with 255 just by convention,

674
00:37:26,000 --> 00:37:30,000
but we could debate that to be some lower value, and for email address

675
00:37:30,000 --> 00:37:34,000
just to be consistent 255, but again, we could have the same debate.

676
00:37:34,000 --> 00:37:36,000
But I'm going to do one other thing over here on the right-hand side.

677
00:37:36,000 --> 00:37:40,000
>> What's powerful about a database is that it can do a lot of heavy lifting

678
00:37:40,000 --> 00:37:42,000
or complex work for you.

679
00:37:42,000 --> 00:37:46,000
In particular, I really don't care what my student's ID numbers are.

680
00:37:46,000 --> 00:37:49,000
It's just meant to be a unique identifier in a database

681
00:37:49,000 --> 00:37:52,000
so I have a 32-bit succinct representation of that student

682
00:37:52,000 --> 00:37:55,000
so that I have some way of uniquely identifying them 

683
00:37:55,000 --> 00:37:58,000
lest there be 2 Davids, for instance, in a class.

684
00:37:58,000 --> 00:38:01,000
In fact, I'm going to check this AI box, auto increment,

685
00:38:01,000 --> 00:38:04,000
so that the database, MySQL, figures out 

686
00:38:04,000 --> 00:38:08,000
what every newly inserted student's ID is going to be. 

687
00:38:08,000 --> 00:38:11,000
I don't even have to care about that in my code,

688
00:38:11,000 --> 00:38:13,000
and I'm also going to choose something under the index menu.

689
00:38:13,000 --> 00:38:17,000
The index drop down here has primary, unique, 

690
00:38:17,000 --> 00:38:19,000
index and full text.

691
00:38:19,000 --> 00:38:21,000
You can maybe guess what a couple of these things are, 

692
00:38:21,000 --> 00:38:24,000
but it turns out in relational databases

693
00:38:24,000 --> 00:38:28,000
you the programmer or the database administrator get to preemptively 

694
00:38:28,000 --> 00:38:32,000
give hints to the database as to what fields

695
00:38:32,000 --> 00:38:34,000
in a table are a little special.

696
00:38:34,000 --> 00:38:37,000
>> For instance, in this case I'm going to say that ID

697
00:38:37,000 --> 00:38:42,000
is going to be a primary index, otherwise known as a primary key.

698
00:38:42,000 --> 00:38:44,000
What this means by definition is that ID henceforth 

699
00:38:44,000 --> 00:38:48,000
will uniquely identify students in this table.

700
00:38:48,000 --> 00:38:53,000
No student will have the same ID because I'm imposing this constraint or this index.

701
00:38:53,000 --> 00:38:55,000
Moreover, what this is going to do for me is it's going to tell 

702
00:38:55,000 --> 00:38:58,000
MySQL that ID is special.

703
00:38:58,000 --> 00:39:03,000
I care particularly about ID, so go ahead and do your fancy data structure's magic,

704
00:39:03,000 --> 00:39:05,000
build up some kind of tree.

705
00:39:05,000 --> 00:39:08,000
Typically it's something called the B-tree, which we didn't look at weeks ago,

706
00:39:08,000 --> 00:39:11,000
but it's another such data structure similar in spirit to the binary trees

707
00:39:11,000 --> 00:39:15,000
and tries that we looked at, but it's going to say

708
00:39:15,000 --> 00:39:18,000
to the database this field is so important that I probably 

709
00:39:18,000 --> 00:39:22,000
want to be able to search on it, go ahead and build up some fancy

710
00:39:22,000 --> 00:39:25,000
data structure in memory to expedite searches so that ideally 

711
00:39:25,000 --> 00:39:28,000
they're constant time or at least as close to that as possible

712
00:39:28,000 --> 00:39:32,000
so that it doesn't devolve into linear search, which is not going to be

713
00:39:32,000 --> 00:39:34,000
the most high performing approach.

714
00:39:34,000 --> 00:39:37,000
By contrast, email address could have been a primary key.

715
00:39:37,000 --> 00:39:41,000
>> In theory, everyone's email address is unique, unless you're sharing some account,

716
00:39:41,000 --> 00:39:45,000
but it's generally not good to use something like a string

717
00:39:45,000 --> 00:39:49,000
as a primary key because if its purpose in life is to uniquely identify

718
00:39:49,000 --> 00:39:55,000
rows in your table there's no reason to use 255 bytes maximally 

719
00:39:55,000 --> 00:39:58,000
to uniquely identify someone if you can get away with just 4 bytes

720
00:39:58,000 --> 00:40:00,000
or a 32-bit int.

721
00:40:00,000 --> 00:40:03,000
In general, a primary key should be short and succinct 

722
00:40:03,000 --> 00:40:07,000
and ideally something like an integer or a big int, which happens to be 64 bits.

723
00:40:07,000 --> 00:40:11,000
But an email address should be unique, and one of the features of a database too

724
00:40:11,000 --> 00:40:14,000
is to enforce uniqueness for me.

725
00:40:14,000 --> 00:40:18,000
By selecting unique here next to email, even though email itself

726
00:40:18,000 --> 00:40:21,000
is scrolled off the screen, I'm saying to the database

727
00:40:21,000 --> 00:40:23,000
don't trust me.

728
00:40:23,000 --> 00:40:26,000
Don't let me insert into the database

729
00:40:26,000 --> 00:40:29,000
the same email address twice, even if I'm an idiot and am not

730
00:40:29,000 --> 00:40:32,000
very good with my ifs and else ifs and actual PHP code

731
00:40:32,000 --> 00:40:37,000
and I accidentally let the user register with an existing email address

732
00:40:37,000 --> 00:40:40,000
the database is yet another level of defense for correctness

733
00:40:40,000 --> 00:40:44,000
to ensure that that duplicate email address doesn't end up in the table.

734
00:40:44,000 --> 00:40:49,000
>> Now, by contrast, for name you probably don't want to make that unique

735
00:40:49,000 --> 00:40:51,000
because then there could never be 2 Davids or 2 Mike Smiths, for instance,

736
00:40:51,000 --> 00:40:55,000
in your database, so that one we'll just leave alone.

737
00:40:55,000 --> 00:40:58,000
I'm going to go ahead and click save at bottom right,

738
00:40:58,000 --> 00:41:02,000
and everything looks good, but notice up here

739
00:41:02,000 --> 00:41:04,000
this is a part that for now we won't spend too much time on

740
00:41:04,000 --> 00:41:07,000
because the syntax is a little complex, and we don't have to create tables 

741
00:41:07,000 --> 00:41:10,000
all that often, but SQL itself is a language,

742
00:41:10,000 --> 00:41:13,000
the syntax for which is right here that I've highlighted.

743
00:41:13,000 --> 00:41:18,000
What phpMyAdmin really does is it creates a web-based GUI for you

744
00:41:18,000 --> 00:41:23,000
with which you can save time and not have to manually type out 

745
00:41:23,000 --> 00:41:26,000
a fairly long SQL query like that.

746
00:41:26,000 --> 00:41:29,000
>> In other words, if you wanted to manually create this table,

747
00:41:29,000 --> 00:41:32,000
either at that black and white prompt or even in phpMyAdmin

748
00:41:32,000 --> 00:41:35,000
by using this other tab, this SQL tab where you can type in any SQL queries

749
00:41:35,000 --> 00:41:38,000
you want, frankly, this would have taken me a minute

750
00:41:38,000 --> 00:41:41,000
to actually remember the entire syntax, and even then I probably would have

751
00:41:41,000 --> 00:41:45,000
made some typos, so this tool is useful for things like that, and it's also instructive.

752
00:41:45,000 --> 00:41:49,000
You can begin to infer what the syntax is 

753
00:41:49,000 --> 00:41:52,000
just by the nice color coding that phpMyAdmin is adding

754
00:41:52,000 --> 00:41:54,000
for our visual convenience.

755
00:41:54,000 --> 00:41:56,000
But now let's do this instead. 

756
00:41:56,000 --> 00:42:00,000
Let me go to the insert tab at top, and let me go ahead and insert for instance

757
00:42:00,000 --> 00:42:04,000
an ID of let's say—actually I don't care.

758
00:42:04,000 --> 00:42:07,000
It's going to auto increment. I'm going to let the database deal with this.

759
00:42:07,000 --> 00:42:11,000
But I shall be David, and my email should be malan@harvard.edu.

760
00:42:11,000 --> 00:42:16,000
>> Let's go ahead here and put in Mike Smith as another one.

761
00:42:16,000 --> 00:42:18,000
I'll give myself a last name as well,

762
00:42:18,000 --> 00:42:22,000
and we'll have him be smith@example.com,

763
00:42:22,000 --> 00:42:24,000
and then where do I go next?

764
00:42:24,000 --> 00:42:27,000
Well, it looks like go is the button to click, and voila.

765
00:42:27,000 --> 00:42:30,000
Notice at the top 2 rows inserted.

766
00:42:30,000 --> 00:42:32,000
This is the actual SQL query.

767
00:42:32,000 --> 00:42:36,000
That's the phpMyAdmin tool executed for me,

768
00:42:36,000 --> 00:42:40,000
but the end result, notice, if I now go to the browse tab,

769
00:42:40,000 --> 00:42:43,000
is to see 2 rows in this table, very reminiscent aesthetically

770
00:42:43,000 --> 00:42:46,000
of the table we saw earlier for our users from pset 7,

771
00:42:46,000 --> 00:42:51,000
one of whom is David Malan, one of whom now is Mike Smith.

772
00:42:51,000 --> 00:42:54,000
But just to be clear, I don't need to use phpMyAdmin, 

773
00:42:54,000 --> 00:42:56,000
and indeed, you're going to soon be writing code for pset 7

774
00:42:56,000 --> 00:43:01,000
that automates the process of adding rows, deleting rows, updating rows and the like,

775
00:43:01,000 --> 00:43:04,000
so let me instead go to the SQL tab up here

776
00:43:04,000 --> 00:43:14,000
and type in select * from students where 

777
00:43:14,000 --> 00:43:18,000
email = "malan@harvard.edu."

778
00:43:18,000 --> 00:43:21,000
>> In other words suppose now you have 

779
00:43:21,000 --> 00:43:26,000
some HTML form, and the user types in their email address, among other fields,

780
00:43:26,000 --> 00:43:29,000
and the goal now is in PHP on the back end code

781
00:43:29,000 --> 00:43:31,000
to actually look up that user's other details.

782
00:43:31,000 --> 00:43:34,000
What is your full name? What is your ID number?

783
00:43:34,000 --> 00:43:37,000
You could write a SQL query like this, select * from students

784
00:43:37,000 --> 00:43:40,000
where email = "malan@harvard.edu."

785
00:43:40,000 --> 00:43:46,000
And if I then click go, notice that I should, and indeed I do, get back just one row.

786
00:43:46,000 --> 00:43:50,000
Mike is omitted from this result set, as the collection of rows

787
00:43:50,000 --> 00:43:53,000
is generally called, because he doesn't have the same email address as me.

788
00:43:53,000 --> 00:43:57,000
>> Now, again, here for pset 7 you'll use phpMyAdmin as an administrative tool

789
00:43:57,000 --> 00:44:00,000
and a pedagogical tool to learn your way

790
00:44:00,000 --> 00:44:03,000
around the world of SQL, but at the end of the day

791
00:44:03,000 --> 00:44:08,000
you're going to be writing these queries inside of actual PHP code,

792
00:44:08,000 --> 00:44:11,000
and so stay tuned in Zamyla's walkthrough in particular

793
00:44:11,000 --> 00:44:14,000
where you'll get a tour of the distribution code for this problem set

794
00:44:14,000 --> 00:44:18,000
where we've given you not only the aesthetics for the login page

795
00:44:18,000 --> 00:44:21,000
and the nice sexy logo that says CS50 Finance, but we've also given you 

796
00:44:21,000 --> 00:44:24,000
a bunch of functions that will make your life a bit easier.

797
00:44:24,000 --> 00:44:27,000
We've also written part of the pset for you,

798
00:44:27,000 --> 00:44:32,000
the login portion of it in particular, to give you a sense of a representative design

799
00:44:32,000 --> 00:44:36,000
that actually uses a controller, for instance, index.php, 

800
00:44:36,000 --> 00:44:40,000
login.php and the like, and then you'll see the pset also has a templates directory

801
00:44:40,000 --> 00:44:43,000
that has all of your views, all of the aesthetics.

802
00:44:43,000 --> 00:44:46,000
And so the overall workflow in pset 7 is going to be that

803
00:44:46,000 --> 00:44:49,000
your users visit a controller via a URL in a browser.

804
00:44:49,000 --> 00:44:53,000
That controller contains PHP code that you wrote, and inside of your PHP code

805
00:44:53,000 --> 00:44:57,000
might be some lines of SQL nested in between double quotes

806
00:44:57,000 --> 00:45:00,000
and passed to a function we wrote called query

807
00:45:00,000 --> 00:45:03,000
that will help you talk to the database without using something like

808
00:45:03,000 --> 00:45:05,000
an administrative tool like phpMyAdmin.

809
00:45:05,000 --> 00:45:08,000
>> You'll be able to write SQL statements in PHP code

810
00:45:08,000 --> 00:45:11,000
and get back a PHP array of the result set, 

811
00:45:11,000 --> 00:45:14,000
of the rows that actually match that query.

812
00:45:14,000 --> 00:45:18,000
And similarly will you be able to do inserts or deletes

813
00:45:18,000 --> 00:45:22,000
or updates or the like, the syntax for which is fairly similar,

814
00:45:22,000 --> 00:45:25,000
and you'll see from some online references, from the distribution code

815
00:45:25,000 --> 00:45:29,000
and from the pset pack itself exactly how to go about doing that.

816
00:45:29,000 --> 00:45:33,000
Realize ultimately we're really just scratching the surface of SQL

817
00:45:33,000 --> 00:45:36,000
and of MySQL, but the power of it really is that it frees you 

818
00:45:36,000 --> 00:45:40,000
to focus on the problems you want to solve, the use cases you want to implement

819
00:45:40,000 --> 00:45:43,000
without having to worry quite as much, at least early on,

820
00:45:43,000 --> 00:45:47,000
about where and how to store and search your database,

821
00:45:47,000 --> 00:45:50,000
and this is quite literally where Facebook itself got its start

822
00:45:50,000 --> 00:45:53,000
using MySQL and then using more MySQL servers and then more MySQL servers

823
00:45:53,000 --> 00:45:57,000
before long until they then had to really start thinking hard about how to 

824
00:45:57,000 --> 00:46:00,000
store data, how to store things even more efficiently,

825
00:46:00,000 --> 00:46:04,000
so even though we'll take for granted the fact that indexes and unique constraints

826
00:46:04,000 --> 00:46:08,000
and so forth just work there's a very interesting conversation

827
00:46:08,000 --> 00:46:12,000
that this can all eventually lead to, so realize that we're just scratching the surface

828
00:46:12,000 --> 00:46:17,000
of what could eventually for you or your projects become quite a bit of big data.

829
00:46:17,000 --> 00:46:22,000
>> With that said, let's end here, and we'll see you next week.

830
00:46:28,000 --> 00:46:30,000
[CS50.TV]