[ 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 ]