[ Silence ]
>> Alright, this is CS50, the start of week 9.
So we've got a great week of stuff in store for you more
on web, more on database,
more on actually looking toward the final project
but first, the blink tag.
So this is a tag in HTML that has been largely deprecated,
which means the world hated it and ripped it
out of more recent versions of HTML.
But this week, well, we teach you by way
of a language called JavaScript
to re-implement this incredibly annoying feature
of websites 1990 style.
But our other teaser for today is this.
So, thanks to our friends at Microsoft,
they have a brilliantly named research and development center
in Kendall Square called NERD
or New England Research and Development.
It's a beautiful space, overlooks the Charles River.
They've got a huge digital screen
and they're gonna have a lot of pizza
and candy waiting for us this Friday.
So the first ever CS50 movie night will be this Friday.
If you would like to RSVP for this and join us, friends
and family and dates and such are welcome.
Just go to cs50.net/movie,
it's where everyone will be this Friday night.
And what we will do is CS50 now operates its own shuttle
services, so thanks
to Shuttleboy shuttle services will be shuttling us
over from Harvard Square to Kendall and back.
Event itself is at 7:30 and the movie that will be
on the screen is this year's course favorite.
So, for those unfamiliar, a little teaser trailer.
[ Pause ]
[ Noise ]
[ Background Noise ]
>> From Mike Judge creator of Beavis and Butt-head
and co-creator of King of the Hill comes a movie
about people who go to work.
[ Noise ]
>> Who are part of a team.
>> And remember next Friday is Hawaiian shirt day.
>> Okay, if it-- I could take the building on fire.
>> Who respect their boss.
>> We need to talk about your flare.
>> Well, I have 15-- 15 pieces on.
>> 15 is the minimum.
Brian for example has 37 pieces of flare out there.
[ Laughter ]
>> And a terrific smile.
>> And need to escape.
>> I don't like my job
and I don't think I'm gonna go any more.
>> One of these days I-- I just kicked this piece of-
>> I'm thinking now it might be more fun to just get fired.
>> And I've always wondered what that would take.
>> Oh here, listen.
>> It looks like you've been missing quite a bit
of work lately.
>> Well, I wouldn't say I've been missing it, Bob.
[ Laughter ]
>> That's just a straight shooter
with upper management written all over him.
>> We're gonna be getting rid of these people here,
Mr. Samir [inaudible].
[ Inaudible Remark ]
>> Not gonna work here anymore anyway.
>> You haven't even shown up and you get to keep your job.
>> Actually I'm being promoted.
>> Thank you, Bob.
>> This is a-- a suck.
>> They're gonna throw you out on the street
so that Bill Lumbergh stock will go up.
>> Oh, it's completely unfair.
Initech deserves to go down.
We're just the guys to do it.
Tell me about that virus you're always talking about,
the one that could rip off the company for a bunch of money.
>> I'm not going to do anything illegal.
>> Illegal?
Samir, this is America.
[ Music ]
>> The worst they're gonna do is they put you
in a white collar minimum security resort
for a couple of months.
Do you know they have conjugal visits there?
>> I might be showing her my oh face.
Oh, oh--
>> They let you have sex with women?
>> They sure do.
>> Okay, I'll do it.
[ Background Music ]
>> Office Space.
>> I know how you're getting pretty depressed about your job
and everything and so I just want to tell you,
good things can happen in this world.
I mean look at me.
[ Laughter ]
[ Music ]
>> So coming this Friday to CS50.
So if you think you can go, do RSVP by the website.
Space will be limited but we will follow
up via email once we know the logistics.
So also coming up, our CS50 seminars,
this is a tradition we've had for several years
where the teaching fellows and course assistants
and Harvard affiliates offer some hands on
or conceptual seminars on material bits.
Either just of technical interest to folks
on campus specifically CS50 students or also on topics
that might be of interest to you for final projects
so that you can pick up some new skill
or leverage some new understanding.
This year's line up includes such titles as these,
Blackberry application development or crash course
in Java, creating awesome websites with Ruby on Rails,
which is another programming language and framework .
Developing apps for iPhone, iPod Touch and iPad,
educational software developments
and Linux demystified, if you're still a little mystified.
Making SMS mobile applications,
modern client side web programming,
socializing your apps with the Facebook platform VIM,
and then we have dozens of past seminars
and videos thereof on this website here.
So if you go to cs50.net/seminars, you can one,
see those videos and two, RSVP for this year's lineup.
Essentially all of these seminars are in the process
of being scheduled now.
They'll be like an hour or two some time, ideally between now
and Thanksgiving, and the seminar leader will follow
up with you once you've RSVP'd for it to figure
out what might be an optimal day and time.
And rest assured if you can't make this seminar's day time
this year, it too will be videotaped and put online
within a couple of days thereafter, so plenty of time
to pick up some new skills for the final project.
And just to plant this reminder,
the final project's pre-proposal is due this coming Monday.
This is a very casual but very much expected email
to your teaching fellow just to let him
or her know what you're thinking, to bounce ideas off
of them and to perhaps have a little sanity check
as to whether what you're thinking is too easy, too hard,
too much time, too little time, it's really just an opportunity
to get the juices flowing.
And then in a couple of weeks will the actual proposal be due.
Also coming up from the IOP, if you would like today
in Mather House at 5 p.m., you can met Alec Ross
who is the current senior innovation adviser
to Hillary Clinton where he leads the state department's
efforts to find practical technology solutions for some
of the globe's most vexing problems on healthcare, poverty,
human rights and ethnic conflicts.
So if you're interested in how technology
and social networking tools can be used in diplomacy
and social development, do check out this event
in Mather House this afternoon.
So, my teaching fellows and CA's and some
of your classmates had a little Halloween party yesterday
and I couldn't help but delight in the geekery of the jokes
that get tossed around at such events like this.
So the risk of embarrassing one of them anonymously,
we heard this line here during the preparation of the event
where some balloons were being hung
and some Halloween decorations were being hung.
This volunteer simply asked this here.
And his friend's answer, his or her friend's answer was--
[laughter] That's sad, we all laugh now 'cause we all get it
for better or for worse.
So, that was a cute thing to see certainly for a teacher.
So, I got this spam actually this morning.
Some of you probably did too.
Hopefully none of you actually clicked the link
that you were given.
This is from Harvard University, it was sent to me,
a dear web mail user and they explained that due to congestion
in all Harvard University exclamation point,
web mail users' accounts, Harvard University,
would be shutting down some unused web mail account.
In order to avoid the deactivation
of your web mail account, you will have to confirm in a--
confirm that it is a present used account
by clicking the secure link, all caps below.
The personal information requested is
for the safety of your accounts.
Please leave all information requested,
then you can click here and go
to what's clearly harvard.edu/secure/login,
which makes it secure, and this is
of course copyright the president
and the fellows of Harvard College.
So the thing is if you actually clicked through that link,
you actually end up on this to your website.
Too bad I clicked too late because whoever had--
you signed up for this account has been deactivated.
But we probably would have seen a little web based form
with text fields and a submit button.
One of which probably would have said my user name.
One of which would have said my password and, you know,
we kind of maybe smile now or maybe you're a little worried
that you did fill out a form at 123contactform.com.
But it really is easy to do this.
I mean this is what's generally known as a fishing attack
where the adversary is essentially trying to bait you
into doing something that sounds legitimate technologically
or socially, and it looks like this is a harvard.edu link.
So surely it must actually lead there.
But of course as we've already realized a week ago in looking
at HTML, all it takes to make a link in a web page is
to say A HREF equals quote unquote HTTP slash slash.
And then I could go to 123contacform.com
and then some other stuff.
But then I can close this tag,
but recall that what goes inside this tag is the actual text
that the user sees.
So some websites would just say something like click here,
some websites would say something like form,
but some websites could very well just say
http://harvard.edu/secure/login and voila,
you have this duality.
The link says one thing but actually goes somewhere else.
So if you're not sort of savvy when it comes
to detecting these things now, you know,
most any email clients, you can hover your mouse over a link
like that and actually see where it actually leads.
You can click it but at least after you graduate
from this course, stop short of actually filling
out the form and clicking submit.
At that point, you've indeed gone too far.
But it all boils down to the basics
that we've started looking at today.
So, where are we going with this?
Well, problem set 7, which some of you have dived
into is on CS50 finance.
This is your first of two web based programming challenges
whereby you'll implement an e-trade like website,
whereby thanks to Yahoo Finance, you'll be able
to have users login or register for accounts.
>> They'll be handed some 10,000 virtual dollars
and then you can actually get nearly realtime stock quotes
by typing in the stock symbol for a company.
You can "buy" the stock from your 10,000 dollars in cash
by checking with the current prices
and then buying those numbers of shares.
And then the code you will write
for this problem set will actually keep track of all
of these transactions, all of the buys, all of the sales,
the sell point, the buy point,
the prices at which you actually transacted these changes.
And all of this will be logged in what's called a SQL database
which is what we'll now introduce as a mechanism
for actually storing data around on disk or in memory
so that the next time the user visits this page,
they can actually interact with it seeing their own profile.
So if I go actually to cs50.net/finance,
you'll see the stacks implementation of this.
And if you actually follow the link here
that says play the big board, you'll actually log
in as your self, not as some fake testing account.
And if you do that and then follow up through
to the big board for problem set 7, you'll see that all
of us have lost at least 1 percent of our 10,000 dollars,
those of us who opted in to this here.
So much like P set 6 had its own big board similarly will this
one just for fun have its own big board.
And now that I look at this,
the mathematics are clearly off 'cause Michael Shan [phonetic]
is-- has up to 32,000 dollars but he's only up 2 percent
and he's ranked the last.
So this is an example of a bug at the moment.
In my defense, the markets were closed this weekend
when I was testing.
So I couldn't really get real data
but this is clearly my fault.
So I will fix.
But as it has it, David J. Malan is actually number 1
at the moment, so--
[ Laughter ]
>> -- maybe I should leave it alone.
So, what's really cool about this is
that this project originated really just as kind
of a random idea where we wanted to do something related
to finance but to do this well,
you really wanna get some kind of real data ideally.
'Cause if we just handed you a big Excel file
and said here are the prices of all these company's stocks
on October 1st, 2010, I mean who cares, right?
You could use that data, much more interesting
to interact with it in realtime.
So if I do a look up for a company
like Google whose symbol is GOOG, what you'll get
on Yahoo Finance really, any of these websites
that give you free stock information are details
like the last trade price,
Google currently goes for 614 dollars.
When the last trade was transacted, what the bid
and asking prices are.
If you're not familiar with some of this jargon,
it's not really a big deal for the P set but we link
to a nice tutorial and the PDF this week
that will bring you up to speed on this.
But what caught our eye is
that there's all these interesting data on the page
and we could resort
to a technique called screen scraping.
Screen scraping or web scrapping is just the buzz word
that describes writing a program usually in a language like PHP
or in Ruby or in Python,
something called a scripting language
which we'll revisit today and onward.
That is whose purpose in life is to pretend to be a browser,
make an HTTP request, and this is just a program you could run
at the command line, on the cloud,
on your own computer, wherever.
It can be written in any
of these new languages we're starting to play with.
It makes HTTP request to yahoo.com
as though it's a browser, and that's not all
that hard 'cause we sniffed my own traffic last week and saw
that when you make an HTTP request, this just sends some
of these HTTP headers, host colon and GET and then dot, dot,
dot and so you can mimic that stuff very easily
with a few print calls in a program.
So we could pretend to be a browser, get this page,
specifically this URL, because notice what's nice here is Yahoo
apparently is using the GET mechanism
which means you can parameterize requests of a web page
by using a HTTP parameter.
In this case, notice that the URL starts
with finance.yahoo.com/q, probably for query,
question mark, and the question mark means here comes some
parameters, here comes some inputs to this server
and the first input is called S, that's a parameter
and its value is apparently equal to GOOG.
So what this means is I could literally copy this,
paste this URL into my own PHP program and write code
that says go get that URL.
And I can say go get that URL every minute or every hour.
I can use something as an aside called the cron job
which actually automates the process
of running your code on a schedule.
So with all these little building blocks,
something like PHP, something like a so called cron job,
I can actually grab this data again and again.
But unfortunately, even though it looks obvious to me,
the human, that the number I probably want the most is this,
the last trade time.
Or you know what, maybe it'd be interesting
to get the current change.
Maybe it'd be interesting
to get the current opening price or the bid part.
In other words, there might be a lot of pieces of data
on the screen and so those of--
those people in this world
without a computer science background
or any programming jobs probably would open up Excel
or Google spreadsheet and then just start transcribing these
numbers manually, right, copy and paste.
And that's about as efficient as you could get.
But remember that underneath the hood here is some HTML.
So if I actually go and view the source of this page,
let's do it this way, view source,
I'll see all of these HTML.
But my God, now finding those little needles in this mess
of a haystack is really quite challenging.
Even though the 600 dollars is in there, this just looks
like a mess, and this is what would come back
if I wrote a program
that requested a URL pretending it's a web page.
Browsers of course know how to render this nice and prettily
like this in a web page.
But if I'm writing a command line program with PHP,
now I have to pars or analyze all of this code,
and this is where screen scraping,
the process of writing a program that just crosses its fingers
that that web page structurally is gonna stay the same day
after day so you can just grab data
from it is actually nontrivial.
We as a course actually do this every night
with Harbor Dining Services website to grab all
of the meal data everyday so that you guys
for your final projects can use that kind of information.
But long story short, it's not fun, generally,
though sometimes you have to resort to it,
but what caught our eye is at the bottom of this page
under this thing they called toolbox is download data.
And in fact when I hover over this,
I see that this is just a URL.
It's gonna look a little small here but I can see
that this download data link is just another URL, a get request
where S equals GOOG and then F equals a long string
and then CSV.
CSV is comma-separated values and it's sort
of a poor man's Excel file.
It's a spreadsheet but it just uses commas to separate columns.
And so if you have a program like Microsoft Excel that knows
to look for commas as delimiters
between what conceptually should be commas,
you can then open this file in something like Excel.
So I'm gonna go ahead and click download data.
It indeed says I'm downloading a file called quotes.csv,
so let me go ahead and save that.
And then if I double click this file, because I have Excel
on this particular computer, it's Excel that opens.
And what's really neat is that I have the illusion
of some columns here, GOOG in the left column.
Then what appears to be its last trade price, what appears
to be the date at which it was last traded today,
the time at which it was last traded today.
There are of course slight delay is in a lot of these websites
which is why it's trailing by a few minutes,
and then some other data that if I compare back and forth
with the website, I could figure out which
of these fields means what.
But if I actually look at the text file
that I just downloaded, this is just a file called quotes.csv.
If I actually open this with something like text edit,
this simple little text program, notice that's all in this--
all that's in this file is that data there separated by commas.
And now, as a programmer, this is really interesting to me
because now I don't have all this [inaudible] of HTML
and hyperlinks and colors and fonts,
all these useless information.
All I have now is the raw data.
So this caught our eye, and so what you'll do in problem,
set 7 for instance, is write code that calls a function
that we wrote that grabs this URL
that has a stock symbol in it.
Whatever stock symbol you wanna pass in as an argument
to this function, we make the request to the web page,
get back what essentially is an Excel file.
A CSV file, we then split it on all these commas
and then hand you back a struct essentially called a class
in PHP that contains all of these interesting information.
Now, not to dwell too much on Yahoo Finance, but moving ahead,
whether it's for your research in the physical sciences
or biological science or economics or really any field
where you might actually wanna analyze data
or manipulate something or do something more efficiently,
then you, the human could do by a copy and paste,
looking on the web or looking on computers
for actually machine readable data like this is
where things get really interesting
because it means you can automate so much.
And the simplest thing we automated last week, recall,
was simply automating the idea of freshmen intramurals.
And what we were able to do last week is
when the user submits a form saying this is my name,
this is my dorm, my gender, I'm gonna be a captain or not,
we were able to first completely throw that information away,
which clearly was not all that useful.
We just said, you're registered but not really.
But in the third version of last week's Frosh IMs example,
what did I finally do with that data.
Yeah, so I sent myself an email, and back in the day,
this would have been an email not to me but to the proctor
who was actually running the freshmen intramurals
sports program.
And if I take a look at that code, what you'll see here is
that this was register3.php.
Recall that at the very tail end of that last lecture,
sending an email on a computer program reduced
to just a few lines of code.
I had a few variables and recall that in PHP, a variable starts
with a dollar sign just by convention,
so I declared a variable called two and just put
in my own email address.
And notice the similarities with C, double quotes around strings,
although as an aside in PHP, you can often use single quotes
as well, semicolon at the end, so that's the same.
Subject equals registration, body equals--
and this one got a little interesting 'cause I didn't
wanna send like a short phrase.
>> I want kind of a longer sentence and so I wrote
out body equals this person just registered colon backslash n
backslash n 'cause I wanna move the cursor down a couple
of lines in the email.
And then notice this, the period.
So the period or the dot is the concatenation operator in PHP.
So whereas in C, it's really actually a pain
to take one string and add more string to it
and more string and more string.
Because remember that a string in C is an array.
An array usually has a fixed amount of memory
so you can't just blindly add more and more characters
to the end of it, but in PHP, you can.
Underneath the hood, the same kind
of magic is still happening.
The same kind of memory is getting allocated
and deallocated as needed but all
of that is taken care of for you.
And so notice what I concatenate on to that phrase is this.
Dollar sign underscore post bracket quote unquote name.
So what was this thing called dollar sign underscore post,
generally speaking?
What was the point of this thing?
Awkward silence.
Someone must know.
Just conceptually like what's-- what is this thing?
>> It's an array.
>> So it's an array.
So it's an array and you can infer that frankly
from the square brackets.
The notation is just like C. We don't have a number inside
of those square brackets but this is because we get a new
and improved version in array called an associate of array
in PHP, whereby the indices, the things that you use to index
into the array no longer have to be numbers.
They can actually be words or phrases, sentences
or more generally, things called keys.
And so what I can now do is grab the value inside of this array
at the location called name.
Yeah.
[ Inaudible Remark ]
>> Good question.
What if you use the same syntax and I say,
you know what I actually want to include
in this email even though I'm completely making this up,
I wanna say the value of the foo key, F-O-O.
Well, what would come back is essentially the "empty string",
nothing would actually happen.
Alright, so maybe that's okay
if it wasn't actually a required field
but in this case I know what these fields were called.
Now what's special about dollar sign underscore post is that,
yes, it's an array.
Yes, it's an associate of array
but it's generally what PHP calls a super global.
It's a special global variable that you just get handed
for free anytime you write a PHP based website
that is automatically filled with any parameters
that were submitted by some user via form submissions.
So remember we had the whole premise here was
that we made a form, open bracket form in HTML.
Recall that we specified method equals something rather,
so let me actually pull that up.
Froshims3.php, recall that we introduced this notion
of a form last week, and this is fairly self explanatory once you
start-- once you get the hang of the tags,
up at the top here we have form action equals register3.php.
So action just says, what is the action of this form?
Where does it send its data to?
Where does the data get submitted?
And I decided that in addition
to this file called froshims3.php,
I'm gonna write a file called register3.php.
The purpose in life is to receive the form submission
from this particular page.
Alright, the method I'm gonna use is POST
and we quickly mentioned this last week,
there's two main methods.
One is POST and one is GET.
And what's perhaps the most obvious difference
between the two?
[ Inaudible Remark ]
>> Yeah, so using method equals "GET" means
that the parameter show up in the URL.
So in the case of Yahoo Finance, when I filled out that form
in the top left corner and type GOOG and hit enter,
notice that-- recall that the URL changed
and you could actually see what I typed in the URL,
and so that's because underneath the hood,
Yahoo must be using the GET method.
Now why is that a good thing perhaps?
Why use GET?
Yeah.
>> Bookmark it.
>> Yeah, so you can bookmark it, right?
If you wanna add a bookmark to your browser, if you wanna copy
and paste it into an email,
what's nice about the GET method is that state,
the state of the world
as you currently see it is embedded inside of the URL
so the state is retained.
Someone else can follow that link
and see exactly the same thing as you.
Now this is probably not the best method if you're trying
to log into some account.
You fill out a form with your user name and password
or maybe your credit card number and your 3
or 4 digit secret code on the back of it and then hit submit.
Well, it's probably not a huge deal if you're sitting at home
or in your dorm and the form is getting submitted
from your own laptop to some server.
But if you're on a public machine
or if other roommates are in the habit of using your computer,
anytime the URL changes that's in your browser,
what's a side effect of using GET?
What's that?
>> Password shows up as a plain text.
>> Yeah, so the password is gonna show up as plain text
in the URL, or more specifically,
your history is going to remember
that you visited that URL.
So if you've ever gotten a little nosy and crossed that,
let's call it unethical line,
and poked around at what URL is your friends
or roommates have actually been visiting,
it's got to be a few of you here, right?
So that's the so called history of a browser,
and that pretty much records pretty much every GET request
that was made with the browser,
it's just logged in that drop-down.
And of course you can go to like tools and options and clear
that kind of cache, but GET is not very good for privacy sake.
POST is generally better for that and POST is actually called
for anytime you wanna have the user upload something
like a JPEG to Facebook,
you can't really fit an image conceptually or even technically
in a URL, you can change it to zeros and ones or some kind
of encoding but it doesn't really fit very well in the URL.
So, POST is also used when you wanna submit large amounts
of information.
But the relevance to the Frosh IMs example is
that inside this form, there's an input called name.
There's an input called captain.
There's an input called gender and each of this happens to be
of slightly different types.
I know this by just having read the manual
or having learned how HTML works and so I know that in input,
its type can be text if you want a text field.
It can be check box if you want a check box.
Radio, if you want a radio button,
and there're a few others.
But pretty much it's straightforward
as just saying what piece of-- or what type of data you want.
But because the action of this form was register3.php,
what happens when I actually click submit is that this page,
register3.php, which lives at a URL.
If I go to this in a browser, I can absolutely visit this.
I can go to froshim3.php which, recall, looked like this.
I can also go directly to register3.php but notice that--
let me disable this for a moment.
I'm gonna do one quick thing here.
Print, yes, here exit.
Just to prove that this file called register3.php can
in fact be visited directly, I click here,
and indeed this is now just a web page on the internet.
It's not actually returning legit HTML yet, but the URL is
in fact web accessible.
So, I'm not actually submitting any parameters
to this form right now but if I get rid of this,
and actually access register 3 by going to froshims3
and saying, I am David, I'll be a captain and Mathews register,
now it claims you are registered really.
Notice I'm again at register3.php,
so that begs the question, what happened this time?
Well, if we look at this code, notice at the very top
of the file, register3.php, notice the very first character,
open bracket question mark.
This says here comes some PHP code.
Then the rest of the stuff for a little while is just comments,
so uninteresting for our purposes now.
But down here, we actually see some logic
and then we see close bracket--
close question mark close bracket
which means end of PHP mode.
So what is this PHP code doing?
Well, it is this code that sort of magically,
thanks to its being accessed by the web,
has access to a superglobal associative array,
inside of which are all those key value pairs from the form
that I myself created.
So if I had made a much longer form with ten fields,
this associative array would have ten different keys inside
of it handed to me with the users input.
So I first do a sanity check.
If the name key in this ray, it is not equal to quote unquote,
and the gender keys value is not equal to quote unquote
and the dorm keys value is not equal to quote unquote,
what's the implication sort of just in layman's terms?
[ Inaudible Remark ]
>> They build out the form, right?
They must-- because those values are not the empty string
because they're not blanks,
obviously the user typed something in
and so they're not blank,
and so now I wanna do something with that.
I'm going to assume without doing much error checking
in this version, but you know what,
this person just registered.
So I'm gonna compose this email to the proctor
or to myself in this case.
I'm gonna-- I read the manual to figure out that
if I wanna send it from myself, I can just say from:
malan@cs50.net and then the stupid convention here backslash
r backslash n, you might recall that, but that was just
from the documentation.
But the real magic is just in this one line.
It turns out that there is a function built into PHP,
definitely doesn't exist in C, that is called mail,
it takes 4 arguments, in this case, the two argument,
the subject, the body and then those things called headers,
the additional metadata at the top.
And the end result, recall, was that I was able
to send an email to myself.
And if I actually go check my mail now here
in this Gmail interface, this is what the proctor would see.
But here is the sort of tidbit that's worth mentioning perhaps
for security and privacy sake.
If I actually view the details of this, indeed,
it looks like I've sent an email from myself to myself.
But it looks like with this programming power, I can say,
you know what, I don't want these emails coming from me.
I want them coming from Cansu, aydede@fas.harvard.edu.
Save that, Cansu is not here today.
So, now I'm gonna go back to my inbox.
I'm gonna go back to the form.
I'm gonna reregister as, let's say, Yuhki this time,
also for Mathews, register.
>> Says I'm registered.
Let's go to CS50 mail, reload the page.
Oh, Cansu emailed me.
And indeed I have an email now from aydede@fas.harvard.edu.
So, don't even get us started
on the insecurity of email, frankly.
There is absolutely no fundamental authentication built
into the email accounts that you and I use everyday.
Very often, if you have a sponsored account
with a university or a company,
the system administrators could very well prevent you
from changing that address to something else.
But even I use Gmail all the time.
Yet if you've ever gotten an email from me personally,
it still says harvard.edu
because I'm essentially spoofing the from address.
Now sometimes when you do this, you increase the risk
of your email getting flagged as spam
because servers are getting increasingly sophisticated
and they realize, wait a minute, this is from harvard.edu
but it clearly came from Google servers
on the West Coast, this can't be.
This feels like it's probably spam.
So it's not a perfect system, but fundamentally now,
emails are very easily forged.
And that's why I was able to get that email about the 123--
what was that, 123contactform.com.
It's just as easy for spammers
to forge any one of our addresses.
So as a rule of thumb, if you ever do get an email
that perhaps seems to be forged, frankly what you can do
in various email clients including Google,
if you look at the original,
you'll see some interesting things, and we won't spend time
on this here but every line in the original part
of an email shows you the IP address or the name
of the server that this email passed through.
So you can often do a bit of forensics and infer
from these headers where this email actually came from.
And if we dwelled on this example, we would see that,
it came from aydede@fas and yet entirely
through Google server, so how could that be.
Or actually not even Harvard's Google servers,
in this case it came from our own cloud server,
which is not FAS, it's of course CS50's own domain.
So I would be remiss if I didn't say don't actually do this.
Actually I-- and I'll say this from personal example.
Freshman year, I was really proud of my self
when I learned this trick
in how you can just put any email address you want
in your email client and voila, send an email.
So I made the unfortunate social and perhaps political decision
to send an email from someone else in my proctor group,
but like a complete moron forgot that I sent out emails
at the time with an automatic signature
which happened to say DJM.
So this email I sent to my whole proctor group
on some stupid kind of issue has made some point on behalf
of a roommate whose opinion I was trying to change,
whatever the stupid-- the story was at the time.
And it was like sincerely Joe or whoever the person was, DJM.
[ Laughter ]
>> Caught.
That was not a good time.
So, again, do as I say, not as I actually do.
Kind of recurring theme here perhaps.
So-- but really the takeaway for today is
that the cool power here is our ability now
to automate very mundane, very tedious tasks and also
to have these processes scale.
Frankly, one of the neatest things about being able
to make a website that's not static,
but that's dynamic using a language like PHP
and we'll see today using a database like MySQL is
that you write this kind of code
and then you walk away, you go to dinner.
You go about your business and the thing runs itself.
Frosh IMs continues to get registrations.
Some of you have probably RSVP'd for the movie already and that's
because we set up a form that's doing that automatically.
So you really get this ability to automate processes and free
up your own human cycles
to solve much more interesting problems.
We do this all of the time.
Certainly this semester, with 500 some students,
anytime you submit those damn forms,
we get 500 new submissions to deal with
but we automate the process of dispatching all
of your individual responses to your teaching fellow.
We synchronize with the server to do so and really humans have
to be relatively uninvolved in that process.
So, you can start to do some neat things
that benefit you, the person.
Alright. So, what more can we do?
Well, let's take a look at froshims4.php
and inch our way toward actually using a database 'cause frankly
at some point, the proctor's expectations are going to rise.
And he or she is not gonna be cool
with getting an email every time one
of 1600 freshmen registers for a sport.
It'd much rather this information end
up in a spreadsheet or end up in a database so that he
or she can look at it anytime they want but not have
to manually copy and paste from this email.
So in 1996 or whatever, sending an email, this was improvement,
'cause it meant no more paper.
But we can do much better
and we can do much better relatively easily.
So first, take notice of this.
This is froshims4.php and you may encounter this paradigm
in section or an example code that you see in books or online,
realize the action of a form does not need
to point to a separate file.
You'll see in problem set 7 that we do this consistently.
We have one file, say, login.php submit
to login2.php 'cause I think in terms
of the mental model, it's nice and simple.
There's step 1 and then there's step 2.
But realize once you now have the ability
to write this code yourself, you can actually have,
as in this case, a file called froshims4.php submit back
to its self.
Now why in the world would you want a web page like a form
to submit not to a different URL but to the same exact URL,
to the same file, in fact?
What might be the value in that?
Some [inaudible].
Yeah.
[ Inaudible Remark ]
>> Update the what?
So, update the web page of the user data.
Possibly, possibly.
Let me point out one of the sort of downsides
of this current approach.
If I go back to register 3,
suppose that I actually don't fill out this form
and just click register-- oh, actually what--
I can't demo with this example.
What happens on the most annoying of sites?
You click submit, you then get to a page and it says, sorry,
you need to provide your email address.
And then you have to hit the back button and sometimes
on this horribly designed website,
the whole form is then blank and you have
to start all over again.
So if you're actually sending the user from point A
to point B, the problem is
that point B is probably gonna look different and
yet if they made a mistake, you want them to go fill
out point A again, and so why not just leave them there
if possible.
So, one of the tricks we can see here in this next example is
if I look at froshims5.php, if I want to detect
that an error has happened, I can do this at the top
of the same file and then leverage this information.
So, let's see what this thing does.
First, I'm gonna go to froshims4, 5 rather .php.
Let me suppress this error message with a little trick
for now, ignore that little detail, I'll fix it later.
Oh, another trick, ignore what I just did there, fix that later.
Okay. Dammit.
Alright, close your eyes for another moment, line 37,
the server I'm using is slightly different
from the server you'll be using.
That's my excuse.
Alright, I'm losing credibility today.
Okay, perfect form, right?
So now this is froshims5.
Now suppose I just say, alright, you don't need
to know this information, I'm just David, register.
Dammit, line 15th.
Here.
[ Inaudible Remark ]
>> This is fast.
This is one line of configuration on the server
that will make that problem go away.
Okay, so notice here, I said David.
I clicked register and then I got yelled at in red text
but I'm on the same form.
So it looks as though that that red text you must fill
out the form is maybe being conditionally shown.
And sure enough, if we just have some logic in the same PHP file,
we can check was there an error.
If so, let me yell at the user,
otherwise let me just show the form as usual.
So this is froshims5 and notice that froshims5.php first of all,
this is almost copy and paste
from everything we've seen thus far.
So I gloss over some details.
Notice that froshims5.php submits back to itself.
And that's what initially is gonna enable this neat trick
of having this immediate feedback loop on the same page.
But at the top of this file, notice what I did,
ignore all of these at signs.
Though for those perhaps more familiar,
the at sign is essentially suppressing these things called
notices that we were seeing, but again,
we'll disable those permanently for you.
So notice this.
If the value of the action parameter is true,
go ahead and do this.
Well, what's going on here?
Well notice here, I'm gonna wave my hands at some of the details
for now but notice that if the value
of name equals equals blank or the value
of gender equals equals blank or the value
of dorm equals equals blank, this time,
I'm actually gonna set a variable called dollar sign
error and just set it equal to true.
One of the minor weaknesses in PHP is
that it's case insensitive sometimes but not always
so you will see some textbooks and resource
that say uppercase true.
Sometimes you'll see lowercase true.
Sometimes you'll see capital T true.
It's kind of a mess.
I would just as always with style be consistent so I'll go
with uppercase this time around.
And so now error is true.
So notice what's gonna happen here.
If I scroll down, notice this line of code.
Recall that you can have if conditions in PHP code
and recall that you can commingle PHP code and HTML.
So notice I have HTML, HTML, HTML, HTML.
Oh, open bracket question mark, here comes some PHP code,
I'm gonna ask a question.
If error is true, so again, ignore the at sign for now.
But if dollar sign error is true, colon, do the following.
So it's okay in PHP to then exit PHP mode because so long
as you've just answered true to that question,
only the following lines are going to print.
So, div style, so this is where I get that red
from div again is the division of the page.
Style is for CSS.
Color colon red makes the text red.
And notice there, there is that sentence.
You must fill out the form, exclamation point,
and at the end of that is going to be my close div tag
which looks like that, and then endif.
So in short, I just have a condition.
In my PHP code, it checks the value of error
which I myself set at the top of this file for the purposes
of reminding my self later
when I actually render the web page was
in fact there an error in this case.
>> But we're still not doing anything with this information.
So let's actually take one more step toward that
and also move away from hard coding everything into our page.
As soon as you find yourself in pset7 or 8 or any final project,
copying and pasting on, it's probably time
to start automating the process somehow.
So it turns out in PHP, you can declare an array,
just as you can in C, and you can statically initialize it
which means you can put values in it right from the get-go.
And they don't have to have numbers associated
with them yet, they'll be infer, and here's how we do this.
I know from just having, you know, looked up some list
of all the dorms on campus or figure them out off the top
of my head that here all the dorms,
feels like this is a good candidate for an array.
Otherwise, I'm gonna have to copy and paste the same HTML
for each of these dorms so I can have
that drop again and again and again.
So let me go ahead and declare a variable called "dorms."
I wrote it in all caps this time just like you might in C
to say this is some global variable,
this is a constant rather,
this is a constant storing all the values
that aren't gonna change.
In PHP, there's actually a function called array whose
purpose in life is to take 1 or--
0 or more arguments and those arguments become the elements
inside that array.
So whereas in C, you would've just done a curly brace.
Unfortunately in PHP made the slightly annoying decision
to call it array as a function and so you have parenthesis
and then white space as in C doesn't really matter
and decided rather than having this be a huge long line of text
with commas and quotes, I decided to put every dorm
on its own line with commas separating them just
to keep things kind of pretty,
but that's just an arbitrary stylistic convention.
But now notice what I do down here.
You can generate forms even dynamically.
So now notice what I've done here.
The TD tags are part of the table.
So TD is Table Data.
So ignore, for today's purposes, some of the markup like TR
which is Table Row, TD which is Table Data,
'cause like we saw last week,
that just let us structure something behind the scenes.
And if it's new to you,
it actually is pretty quick to pick up on.
But the point for now is this.
There is this other element,
for some reason it's not called an input, it's called a select.
This is what the code with which you make a drop-down menu
in HTML.
But notice, I can commingle some PHP here, too.
I'm gonna say select name equals dorm.
So this is declaring a drop-down menu called "dorm,"
whose size is 1, which means you're only gonna see one dorm
at a time which is generally the default.
Notice that I wanted this menu to be blank initially
so I declared an option whose value is blank
and who has nothing inside the open tag and close tag,
and that's how you just create a blank option in one
of these drop-down menus and then I have this.
And this is where it gets interesting.
What's really nice about PHP is
that it doesn't just have four loops in wild loops
and do wild loops, it has some other useful mechanisms
that some of you might have seen in Java
or other language namely foreach.
Foreach is just a for loop.
It doesn't do anything fundamentally new
but it allows you to write some code a little more simply
and a little more quickly and I can say something like this,
foreach array as variable.
So what do I mean by that?
Its first keyword inside its pair of parenthesis is
to put the name of an array--
the variable for an array in which case, dorms.
Then you literarily write as,
and then you choose any variable name you want, dollar sign I,
dollar sign N, dollar sign dorm in this case,
just to be conceptually consistent.
And what's really neat about this construct is
on every iteration of this foreach loop,
it's going to assign to this value the first element
in dorms; then on the next iteration, the second element
in dorms; then on the next iteration, the third
and so forth so you don't have
to do this very annoying bracket dollar sign I notation
that we've been doing for weeks.
You can just conceptually loop over all the elements in array
and assign each of them to dollar sign dorm one at a time.
Now I need to dynamically output this option element.
So notice what I'm doing here, option value equals "".
But notice again, I'm commingling PHP and HTML here
so I need to quickly drop into PHP mode, output the value
of some variable, then drop out of PHP mode
and so that's what I'm doing, open bracket ?
equal sign which says put the value
of the following variable here.
What variable?
Dorm, "". And then also because the option element not unlike
hyper references has both a value that you actually--
that actually get stored like a URL and what you see.
I'm also just gonna put verbatim,
the dorm's name out here as well.
And so the end result if we go to this example here,
froshims6.php, is really the same form.
But now we're starting to leverage some
of our program-- oh, dammit.
Now we're starting to leverage some of our programming skills.
I'm gonna cheat and tell
that this is it 'cause I already fixed this one.
And now we have this drop-down whose first element is blank,
but then whose subsequent elements actually have all
of these dorms.
And so here, too, is where things start
to get even more and more powerful.
Just to contextualize this, something like--
I mean, frankly, even something like Harvard FML
which is actually outsourced to, I think, Tumblr,
which is a third party blogging software.
They use some kind of database and in that database are row
by row, all of the POSTs that people have made.
If that site happens to be implemented in PHP,
it doesn't have to be, but maybe it is,
you can imagine getting back an array of all of the POST
on Harvard FML and then just using some fairly succinct
syntax like foreach POST as POST
and then this is how you see the exact same structure
for everyone of the POST on something like Harvard FML
but the content changes.
But it's really just being generated by a for loop.
So much like we use for loops in C to generate any kind
of cyclical structures,
similarly might we do this here in PHP as well.
So let me go ahead and spoil just a couple of more details
and show us the most fun one which is going to be this one.
Alright, so froshims8.php,
we finally have-- alright, there we go.
This one is clean.
In froshims8.php, notice that it actually submits
to a file called register8.php.
And-- I don't actually have eight versions of registration.
I just wanted the numbers to line up,
if you're following along on paper.
So this form looks exactly the same as every other,
but it submits to register8.php.
'Cause I got tired being the proctor of dealing with all
of these emails, so now I actually wanna store this
information in a database.
So I'll look back at the code in a moment, but let's go ahead
and introduce the actual database
in which we'll store this.
So a database is really just--
a database as most people know it is what's called a
relational database.
And a relational database is essentially some kind
of special server software that stores lots of tables.
And a table is just something with rows and columns.
It's just a spreadsheet for-- if that's the most familiar notion.
So there exist various implementations of this idea
of a database, there exist Microsoft Access,
Microsoft SQL Server, Oracle,
and then something called MySQL, and dozens of others.
But MySQL is one of the most popular, free,
open source database servers.
And by open source, that means you can look at the source code
that other people have written and it power
such sites as Facebook.
Frankly, it was Facebook--
was really one of its biggest proponents in recent years
that really pushing it to its limits in terms of scalability
and they still use it for much of their data.
So what better option is there when it's free
and apparently it scales pretty darn well?
So we too, because of its relative ease of use
and its free availability, use MySQL for this portion
of the course, which is to say that we, the course,
run a database server and what's neat is you can go
on your own laptop and download the same software
and run a database on your laptop.
I'm sorry, that's not useful
for other people 'cause your laptop is probably not a web
server on the internet.
So generally, when you have a database server,
it leaves on some central server, like the cloud.
If you decide to commercialize
or just migrate your final projects though off
to a commercial web host at the end of the semester,
almost every commercial web POST
out there these days provides MySQL server.
So it's very easy to run your data elsewhere.
So what does this actually mean?
Well, I need at some way
of actually creating these things called tables
in the course's database server.
And for this, something like this froshims feels
like I just need one table, one spreadsheet,
each of whose columns are gonna name and then a captain column
and a gender column, and a dorm column, and that's about it.
So if you were to pull up Google Docs or Google Spreadsheets
or Excel, you would just write those labels
at the top of the field.
But because we're actually using this in a programing context,
I'm actually gonna pull up this tool.
So there is a program that leaves per Problem Set 7
at cloud.cs50.net/phpmyadmin.
It's actually coincidence that the thing is called phpmyadmin.
It does happen to be in--
written in PHP but we're using it
because it's a wonderful user-friendly web-based GUI
for interacting with a database server.
So when you pull up this URL, you're prompted to login
with a username and password, pset7 will tell you exactly what
that username and password are and you'll see this interface.
And frankly, this is a little overwhelming at first
but almost everything
on the right hand side we can ignore from now on.
Because the only I care about at the moment is the fact that now
that I've logged in, I have access to a database
that we've created for you all and mine happens
to be called Malan_Lecture.
Yours will be called Username_pset7.
So I'm gonna click this.
And now notice, no tables found in database.
So this is what would happen too in a commercial web host
or if you install the software on your own laptop,
you would initially have no actual tables in your database.
So you can think of table really as a worksheet,
like a spreadsheet has multiple worksheets in Excel
and Google Docs, so I have no tables yet, so I need one.
So I'm gonna create one called registrants, let's say,
for all the people who have registered,
and then how many fields, quick sanity check for,
so then I'm gonna click go and realize there are different ways
of interacting with a database server.
This is just one free user friendly way of doing it.
So now you'll see on the page that appears.
>> It's kind of wide on my screen here.
But I'm being asked for a few answers to questions.
First, I'm being asked to answer
"What do you want me to call this field?"
So I'm gonna quickly say name field.
This one is going to be, let's see, what was it, captain.
This next one is gonna be gender.
And this last one is going to be dorm.
But there're a few other questions I need to answer.
Unlike PHP which doesn't really have explicit data types,
at least that you need to mention, it's a little more
like C MySQL because when you're using a database,
it's actually really useful to know what type
of data you're storing for performance reasons.
Now for our example here, there's never gonna be more
than like 1600 freshman register,
but again consider Facebook, consider Google Docs,
consider any concepts where there's actually thousands
of entries or millions of entries.
In fact, we've been running some of those CS50 apps now
that you-- you might have played with this semester for two
or three years and some of those databases case and points,
if we go to tweets.cs50.net, we have a MySQL table
that stores all the tweets and right now it has 105,654 tweets
that people have tweeted just in a row in a database
and that's not even considered that big.
So you can actually do some neat things with the software
but we need to choose a type.
Now for a name, we want a string conceptually.
But in MySQL, there's different type of strings
but we'll highlight just one for now,
VARCHAR is perhaps the most common.
VARCHAR is variable char and this is
when you know you want a string of characters
but you're not quite sure how long that string is gonna be
but MySQL does demand that you give it an upper bound.
So very common for name might be, how many let--
characters do we need to allow for a person's name.
I mean like 10 is feels too short, 20 feels like it's short,
I mean frankly there's not that much harm
in just rounding up, frankly.
So let's say 255 is a pretty good upper bound even
for certain foreign names which might be longer
than traditional American names
like that gives us a pretty good buffer
but we could go even higher, thousand and 24, you know,
generally people have rules of thumb that they stick to,
255 is a popular convention because years ago,
this actually used to be the maximum though now it's 65,000
characters, that's probably overkill for someone's name.
Now what about captain?
So how do you implement the notion of a captain,
what data type instinctively might you want?
So a Boolean.
So it turns out that MySQL
and generally SQL databases might not support Booleans per
se but they can mimic them.
In fact even if we see it here, let me scroll back up,
there is a Boolean value there
but you can actually implement a Boolean frankly with just an int
or short or 8-- or char frankly
so that's actually what the database server is doing.
It's calling it a Boolean but to be honest,
this is probably gonna cost us at least 8 bits.
It's just the computers deal usually with bytes,
not individual bits, so that's fine.
Now captain is gonna be a Boolean.
Gender, meanwhile, I actually have a few options here.
So I could do something like another Boolean
and I could rename gender to be female
and then it can just be true or false
or could be male and be false or true.
I could do something like an int and just assume
that 0 is female, 1 is male, I could do a string
and actually store M or F, I could store male or female.
Frankly, there's so many different ways.
Let's go ahead here and let's just leave it as, let's say,
let's just do 255 again 'cause we won't think
about it too hard here but it's gonna be a string.
We'll store male or female or something like that
but it might not be the best decision
but it will get the job done for now.
And then dorm, this probably should be a VARCHAR
and we'll say 255.
But again, we're not using 255 bytes necessarily.
It will use a variable number.
This is just an upper bound.
Now finally if I scroll over here,
you're asked a few more questions
which eventually become relevant.
Do you want this thing to have a default value?
For instance, do you want it to be null,
do you want everybody's default name to be John Harvard?
You could do that at the database level by typing
into this field but for now I'm just gonna go ahead
and click save.
And now notice I'm returned to this tab here.
This is the so-called structure tab
and now you just see a little reminder of all
of the fields you just created, their default types
and then only because MySQL was originally written
by some Swedes, the default coalition is--
refers to Swedish, it's not a problem.
You can fit all the American characters inside
of the same encoding scheme.
So it's not a bug, it's a feature.
They wrote it, they get to decide the default encoding.
So what's now the takeaway?
Well let me go back to froshims8 and register8.
Notice now I have the following code and I'll just tease you
with some of it first then we'll look at more detail.
It turns out there's a function called
"mysql_connect" that's using my very public user name
and password now.
I then have a function called "mysql_select_database"
because there's gonna be hundreds of databases
on this server, I wanna select just mine called Malan_Lecture
then there's gonna be some code down here where I'm going
to scrub the user's input.
It turns out there's a lot of bad things people can do
by knowing this language we're about to look
at called SQL whereby they can log into your website,
they can delete your data if they know what they're doing.
But the real magic boils down to this.
Instead of sending an email,
I'm ultimately gonna call a function called mysql_query
whose purpose in life is to take a query which will right
in a moment store that data not in an email
but into that database.
So now, if I've done all this correctly,
I can visit froshims8.php, I can say David Malan
and will be a captain, gender and we'll say Matthew's
and click register and it says you are registered really,
that's means nothing.
So now let me go back to my database
which the proctor could do
or frankly I probably make a more user-friendly interphase
with proctor, I being the programmer don't mind so much.
I'm gonna click browse and, wow, I now have stored in my table,
my spreadsheet, now this record,
which means now I can do anything I want with this data,
I can email it out later, I can render it on a website,
I can search it, sort it, more on that after a 5-minute break.
Alright, so we are back.
That was enough time for me to fix my bug.
I forgot how to compute percents so
and I also forgot how to sort.
So both bugs had been fixed much
to my disappointment though thankfully Ben, Joseph,
and Brook [phonetic] have pulled behind me in the leadings.
I am now back at 0 percent, rank number 15 here since my stock,
my penny stocks have probably gone back up but Michael
and Zack [phonetic] are doing pretty well.
They are now up 200 and 100 percent respectively,
so good luck.
Inevitably, this turns out to be not so much.
He was really good in investing at Harvard but who's really good
about leveraging certain weaknesses
in the big board namely that one comes to mind, not to spoil.
We'll spoil this.
We'll give you guys the edge and for those not here,
they won't know this trick.
So it turns out that if stock quotes are not realtime,
and there are something like Yahoo!
Finance, but other stock quotes are realtime unlike Bloomberg TV
or CNN or your own E-Trade account,
turns out that you can effectively see
into the future by about 15 minutes.
So if you are in fact watching certain penny stocks
and realize, wow, that stock just shut
up on my real E-Trade account, still have about 15 minutes
to scoop up as many shares as you can
on the CS50 finance version of it.
Also too, with a lot of these penny stocks,
actually I don't mind just divulging this
because inevitably every semester, there is some student
who thinks here she's really being clever
in gaming the system when really we're not just telling you
about these weaknesses.
And now I pull the disclosure.
Let's see if we can really push the maximum amount
of money earned in three weeks' time up.
The other weakness of course is if you're buying
like 10,000 shares or 100,000 shares of some penny stock
in our simulator here, probably in the real world,
you yourself are gonna drive the price way up
and so you're not gonna actually get
that penny stock for a penny.
So realize that there-- you gotta take some of these
with a grain of salt but what's neat is you can actually see
what each other are doing.
If you'd like to learn just how good they are
or just how clever they're being.
For instance Michael has really been an active day trader here
in the one day this is available playing with our--
actually not some terribly cheap stocks.
So perhaps he has some ability to see into the future.
I just went with one penny stock at 41 cents there.
I'm gonna hang on to it.
I'm a long term investor with these penny stocks.
[Laughter] So in any case,
how do we actually now start storing this data just
like the data you're seeing now on the big board
into an actual database.
Well it turns out the magic there derives
from a language called SQL, Structured Query Language.
This is not so much a programming language
as it is as the cue denotes.
A query language whereby if you have a database
and you wanna grab data from that database, you need some way
of expressing it and it turns
out you can do most database manipulations
with just four basic commands.
One is Insert and we actually used that already
and we'll pull back that layer in a moment of my code.
Select if you wanna grab data from the database.
Update if you just wanna tweet something.
And Delete if you wanna delete from the database.
And there are a bunch of other statements
that you can actually get in SQL but these are probably
by far the most common and certainly the most useful
to learn right up at-- right at from the get-go.
So let's go ahead and take a look here.
So if I go back into this very last froshims example,
you will see in froshims--
in register8 that I did the following.
At the very top of the file, first I have my sanity check.
If name is blank or gender is blank or dorm is blank,
redirect the user to this location which is
that long URL just 'cause I put a lot of subdirectories there
but in other words get them out of here, like send them back
to the form and let them resubmit.
This isn't the most user friendly form.
There's no feedback here.
So realize each of these examples should be looked
in isolation.
Sometimes we have read texting, there's an error,
sometimes we don't bother.
Each of these examples is meant to illustrate per the comments
to top each a different point.
But let's supposed that we passed the validation.
In other words the user has not left any of those fields blank
so we don't execute this branch
and so we reach this line of code.
>> It turns out that if you wanna connect
to a MySQL database in PHP code, it's very simple and realize
that MySQL is not at all tied to PHP.
You can connect to it in Java, in C, in C plus plus,
in C sharp, really any of your favorite languages you can use
to connect to this type of database.
And so that's nice.
We're really not tying our hands with one language here.
So how do you connect to a database?
Well, I read the manual and found
that there's a function called mysql_connect
and it takes three arguments.
One, the IP address or the domain name of the server
to which you want to connect, as you'll see in the problems set
and in your distribution code, mysql.local domain is the made
up domain name that we created for our database server.
And I say made up because we don't want this database server
out there in the internet for other people
to connect to and interact with.
We want it just to be private to us on the cloud so we came
out with this local domain moniker instead
of using .com or .edu.
It's only accessible within the Cloud.
Now it takes my user name as my second argument.
My password as my third argument and then I now have a connection
to that database assuming everything was correct
and so now I have to select the specific database because again,
there's like 500 databases on this server.
I wanna choose specifically my own
which in this case it's called Malan_Lecture.
Yours again will be called JHarvard_pset7
or something like that.
Now notice this and I've comment to the codes
to make clear what's going on.
There're not many lines of codes required to get data
into a database, pretty much the same number as everybody
as required to make an email.
So scrub inputs.
So this is actually a good habit to get in to very early on.
There is what's known in the world something called a
"SQL injection attack."
This essentially is an attack
where by a sufficiently clever adversary goes to your web page
which has some kind of HTML form and instead of typing in David
to the form and instead of typing in any expected text
into that form, instead this adversary starts tossing
in weird characters like maybe some quotes,
maybe some semicolons, maybe the keyword delete.
The whole idea of being they're trying
to insert dangerous commands into your database
without you realizing it so that you might be trick
into executing this code.
We'll talk more about this in due time perhaps.
But for now, know that anytime you insert data into a database
that was handed to you by a user,
you should call this idiotically named function.
Frankly, mysql_real_escape_string,
so 0 point for style there, frankly.
But that's what we're stock with.
That function's purpose in life is essentially
to put backslashes in front of any of those scary characters
like semicolons and quotes, they're just
in general had caused us a little bit of concern
because they're used for programming syntax.
So for now, realize what I'm doing.
I'm declaring a variable called dollar sign name.
I am storing and ultimately, whatever the user types in,
the value of name and that's super global,
but I'm first escaping it with mysql_real_escape_string.
Now notice what I'm doing next.
It turns out POST for captain,
recall what's submitted for captain.
We saw this last week.
If you checked that box, the value of ON, O-N is submitted.
And if you don't check that box, nothing is submitted.
So my little trick here is to just treat
that kind of as Boolean value.
If the captain field and the super global evaluates the true
and it will if there's anything there.
So remember there's a lot of implicit casting that's possible
in programing whereby anything non-zero is essentially true,
so I'm leveraging that understanding.
And I'm saying if the captain field is anything other
than false anything other than blank, then I'm gonna assumed
that the user check that box
and I'm gonna set a local variable called captain equal
to 1.
Else, I'm going to say it captain equal to 0.
Now here's a curiosity about PHP too and it kind
of gets a bad wrap for being a little too loose
with some of these details.
You do not need to declare variables in PHP.
Notice, I am just on the fly whenever the heck I want
declaring variables, dollar sign name, dollar sign captain.
In fact, in the context of C,
this would be a big no, no, right?
This is essentially-- this variable is scoped here
and you can use curly braces in PHP.
But like C, if you only have one line of code it doesn't matter.
In C, I think even on quiz 0, this got us into trouble
because these variables were scoped too tightly.
In PHP, once you declare a variable you're stock with it.
It now exists everywhere in that file even inside
of curly braces and outside of them.
So on the one hand, this is kind of useful
because it can let you, the programmer, be a little more lax
when it comes to the structuring of your code
but it can also very quickly lead to bad habit.
So realize here, it feels okay to be doing what I'm doing
because I'm always initializing captain to 1 or 0
but even I have got corners in the past where recall
from a previous example I set
that variable called dollar sign error and I set it to true
but nowhere in my code
that I ever explicitly said it's a false.
I just assumed that if I don't set it
to true, it would be false.
But again this is really just a different programming paradigm.
It's a language like PHP people do tend
to take these liberties whereas something
like C is much more anal about how you have to write your code.
So it's a good thing and a bad thing so long as you start
to not abuse some of these features
by just declaring things all over the place.
But here, I proceed to declare gender.
I'm storing the value of the escape version of gender
in that variable and same with dorm.
So in short, with those several lines of code,
I just wanted four new variables, name, captain,
gender, dorm that stored the same thing that the user typed
in but the escape versions of them.
Just so I don't accidentally delete my data by being tricked
into registering delete Malan and not David Malan.
So now I prepared the query.
There's different ways to do this.
But notice what I've done here.
Ultimately, I need to execute a query like this.
INSERT INTO, the name of the table.
Then in parenthesis, I specify the comma--
a comma separated list of the fields that I want
to insert values into.
What's nice is with the database table, you can have 20 fields
and you don't have to fill all of them out.
It might be reasonable for some fields to be blank.
Maybe someone doesn't have a website, and so the URL
of their homepage should be blank.
Maybe they don't wanna give you their email address
so maybe you don't wanna fill in that field.
So with-- by parenthesizing this list of field names,
you can say, "I only wanna insert a row into my table
for the specific fields."
In this case, it's of course all four of them
but it doesn't have to be.
Now what values do we wanna put in there?
Well notice here, this wrap onto two lines and that's okay.
I just move the second down the below and that's fine in PHP.
Notice I have now values, parenthesis, close parenthesis,
and then another comma separated list but you have to bear
in mind and this is one of the most common sort of gotchas
in office hours and in these psets,
you realize that anytime you have a string
in PHP, you must quote it.
Now you can use double quotes, you can use single quotes
so long as you're consistent.
I use single quotes here but notice I put single quotes
around name, around gender, and around dorm but not
around captain because captain I decided would be an integer
so it doesn't need to have them there.
So at the end of the day, the string that I've just stored
in a variable called SQL looks a little something like this.
Insert into registrants the name, captain, gender, dorm,
values, "David Malan" captain I think I said--
well, I think I sent an M and then dorm was Mathews.
So this is the string that I've just constructed dynamically
by way of using these variables and these placeholder,
these variables inside of this bigger string.
So the magic then happens with this one line of code.
I've now constructed what's called a SQL statement,
a SQL query, that's the insert statement.
I then past it to the function called my SQL query whose
purpose in life because I've already connected
to the database, I've already selected my database,
all that happened before is now to execute that query
against the database and so the reason that this row ended
up in my table a moment ago was
because that insert statement just got executed.
In fact, I can simulate this myself.
Rather than go through the web, let me actually show you
that this thing is in fact doing what I say.
I'm gonna go to PHP my admin, just this little tool.
We've seen the structure tab.
That just reminds me what this thing looks like.
We've seen the browse tab.
That shows me what's inside there.
I can actually interact with SQL directly.
If I click the SQL tab, notice I just get a big text area
that I can type any commands into that I want and here too,
this is why it's a wonderfully useful learning tool
because you can just play and it doesn't really matter especially
when you have no data to lose, if you get something wrong.
So I'm gonna try pasting this in and instead
of registering myself,
I'll register Jan Sue [phonetic] this time.
I'll change this to female, this to Eliot
and then let me go back here and click go and now it says up top,
one row inserted, so let's see if that's true.
Click Browse, Zoom Out and sure enough,
now I have two rows in this table.
Now, suppose, I want to get all those users back,
come to the question just for a moment.
Now I want them all back, I'm gonna actually go
to another piece of code I wrote here called registrants dot PHP
and now I dynamically generated a webpage
that executed instantaneously that does not have David
or Jan Sue hard coded into it but it's apparently
out putting a bulleted list by way of a select query
which we'll see in just a second.
Yeah.
[ Inaudible Remark ]
>> Very similar, yes.
So in C, you can use sprintf.
That actually exists in PHP as well.
There is many, many ways to construct the string on the fly
in PHP and just plugging in these variables
as I've done here is legitimate in PHP and frankly,
it's arguably a little simpler
than having use those percent S placeholders everywhere.
So let me draw our attention then to this.
So this might be for instance the PHP page that I give
to the proctor when it comes time for him or her
to see who's registered for some Spork at the top
of this page, registrants dot PHP.
Notice that I connect to the database again
with my username, with my password.
I select the same database and then I create this query,
select star where star means everything,
give me all of the rows from registrants.
Now I execute this query but this time because I'm asking
for data, I have to remember the return value.
I can't just call my SQL query and then call it a day.
I need to get back the server's answer so I store
in a variable a called result.
Now, let's fast forward.
I seem to be exiting PHP mode outputting a default webpage
but then notice this, what's really neat, I have this UL tag
which stands for Unordered List and if you look this
up in the manual, a UL tag has to have LI, list item tags,
inside of it and every one
of those list item tags becomes a bullet.
And so what I'm doing here is outputting one or more bullets
so how am I iterating over the results and here is
where the really neat feature is of this prog--
of this being able to program against the database.
I can call a function called my SQL fetch array.
Where do I want to fetch an array from?
I specified dollars on results as the argument
and that says fetch them from the results I've just got
from the database on every iteration of this loop,
store that array in a variable called dollar sign row
because conceptually, when I say select star,
that means give me all the rows.
So when I iterate over each of these, I'm being handed
that row really nicely as an associative array.
So just these columns have names, name and dorm and gender
and captain, I can now get those values just
as though they were from an HTML form.
This time they're coming from a database by way of their names
and so this time, I'm gonna print a list item tag then I'm
gonna close it but inside of that, I'm gonna print
out dollar sign row "name"
and what that's gonna do is I'll put David Malan then Jan Sue
[inaudible] and then that's it for that loop.
And as an aside too to get us in the habit of best practices,
HTML special charge is a function that also takes care
of any dangerous characters so that
if a malicious user actually typed in something
like open bracket blink close bracket, they couldn't trick me
into outputting a webpage for this proctor where all
of the names are actually blinking by having entered,
again, a blink tag and not an actual name.
So we're really just scratching the surface
of all of these features.
On Wednesday, we'll introduce a language called Ajax--
JavaScript and with that a technology called Ajax
with which all of today's modern sites are now leveraging.
We'll see you then.
[ Inaudible Remark ]