1
00:00:00,000 --> 00:00:10,393
>> [MUSIC PLAYING]

2
00:00:10,393 --> 00:00:11,037

3
00:00:11,037 --> 00:00:12,120
DAVID J. MALAN: All right.

4
00:00:12,120 --> 00:00:12,830
Welcome back.

5
00:00:12,830 --> 00:00:13,890
This is CS50.

6
00:00:13,890 --> 00:00:15,570
This is the end of week 8.

7
00:00:15,570 --> 00:00:18,360
And as you know, we have pretty
regular office hours in a few

8
00:00:18,360 --> 00:00:21,090
of the dining halls,
including Annenberg.

9
00:00:21,090 --> 00:00:23,860
And some of the team kindly
took some photos lately.

10
00:00:23,860 --> 00:00:26,230
And in honor of
Halloween, we thought we'd

11
00:00:26,230 --> 00:00:30,160
share one that rather caught us by
surprise here in Annenberg Hall just

12
00:00:30,160 --> 00:00:31,490
the other night.

13
00:00:31,490 --> 00:00:36,300
Your classmate Jacob posed for
this photo, but was more amusing

14
00:00:36,300 --> 00:00:39,760
was on Facebook, the ensuing
conversation that happened afterward.

15
00:00:39,760 --> 00:00:43,020
>> His first post in response
to his photo was this.

16
00:00:43,020 --> 00:00:46,740
A few minutes later, he decided
to one up himself with this.

17
00:00:46,740 --> 00:00:53,800
It went on then to go to this,
and then, even more amusingly

18
00:00:53,800 --> 00:00:55,320
is when his mom chimed in.

19
00:00:55,320 --> 00:00:59,240

20
00:00:59,240 --> 00:01:01,800
And then ultimately,
it seems this was just

21
00:01:01,800 --> 00:01:04,860
a wonderful ruse for a
play that's going on.

22
00:01:04,860 --> 00:01:07,080
>> So, if you would like
to see Jacob and others,

23
00:01:07,080 --> 00:01:10,880
among them Cynthia Meng, who's
behind the scenes of CS50 zone staff,

24
00:01:10,880 --> 00:01:13,970
head to this URL and this play here.

25
00:01:13,970 --> 00:01:18,810
So without further ado, today we
continue this look at web programming,

26
00:01:18,810 --> 00:01:21,810
and the actual creation of programs
that don't run at your command line,

27
00:01:21,810 --> 00:01:24,080
but instead run inside of a browser.

28
00:01:24,080 --> 00:01:26,320
>> Presumably now, or very
shortly, you're going

29
00:01:26,320 --> 00:01:30,200
to be in the midst of implementing
your own web server, which

30
00:01:30,200 --> 00:01:31,700
is different from web programming.

31
00:01:31,700 --> 00:01:36,210
The web server in pset6 is all about
writing software that knows how to take

32
00:01:36,210 --> 00:01:39,300
HTTP requests from a browser,
or even from you, a human,

33
00:01:39,300 --> 00:01:42,340
with a program called Telnet, and
then respond to those requests either

34
00:01:42,340 --> 00:01:48,600
by spitting out an HTML file, or a
jpeg, or a gif, or even a .php file.

35
00:01:48,600 --> 00:01:52,490
>> But with a web server, it's not
supposed to just open a PHP file,

36
00:01:52,490 --> 00:01:55,260
something ending in .php, and
then spit out the contents.

37
00:01:55,260 --> 00:01:58,440
It's supposed to do
what to that file first?

38
00:01:58,440 --> 00:01:59,390
So to speak.

39
00:01:59,390 --> 00:02:04,060
Not compile it, we said on Monday,
but rather-- So, interpret it.

40
00:02:04,060 --> 00:02:08,070
>> PHP's an interpreted language, and so
one of the key features in your web

41
00:02:08,070 --> 00:02:11,550
server, albeit implemented by us,
is this ability for your web server

42
00:02:11,550 --> 00:02:12,490
to notice, oh.

43
00:02:12,490 --> 00:02:14,580
This is a file ending in .php.

44
00:02:14,580 --> 00:02:17,970
Let me not just send it to the
user like it's static content,

45
00:02:17,970 --> 00:02:20,970
but rather let me read it line by
line, left to right, and interpret it.

46
00:02:20,970 --> 00:02:23,030
>> And to do, that you
guys will essentially

47
00:02:23,030 --> 00:02:26,520
punt to a program in the appliance,
and on a lot of computer systems,

48
00:02:26,520 --> 00:02:27,500
just called PHP.

49
00:02:27,500 --> 00:02:30,579
That is the name of PHP the
language's own interpreter.

50
00:02:30,579 --> 00:02:33,120
So, that piece we implement for
you, and what's left for you,

51
00:02:33,120 --> 00:02:35,240
ultimately, is a number
of pieces, among which

52
00:02:35,240 --> 00:02:37,960
is implementing support
for static content.

53
00:02:37,960 --> 00:02:40,180
>> But now, and with
problem set seven, you're

54
00:02:40,180 --> 00:02:43,660
going to start to transition to
actually writing the PHP code

55
00:02:43,660 --> 00:02:45,970
that gets interpreted
in talking to a back end

56
00:02:45,970 --> 00:02:47,960
database that stores information.

57
00:02:47,960 --> 00:02:51,020
So let's better understand first
a couple of these superglobals

58
00:02:51,020 --> 00:02:53,720
and just how much power you
get out of the box for free

59
00:02:53,720 --> 00:02:55,250
with a language like PHP.

60
00:02:55,250 --> 00:02:57,350
Things you don't have
to implement yourself.

61
00:02:57,350 --> 00:03:01,700
>> So, we saw on Monday $_GET,
which is a superglobal,

62
00:03:01,700 --> 00:03:05,496
which is just PHP speak for a global
variable you can access anywhere.

63
00:03:05,496 --> 00:03:06,620
And what's inside of $_GET?

64
00:03:06,620 --> 00:03:09,930

65
00:03:09,930 --> 00:03:12,110
What's inside of this
superglobal that we see?

66
00:03:12,110 --> 00:03:15,900

67
00:03:15,900 --> 00:03:19,020
Surely statistically at
least one person knows.

68
00:03:19,020 --> 00:03:21,590
What's inside of $_GET?

69
00:03:21,590 --> 00:03:22,426
Yeah?

70
00:03:22,426 --> 00:03:24,130
>> AUDIENCE: It's the variables
you put in the query string.

71
00:03:24,130 --> 00:03:24,530
>> DAVID J. MALAN: Perfect.

72
00:03:24,530 --> 00:03:26,488
It's the variables you
put in the query string.

73
00:03:26,488 --> 00:03:29,910
So, in our older example of
reimplementing Google when we had

74
00:03:29,910 --> 00:03:34,130
a URL, and then question mark,
which demarcates the start of HTTP

75
00:03:34,130 --> 00:03:37,950
parameters, then we had q equal
something, like q equals cats,

76
00:03:37,950 --> 00:03:41,500
what would automatically go inside
of that $_GET super global for you,

77
00:03:41,500 --> 00:03:47,430
because of PHP, is a key of Q,
and of value thereof of cats.

78
00:03:47,430 --> 00:03:51,250
>> In other words, $_GET and all of
these things are associative arrays,

79
00:03:51,250 --> 00:03:54,530
hash tables of sorts, that
store keys and values.

80
00:03:54,530 --> 00:03:57,980
Now, back in pset5, the hash
table you might have implemented,

81
00:03:57,980 --> 00:04:00,220
or the try you might
have implemented, really

82
00:04:00,220 --> 00:04:04,010
was effectively an associative
array, a data structure

83
00:04:04,010 --> 00:04:07,220
whereby you can associate
keys with values.

84
00:04:07,220 --> 00:04:09,690
>> But in pset5, the values were trivial.

85
00:04:09,690 --> 00:04:12,430
The value was essentially true or false.

86
00:04:12,430 --> 00:04:13,900
Is the word in the dictionary?

87
00:04:13,900 --> 00:04:18,279
So, when you hashed a word like apple
to see if apple is in the dictionary,

88
00:04:18,279 --> 00:04:21,820
your check function presumably
returned true or false.

89
00:04:21,820 --> 00:04:24,120
So, that's effectively the
value we're getting back.

90
00:04:24,120 --> 00:04:26,456
>> But we saw on Monday
briefly, you can certainly

91
00:04:26,456 --> 00:04:28,830
associate more interesting
values than just true or false

92
00:04:28,830 --> 00:04:30,790
with keys, like apple.

93
00:04:30,790 --> 00:04:33,909
You could actually return an
arbitrary string, and indeed,

94
00:04:33,909 --> 00:04:36,200
that's what $_GET and these
other variables let you do.

95
00:04:36,200 --> 00:04:40,595
>> So $_POST is similar in spirit,
but if you submit a form via post,

96
00:04:40,595 --> 00:04:44,490
a different HTTP method that's
used for things like credit cards,

97
00:04:44,490 --> 00:04:48,410
and private information, and even
binary information like photos,

98
00:04:48,410 --> 00:04:51,840
those things end up inside of $_POST.

99
00:04:51,840 --> 00:04:53,770
And actually for files
like jpegs and whatnot,

100
00:04:53,770 --> 00:04:58,290
there's even another that's not
up here called $_FILES as well.

101
00:04:58,290 --> 00:05:01,280
>> So, server we won't dwell on too
much, but it gives you access

102
00:05:01,280 --> 00:05:04,860
to sort of lower level details about
the server itself that you're using.

103
00:05:04,860 --> 00:05:07,430
Cookie and session, though,
we'll effectively see now.

104
00:05:07,430 --> 00:05:10,940
The last is what we use to implement
the notion of a shopping cart.

105
00:05:10,940 --> 00:05:14,480
A super simple one, but recall
that we had this example here,

106
00:05:14,480 --> 00:05:17,640
counting how many times you
had visited this page before.

107
00:05:17,640 --> 00:05:20,850
>> But today, rather than just look at
the effect of this, let's open up

108
00:05:20,850 --> 00:05:22,640
Chrome's Inspector,
which you can usually

109
00:05:22,640 --> 00:05:25,740
do by right clicking or Control
clicking anywhere on a web page,

110
00:05:25,740 --> 00:05:27,250
and then choose inspect element.

111
00:05:27,250 --> 00:05:31,600
Or you can go through the menus
that we describe in pset6's spec.

112
00:05:31,600 --> 00:05:35,020
And I'm going to the Network tab
here, and let's watch for a moment

113
00:05:35,020 --> 00:05:37,590
the HTTP traffic that's
going back and forth.

114
00:05:37,590 --> 00:05:40,929
>> Let me first go ahead
and clear Chrome's cache.

115
00:05:40,929 --> 00:05:43,470
So some of you might be familiar
with this technique already,

116
00:05:43,470 --> 00:05:45,790
and we're going to use it
for debugging purposes here.

117
00:05:45,790 --> 00:05:48,890

118
00:05:48,890 --> 00:05:50,890
Now we as computer
scientists are going to start

119
00:05:50,890 --> 00:05:53,920
doing this for debugging
purposes, whereby

120
00:05:53,920 --> 00:05:55,910
we will clear the cache,
typically, so that we

121
00:05:55,910 --> 00:05:57,670
can get rid of things called cookies.

122
00:05:57,670 --> 00:06:01,700
So you're probably generally familiar
with what cookies are, or at least

123
00:06:01,700 --> 00:06:04,370
that they exist, but what's
your understanding of them,

124
00:06:04,370 --> 00:06:06,920
as just a user of
computers, what is a cookie?

125
00:06:06,920 --> 00:06:09,490

126
00:06:09,490 --> 00:06:09,990
Yeah.

127
00:06:09,990 --> 00:06:14,391
>> AUDIENCE: It's a bit of-- well, not
bit in a term of computer science.

128
00:06:14,391 --> 00:06:18,303
It's a piece of data that a
website sends to you in order

129
00:06:18,303 --> 00:06:20,209
to be able to record statistics on you.

130
00:06:20,209 --> 00:06:21,250
DAVID J. MALAN: OK, good.

131
00:06:21,250 --> 00:06:24,980
So it's a piece of data that a
server, puts onto your computer,

132
00:06:24,980 --> 00:06:28,840
and let's generalize it even
more, it's a key value-- well,

133
00:06:28,840 --> 00:06:30,064
that's getting more precise.

134
00:06:30,064 --> 00:06:31,980
It is a piece of
information, a piece of data,

135
00:06:31,980 --> 00:06:34,430
that a server is able
to put on your computer

136
00:06:34,430 --> 00:06:38,592
and very often, the server does
this so as to remember who you are.

137
00:06:38,592 --> 00:06:40,300
So for instance, odds
are you're probably

138
00:06:40,300 --> 00:06:42,982
logged into sites like Facebook,
or Gmail, or others before,

139
00:06:42,982 --> 00:06:44,940
and you log in with your
username and password,

140
00:06:44,940 --> 00:06:49,000
and then after that, for some number
of minutes or hours or even days,

141
00:06:49,000 --> 00:06:52,970
the server remembers that
you are, in fact, logged in.

142
00:06:52,970 --> 00:06:54,600
Now, how is that actually happening?

143
00:06:54,600 --> 00:06:58,630
Because you're certainly not retyping
your username and password every time

144
00:06:58,630 --> 00:07:00,760
you navigate to a
different page on Facebook.

145
00:07:00,760 --> 00:07:02,570
So it turns out the
cookies are the answer.

146
00:07:02,570 --> 00:07:05,360
>> A cookie you can think of as,
sort of like, a digital hand

147
00:07:05,360 --> 00:07:09,200
stamp that you might get at an amusement
park or a club that essentially

148
00:07:09,200 --> 00:07:11,740
indicates you have been here
before, and you've already

149
00:07:11,740 --> 00:07:16,070
shown your ID to the bouncer, for
instance, and that the club or the park

150
00:07:16,070 --> 00:07:19,050
should now assume that you
have authenticated already.

151
00:07:19,050 --> 00:07:21,270
You have already been identified by it.

152
00:07:21,270 --> 00:07:24,740
>> So with that in mind,
let's open up counter here.

153
00:07:24,740 --> 00:07:27,220
Let me go ahead, I just did,
and clear all of my cookies.

154
00:07:27,220 --> 00:07:29,970
And now what I'm going to do is
hold Shift, just for good measure,

155
00:07:29,970 --> 00:07:31,740
and forcibly reload the page.

156
00:07:31,740 --> 00:07:34,170
Shift just make sure
that nothing gets cached.

157
00:07:34,170 --> 00:07:36,850
And here's the request
that went back and forth.

158
00:07:36,850 --> 00:07:41,560
So over here we have a request, and let
me zoom in down here, and a lot of this

159
00:07:41,560 --> 00:07:44,710
is sort of uninteresting details for
now that the browser has automatically

160
00:07:44,710 --> 00:07:47,800
sent, but let's click View
Source to see the raw headers.

161
00:07:47,800 --> 00:07:51,700
>> And if you've dived into pset6 already,
you'll certainly recognize things

162
00:07:51,700 --> 00:07:54,990
like this, and maybe some
of these other lines here,

163
00:07:54,990 --> 00:07:59,040
but what's more interesting for today
if I scroll down, not to the request

164
00:07:59,040 --> 00:08:02,870
but to the so-called response,
this line probably looks familiar.

165
00:08:02,870 --> 00:08:04,977
That's a good thing
when you see a 200 OK.

166
00:08:04,977 --> 00:08:07,060
Apparently this is the
date and time on the server

167
00:08:07,060 --> 00:08:08,268
and there's a bunch of stuff.

168
00:08:08,268 --> 00:08:09,290
Oh, this is interesting.

169
00:08:09,290 --> 00:08:13,430
>> Turns out whenever you use
PHP, at least in this server,

170
00:08:13,430 --> 00:08:16,360
the server spits out what
version of PHP you're using.

171
00:08:16,360 --> 00:08:18,962
Which, actually, for security
purposes, is not a good thing.

172
00:08:18,962 --> 00:08:21,170
But, we'll come back to that
some other time perhaps.

173
00:08:21,170 --> 00:08:25,740
But now this is the juicy line today,
and we saw briefly some of these,

174
00:08:25,740 --> 00:08:29,240
I think with Facebook when we poked
around the Inspector at that time,

175
00:08:29,240 --> 00:08:33,380
set cookie is what is planting
that little piece of information

176
00:08:33,380 --> 00:08:34,890
onto your computer.

177
00:08:34,890 --> 00:08:37,490
>> This is an HTTP header
that's effectively

178
00:08:37,490 --> 00:08:39,970
telling your browser,
Chrome, IE, whatever,

179
00:08:39,970 --> 00:08:44,480
hey browser store on the user's
hard drive, or in the user's RAM,

180
00:08:44,480 --> 00:08:49,680
a key called PHPSESSID, which is a
shorthand notation for session ID,

181
00:08:49,680 --> 00:08:53,670
and give it a value of
0vlk8t, dot, dot, dot.

182
00:08:53,670 --> 00:08:56,480
A really long pseudo
random alphanumeric string.

183
00:08:56,480 --> 00:08:59,480
It's just a really big number, but
it's encoded with letters and numbers

184
00:08:59,480 --> 00:09:03,550
so that the size of it can be
even larger than numbers alone.

185
00:09:03,550 --> 00:09:06,947
And then, by the way, Path=/, that
just means that this cookie should be

186
00:09:06,947 --> 00:09:08,780
associated with the
entirety of the website,

187
00:09:08,780 --> 00:09:11,150
not just a specific
page the whole thing.

188
00:09:11,150 --> 00:09:12,930
So this is that virtual hand stamp.

189
00:09:12,930 --> 00:09:16,330
It's as though the server, Facebook,
or in our case the appliance,

190
00:09:16,330 --> 00:09:21,140
has literally written 0vlk8t
and so forth, on your hand.

191
00:09:21,140 --> 00:09:24,360
Notice what the server's,
not doing is it's not

192
00:09:24,360 --> 00:09:27,730
storing my username, certainly
not storing my password.

193
00:09:27,730 --> 00:09:31,710
>> Instead, it appears to be
storing pseudo random information

194
00:09:31,710 --> 00:09:35,010
so that no one can guess
what my hand stamp is.

195
00:09:35,010 --> 00:09:37,590
On the server side,
meanwhile, the server

196
00:09:37,590 --> 00:09:40,370
is going to remember, probably
in a database or something,

197
00:09:40,370 --> 00:09:46,490
that the user, who in the future
presents a hand stamp of 0vlk8t, dot,

198
00:09:46,490 --> 00:09:51,440
dot, dot, should be associated with this
particular shopping cart, so to speak.

199
00:09:51,440 --> 00:09:55,060
In other words, if I now go
back here and reload this page,

200
00:09:55,060 --> 00:09:58,020
how does the server know
that I visited one time?

201
00:09:58,020 --> 00:10:01,730
>> Or if I do it again, how does the server
know that I've visited it two times?

202
00:10:01,730 --> 00:10:04,680
Well if I go down to this
most recent request, which

203
00:10:04,680 --> 00:10:09,150
is now the third that I've sent
in total, notice my request now.

204
00:10:09,150 --> 00:10:11,300
There is still this
request up here, same

205
00:10:11,300 --> 00:10:15,040
as before, there's still a whole bunch
of stuff that we've ignored as before,

206
00:10:15,040 --> 00:10:19,350
but the very last header, this
time, because I've been here before,

207
00:10:19,350 --> 00:10:21,980
is a presentation of
this virtual hand stamp.

208
00:10:21,980 --> 00:10:28,957
>> Whereby this line here, not set cookie
but cookie colon PHPSESSI= 0vlk8t,

209
00:10:28,957 --> 00:10:32,040
that's just my browser's automatic
presentation of this hand stamp so that

210
00:10:32,040 --> 00:10:37,910
now the server, as soon as it realizes,
ooh, this is user 0vlk8t dot, dot, dot,

211
00:10:37,910 --> 00:10:42,010
I can now remember who he or she is
and reassociate with that user whatever

212
00:10:42,010 --> 00:10:46,450
information I want to, and all of
that information can be stored by you,

213
00:10:46,450 --> 00:10:50,130
the programmer, in $_SESSION.

214
00:10:50,130 --> 00:10:57,170
>> So to be clear, if I open up real quick
in gedit that actual file, counter.php,

215
00:10:57,170 --> 00:11:02,340
in my local host public directory
as before, notice that, indeed,

216
00:11:02,340 --> 00:11:06,860
I'm ultimately storing in
$_SESSION quote unquote "counter,"

217
00:11:06,860 --> 00:11:10,110
the value of the previous counter which
I get from these lines up here that we

218
00:11:10,110 --> 00:11:13,010
looked at last time plus one.

219
00:11:13,010 --> 00:11:14,980
So underneath the hood,
that's all cookies are.

220
00:11:14,980 --> 00:11:17,563
It's just the sort of digital
hand stamp going back and forth,

221
00:11:17,563 --> 00:11:20,450
and frankly if you open Chrome's
Inspector on any website

222
00:11:20,450 --> 00:11:22,580
you visit today, with
super high probability,

223
00:11:22,580 --> 00:11:25,450
you're going to see maybe one,
maybe half a dozen cookies

224
00:11:25,450 --> 00:11:26,650
being remembered by you.

225
00:11:26,650 --> 00:11:29,500
>> And worse yet, if those
website you're visiting

226
00:11:29,500 --> 00:11:32,640
all have advertisements, which
is certainly quite common today,

227
00:11:32,640 --> 00:11:36,100
and if those advertisements are coming
from some central party, someone

228
00:11:36,100 --> 00:11:39,000
like Google or AdWords as they
call one of their products

229
00:11:39,000 --> 00:11:42,880
or other such vendors that
sell ads, what's interesting,

230
00:11:42,880 --> 00:11:46,510
and frankly what's a little
worrisome, about how HTTP works,

231
00:11:46,510 --> 00:11:50,855
is that if you have an ad embedded
in Facebook.com, and Google.com,

232
00:11:50,855 --> 00:11:54,240
and Harvard.edu, any number
of websites, so it's such

233
00:11:54,240 --> 00:11:58,130
that there's a middle man who is serving
up ads for all three of those websites,

234
00:11:58,130 --> 00:12:02,110
it turns out that the
cookies are per domain.

235
00:12:02,110 --> 00:12:05,910
>> So if you have an ad coming from the
same company on different websites,

236
00:12:05,910 --> 00:12:11,140
that company can effectively track who
you are across all of those websites.

237
00:12:11,140 --> 00:12:13,140
Harvard might not know
you're visiting Facebook.

238
00:12:13,140 --> 00:12:15,306
Facebook might not know and
you're visiting Harvard.

239
00:12:15,306 --> 00:12:18,160
But whatever ad service
they're using if that domain is

240
00:12:18,160 --> 00:12:21,710
present in both Harvard.edu web
pages and Facebook.com webpages,

241
00:12:21,710 --> 00:12:26,850
this middle man surely knows who you are
because of these cookies being shared

242
00:12:26,850 --> 00:12:30,910
across, or rather to,
that so-called middleman.

243
00:12:30,910 --> 00:12:33,820
>> So we'll come back to this in
security implications thereof,

244
00:12:33,820 --> 00:12:37,170
but there's a lot of information
being stored about you any time you

245
00:12:37,170 --> 00:12:40,120
visit most any web page on
the internet and it really

246
00:12:40,120 --> 00:12:42,877
reduces to this very simple mechanism.

247
00:12:42,877 --> 00:12:44,710
What happens, then, if
you're super paranoid

248
00:12:44,710 --> 00:12:48,190
and you decide to go into Chrome or IE
or whatever and turn off your cookies?

249
00:12:48,190 --> 00:12:49,365
What happens?

250
00:12:49,365 --> 00:12:50,790
Yeah?

251
00:12:50,790 --> 00:12:53,170
You really-- you've done this right?

252
00:12:53,170 --> 00:12:54,350
OK.

253
00:12:54,350 --> 00:12:55,994
No, go ahead.

254
00:12:55,994 --> 00:12:59,645
>> AUDIENCE: Certain websites don't have
a function without it like Facebook.

255
00:12:59,645 --> 00:13:00,520
DAVID J. MALAN: Yeah!

256
00:13:00,520 --> 00:13:02,311
So certain websites
will just stop working.

257
00:13:02,311 --> 00:13:05,520
And in most websites these days
that fundamentally rely on cookies,

258
00:13:05,520 --> 00:13:08,360
especially if they have you log
in, they're just going to break.

259
00:13:08,360 --> 00:13:10,360
Because consider the
alternative, if the website

260
00:13:10,360 --> 00:13:14,480
has no way of remembering who you are,
and therefore your web browser is not

261
00:13:14,480 --> 00:13:16,949
representing with every HTTP
request of this hand stamp,

262
00:13:16,949 --> 00:13:18,740
effectively a website
like Facebook's going

263
00:13:18,740 --> 00:13:22,050
to have to prompt you to
log in every darn time

264
00:13:22,050 --> 00:13:26,200
you change pages, or click a link, which
is surely not a particularly good user

265
00:13:26,200 --> 00:13:26,920
experience.

266
00:13:26,920 --> 00:13:30,020
>> So that there, too, is
among the trade offs.

267
00:13:30,020 --> 00:13:34,140
So without further ado, let's take
for granted that with web programming,

268
00:13:34,140 --> 00:13:37,630
in languages like PHP, you can remember
information like that in problem set

269
00:13:37,630 --> 00:13:41,550
seven when you implement your own
E*Trade-like website with which to buy

270
00:13:41,550 --> 00:13:45,710
and sell stocks, you'll remember exactly
what the user has bought and sold

271
00:13:45,710 --> 00:13:49,110
and who he or she is
by way of this session.

272
00:13:49,110 --> 00:13:51,110
But we're going to need
a fancier way than email

273
00:13:51,110 --> 00:13:52,640
to start keeping information around.

274
00:13:52,640 --> 00:13:53,140
Right?

275
00:13:53,140 --> 00:13:56,780
>> On Monday, we talked about Frosh IMs
and how in version one of that website,

276
00:13:56,780 --> 00:14:00,250
years ago, all we did was
email the Proctor who's

277
00:14:00,250 --> 00:14:04,250
in charge of the intramural sports
program, the name, and the gender,

278
00:14:04,250 --> 00:14:07,330
and whether or not they're a captain,
and the dorm of someone who's

279
00:14:07,330 --> 00:14:10,136
registering for an intramural sport.

280
00:14:10,136 --> 00:14:13,010
So it's not bad, but he or she then
had to troll through their email,

281
00:14:13,010 --> 00:14:16,010
make a spreadsheet or something like
that, to keep everything organized.

282
00:14:16,010 --> 00:14:19,750
So surely we as programmers
can do this for that proctor.

283
00:14:19,750 --> 00:14:22,970
And so enter in SQL,
Structured Query Language,

284
00:14:22,970 --> 00:14:26,050
which is going to look pretty
different to both C and PHP,

285
00:14:26,050 --> 00:14:30,990
and you'll dive in much more hands on
PHP and problem set seven but also SQL,

286
00:14:30,990 --> 00:14:35,310
or SQL, this is a language that
you use to talk to a database.

287
00:14:35,310 --> 00:14:36,480
>> But what's a database?

288
00:14:36,480 --> 00:14:38,440
Well you think of a
database, at least for now,

289
00:14:38,440 --> 00:14:41,750
as just being like an Excel file, or
if you're a Mac user a numbers file,

290
00:14:41,750 --> 00:14:44,400
or if you're a Google Apps
user a Google spreadsheet,

291
00:14:44,400 --> 00:14:49,120
it's effectively a database, or really
specifically a relational database.

292
00:14:49,120 --> 00:14:53,070
A relational database is just
something that has rows and columns,

293
00:14:53,070 --> 00:14:56,440
and you can store any kind of
information in these rows or columns.

294
00:14:56,440 --> 00:15:00,480
>> But what's nice about SQL, and
about actual databases, not just

295
00:15:00,480 --> 00:15:04,910
spreadsheets or Google spreadsheets,
is that you can use a language

296
00:15:04,910 --> 00:15:09,000
to actually execute queries to
insert data, to remove data,

297
00:15:09,000 --> 00:15:11,620
to look for data, even
most importantly, and you

298
00:15:11,620 --> 00:15:16,110
don't have to use it fairly manually
as you might typically a Google

299
00:15:16,110 --> 00:15:17,690
spreadsheet like this.

300
00:15:17,690 --> 00:15:22,217
>> So in SQL, there's a bunch of
fundamental statements or pieces

301
00:15:22,217 --> 00:15:23,300
of functionality built in.

302
00:15:23,300 --> 00:15:26,450
There's many more than these,
but you can go a huge distance

303
00:15:26,450 --> 00:15:28,620
just by knowing that
this language called

304
00:15:28,620 --> 00:15:30,840
SQL has at least four
statements you can leverage.

305
00:15:30,840 --> 00:15:34,420
>> Delete, for removing data,
Insert, for adding rows,

306
00:15:34,420 --> 00:15:37,340
Update, for changing
rows, and Selecting,

307
00:15:37,340 --> 00:15:39,860
for getting back rows and
that's indeed what SQL does.

308
00:15:39,860 --> 00:15:43,810
It operates entirely on rows so
that when you insert, or remove,

309
00:15:43,810 --> 00:15:47,470
or update, or select what you're
getting back as a so-called result set,

310
00:15:47,470 --> 00:15:49,690
like an array of rows.

311
00:15:49,690 --> 00:15:51,700
A bunch of rows from a table.

312
00:15:51,700 --> 00:15:54,050
>> So back in the day,
and even to this day,

313
00:15:54,050 --> 00:15:56,560
you can interact with the
database using a command line,

314
00:15:56,560 --> 00:15:59,691
but it's not particularly fun to use
this black and white style window

315
00:15:59,691 --> 00:16:02,190
and actually execute commands
and poke around your database.

316
00:16:02,190 --> 00:16:06,054
A graphical user interface, or GUI,
is much more preferable, arguably,

317
00:16:06,054 --> 00:16:08,970
and so the tool we recommend and
preinstalled for you on the appliance

318
00:16:08,970 --> 00:16:10,580
is called phpMyAdmin.

319
00:16:10,580 --> 00:16:14,060
It's a total coincidence that the
name of this thing has PHP in it,

320
00:16:14,060 --> 00:16:17,430
it just means that the people
who wrote this program themselves

321
00:16:17,430 --> 00:16:18,670
wrote it in PHP.

322
00:16:18,670 --> 00:16:23,740
>> But it's ultimately about administering
a database server, like a MYSQL server

323
00:16:23,740 --> 00:16:26,589
that you might have, as you
do, in the CS50 appliance.

324
00:16:26,589 --> 00:16:29,130
So there's more detail here than
we need to care about today,

325
00:16:29,130 --> 00:16:33,280
but what's key is that on the left
hand side is a list of the databases

326
00:16:33,280 --> 00:16:36,040
that you have on your computer,
on your CS50 appliance,

327
00:16:36,040 --> 00:16:40,090
or come final projects that you might
have on a third party, a company's

328
00:16:40,090 --> 00:16:43,415
website or web server, that
you might be paying for space.

329
00:16:43,415 --> 00:16:45,290
So on the left is the
databases, one of which

330
00:16:45,290 --> 00:16:48,750
is pset7 which I borrowed from next
weeks pset, and then on the top

331
00:16:48,750 --> 00:16:51,570
there notice there's a
bunch of tabs, one of which

332
00:16:51,570 --> 00:16:55,150
is databases, SQL, status,
users, export and so forth.

333
00:16:55,150 --> 00:16:56,900
So you can go a long
way just by realizing

334
00:16:56,900 --> 00:16:59,770
that most of the user interface
is in the top left column

335
00:16:59,770 --> 00:17:02,650
and across the top right up there.

336
00:17:02,650 --> 00:17:04,980
So what can we actually do with this?

337
00:17:04,980 --> 00:17:08,609
Well, let's start creating a
bit of information as follows.

338
00:17:08,609 --> 00:17:11,760
>> Suppose the following is the case,
as will be in just a few days,

339
00:17:11,760 --> 00:17:14,440
you want to implement a
website, called CS50 Finance,

340
00:17:14,440 --> 00:17:17,328
and this website lets you buy
quote unquote and sell stocks.

341
00:17:17,328 --> 00:17:19,619
And it's going to figure out
the price of those stocks,

342
00:17:19,619 --> 00:17:22,380
ultimately as you'll see,
by talking to Yahoo Finance.

343
00:17:22,380 --> 00:17:26,250
Which, wonderfully, has a free service
whereby you can pass in a stock ticker

344
00:17:26,250 --> 00:17:29,830
like GOOG for Google, and it will
give you back Google's current stock

345
00:17:29,830 --> 00:17:32,250
price within the past
few minutes at least.

346
00:17:32,250 --> 00:17:35,080
>> So you'll use that, ultimately,
to pretend for the user

347
00:17:35,080 --> 00:17:37,750
to buy and sell actual
stocks using virtual money,

348
00:17:37,750 --> 00:17:39,750
but the very first thing
the user's going to see

349
00:17:39,750 --> 00:17:43,850
is this login screen which asks them
for their username and password.

350
00:17:43,850 --> 00:17:46,540
And so, one of the first
challenges for you in pset7

351
00:17:46,540 --> 00:17:50,460
is going to be to implement the back end
database, the spreadsheet if you will,

352
00:17:50,460 --> 00:17:53,369
that's going to store
users names and passwords

353
00:17:53,369 --> 00:17:56,660
and ultimately what stocks they own, and
how many, and how much cash they have,

354
00:17:56,660 --> 00:18:00,110
so a bunch of other things in
other tables, or spreadsheets.

355
00:18:00,110 --> 00:18:05,020
>> So let's take a look at how this
might appear at first glance.

356
00:18:05,020 --> 00:18:06,980
I'm going to go back to
the appliance and I'm

357
00:18:06,980 --> 00:18:14,102
going to go to this URL here
phpMyAdmin localhost/phpmyadmin

358
00:18:14,102 --> 00:18:16,060
and you'll see that it
takes me to an interface

359
00:18:16,060 --> 00:18:18,520
exactly as we saw on the
screen shot, and here I

360
00:18:18,520 --> 00:18:21,560
have an additional database
called lecture for today

361
00:18:21,560 --> 00:18:24,280
and let me go ahead
first and click on pset7.

362
00:18:24,280 --> 00:18:27,940
>> I seem to have a couple of options,
one for new , for creating a new table,

363
00:18:27,940 --> 00:18:30,770
and a link to users, which
is a table I already created.

364
00:18:30,770 --> 00:18:31,790
So what's a table?

365
00:18:31,790 --> 00:18:33,740
So if you used Excel
before, and if you've

366
00:18:33,740 --> 00:18:37,110
used numbers or Google
Spreadsheets, you open up a window

367
00:18:37,110 --> 00:18:39,350
and you get a whole bunch
of rows and columns,

368
00:18:39,350 --> 00:18:43,120
but then you usually have worksheets
along the bottom, or separate tabs.

369
00:18:43,120 --> 00:18:46,140
You can think of each
worksheet as a table

370
00:18:46,140 --> 00:18:51,150
so that database, ultimately, is a
combination of one or more tables, one

371
00:18:51,150 --> 00:18:54,064
or more worksheets, in the
world of a normal spreadsheet.

372
00:18:54,064 --> 00:18:55,980
So let me go ahead and
click on this worksheet

373
00:18:55,980 --> 00:18:59,420
that I premade, called users, a.k.a.

374
00:18:59,420 --> 00:19:00,700
Database table.

375
00:19:00,700 --> 00:19:04,130
And if I scroll down here,
let me zoom out a bit,

376
00:19:04,130 --> 00:19:08,479
this is what phpMyAdmin is telling
us is inside of this table right now.

377
00:19:08,479 --> 00:19:11,020
It's a little confusing at first
glance because the UI is not

378
00:19:11,020 --> 00:19:15,140
the prettiest thing in the world, but
what is interesting is this part here.

379
00:19:15,140 --> 00:19:17,970
ID, username, and hash.

380
00:19:17,970 --> 00:19:20,510
>> In advance, and you'll be handed
this in problem set seven,

381
00:19:20,510 --> 00:19:25,050
we give you a file containing a super
small database table, borrowed actually

382
00:19:25,050 --> 00:19:27,070
from the hacker edition
of problem set two,

383
00:19:27,070 --> 00:19:29,480
inside of which there are six rows.

384
00:19:29,480 --> 00:19:32,720
One for Belinda all the
way down to one for Zamyla,

385
00:19:32,720 --> 00:19:35,980
and notice to the left of those
usernames are unique IDs like one,

386
00:19:35,980 --> 00:19:39,410
two, three, four, five, six, integers,
and then to the right are hashes.

387
00:19:39,410 --> 00:19:42,780
>> And if, odds are, you didn't do
the hacker edition problem set two,

388
00:19:42,780 --> 00:19:46,560
but a hash is just like an encrypted
password with a few caveats.

389
00:19:46,560 --> 00:19:49,470
And so, what you see here are
the encrypted versions of all six

390
00:19:49,470 --> 00:19:52,950
of our passwords from problem
set two's hacker edition.

391
00:19:52,950 --> 00:19:56,500
Now to the left is just some GUI stuff,
editing this row, copying this row,

392
00:19:56,500 --> 00:19:57,630
deleting this row.

393
00:19:57,630 --> 00:19:59,840
>> But what's interesting
now is the following.

394
00:19:59,840 --> 00:20:03,810
I can actually start
experimenting with this table.

395
00:20:03,810 --> 00:20:07,330
So if I go and click the SQL
tab, I get this big text box.

396
00:20:07,330 --> 00:20:10,190
And this is not how we're going to
do it when actually writing code.

397
00:20:10,190 --> 00:20:12,700
To be clear, phpMyAdmin
is just a tool that's

398
00:20:12,700 --> 00:20:16,450
going to let us poke around the database
and let us experiment with queries.

399
00:20:16,450 --> 00:20:19,430
>> So for instance, suppose
I execute exactly this.

400
00:20:19,430 --> 00:20:22,820
Select, which is one of those
keywords I mentioned earlier, star,

401
00:20:22,820 --> 00:20:25,900
which represents all
the columns in a table.

402
00:20:25,900 --> 00:20:26,820
From what table?

403
00:20:26,820 --> 00:20:27,990
Well, users.

404
00:20:27,990 --> 00:20:29,950
And notice there's this
weird convention in SQL

405
00:20:29,950 --> 00:20:32,140
where you actually use
back ticks, typically,

406
00:20:32,140 --> 00:20:35,940
not single quotes and not double quotes
when you talk about tables names,

407
00:20:35,940 --> 00:20:38,990
so the back quote is the thing on the
top left hand of your keyboard most

408
00:20:38,990 --> 00:20:39,720
likely.

409
00:20:39,720 --> 00:20:41,850
>> So let me go ahead now
and just leave that alone

410
00:20:41,850 --> 00:20:46,020
and scroll down and click Go, and we're
actually going to see the same thing.

411
00:20:46,020 --> 00:20:52,410
We have just executed a SQL query
saying select everything star

412
00:20:52,410 --> 00:20:55,610
from table called users, and
what you get back is this.

413
00:20:55,610 --> 00:20:58,400
Ultimately, we'll be able to
do that same thing in code,

414
00:20:58,400 --> 00:21:02,109
but for now all I wanted to
do was see it in my browser.

415
00:21:02,109 --> 00:21:03,900
Well let's do something
a little different.

416
00:21:03,900 --> 00:21:08,330
Let me go back to the SQL tab,
and let's just say that what?

417
00:21:08,330 --> 00:21:11,520
Zamyla has lost all of her
money, and therefore it's

418
00:21:11,520 --> 00:21:13,190
time for us to delete her as a user.

419
00:21:13,190 --> 00:21:14,630
She's no longer logging in.

420
00:21:14,630 --> 00:21:18,870
>> So I'm going to say delete from--
well, maintain capitalization

421
00:21:18,870 --> 00:21:23,080
for consistency, delete
from users where.

422
00:21:23,080 --> 00:21:25,430
And so, we can have these
predicates, or these

423
00:21:25,430 --> 00:21:31,180
qualifiers, at the end of my statement
where and how could I delete Zamyla?

424
00:21:31,180 --> 00:21:34,190
By her name Zamyla, so the
column, one of the columns

425
00:21:34,190 --> 00:21:37,950
was named, so where name = "Zamyla".

426
00:21:37,950 --> 00:21:40,000
And here I use double
quotes or single quotes,

427
00:21:40,000 --> 00:21:42,958
you only use the back ticks when
talking about the names, for instance,

428
00:21:42,958 --> 00:21:45,130
of tables or fields.

429
00:21:45,130 --> 00:21:47,440
And let me click Go here.

430
00:21:47,440 --> 00:21:50,400
And now, the web page is
being a little uptight.

431
00:21:50,400 --> 00:21:53,620
>> Or, do you really want to execute delete
from users where name equals Zamyla?

432
00:21:53,620 --> 00:21:54,680
Yes.

433
00:21:54,680 --> 00:22:01,900
So now, if we go back to my table
by clicking users, notice that Hm.

434
00:22:01,900 --> 00:22:02,530
I goofed.

435
00:22:02,530 --> 00:22:04,070
And in fact, I kind of
clicked away so fast

436
00:22:04,070 --> 00:22:06,195
you didn't even see the
red error message, perhaps.

437
00:22:06,195 --> 00:22:07,649
What did I do wrong?

438
00:22:07,649 --> 00:22:09,690
AUDIENCE: You didn't need
to capitalize her name.

439
00:22:09,690 --> 00:22:11,260
DAVID J. MALAN: Yeah I
capitalized her name,

440
00:22:11,260 --> 00:22:13,770
but her username-- actually I
made a couple mistakes, right?

441
00:22:13,770 --> 00:22:16,720
One, her username is
zamyla, lowercase Z,

442
00:22:16,720 --> 00:22:20,140
and the column name is username,
not name, so let's do this again.

443
00:22:20,140 --> 00:22:25,750
Let me go ahead and
delete from users where

444
00:22:25,750 --> 00:22:28,990
username equals quote unquote "Zamyla".

445
00:22:28,990 --> 00:22:29,490
All right?

446
00:22:29,490 --> 00:22:32,600
So this looks a little better, let
me go scroll down and click Go.

447
00:22:32,600 --> 00:22:34,730
It's still going to
yell at me to be sure.

448
00:22:34,730 --> 00:22:37,500
I click Yes, and now we see,
frankly this happened, really

449
00:22:37,500 --> 00:22:39,870
fast, less than one
second certainly, this

450
00:22:39,870 --> 00:22:41,720
is exactly the query that got executed.

451
00:22:41,720 --> 00:22:45,617
To confirm, let me click users
and indeed now Zamyla is gone.

452
00:22:45,617 --> 00:22:46,700
Now let's do the opposite.

453
00:22:46,700 --> 00:22:49,320
Suppose that Gabe wants to
register for the website.

454
00:22:49,320 --> 00:22:52,825
What's the SQL query, what's the
command I could type to add Gabe?

455
00:22:52,825 --> 00:22:54,200
Well it's pretty straightforward.

456
00:22:54,200 --> 00:22:58,260
Insert into users, and now
it gets a little cryptic.

457
00:22:58,260 --> 00:23:03,190
I need to specify, to the server,
what fields I want to assign.

458
00:23:03,190 --> 00:23:06,630
I don't really care what Gabe's ID
number is, so I'm going to skip that.

459
00:23:06,630 --> 00:23:11,360
I'm instead going to say
username, hash, and then

460
00:23:11,360 --> 00:23:14,960
the values I want to put
there is going to be Gabe.

461
00:23:14,960 --> 00:23:16,800
And then his hash, I don't know.

462
00:23:16,800 --> 00:23:19,900
So for now, I'm going to
leave that as a big to do.

463
00:23:19,900 --> 00:23:21,650
We'll come back to
that in the problem set

464
00:23:21,650 --> 00:23:23,390
spec as to how you actually do that.

465
00:23:23,390 --> 00:23:24,630
>> So notice, again, the syntax.

466
00:23:24,630 --> 00:23:28,430
Insert into table name, then a
parenthesized list of the fields,

467
00:23:28,430 --> 00:23:30,980
the columns you want to add
values to, and then just

468
00:23:30,980 --> 00:23:34,495
the same exact ordering left to
right of the values you want to add,

469
00:23:34,495 --> 00:23:36,870
and it's just wrapping because
the text is a little long.

470
00:23:36,870 --> 00:23:38,520
So now let me click Go.

471
00:23:38,520 --> 00:23:39,830
One row inserted.

472
00:23:39,830 --> 00:23:43,020
And now if I go back to
users, what's interesting

473
00:23:43,020 --> 00:23:48,960
is that not only is Gabe now in the
database, what is apparently his ID?

474
00:23:48,960 --> 00:23:49,820
>> Well it's seven.

475
00:23:49,820 --> 00:23:51,479
Why is it seven when I didn't add it?

476
00:23:51,479 --> 00:23:54,020
So this, too, is one of the
features you get of the database.

477
00:23:54,020 --> 00:23:55,750
A lot of built in functionality.

478
00:23:55,750 --> 00:23:57,950
It turns out that when
created this table,

479
00:23:57,950 --> 00:24:01,390
I preconfigured it's automatically
assign an ID in such a way

480
00:24:01,390 --> 00:24:02,480
that it increments.

481
00:24:02,480 --> 00:24:05,470
So if you've ever poked around,
and looked at what your Facebook ID

482
00:24:05,470 --> 00:24:09,292
number is, these days it's not really
a thing to do, but Facebook as an API,

483
00:24:09,292 --> 00:24:11,750
Application Programming Interface,
whereby you can get back

484
00:24:11,750 --> 00:24:14,430
a whole bunch of data about
yourself, about your friends,

485
00:24:14,430 --> 00:24:15,347
and your connections.

486
00:24:15,347 --> 00:24:17,430
And what used to be kind
of cool, back in the day,

487
00:24:17,430 --> 00:24:19,510
was to look up what your
Facebook ID number was.

488
00:24:19,510 --> 00:24:22,390
Mark Zuckerberg's,
for instance, is three

489
00:24:22,390 --> 00:24:23,890
since he was the author of the site.

490
00:24:23,890 --> 00:24:27,610
And as the story goes, he created
two test accounts, users one and two,

491
00:24:27,610 --> 00:24:28,690
which he then deleted.

492
00:24:28,690 --> 00:24:32,780
And so, Zuck, as is his username
on Facebook, is ID number three,

493
00:24:32,780 --> 00:24:36,110
and all of us have numbers much
larger than three these days.

494
00:24:36,110 --> 00:24:37,980
In fact, at some point
Facebook moved away

495
00:24:37,980 --> 00:24:42,410
from even using an int, which
is a 32-bit value, to using

496
00:24:42,410 --> 00:24:44,480
the next step up,
essentially a long long so

497
00:24:44,480 --> 00:24:47,150
that they could accommodate
even more users registering.

498
00:24:47,150 --> 00:24:49,420
So a fun little historical fact.

499
00:24:49,420 --> 00:24:51,660
>> So that's just the
basic syntax with which

500
00:24:51,660 --> 00:24:54,470
we might execute a couple of
queries, but we can actually

501
00:24:54,470 --> 00:24:56,744
do a bunch more things with SQL.

502
00:24:56,744 --> 00:24:58,910
And you'll see, ultimately,
in the problem set seven

503
00:24:58,910 --> 00:25:01,034
that you have to make a
number of design decisions,

504
00:25:01,034 --> 00:25:03,290
among them is going to be
what data types to use.

505
00:25:03,290 --> 00:25:08,240
So just like in C, there are data
types in a database, like MySQL,

506
00:25:08,240 --> 00:25:12,640
and the data types you have to choose
from include these fields here.

507
00:25:12,640 --> 00:25:17,287
Char, varchar, Int, big int, decimal
and date time, and many others.

508
00:25:17,287 --> 00:25:18,370
So let's actually do this.

509
00:25:18,370 --> 00:25:21,060
Let's pretend that we didn't
hand you this user's table

510
00:25:21,060 --> 00:25:25,080
and let me go ahead and create, for
myself, in the lectures database--

511
00:25:25,080 --> 00:25:31,000
actually let me go ahead and delete
the table I have in here already

512
00:25:31,000 --> 00:25:32,940
so that we can actually create this.

513
00:25:32,940 --> 00:25:33,550
Whoops.

514
00:25:33,550 --> 00:25:35,970
I'm going to drop this
table, and now I'm

515
00:25:35,970 --> 00:25:38,337
going to go again to the
lecture database over here,

516
00:25:38,337 --> 00:25:40,420
I'm going to create a table
called users and let's

517
00:25:40,420 --> 00:25:43,010
just do three columns
initially and click Go.

518
00:25:43,010 --> 00:25:44,990
>> Now, for the most part,
again, this is just

519
00:25:44,990 --> 00:25:48,570
using this graphical tool called
phpMyAdmin, and what we're doing now

520
00:25:48,570 --> 00:25:49,600
is creating a table.

521
00:25:49,600 --> 00:25:53,170
So this is like going File, New,
and creating a new Excel file.

522
00:25:53,170 --> 00:25:55,440
So it's asking me a few
questions, from left to right,

523
00:25:55,440 --> 00:25:58,620
what's the name of the first column,
and then the name of the second column,

524
00:25:58,620 --> 00:25:59,560
and the name of the third.

525
00:25:59,560 --> 00:26:00,518
So let's recreate this.

526
00:26:00,518 --> 00:26:05,460
ID, and then username was one,
and then hash was another.

527
00:26:05,460 --> 00:26:08,970
So what should the data type
be now for a field like ID?

528
00:26:08,970 --> 00:26:14,470
>> Here is the whole list of data types
available to you in a database,

529
00:26:14,470 --> 00:26:16,070
and for now let's just go with int.

530
00:26:16,070 --> 00:26:18,160
32-bit value, I don't
think realistically I'm

531
00:26:18,160 --> 00:26:21,484
going to have more than 4 billion
users in my account, in my service,

532
00:26:21,484 --> 00:26:23,650
so I'm going to keep moving
on to the next question.

533
00:26:23,650 --> 00:26:25,490
I'm not going to specify
a length or values,

534
00:26:25,490 --> 00:26:28,540
it's not applicable
here for an int, per se.

535
00:26:28,540 --> 00:26:30,740
And now I can specify,
apparently, a default

536
00:26:30,740 --> 00:26:33,970
value, which I'm not going to specify.

537
00:26:33,970 --> 00:26:36,050
A collation, I don't know what that is.

538
00:26:36,050 --> 00:26:37,290
An attribute.

539
00:26:37,290 --> 00:26:39,455
Now we actually do
have a design decision.

540
00:26:39,455 --> 00:26:42,580
So there's a few fields here, not all
of which are applicable, but unsigned

541
00:26:42,580 --> 00:26:43,380
just means what?

542
00:26:43,380 --> 00:26:45,400
That the int must be?

543
00:26:45,400 --> 00:26:46,210
Just non-negative.

544
00:26:46,210 --> 00:26:48,090
So it has to be 0 on up.

545
00:26:48,090 --> 00:26:51,120
No, I'm not going to check because
I want every user to have an ID,

546
00:26:51,120 --> 00:26:52,470
it cannot be null.

547
00:26:52,470 --> 00:26:55,949
And then, we get to some more
interesting design decisions like this.

548
00:26:55,949 --> 00:26:58,990
We'll come back to this in a moment,
but what another feature of database

549
00:26:58,990 --> 00:27:04,200
is, is that you can tell
the database server go ahead

550
00:27:04,200 --> 00:27:07,100
and optimize yourself, your
RAM and your hard disk space,

551
00:27:07,100 --> 00:27:11,770
so that selects, and inserts, and
deletes, and updates are really fast.

552
00:27:11,770 --> 00:27:13,250
Contrast this with pset5.

553
00:27:13,250 --> 00:27:16,259
>> If you wanted to look up something
in your hash table, which

554
00:27:16,259 --> 00:27:18,300
you think of as a database,
who had to do all the

555
00:27:18,300 --> 00:27:21,500
work for making your hash table fast.

556
00:27:21,500 --> 00:27:22,840
It's like, obviously, you.

557
00:27:22,840 --> 00:27:23,060
Right?

558
00:27:23,060 --> 00:27:26,080
You had to put in all the time fine
tuning things, getting a hash function

559
00:27:26,080 --> 00:27:27,820
right, figuring out how
many buckets to have.

560
00:27:27,820 --> 00:27:29,611
>> But what's nice, again,
about a database is

561
00:27:29,611 --> 00:27:31,762
you just punt all of
this to other people

562
00:27:31,762 --> 00:27:33,720
who have thought this
through for you, and what

563
00:27:33,720 --> 00:27:37,170
I'm going to say here under
index is that my ID field

564
00:27:37,170 --> 00:27:41,149
is going to be the primary way of
identifying users in this database.

565
00:27:41,149 --> 00:27:42,940
I'm not going to think
of Zamyla as Zamyla,

566
00:27:42,940 --> 00:27:45,800
I'm going to think of
her as the number 6.

567
00:27:45,800 --> 00:27:49,814
>> Why is it, perhaps, better
intuitively to think of and model

568
00:27:49,814 --> 00:27:52,480
each of your individual rows using
a number instead of something

569
00:27:52,480 --> 00:27:56,480
like a string, like the Zamyla
or Gabe or longer string still?

570
00:27:56,480 --> 00:27:57,444
Yeah?

571
00:27:57,444 --> 00:28:00,117
>> AUDIENCE: An ID is unique?

572
00:28:00,117 --> 00:28:01,200
DAVID J. MALAN: Say again?

573
00:28:01,200 --> 00:28:02,283
AUDIENCE: An ID is unique?

574
00:28:02,283 --> 00:28:04,400
DAVID J. MALAN: An ID
is unique, but suppose--

575
00:28:04,400 --> 00:28:06,320
as the case in general
with usernames, suppose

576
00:28:06,320 --> 00:28:10,110
I also said there can only be one
Zamyla in the world, and only one Gabe.

577
00:28:10,110 --> 00:28:13,730
I could impose the uniqueness
constraint on strings, too, if I wanted.

578
00:28:13,730 --> 00:28:15,550
So not a bad thought.

579
00:28:15,550 --> 00:28:16,500
>> AUDIENCE: More secure.

580
00:28:16,500 --> 00:28:17,874
>> DAVID J. MALAN: More secure, why?

581
00:28:17,874 --> 00:28:20,705
AUDIENCE: You can't tell which
is which, as in the user.

582
00:28:20,705 --> 00:28:22,580
DAVID J. MALAN: OK, you
can't tell which user

583
00:28:22,580 --> 00:28:24,380
is which so there's a
privacy aspect to it,

584
00:28:24,380 --> 00:28:27,810
especially if the IDs maybe
appearing in the URLs.

585
00:28:27,810 --> 00:28:29,960
So sure, that could kind of work, too.

586
00:28:29,960 --> 00:28:30,640
Other thoughts?

587
00:28:30,640 --> 00:28:31,383
Yeah?

588
00:28:31,383 --> 00:28:34,316
>> AUDIENCE: It's easier to
perform operations on an int.

589
00:28:34,316 --> 00:28:35,940
DAVID J. MALAN: That's the real kicker.

590
00:28:35,940 --> 00:28:38,850
It's just more efficient,
or easier for the computer,

591
00:28:38,850 --> 00:28:40,431
to perform operations on an integer.

592
00:28:40,431 --> 00:28:40,930
Right?

593
00:28:40,930 --> 00:28:43,905
An int is guaranteed to
be 32-bit, whereas Zamyla

594
00:28:43,905 --> 00:28:47,660
is a few characters long, Gabriel
is a few more characters long,

595
00:28:47,660 --> 00:28:51,930
Davenport is really long, and so it's
not particularly efficient to use

596
00:28:51,930 --> 00:28:55,860
strings to compare values and look
for fields, and update fields,

597
00:28:55,860 --> 00:28:57,790
if you can get away
with just one integer.

598
00:28:57,790 --> 00:28:59,090
Just 32 bits.

599
00:28:59,090 --> 00:29:02,570
So usernames, too, this way,
don't have to be unique,

600
00:29:02,570 --> 00:29:05,040
although they probably should
be, and even in this way too

601
00:29:05,040 --> 00:29:07,520
a user could be allowed to
change his or her username.

602
00:29:07,520 --> 00:29:10,810
>> So let's now leave this as the
primary means of identifying the user.

603
00:29:10,810 --> 00:29:13,510
This is telling the database
go ahead and optimize yourself

604
00:29:13,510 --> 00:29:17,065
so that look ups on ID are super fast.

605
00:29:17,065 --> 00:29:19,620
AI, horribly named, just
means Auto Increment,

606
00:29:19,620 --> 00:29:21,500
and this is the check
box we need to check

607
00:29:21,500 --> 00:29:24,614
to specify that the ID field to
be automatically updated for me,

608
00:29:24,614 --> 00:29:26,530
and then I'm going to
scroll to the right here

609
00:29:26,530 --> 00:29:29,279
and frankly I'm not really interested
in any more of these fields.

610
00:29:29,279 --> 00:29:30,630
Certainly not today.

611
00:29:30,630 --> 00:29:33,770
>> So I'm going to go back here,
to the first column, where

612
00:29:33,770 --> 00:29:35,830
I need to specify username
and hash, and let's

613
00:29:35,830 --> 00:29:38,080
at least focus on the
second one for now.

614
00:29:38,080 --> 00:29:41,498
Int is probably not the right call,
so what makes more sense perhaps?

615
00:29:41,498 --> 00:29:42,741
>> AUDIENCE: Text.

616
00:29:42,741 --> 00:29:43,824
DAVID J. MALAN: Say again?

617
00:29:43,824 --> 00:29:44,710
AUDIENCE: Text.

618
00:29:44,710 --> 00:29:44,980
DAVID J. MALAN: Text?

619
00:29:44,980 --> 00:29:45,590
OK, I heard text.

620
00:29:45,590 --> 00:29:46,090
What else?

621
00:29:46,090 --> 00:29:50,520

622
00:29:50,520 --> 00:29:53,860
We kind of have a bunch of choices
that are textual in nature.

623
00:29:53,860 --> 00:29:55,990
So when, and why, do
you use some of these?

624
00:29:55,990 --> 00:29:59,560
Well char, contrary to what you might
think, is not a single character.

625
00:29:59,560 --> 00:30:01,550
It's a specific number of characters.

626
00:30:01,550 --> 00:30:04,600
So if we know that all usernames
must be like eight characters,

627
00:30:04,600 --> 00:30:08,490
as used to be common in older
computer systems, I could say char

628
00:30:08,490 --> 00:30:09,830
and then I could say 8 here.

629
00:30:09,830 --> 00:30:12,930
That's when the third column becomes
applicable when creating a table.

630
00:30:12,930 --> 00:30:15,450
>> But that's kind of annoying
because some people might

631
00:30:15,450 --> 00:30:17,660
want to have a longer username
than eight characters,

632
00:30:17,660 --> 00:30:19,743
some people might want to
have a shorter username,

633
00:30:19,743 --> 00:30:22,210
so why commit myself
to a specific number?

634
00:30:22,210 --> 00:30:24,710
Why not have a variable
number of chars and just

635
00:30:24,710 --> 00:30:28,580
say that the maximum length of a name
is, I don't know, like 64 characters.

636
00:30:28,580 --> 00:30:31,780
I can't think of any friends who
have names longer than 64 characters,

637
00:30:31,780 --> 00:30:34,810
and even if that's too short you could
certainly bump it up arbitrarily.

638
00:30:34,810 --> 00:30:37,330
>> So varchar is a variable
number of chars.

639
00:30:37,330 --> 00:30:41,010
Text is not a bad instinct, and
frankly that sort of does what it says,

640
00:30:41,010 --> 00:30:45,460
but a text field can be
like 65,000 bytes at least.

641
00:30:45,460 --> 00:30:50,790
That's probably overkill for a
field, and in fact, yup, 65,535.

642
00:30:50,790 --> 00:30:53,740
That's probably overkill for a
name, so we'll stick, typically,

643
00:30:53,740 --> 00:30:56,910
with varchars for textual
field and hash, too.

644
00:30:56,910 --> 00:30:59,990
Hash, it turns out, we could do a
varchar as well or something like that,

645
00:30:59,990 --> 00:31:03,080
but we won't focus today on the
cryptography there and the numbers

646
00:31:03,080 --> 00:31:05,210
that we might actually
want to use for its length.

647
00:31:05,210 --> 00:31:07,430
>> But let me scroll down to the right.

648
00:31:07,430 --> 00:31:11,280
You can only have one
primary index for a table,

649
00:31:11,280 --> 00:31:16,380
but do I want to apply any of these,
now, to username, would you say?

650
00:31:16,380 --> 00:31:21,980
What should username be based on a vague
understanding of these four options?

651
00:31:21,980 --> 00:31:23,340
Just by their names?

652
00:31:23,340 --> 00:31:24,140
>> AUDIENCE: Unique.

653
00:31:24,140 --> 00:31:25,100
>> DAVID J. MALAN: So unique, right?

654
00:31:25,100 --> 00:31:28,190
So it turns out that not only could
you tell a database, in advance,

655
00:31:28,190 --> 00:31:30,380
this is the primary way
of identifying fields.

656
00:31:30,380 --> 00:31:32,990
You can also say this is
going to be a unique field.

657
00:31:32,990 --> 00:31:34,700
It's not going to be
the thing I rely on,

658
00:31:34,700 --> 00:31:38,490
but I would like the database to
essentially have that if condition, so

659
00:31:38,490 --> 00:31:42,340
that if I ever tried to register
two users with the same name,

660
00:31:42,340 --> 00:31:44,360
the database flat out
is not going to let me.

661
00:31:44,360 --> 00:31:47,490
I might have some additional code
in PHP that prevents as much,

662
00:31:47,490 --> 00:31:50,640
but the database, too, can ensure
that that's never going to happen.

663
00:31:50,640 --> 00:31:53,370
>> Now, as an aside, especially as
you think about final projects,

664
00:31:53,370 --> 00:31:57,030
keep in mind it index and full
text are actually quite useful.

665
00:31:57,030 --> 00:32:01,080
If you have a larger database, not with
dozens, but with hundreds or thousands

666
00:32:01,080 --> 00:32:05,270
or even millions of fields, you can
also tell the database in advance

667
00:32:05,270 --> 00:32:07,980
this is a field I'm going
to be searching on a lot.

668
00:32:07,980 --> 00:32:10,520
Maybe its username,
maybe it's bio, if you're

669
00:32:10,520 --> 00:32:13,750
making a Facebook-like website that
has paragraphs that the user's allowed

670
00:32:13,750 --> 00:32:16,799
to save, and if you want to
tell the database in advance

671
00:32:16,799 --> 00:32:20,090
I'm going to be searching on this field
a lot, but it's not necessarily unique,

672
00:32:20,090 --> 00:32:22,800
you can specify create me an index.

673
00:32:22,800 --> 00:32:27,990
Or, you can say also allow me to do
sort of arbitrary searches like Command

674
00:32:27,990 --> 00:32:30,420
or Control F, like you
might in a Word Processor,

675
00:32:30,420 --> 00:32:34,184
so you could look arbitrary strings
or substrings in this field.

676
00:32:34,184 --> 00:32:36,600
In other words, we're getting
to the point in the semester

677
00:32:36,600 --> 00:32:40,720
where you don't have to worry about
how to implement things efficiently.

678
00:32:40,720 --> 00:32:44,540
You just need to know about what
design decisions to make so that you're

679
00:32:44,540 --> 00:32:48,470
using the right tools for the
trade in order to leverage features

680
00:32:48,470 --> 00:32:50,380
that other people have built for you.

681
00:32:50,380 --> 00:32:54,240
So to recap, primary should only
have one, you can only have one,

682
00:32:54,240 --> 00:32:59,630
and it's the thing you're committing to
using to identifying fields uniquely.

683
00:32:59,630 --> 00:33:02,710
Unique is just similar in spirit, but
you might only occasionally use it,

684
00:33:02,710 --> 00:33:04,530
but you want the database to impose it.

685
00:33:04,530 --> 00:33:08,050
Index just means preemptively
speed things up in the future

686
00:33:08,050 --> 00:33:10,230
so that I can search for
things in this field.

687
00:33:10,230 --> 00:33:13,700
And then full text is generally for
paragraphs, or essays, or large bodies

688
00:33:13,700 --> 00:33:16,270
of text where you
might also want to have

689
00:33:16,270 --> 00:33:19,420
wild cards like the equivalent of star.

690
00:33:19,420 --> 00:33:19,920
Right.

691
00:33:19,920 --> 00:33:22,580
>> So that was kind of
a lot to all at once.

692
00:33:22,580 --> 00:33:25,220
Let's see if we can't distill
a couple of these features

693
00:33:25,220 --> 00:33:29,540
and then build something
fairly simple, but powerful.

694
00:33:29,540 --> 00:33:31,380
So among the other
design decisions you're

695
00:33:31,380 --> 00:33:34,005
ultimately going to have is along
the lines of storage engines.

696
00:33:34,005 --> 00:33:37,370
And let me just make mention of this
in anticipation of final projects,

697
00:33:37,370 --> 00:33:42,020
and anticipation of let's
say-- no let's do this.

698
00:33:42,020 --> 00:33:43,820
Let's build this little
application first.

699
00:33:43,820 --> 00:33:48,070
I'm going to go into my terminal
window, and in here is not

700
00:33:48,070 --> 00:33:52,500
only counter.php, which we're now going
to get rid of as no longer germane,

701
00:33:52,500 --> 00:33:54,570
but we have a whole bunch
of directories and this

702
00:33:54,570 --> 00:33:58,080
is going to be very similar in spirit
to what you'll see in problem set seven.

703
00:33:58,080 --> 00:34:00,980
>> So we have three directories
includes public and templates, which

704
00:34:00,980 --> 00:34:05,040
is exactly where we left off on
Monday with our whole MVC paradigm.

705
00:34:05,040 --> 00:34:09,290
And to recap, in public is going to go
any file that I want users to actually

706
00:34:09,290 --> 00:34:12,969
v be able to visit in
their browser via URL.

707
00:34:12,969 --> 00:34:13,502
Template.

708
00:34:13,502 --> 00:34:14,710
What did we put in templates?

709
00:34:14,710 --> 00:34:17,070
What kind of stuff?

710
00:34:17,070 --> 00:34:21,659
There wasn't much but a couple
files at least on Monday.

711
00:34:21,659 --> 00:34:22,619
Yeah.

712
00:34:22,619 --> 00:34:23,100
>> AUDIENCE: Header and Footer?

713
00:34:23,100 --> 00:34:24,516
>> DAVID J. MALAN: Header and Footer.

714
00:34:24,516 --> 00:34:26,679
So we have something similar today, too.

715
00:34:26,679 --> 00:34:30,330
We've got a few more files but
Footer I see, Header I see,

716
00:34:30,330 --> 00:34:31,909
and then a bunch of other files.

717
00:34:31,909 --> 00:34:35,482
So this is the equivalent
of the V MVC view, which,

718
00:34:35,482 --> 00:34:37,690
again, will be a bit more
clear in problem set seven,

719
00:34:37,690 --> 00:34:40,380
but this is just a folder I'm
putting a lot of my aesthetics.

720
00:34:40,380 --> 00:34:42,840
A lot of my HTML, a lot of my forms.

721
00:34:42,840 --> 00:34:46,899
Meanwhile, includes, is another
directory that has these three files

722
00:34:46,899 --> 00:34:48,440
and let's take a quick look at these.

723
00:34:48,440 --> 00:34:51,699
>> I'm going to go ahead
and open up config.php.

724
00:34:51,699 --> 00:34:54,610
As it turns out, much
like earlier in the term,

725
00:34:54,610 --> 00:34:57,850
you sharp included
CS50 dot h with pset7.

726
00:34:57,850 --> 00:35:00,780
In today's example, you're going
to do the equivalent of that

727
00:35:00,780 --> 00:35:03,600
with a require statement
that effectively

728
00:35:03,600 --> 00:35:05,340
includes these several lines.

729
00:35:05,340 --> 00:35:08,225
So to be clear, this is
a file called config.php.

730
00:35:08,225 --> 00:35:09,350
And notice what it's doing.

731
00:35:09,350 --> 00:35:11,970
It's apparently doing something
cryptic, turning on error messages

732
00:35:11,970 --> 00:35:13,680
so that you can see them in the browser.

733
00:35:13,680 --> 00:35:15,860
It's, then, apparently
requiring two other files

734
00:35:15,860 --> 00:35:19,530
so this is like #include in C,
and then this one we did see,

735
00:35:19,530 --> 00:35:22,720
and we've relied on, this turns on
that shopping cart like functionality.

736
00:35:22,720 --> 00:35:25,610
>> This means a cookie will
be sent back and forth.

737
00:35:25,610 --> 00:35:27,290
So why is this interesting?

738
00:35:27,290 --> 00:35:32,460
Well, if we go back to this
directory and open up, for instance,

739
00:35:32,460 --> 00:35:33,741
constance.php.

740
00:35:33,741 --> 00:35:38,840
Notice that PHP does support constants,
it's not quite like #define in C.

741
00:35:38,840 --> 00:35:41,290
Instead, you literally
say defined, and notice

742
00:35:41,290 --> 00:35:44,110
that I've stored in advance
four constants in this file.

743
00:35:44,110 --> 00:35:47,020
One for today's database, for
my password, for my username,

744
00:35:47,020 --> 00:35:48,690
and for the name of the server.

745
00:35:48,690 --> 00:35:51,644
So these are actually going to be
pretty similar in problem set seven.

746
00:35:51,644 --> 00:35:54,560
And lastly, and this is where I'm
going to get some nice functionality

747
00:35:54,560 --> 00:35:59,000
from the staff, in functions.php
is a bunch of code we've written,

748
00:35:59,000 --> 00:36:01,040
and I stole some of this
from problem set seven

749
00:36:01,040 --> 00:36:05,920
for today, that does a bunch of things
and let's just look at one of them

750
00:36:05,920 --> 00:36:07,270
in particular.

751
00:36:07,270 --> 00:36:09,720
This function here,
query, is going to be

752
00:36:09,720 --> 00:36:13,600
the PHP function we call
in order to execute SQL.

753
00:36:13,600 --> 00:36:16,070
A moment ago we were using
phpMyAdmin, but that's just

754
00:36:16,070 --> 00:36:18,720
for sort of learning purposes
and diagnostic purposes

755
00:36:18,720 --> 00:36:20,494
and forgetting your database set.

756
00:36:20,494 --> 00:36:22,660
When you actually use your
database, you, the human,

757
00:36:22,660 --> 00:36:24,100
are obviously not going
to be pulling up a web

758
00:36:24,100 --> 00:36:25,740
page every time someone registers.

759
00:36:25,740 --> 00:36:29,870
You're going to write code that
inserts and deletes users on demand,

760
00:36:29,870 --> 00:36:32,490
and we're going to do this
by way of the query function.

761
00:36:32,490 --> 00:36:35,360
If I now scroll down, there's
going to be a few more features.

762
00:36:35,360 --> 00:36:37,170
Redirect is going to
be a function we wrote

763
00:36:37,170 --> 00:36:40,160
for you that allows you to
send the user to another URL,

764
00:36:40,160 --> 00:36:43,780
and render is a function, quite like
we saw on Monday, that actually renders

765
00:36:43,780 --> 00:36:48,000
a template, but more on these in the
form of pset7's own walk through.

766
00:36:48,000 --> 00:36:50,500
For now, let's go ahead and do this.

767
00:36:50,500 --> 00:36:54,860
>> Let me go into my lectures table and
see that there's currently nothing

768
00:36:54,860 --> 00:36:59,640
here just yet, and let me also go
into my public directory, where

769
00:36:59,640 --> 00:37:02,780
there's just one file, index.php.

770
00:37:02,780 --> 00:37:06,920
This file appears to be super simple
at the moment, it looks just like this.

771
00:37:06,920 --> 00:37:09,110
Very much like how we
left off on Monday.

772
00:37:09,110 --> 00:37:11,945
I'm requiring this file,
config.php, which is in

773
00:37:11,945 --> 00:37:15,160
an includes directory, which
is in dot dot, my parents,

774
00:37:15,160 --> 00:37:17,650
and then it's just rendering this file.

775
00:37:17,650 --> 00:37:18,960
So what is this file?

776
00:37:18,960 --> 00:37:24,700
>> Let's open up in my templates
form.php, and we'll see this.

777
00:37:24,700 --> 00:37:28,500
Super simple, apparently this form is
going to submit by a $_GET or $_POST.

778
00:37:28,500 --> 00:37:29,320
Quick sanity check.

779
00:37:29,320 --> 00:37:33,760

780
00:37:33,760 --> 00:37:35,690
Literally visually search the file.

781
00:37:35,690 --> 00:37:36,610
Method equals post.

782
00:37:36,610 --> 00:37:39,280
So it's not going to use the URL, like
Google does, it's going to sort of hide

783
00:37:39,280 --> 00:37:41,030
the information behind
the scenes and it's

784
00:37:41,030 --> 00:37:43,580
going to submit to a
file called register.php,

785
00:37:43,580 --> 00:37:45,660
and that's the file
we haven't yet written

786
00:37:45,660 --> 00:37:47,610
but what this is going
to look like is this.

787
00:37:47,610 --> 00:37:52,670
>> If I go to a separate page This is
what localhost/index.php looks like.

788
00:37:52,670 --> 00:37:56,930
And again, the server's
just assuming index.php.

789
00:37:56,930 --> 00:37:57,910
Enter.

790
00:37:57,910 --> 00:37:59,870
So that's where we're
at, and what I want to do

791
00:37:59,870 --> 00:38:02,450
is be able to type things
like David, and then

792
00:38:02,450 --> 00:38:08,050
my phone number, which will say
617-555-1212 for now, register

793
00:38:08,050 --> 00:38:09,910
and now register.php was not found.

794
00:38:09,910 --> 00:38:11,440
So I need to implement this.

795
00:38:11,440 --> 00:38:13,320
So let's quickly whip
something like this up.

796
00:38:13,320 --> 00:38:18,640
Let me go into my public directory
and do gedit of register.php,

797
00:38:18,640 --> 00:38:22,300
and now I'm going to go ahead and
start PHP mode, like we did on Monday,

798
00:38:22,300 --> 00:38:25,430
and close PHP's tag, and
let's do a couple of things.

799
00:38:25,430 --> 00:38:28,336
>> So one, I know, from
having written that form,

800
00:38:28,336 --> 00:38:29,960
that I want to check for the following.

801
00:38:29,960 --> 00:38:35,670
If it is empty, whatever the user
typed in to the name field, then

802
00:38:35,670 --> 00:38:39,860
I'm going to say something
like apologize missing name.

803
00:38:39,860 --> 00:38:42,380
Apologize, meanwhile, is
not a built in PHP thing,

804
00:38:42,380 --> 00:38:45,970
it's a function we wrote
in functions.php for pset7

805
00:38:45,970 --> 00:38:47,940
so that you have access to it.

806
00:38:47,940 --> 00:38:53,830
Else if the other field
is empty, number, then I'm

807
00:38:53,830 --> 00:38:58,370
going to apologize to the
user and say missing number.

808
00:38:58,370 --> 00:38:59,320
Save this file.

809
00:38:59,320 --> 00:39:02,640
>> Now let's go back to my browser,
go back to the forum try again.

810
00:39:02,640 --> 00:39:04,070
Register.

811
00:39:04,070 --> 00:39:05,090
OK.

812
00:39:05,090 --> 00:39:06,730
Nothing happened, which is good.

813
00:39:06,730 --> 00:39:08,120
I didn't get an error message.

814
00:39:08,120 --> 00:39:11,651
But if instead, let's reload this
page, and not provide anything.

815
00:39:11,651 --> 00:39:12,150
Damn it.

816
00:39:12,150 --> 00:39:15,350

817
00:39:15,350 --> 00:39:17,140
Do that.

818
00:39:17,140 --> 00:39:18,810
Register.

819
00:39:18,810 --> 00:39:20,350
What did I do wrong?

820
00:39:20,350 --> 00:39:24,860
If empty, $_POST name.

821
00:39:24,860 --> 00:39:26,350
Say again?

822
00:39:26,350 --> 00:39:27,670
>> Oh, of course.

823
00:39:27,670 --> 00:39:30,919
I forgot the most important part, which
is require("../includes/config.php.").

824
00:39:30,919 --> 00:39:34,210

825
00:39:34,210 --> 00:39:36,460
I need to have access to the
apologize function, which

826
00:39:36,460 --> 00:39:37,770
is why nothing was happening.

827
00:39:37,770 --> 00:39:39,460
The function doesn't actually exist.

828
00:39:39,460 --> 00:39:40,640
So let's try this again.

829
00:39:40,640 --> 00:39:42,350
Let's reload the page, click Register.

830
00:39:42,350 --> 00:39:43,060
OK.

831
00:39:43,060 --> 00:39:43,770
There it is.

832
00:39:43,770 --> 00:39:45,700
So, the output we're
seeing here is the result

833
00:39:45,700 --> 00:39:47,685
of calling an apologize
function, super simple,

834
00:39:47,685 --> 00:39:50,060
and it just prints out whatever
I give it as an argument.

835
00:39:50,060 --> 00:39:51,370
>> All right, so let's cooperate.

836
00:39:51,370 --> 00:39:54,240
Let's provide my name
like David, register,

837
00:39:54,240 --> 00:39:56,890
missing number OK let's
provide that, too.

838
00:39:56,890 --> 00:39:58,650
617-555-1212.

839
00:39:58,650 --> 00:39:59,250
Register.

840
00:39:59,250 --> 00:39:59,750
OK.

841
00:39:59,750 --> 00:40:02,760
So all is well now, just nothing
interesting is happening.

842
00:40:02,760 --> 00:40:06,000
So now let's make something more
interesting happen like this.

843
00:40:06,000 --> 00:40:09,980
Let me go into phpMyAdmin, and let's
actually create a table called users,

844
00:40:09,980 --> 00:40:12,330
I'm going to give it three
columns, and I'll quickly

845
00:40:12,330 --> 00:40:16,250
create ID, and then
name, and then number,

846
00:40:16,250 --> 00:40:18,832
and the ID field I'm
going to leave as an int.

847
00:40:18,832 --> 00:40:20,790
The name field I'm going
to leave as a varchar,

848
00:40:20,790 --> 00:40:23,257
and we'll say 64, somewhat arbitrarily.

849
00:40:23,257 --> 00:40:25,090
The number I'm going
to make, you know what?

850
00:40:25,090 --> 00:40:27,350
We're going into
support US numbers here,

851
00:40:27,350 --> 00:40:31,510
so I'm going to do something
like char and then 10 characters

852
00:40:31,510 --> 00:40:34,540
max for an area code
and then seven digits.

853
00:40:34,540 --> 00:40:37,870
And then over here, I'm going to
specify auto increment this field,

854
00:40:37,870 --> 00:40:40,550
make this a primary key, and
I'm going to go ahead and not

855
00:40:40,550 --> 00:40:42,240
check any of these other boxes.

856
00:40:42,240 --> 00:40:48,030
>> So when I now finally click Save,
and I go back to my users table,

857
00:40:48,030 --> 00:40:52,270
this is what it looks like if I
now click a New Tab structure.

858
00:40:52,270 --> 00:40:54,550
So this, to be clear,
is just phpMyAdmin's way

859
00:40:54,550 --> 00:40:58,570
of saying your database table
has an ID, a name, and a number

860
00:40:58,570 --> 00:41:02,040
with those particular configurations
and we'll ignore the rest of the fields

861
00:41:02,040 --> 00:41:03,140
there for now.

862
00:41:03,140 --> 00:41:04,810
>> So now what do I want to do?

863
00:41:04,810 --> 00:41:09,060
So if I go now into my
source code, if all is well

864
00:41:09,060 --> 00:41:11,190
I want to execute the following query.

865
00:41:11,190 --> 00:41:14,970
Insert into, and I can just
say users I don't strictly

866
00:41:14,970 --> 00:41:18,620
need those back ticks if it's
not a dangerous word like users.

867
00:41:18,620 --> 00:41:22,810
I'm going to say name,
number, and then here I'm

868
00:41:22,810 --> 00:41:24,960
not going to hard code the
digit of the values yet.

869
00:41:24,960 --> 00:41:26,760
I'm going to put two question marks.

870
00:41:26,760 --> 00:41:29,320
And this is a convention
in many languages

871
00:41:29,320 --> 00:41:31,730
whereby if you want to have
a placeholder for a string

872
00:41:31,730 --> 00:41:34,105
you're going to use the question
marks, for reasons we'll

873
00:41:34,105 --> 00:41:36,370
come back to chat about
security, and here

874
00:41:36,370 --> 00:41:39,420
I'm going to pass in those
two fields post name,

875
00:41:39,420 --> 00:41:44,850
and then post number,
and now save the file.

876
00:41:44,850 --> 00:41:47,090
>> And now I'm going to
go down here is a super

877
00:41:47,090 --> 00:41:55,690
simply say rendersuccess.php, which
is going to be another template.

878
00:41:55,690 --> 00:41:57,380
I'm going to create really fast.

879
00:41:57,380 --> 00:42:06,270
Geditsuccess.php and I'm just going
to say H1 success in that file.

880
00:42:06,270 --> 00:42:06,990
All right.

881
00:42:06,990 --> 00:42:11,312
So now, let's go back to the
browser, where I visited before.

882
00:42:11,312 --> 00:42:14,270
Let's go ahead and confirm I wrote
in David, I wrote in a phone number,

883
00:42:14,270 --> 00:42:15,390
register.

884
00:42:15,390 --> 00:42:16,100
Damn it.

885
00:42:16,100 --> 00:42:17,420
What did I do wrong?

886
00:42:17,420 --> 00:42:20,850
So I'm seeing an error here, you
have an error in your SQL syntax.

887
00:42:20,850 --> 00:42:24,900
Let me jump back to gedit, let
me go back to register.php,

888
00:42:24,900 --> 00:42:28,830
and what did I omit that
was important last time?

889
00:42:28,830 --> 00:42:29,722
I need this.

890
00:42:29,722 --> 00:42:32,930
You want to know that other than from
having noticed before, but I need this.

891
00:42:32,930 --> 00:42:35,596
>> So now let's go back, and this
was helpful to see in the browser

892
00:42:35,596 --> 00:42:37,680
and that's why in config.php
we spit out errors.

893
00:42:37,680 --> 00:42:41,770
Let's go ahead and reload,
click Continue, success.

894
00:42:41,770 --> 00:42:47,060
So now let me go over to my
database here and click on Users,

895
00:42:47,060 --> 00:42:51,680
and browse, and notice I now
have David in my database here.

896
00:42:51,680 --> 00:42:55,810
Now technically this website is
not yet on the public internet,

897
00:42:55,810 --> 00:42:57,890
so I can't have other
people in putting here,

898
00:42:57,890 --> 00:43:01,120
but if I now wanted to, for
instance, send myself a text message.

899
00:43:01,120 --> 00:43:03,920
Let's go out on a limb here
and see if this actually works.

900
00:43:03,920 --> 00:43:07,331
I'm going to go ahead
and delete this row

901
00:43:07,331 --> 00:43:09,080
and we'll blur this
out in the video later

902
00:43:09,080 --> 00:43:11,900
so we don't have the
entire internet texting me,

903
00:43:11,900 --> 00:43:17,270
and we will now go up to the
browser and we'll go over to lecture

904
00:43:17,270 --> 00:43:22,040
and we'll type in different
number here, register, success.

905
00:43:22,040 --> 00:43:25,550
>> So now, my own number's presumably in
the database, and now the fun part.

906
00:43:25,550 --> 00:43:28,774
Let's actually use PHP to do
something programmatically,

907
00:43:28,774 --> 00:43:30,940
either from the command
line or from somewhere else,

908
00:43:30,940 --> 00:43:32,773
and for now I'm just
going to keep it simple

909
00:43:32,773 --> 00:43:36,230
and I'm going to go into my
directory here and do the following.

910
00:43:36,230 --> 00:43:44,920
Gedit script let's say, we'll
call it text, #!/user/bin/env PHP,

911
00:43:44,920 --> 00:43:46,700
like we saw last time.

912
00:43:46,700 --> 00:43:47,950
PHP.

913
00:43:47,950 --> 00:43:55,055
>> Now I'm going to do require
includes config.php,

914
00:43:55,055 --> 00:43:57,360
even though this might
induce a slight error.

915
00:43:57,360 --> 00:44:03,960
And now I'm going to go ahead and say
rows, query, select star from users,

916
00:44:03,960 --> 00:44:08,149
and now here I'm going to do a technique
from last time for each rows as row.

917
00:44:08,149 --> 00:44:09,690
And I'm going to do something simple.

918
00:44:09,690 --> 00:44:19,090
Printf let's say name is this,
and number is this, backslash n.

919
00:44:19,090 --> 00:44:23,320
And now I'm going to pass
in row quote unquote name,

920
00:44:23,320 --> 00:44:28,140
and row quote unquote number,
and now let's go ahead

921
00:44:28,140 --> 00:44:31,430
and my terminal window
chmod this a + x to make

922
00:44:31,430 --> 00:44:33,970
this script called text executable.

923
00:44:33,970 --> 00:44:36,080
And now let's run text.

924
00:44:36,080 --> 00:44:37,590
>> OK, so progress.

925
00:44:37,590 --> 00:44:39,960
So I've now written a
command line script,

926
00:44:39,960 --> 00:44:43,300
in a language called PHP, that,
because of that require line,

927
00:44:43,300 --> 00:44:46,380
has access to all those configuration
constants that I specified.

928
00:44:46,380 --> 00:44:48,177
The name of the database and so forth.

929
00:44:48,177 --> 00:44:50,260
In fact, just to be clear
that this isn't a fluke,

930
00:44:50,260 --> 00:44:54,730
let me go ahead and register,
really fast, someone else like Rob

931
00:44:54,730 --> 00:44:58,890
and will give him the 555-1212 number.

932
00:44:58,890 --> 00:45:01,557
>> And now, if I run the script
again, notice the power

933
00:45:01,557 --> 00:45:03,140
of what we're doing with the database.

934
00:45:03,140 --> 00:45:07,680
Now I've immediately seen what the
other two rows are in my database.

935
00:45:07,680 --> 00:45:10,699
So now let's try to do something
even fancier inside of,

936
00:45:10,699 --> 00:45:12,740
and this is the part we've
not tested in advance,

937
00:45:12,740 --> 00:45:15,910
so the last time I did this
things went horribly awry,

938
00:45:15,910 --> 00:45:17,120
we have video to that effect.

939
00:45:17,120 --> 00:45:18,286
>> Actually, yeah, funny aside.

940
00:45:18,286 --> 00:45:20,480
So the last time, in a
lecture like two years ago,

941
00:45:20,480 --> 00:45:23,230
we decided, I decided, to be
all this would be a great idea

942
00:45:23,230 --> 00:45:28,150
to dynamically generate emails in
class, using the entire database CS50

943
00:45:28,150 --> 00:45:33,390
students, who had given us their numbers
and their cellphone carriers which you

944
00:45:33,390 --> 00:45:36,290
might recall from pset0,
how to reason, it turns out

945
00:45:36,290 --> 00:45:40,650
I had a minor bug in my program and
did a couple mistakes in 2012, I think.

946
00:45:40,650 --> 00:45:43,997
>> Whereby, one I had for loop that
did exactly this kind of thing,

947
00:45:43,997 --> 00:45:46,580
iterating over the database,
getting a name from the database,

948
00:45:46,580 --> 00:45:49,940
name from the database, and then on each
iteration of that loop I sent an email.

949
00:45:49,940 --> 00:45:54,130
But instead of sending one email, I
sent one email the first iteration,

950
00:45:54,130 --> 00:45:58,200
and two emails a second iteration, sent
three emails a second iteration, which

951
00:45:58,200 --> 00:46:01,230
as you might recall from our
discussion of asymptotic notation

952
00:46:01,230 --> 00:46:06,400
this big O of bad, like n squared
is how many messages I sent,

953
00:46:06,400 --> 00:46:08,560
but it wasn't even emails
it was text messages.

954
00:46:08,560 --> 00:46:12,070
>> And as you know, attendance isn't
super high toward the end the semester

955
00:46:12,070 --> 00:46:15,360
and so I thought it would be cute at
the time to say, "Why aren't you class?"

956
00:46:15,360 --> 00:46:17,880
In the text message I
sent to the whole class,

957
00:46:17,880 --> 00:46:22,140
and it was funny to like 50% of the
class, but the other 50%, some of whom

958
00:46:22,140 --> 00:46:26,102
freaked out, i sent incredibly
apologetic sweet notes

959
00:46:26,102 --> 00:46:28,560
to the staff apologizing for
having missed the lecture just

960
00:46:28,560 --> 00:46:29,530
this once, right?

961
00:46:29,530 --> 00:46:32,790

962
00:46:32,790 --> 00:46:34,030
>> So that would horribly awry.

963
00:46:34,030 --> 00:46:37,030
So in that spirit, let's try this
again but just with my number.

964
00:46:37,030 --> 00:46:41,940
In advance, in functions.php,
I've written this function here.

965
00:46:41,940 --> 00:46:44,250
It's called text, and it
takes in three arguments.

966
00:46:44,250 --> 00:46:46,360
A number, a carrier, and a message.

967
00:46:46,360 --> 00:46:50,390
>> I'm using a switch statement, which
wonderfully PHP take strings, not just

968
00:46:50,390 --> 00:46:53,350
integers, and I didn't implement
all the support for this yet,

969
00:46:53,350 --> 00:46:55,370
I've just done AT&T and Verizon.

970
00:46:55,370 --> 00:46:57,610
Because it turns out
that with these carriers

971
00:46:57,610 --> 00:47:00,570
they have email to SMS gateways,
whereby you can actually

972
00:47:00,570 --> 00:47:05,529
send an email to an address
like phone number at vtext.com

973
00:47:05,529 --> 00:47:08,070
and if the user hasn't blocked
the messages, it'll go through

974
00:47:08,070 --> 00:47:09,340
is a text message.

975
00:47:09,340 --> 00:47:13,270
>> Now to do this, I'm going to have to add
one field really fast to my database.

976
00:47:13,270 --> 00:47:15,470
I'm going to go into
my structure, and I'm

977
00:47:15,470 --> 00:47:21,880
going to go ahead and add a
field at the end of the table.

978
00:47:21,880 --> 00:47:25,227
Let's click Go, and I'm
going to call this carrier

979
00:47:25,227 --> 00:47:27,310
and for now I'm going to
leave this as a bar text,

980
00:47:27,310 --> 00:47:29,320
but we can be fancier in the future.

981
00:47:29,320 --> 00:47:31,961
I'm going to quickly go
into my table, and I'm

982
00:47:31,961 --> 00:47:34,210
going to get rid of Rob,
because that's a fake number,

983
00:47:34,210 --> 00:47:38,540
I'm going to go into edit here and I'm
going to change my carrier manually

984
00:47:38,540 --> 00:47:43,410
to be Verizon, which it
is, and now over here.

985
00:47:43,410 --> 00:47:44,980
>> Let's do a quick sanity check.

986
00:47:44,980 --> 00:47:52,730
Let's open up our text script, which
looks like this, carrier is %s.

987
00:47:52,730 --> 00:47:58,230
We're doing a lot more error
checking than I did in 2012, carrier.

988
00:47:58,230 --> 00:48:01,160
And now, I'm going to go
ahead and re-run the script.

989
00:48:01,160 --> 00:48:01,660
OK.

990
00:48:01,660 --> 00:48:06,100
Carrier is Verizon, which means
now hopefully I can do just this.

991
00:48:06,100 --> 00:48:08,360
Correctly this year,
hopefully, here we go.

992
00:48:08,360 --> 00:48:12,200
>> So inside of this for loop, I'm
going to not only have this printf,

993
00:48:12,200 --> 00:48:15,990
I'm also going to call text and
the usage of this function recall

994
00:48:15,990 --> 00:48:19,670
was it takes a number, a
carrier, and a message.

995
00:48:19,670 --> 00:48:23,310
So let's see, number is going to
be row quote unquote "number,"

996
00:48:23,310 --> 00:48:31,660
row quote unquote "carrier,"
and the last one was message.

997
00:48:31,660 --> 00:48:36,250
Don't screw up this year, semicolon.

998
00:48:36,250 --> 00:48:36,780
>> OK.

999
00:48:36,780 --> 00:48:38,280
Fingers crossed.

1000
00:48:38,280 --> 00:48:39,970
Let's see if this works.

1001
00:48:39,970 --> 00:48:41,720
All right, so.

1002
00:48:41,720 --> 00:48:43,000
Here we go.

1003
00:48:43,000 --> 00:48:47,380
Let's unlock the phone,
cross your fingers, damn it.

1004
00:48:47,380 --> 00:48:50,300
Undefined variable may-- oh
wait, wait, wait, real fast.

1005
00:48:50,300 --> 00:48:51,340
Real fast, real fast.

1006
00:48:51,340 --> 00:48:53,380
This is totally worth it.

1007
00:48:53,380 --> 00:48:57,710
Let me grab, let me grab, uh-oh.

1008
00:48:57,710 --> 00:48:59,965
Thank you, the texts have
started from someone else.

1009
00:48:59,965 --> 00:49:04,770

1010
00:49:04,770 --> 00:49:11,650
Let me go ahead and open up real
fast, dropbox.php/mail in here.

1011
00:49:11,650 --> 00:49:12,660
Standby.

1012
00:49:12,660 --> 00:49:14,455
Totally worth it.

1013
00:49:14,455 --> 00:49:17,430
Downloads.

1014
00:49:17,430 --> 00:49:18,560
OK, source src8m.

1015
00:49:18,560 --> 00:49:19,700
OK.

1016
00:49:19,700 --> 00:49:21,380
>> Need one more line here.

1017
00:49:21,380 --> 00:49:24,530
Oh there it is, it's in Frosh
IMs, it's in register at three.

1018
00:49:24,530 --> 00:49:28,820
Oh hello, Margo, thank you very much.

1019
00:49:28,820 --> 00:49:31,130
OK, and I was missing this line here.

1020
00:49:31,130 --> 00:49:33,010
So let me quickly grab
this line of code,

1021
00:49:33,010 --> 00:49:36,200
which includes the mail or library
that I actually want to use,

1022
00:49:36,200 --> 00:49:38,300
I'm going to quickly
go back into functions,

1023
00:49:38,300 --> 00:49:42,337
I'm going to go to the top of this
file and require this file as well,

1024
00:49:42,337 --> 00:49:45,420
and now I'm going to really cross my
fingers when I go back to the command

1025
00:49:45,420 --> 00:49:49,530
line script, which is inside of
today's local host directory.

1026
00:49:49,530 --> 00:49:50,610
Run text.

1027
00:49:50,610 --> 00:49:52,720
Enter.

1028
00:49:52,720 --> 00:49:53,220
Mail.

1029
00:49:53,220 --> 00:49:53,719
Standby.

1030
00:49:53,719 --> 00:49:58,850

1031
00:49:58,850 --> 00:49:59,600
Standby.

1032
00:49:59,600 --> 00:50:01,680
Mail.

1033
00:50:01,680 --> 00:50:02,290
Oh, OK.

1034
00:50:02,290 --> 00:50:03,870
Here we go.

1035
00:50:03,870 --> 00:50:06,880
>> Mail gets new PHP mailer.

1036
00:50:06,880 --> 00:50:09,970
Did I do this right?

1037
00:50:09,970 --> 00:50:11,067
Damn it.

1038
00:50:11,067 --> 00:50:12,150
To-- oh, wait, wait, wait.

1039
00:50:12,150 --> 00:50:12,649
Stand by.

1040
00:50:12,649 --> 00:50:15,820

1041
00:50:15,820 --> 00:50:18,630
I promise, this is
going to be so worth it.

1042
00:50:18,630 --> 00:50:20,340
Address.

1043
00:50:20,340 --> 00:50:24,390
This is why I don't make the
examples right before class.

1044
00:50:24,390 --> 00:50:26,350
Ugh.

1045
00:50:26,350 --> 00:50:27,910
The following recipients failed.

1046
00:50:27,910 --> 00:50:31,500

1047
00:50:31,500 --> 00:50:33,040
Let's try one last thing.

1048
00:50:33,040 --> 00:50:40,660
SMTP set from, add address,
the address is indeed that.

1049
00:50:40,660 --> 00:50:43,980
Let's try this last part in address.

1050
00:50:43,980 --> 00:50:47,210
Aw, I'm really sad right now.

1051
00:50:47,210 --> 00:50:47,854
Thank you.

1052
00:50:47,854 --> 00:50:50,270
But I really appreciate all
the texts you've been sending.

1053
00:50:50,270 --> 00:50:53,130

1054
00:50:53,130 --> 00:50:56,320
You've got this David.

1055
00:50:56,320 --> 00:50:59,310
You're blowing it.

1056
00:50:59,310 --> 00:51:01,720
Let's leave it there and
we will fix on Monday.

1057
00:51:01,720 --> 00:51:04,290
See you then.

1058
00:51:04,290 --> 00:51:08,090
>> DAVEN FARNHAM: And now Deep
Thoughts by Daven Farnham.

1059
00:51:08,090 --> 00:51:11,340

1060
00:51:11,340 --> 00:51:17,590
If a binary tree falls in a forest and
no one is around to C it-- [CHUCKLING].

1061
00:51:17,590 --> 00:51:18,998