[ Silence ] 

>> Alright everybody, welcome to the Walkthrough for our pset7: CS50 Finance. So now, we are beginning our transition from C and just working on the appliance and the terminal and writing C and compiling it into web applications which I think personally are much more exciting. And I've spent a lot of time writing these things. And so you're gonna be actually be able to run something in a web browser and show your friends and they won't have to download the appliance and that'll be awesome. So today's music was Basshunter, another classic. So today, we're going to being going through each part of this problem set. So we're gonna talk a lot about the background of web program in general since we haven't seen a lot of that yet, things like file permissions and what PHP in this mysql are and how you can use them, and then just go through each component of the problem set. So before you start, you wanna set up your appliance to be able to act as a server where a server is something that serves you websites whether they'd be HTML or PHP or whatever you have on inside of your server it's basically going to be able to display them to a user who access these items via URLs and their web browser. So in order to do this, the first thing we need to do is create some folder that's going to be the root of our server. So whenever I go to some URL, the server is gonna jump into this folder and start looking for content at the start of this folder. So this folder just happens to be called public_html and you're going to create that inside of your home directory. So if you say, remember from, you know, week one, this mkdir or make directory, you're going to create a new folder inside of your appliance called public HTML. And all of the web application code you write for this pset, the next pset and your final project is going to live inside of this folder. So after you create it, now that's where you wanna run this git command that's gonna download the distribution code which there's a lot but we're gonna go through it so it's not that scary. Because this is gonna pull down all of the starting code for your pset and make sure that you change directory into public HTML first so you don't pull down all this code inside of your home directory as you're used to. But make sure all of that gets downloaded into public HTML so that way you can access it from the web browser. So after you download anything, we need to change the permissions of everything. Oh, not everything but everything we just downloaded. So we have a new command called "chmod" that we may have seen before. But all chmod does is it's going to change the permissions of either a folder or a file. So if you wanted to make something executable for everyone like your home directory which is something you'll need to do, you're gonna say the word "chmod" so the command to invoke this permission changer, then this a + x. So what does that mean? So this A says I wanna operate on all users, so not just me or not just a certain group, I want everyone and I wanna add the executable permission. So that x for executable means that they can run programs or as we'll soon be doing, run this PHP files that can generate some output. And finally, the last one is just what we're changing. So this [inaudible] says I wanna change my home directory which is located in /home/jharvard. So if we wanted to, as the pset spec does change the permissions to make all of thesethree things executable do your home directory, public HTML, and pset7, all of these things have to be executable. So before you go anywhere, you're gonna need to say "chmod a + x" again, make it executable for everyone all three of these directories. You can specify as many as you want in a list and it's going to change all of those permissions at the same time. So that's for directories but we also wanted to set a different permission for things like images and CSS and HTML files. So if you wanna make sure if something is readable, so not necessarily executable like a binary but just readable like you would open a text file, you'd say chmod again a for everyone, + r this time where the r is gonna say, "I want you to add on the read permission." If you want to do something like subtract the read permission, you'd say "chmod a - r." That's gonna say, "Now, everyone can no longer read it." That's not really something you need to do for this pset. So now if you have a direct [inaudible] /star, that star is gonna say, "Well, everything that's inside of that folder." So the folder itself, you didn't just add the read permission to, you added this read permission to everything inside of the folder where the folder itself could have a different permission than all the files inside of it. So why do we wanna do that? Well, we can't really execute an image as a program, right. We can execute Sudoku and it run. But if we try to run like a jpeg, nothing is really gonna happen unless it's a cool jpeg. So here's a list of this more fine grain permissions. So if I say something like chmod 644 file. So this is gonna change permissions to some file. It's going to set them to 644. So this is unlike the syntax we just saw, we're only adding or subtracting something like the read or the executable permission. What this is doing, if it's going to set individually the permission for the user, some group of users, so every user belong to some group and we don't really care about that. And the last digit is for everyone else. So where do these numbers come from? Well, you'll notice if we have rwx, there are three places for letters. So if we have a letter in every single one of those places, we have kind of like the binary number 111. So that's--what that's going to correspond to is the number 7. So in this 644, this first one is a 6. Where did that 6 come from? Well, I can read so that slot is gonna get a 1. I can write so that slot is gonna get a 1. And in binary, that's gonna be 4 plus 2 which is 6. So I've just said that myself, I can read and write this file called "file." Now for everyone else, I've set a permission of 4. So what does that mean? Well, if we can just jump off to our table and 4 in binary is 100 which means only that R is set. So that means everyone else can just read this file but if I'm jharvard and this file lives inside my home directory, then I can read and write that file because I own it. Does that make sense? Okay. So just a little cheat sheet for you, for the purposes of this pset, every folder you create, you're gonna wanna say chmod 711. Every image HTML, CSS and soon JavaScript, you're gonna chmod 644, and every PHP file you're gonna chmod 600. So that's all you really--that's all--all these really boils down to, and if you forget to set one of these permission things, your website is just not gonna work right. So if at some point you end up at a blank screen, first thing you wanna check is your permissions to make sure that your server can actually read in the files that you're trying to read. So the first part of this assignment is to create your very own home page. So to do that, you wanna create a new file called index.html. You wanna put it inside of this public HTML folder. So if I'm a web browser and I'm going to some URL and it doesn't end in something like test.html but it just ends in some folder name, what the server is gonna do is it's going to look for a file called index. So if I just go to say local host or the IP of the appliance which is 192.168.565.0, I've been working with it too much. If you go to that and you don't say, "Well, I want this files specifically," I just say, "I want whatever is there," what the serve is got to do is it's gonna say, "Okay, I'm looking for something called index." So if I find index.html, I'm going to display that out to the user even though that URL doesn't contain index.html. So once you create it, the first thing you wanna do is make sure you do the chmod 644. So where did that come from? Just from our cheat sheet, HTML, so we're gonna have 644. We don't need to execute it. We just need to read it. So to get that, again, if we're inside the appliance, we can just say, "Local host tilde jharvard." If we're outside of the appliance on our own web browser, we need to have that IP address and then we can view our webpage. So as you make changes to this HTML file, no longer do you have to compile it. As soon as you make a change and you reload the page in your web browser, you're gonna see that change reflecting the page. So no more, you don't have to make HTML files or anything like that. You can just change them and then see your changes. So, one thing that's important for this pset is that the HTML you create inside of your home page has to be a valid HTML. So to do that, it's really, really simple, you can just upload your HTML file to validator.w3c.org, and it's just gonna spell out. Is this valid HTML, yes or no? And if it's not, it's gonna say, "On this line, you have a typo, you need to fix this and then it will be valid." So any questions on that part of the pset? Yup? 

>> Do we need to be commenting our webpage? 

>> So do we need to be commenting our webpage? So style counts just as much as it did before inside of web applications as it did for C programs. So maybe inside of your HTML you wanna say, "Like here is where the navigation bar is" like all that file is in navigation bar, here's the footer." You don't really need to say, here's the button, here's the text for the--like you don't need to be that granular. Inside of your PHP though, your actual application logic, you'll probably gonna wanna be as thorough as you were with your C code. But generally with HTML comments, just kind of like where you are in the page roughly, so if I'll just kinda glance at it, I don't need to scroll up and figure out where in the page I'm looking at. I look at the code and the page side by side. I should have a general idea of where things are based on the HTML from the contents--comments. Other questions? Okay. So, no longer are we using the language C but we're now introducing a new language to you called PHP which has one of the best acronyms ever because PH--PHP stands for the PHP hypertext processor. It's one of the best recursive acronyms. So, some major differences between PHP and C, no longer are we writing dot C files, we're now writing dot PHP files. So get out of that habit of saving your files as dot C. So inside of PHP, all of your code is going to be contained within this bracket, question mark and then this PHP is optional. You can omit that if you want if you don't feel like typing it every time. And it's going to close with this question mark bracket. So any PHP code you write must be enclosed between these two characters. You can't just have PHP showing inside of your HTML or else it's not actually going to execute. 

>> So another perhaps really annoying difference with PHP is all of your variables must start with a dollar sign. There is no way around it, sorry, it gets annoying and you finally have a reason to start typing the 4 on your keyboard. And finally, variables are weekly typed. So when you create a variable, in C we needed to say int A equals something and that something needed to be an integer or you are going to get yelled at. In PHP, we can just say something like dollar sign X equals 5. You don't need to tell PHP that X is an integer because PHP, while it's executing, you can say, "Well, 5 looks like an integer so looks like X is going to be an integer. You don't need to explicitly tell it to that. So simply, when you create a function, you don't need to declare the return type of your function. You can just say, "Here is a function and you figure out what it returns because you're really smart." Questions? Okay. So the major difference between a PHP and C is you no longer have to worry about things like malloc and segfaults and pointers and you'll be so excited when your code no longer segfaults. You just have this like nice little warning messages that tell you exactly where you went wrong. So let's just take a look at some examples of PHP. So I'm going to go inside of my web browser and you'll notice that I'm on my own computer, I'm not inside of my appliance and I've typed in this IP address of my appliance. So 192.168.565.0, that's never going to change whenever you have the appliance running even if it's minimized in the background, you can get at it from that URL. So next to it, I have this tilde jharvard and this says that I'm looking for the public_html folder inside of the jharvard folder. So if I had another user then it will be tilde and then that user's name. But because you always login as jharvard, you're gonna say, "Tilde jharvard," and that's where it's gonna look from public HTML. So inside of public HTML I have a folder. It's called pset7 and within that folder, I have a file, it's called "login.php." So when I type in this URL, the server is gonna say, "Okay, I know exactly what file to give you." It's gonna run the PHP code and display the output." So let's just take a look at what login.php does. So let's open up get it. We're gonna say "file open." So here's my public HTML pset7 and here's login. So, okay, so the first thing it does is it immediately starts a block of PHP code. How do we know that? Because we're starting with this bracket question mark. And until we close it with another bracket, question mark bracket, everything inside of that is going to be PHP. So we're just making a simple function call. So you notice first that this comment in PHP is the same as the comment in C, just slash, slash. And we're saying require_once includes/common.php. So what does that do? Well, that's gonna say, okay, there are some file that exists in the folder includes starting from where this PHP file is right now. And inside of that folder, there are some file called common.php. What I want you to do is basically go in to that file and copy and paste whatever is in there right here. So this is kind of like the define statement we saw on C or the include statement, sorry. We're gonna open some file and just copy and paste whatever is in there right here. So let's take a look at what that looks like. Well, it's open. Okay, here's our folder called includes and then here's common.php. So alright, so this looks like it's doing some kind of complicated stuff. So before we get into that, let's just look at the rest of login.php. So we see some title, so here's the top of my page. And at the top of my page, we have some image. And you'll notice that this image is located inside some folder called images and the image is called logo.gif. So let's just take a look at what that looks like. So if I open up my terminal, I'm gonna go into public_html pset7 images. And if I do an ls, well, there we go. I can see my logo.gif. So if I say ls -l, that -l is gonna tell me to display some additional information. And what's the additional information I've displayed? Well over to the left here you can see the permissions of this file. So if I look at logo.gif, I see that this file is currently set to 644, right. We have 2 bits, which is a 6 and then 2--an r, another r, both of those are 4. So the current permissions of logo.g0ff are 644. And that means that I've--if I'm the web server, I can open up that file and I can display it to you and that's why we can see this logo right there. So now, if I did something like chmod 000 logo.gif and we look at the permissions again, you now notice that they're different. Whoa, if I spell ls right. So you notice that they're different. So now, I've removed all permissions. I've said no one can do anything to this file ever. So now if we jump back to the web browser and we refresh the page, that image is gone because we're no longer allowed to read it. So if you find this happening in your code, first thing you wanna do is try to check permissions. Let's just change it back to 644. Refresh our page again and it's back. So everybody understand how that permissions is gonna work and what things are gonna look like? Okay, so let's a jump back into this login.php. So nothing else suspicious here, we're just displaying those two text fields, that makes sense. There's no more PHP on this page which is good for our understanding. So now let's jump in to common.php. So the first thing we're doing, we're just saying okay PHP, whenever you have some error like you encounter a syntax error or something like that, I want you to be super helpful and I want you to tell me exactly where those errors are. So instead of just core dumped I wanna see, "well, there's an error on line 15 and this is what your error is and this is how you can correct it." So that's just what these two lines say, they just say display all the errors as you possibly can and be super helpful please. So the next of this is just the security measure we're saying, "Well, we just wanna make sure that everything that comes from this cookie, that come from this site are restricted to the URL tilda something/pset7." You do not have to worry about how the hell you parched that, just assume that it works and it does. So now, we're doing this something called "Session Start." So this is creating a new session for the user. So a session is as called a "Super Global." So inside of a session I can store information that's going to persist as I go back and forth through pages. So if you've ever been on CS50.net or any site you login, you notice that when you change pages the site remembers who you are. So if you think about this PHP files look like and if I just create a variable on one PHP file I need to somehow pass that variable to any other PHP file you might rem--you might visit so that I can remember who you are, the easy way to do that is just to put this in some magical variable called "Session." That's going to be accessible through every PHP file on the site that you create. So in order for this session global to work or to be active you need to first just make this function called "Session Start." That says "I'm gonna using session somewhere and I wanna make sure that it works." So now, [inaudible] we're gonna require three more files. So we can just open this up. So the first is called constant.php. Oh, thank God. This is really, really simple. This is just a series of define statements. Just like in C we had that sharp defined, we're just saying everywhere you see DB_NAME, I want you to find and replace that with jharvard_pset7. Of course these credentials probably look familiar to you by now just the user's password is going to be jharvard and crimson. So we're going to use that to get into our database which we'll see later. And so now, we just have a couple other files like helpers. And so this is just gonna contain some handy helper functions. So we can do things like display an error message to the user so we don't have to worry about like displaying an error on a page. If we just say apologize and pass it some string. What this is gonna do is redirect the user to some error page. The error message is going to be whatever you pass in, add this value message. We can also use dump which is really handy if we have some variable or some array or some object. We wanna know what everything inside of that is. If we just call dump, this is gonna redirect you to some other magical page that's just going to contain whatever the variable you pass in this. So this variable can be anything. It could be an array. It can be an object, whatever you want. So log out again, some complicated stuff you don't have to worry about. But you notice that the session destroy they're saying, "Okay, remember that session super global, I want you to wipe it. I wanna make sure that there is nothing there anymore." And so we'll get into the remainder of this file in just a bit. So that's gonna be index.php. So there're a couple of other files you need to worry about, namely these two login files. So we looked at login.php but now let's take a look at login2.php. So let's get rid of these guys and open up login2. So again, the first thing we're doing is requiring this common file. This common file is requiring a bunch of other files but just basically make available to you some helpful constants and functions. So now--Well, so we're creating some new variable username. And notice that I did not give username a type but this is the first time I'm using it. So if I just say dollar sign username I'm now creating a new variable called username. So I'm setting that equal to the result of some function, mysql_real_escape_string, dollar sign underscore POST username. Wow! So what this function does, this mysql_real_escape_string is pretty simple. It's just a security measure. If someone is trying to hack your site and they try to inject some nasty code into--as their username, this function is just gonna get rid of it so your site doesn't get hacked and you look like a [inaudible]. This POST is another super global. So just like session, it's gonna start with the dollar sign underscore and have capital letters. And this POST is going to be the result of whatever the users sent you from some form. 

>> So where is that form? Well, remember, that we first went to this login.php and that's where we saw that login form. So let's actually take a look at the HTML from that login form. Now what we care about is this form element. So this is what's going to start off the login form, everything that's contained within the form is gonna be a child of this form element. So there are two important attributes. The first of this is Action. You notice that it says login2.php. What this says is it's gonna be whatever form information you entered I wanna send that over to login2.php. So okay, so after the user submits this form they're gonna be redirected to login2.php. So this second parameter is gonna tell me how do I wanna pass the data from this file, login.php, to my other file, which is login2.php. I'm saying method equals POST. Well, that looks kinda familiar because I'm saying dollar underscore POST username. So okay, so that attributes just telling me how to get up the data. So now, let's take a look at this dump function that we saw. So let's say dump this dollar underscore post. So dump is just a function define the helpers.php, I just wanna take a look at what this dollar sign underscore POST thing is. So let's go back to my web browser and I refresh the page, inside of my username is gonna say Tommy, and my password is gonna be something really secret, I'm gonna click login. Okay, so you'll notice that I was sent to as we expected login2.php because that was the action of our forms. So that's where the form sent us. So now inside of this dollar underscore POST variable is going to be this array. So this array has two elements. It has a username, which is Tommy; and it has a password, which is secret. So okay, so this isn't just your regular, old C array. This is now it's called an associative array. Or effectively a hash table, 'cause we have some array that instead of numbers we're now accessing the indices of this array with strings. So PHP has taken care of pset6 for you and made a really nice hash table. And so we can just say, "Well, I want the value stored for the key username." So now username or password where did those come from? They're not just automatic because you called your file login although that would be really sweet. Instead they came from these two input elements. So this input says. "Okay, I want you to create some text field. And whatever the user types into that text field, I want to save it under the key password. So username and password, and because I said that method POST, that means that inside of the super global called POST, I wanna be able to get at whatever the user typed with the key "username." That makes sense? Okay, so now let's just take a look at some other PHP examples before we jump into the database stuff. So I'm gonna create a new file and I'm just gonna save it as test.php. So I'm just saving it inside of my public_html. So I saved it as test.php, the first thing I wanna do--God it's so helpful--is open up these two tags. And these two tags say, right here is gonna be some PHP. So let's do a couple of things. Let's just create a variable so the variable is gonna be 5. Now let's create a function so when the first functions we wrote in C was a function that returns to the sum of two numbers. So you notice I do not need to specify the return type of my function, I'm just going to say "Function." After that, just like we're used to, it's gonna be the name of the function and then the arguments that the function takes. So again these are going to have to start with a dollar sign because the variables and all variables annoyingly start with a dollar sign. So just like we're used to in C, let's just return A plus B. So a whole new language, PHP, but this is pretty simple. It's very, very similar to C. We're taking the concepts we learned with one language and we can just apply them to this one. We know exactly what that means. It says a little difference syntax. So okay, so that means that some--where in this file I have this variable and I have this function. So now let's print out some text. So this H1 tag, this says, "I wanna make this text really, really big." So now we open up this file we can get there, via the URL, jharvard/test.php. So we run that and we just get high, right? Because that makes sense, the code we wrote didn't actually do anything. It didn't does have any output to the user. We just created some variable and create define some function that we haven't used yet. So now let's say that I wanna print out this variable to the user. So let's just say H2 which is just slightly smaller text than H1. So now let's open up another block of PHP. So again bracket and question mark and we're gonna say "print.f, print variable." Make sense? We have some variable it's defined in this file. And even though it's defined at a different block of PHP we can still access it from this other block. So we're gonna say, "Just print out the value of this variable." So let's close the HTML tag so that we're making attempt to be valid, jump back to our web page and run it. And so now, we printed f5. Something you might have noticed is that we didn't actually have to say "printf percent d/n whatever." We can just print out a variable and it doesn't matter what type that variable is because print is smart enough to just print the value itself. So now if we wanted to do something like print the result of some function, how could we do that? So what I wanna write inside of my PHP block here? So let's just say I wanna add up the number 1 and 2 so I can print the sum of 1 and 2. So again, function defined up there but we can access it down here so let's jump over and we printed out 3. So a shortcut that you'll see throughout the pset, instead of typing this print, we can actually just say, "dollar sign equals and then the variable name." So this is equivalent to what we just wrote, this question mark print and then wherever you wanna print. If you just have this equal sign these are equivalent. If you just wanna quickly printout a value. So if we go back to our page and refresh, we notice nothing have--nothing has changed because these two statements are equivalent. So any questions on how are we gonna be using PHP in this pset? Yup? 

>> So every different function is contained by--if we put in brackets, it's not like in C where you like declare a function inside of something inside of a function then it can't be accessible outside of it, here you can, [inaudible] scope issue? 

>> Yeah, so this--so the question is how does scoping as you work? So it is a little different than C. And this variable is effectively a global variable as far as this file is concerned anywhere inside this file I can access this variable. So you'll also notice that the blocks of PHP are kind of interspersed with block of non-PHP, right, like this H1 that's says it's high, that's not PHP. So when the server runs this file, it's going to process these blocks in the order that they appear. So if I do something above, I can use that anywhere I want below. But if I declare variable blow here and try to use it up at the top, that's not gonna work. So we can inters--we don't have to have all of our PHP at the top or all at the bottom or something. We can actually intersperse it throughout the page, we can do so between HTML tags to take care of things like formatting and placement other stuff like that. Yup? 

>> Can you still see the webpage but it can still be invalid code? 

>> So can I see the webpage if it's invalid code? So let's make it invalid code. Let's just forget that semicolon. So just like in C, this is now a syntax error. So let's see what happens when I open up my webpage. It's like I'm here, we have this really nice message, syntax error expected a function on line 5. So I'm a programmer, I'm gonna go on line 5. Let's say, "Oh, here's a function here." Well, that's kind of unexpected because I'm in the middle of a statement I can't say something like 5 function I need to end this statement before I can create this function so I forgot a semicolon. So if--and you make any syntax errors unfortunately PHP is not smart enough to tell you how to do the pset yet. But things like syntax errors, you're gonna get this nicer message perhaps than you used to seeing for make. Other questions? Yeah? 

>> How does it know to go to public.html like in your URL bar [inaudible]? 

>> So how does it know to get to public_html? So this is just a property of the server we're using, which is called Apache. And so when we're using Apache, Apache is gonna say, "Okay, I see this URL and I need to know somewhere on the server which can contain a bunch of other things that aren't necessarily displayed as a website, I need to know where to start looking." And we've just configured the appliance for you that says "I'm gonna start looking in any folder called public_html that's inside a home directory." You can change it if you want, but you certainly don't need to for this or anything else you'll do. So we've just kind of preset up for you to look inside of this public_html. Other questions? Yup? 

>> Is this webpage accessible if you close the appliance? 

>> Is this webpage accessible if you close the appliance? It's not. Because in order for something to send us this page it needs--something used to be running that's listening for these requests. So if we shut that down by closing the appliance, we can no longer get up this URL. 

>> But if we, you know, minimize or something like that, we're still okay. We don't need to have it in the foreground. It just needs to be running somewhere on your computer. Other questions? Alright, so that's a quick walkthrough of the distribution code and we'll see these functions as we go through all the things that you're gonna need to for the purposes of this pset. So again, login2.php or getting the username from that form and restored inside a post. And now what we need to do is look up the user from the database because we can't just allow anyone to login, right. We need to make sure that, A, the user exists, that they typed in the right password before we can actually log them in. So let's take a look at how we do that. So we understand this line, we're just getting from that form on the previous page. We're getting the value the user typed in. So now, we have what's called a SQL query. So this says, "From my database, where a database is just some collection of tables, where tables have columns and rows and that's how you gonna represent all of your values from that database, I wanna look for someone who's username matches the username that I typed in." So were gonna say, "Okay, I wanna get that result. And if there is one person whose username matches because we can't have two people at the username DJM or we're gonna confuse David. So if there's exactly one person and this username exists, then that's when we wanna check what their password is." So now this is where we're comparing passwords. And so this is--so this is a much more secure approach than we've used years previous but what we're saying is, "I'm gonna call this function crypt." And what crypt does is it's going to take your password and encrypt it." So when we create a new user, they're not--we're not storing their actual password in a database. That will really be insecure because if you're like me and had a bunch of people registered for your site in the CS50 fair and they typed in like what the password they used for everything, you now know what their password is. And from our perspective that's really, really insecure because even though that's cool and you can login to their Gmail now you shouldn't actually have the right to know their password. So for security features, for security purposes, we're going to encrypt that. So when we store the password in the database, we're just gonna see some long--what looks like meaningless string of characters that has what we--what we perceive has no relation to their actual password. And so in order to convert this plain text password to this encrypted password, it's gonna be an algorithm that's a little more sophisticated than Visionaire but in order to invoke it, we don't need to know how it works we just need to know that this magical function crypt is going to do it for us. And so now, if we encrypt the password that was typed in and that matches the encrypted password in our database that must mean that the user typed in the right password. Because if they typed in a different password, that's going to hash to something else, which means that these two passwords don't match. Does that make sense? So here, we're effectively comparing what they typed in to what we remembered as their password, but we don't need to know what their password actually is in order to that. So if this is true, if these two hashes match that means the user typed in the right password and at that point we can log them in because we know that those credentials they entered must be valid. So they must actually be the persons they're trying to login as. So what we wanna do is we wanna remember who they are inside of our session. So remember that because we included common.php at the top here and that called that function session_start, that means that were now green light to use this magic session variable. So as we go through our different pages, we're going to store inside it--we're gonna able to use what's inside of this session variable. So this session is basically just an array. Again it's an associative array where instead of using numbers to access buckets, we access those buckets just with strings. So again just to be cache table. So inside of ID, this key ID, we're going to store the user's ID. So every user is gonna have some unique ID and it's probably just gonna be a number the way we've set it up now. So if we take a look at our database, and to do that, I just want to, again, that same IP address and I want to /phpmyadmin. So this is already installed in the appliance for you. If you go to this URL, you can login with jharvard crimson and take a look at the database that we set up for you. So here's what my database looks like. So you'll see that each of these users has unique ID, it's just a number and it's different for everybody. So here's our username and here's our encrypted password. So we don't actually get to see what David's password is, instead we just see this random string of characters that doesn't tell us really anything about his password. So when David logs in, we're comparing this string to the hash of whatever it is that David typed in. So it's this ID that because it's unique to every user, we can use it to identify which user we are. So if we just remember this ID number then we can remember through all of our pages what user is currently logged in and take action accordingly. That makes sense? How we're logging somebody in? Yup? 

>> Where is the crypt hash function stored? 

>> So where is the crypt hash function stored? So when we're just logging someone in, we don't actually need to store the value, we'll just need to compare it to what's already there. So if this row hash says the row of my table that I just selected--so if I--my username is Malan then I'm now looking at the row that starts with Malan. And if that has password value is equal to the hash of whatever the user typed in, then we're okay. So when we login, we don't actually store this anywhere. This already must exist in the database, right, because if it didn't, then we couldn't login because that user doesn't exist yet. So in this case, and this is gonna be different when registered users, but for just logging in, we assumed that it already exists so we don't need to store anywhere 'cause we're just reading from the database, we're not writing to it. That makes sense? 

>> Yeah. 

>> Other questions? Yup? 

>> Where is the crypt function included? 

>> So where is the crypt function included? So just like things like print F and other stuff like that, this is just part of PHP. So this is written for you and it's just automatically included. And PHP has a lot of functions like a lot, like as soon as someone thought a function was a good idea, they just wrote it and duct tape it. So there's probably a function for anything you can possibly think of in PHP, which is fantastic. Other questions? Okay. So that's our logging someone in, to log someone in just means I'm gonna remember who you are and as you visit all these other pages I know you passed my authentication somehow, so you must be the person that I'm remembering right now. So now let's dissect this select query a little bit more. So SQL says for structured query language and it's not really a programming language as much as a way of describing how to retrieve data from a database. So the query we saw before was some select from users where username equals and we use a dollar sign username. So we don't just have to have a constant here, but if we say dollar sign username, then PHP is smart enough to say, "Okay, well, there's some variable username, I wanna use that, instead of the word username." So maybe that's why they decided variables just start the dollar sign. So this first part is gonna be my verb, what am I doing from the database? Well, I'm selecting data. What data do I wanna select? Well, remember the star means everything, so this says, "I wanna get every single column from the row that I'm selecting." So our database can have multiple tables so these from users that says that there's some table called users and I wanna get data from that. So I can have a bunch of tables and each of those tables must have a unique name so we know where to grab data from. Now this "where" is the condition that must be met in order for the row to be returned. So we're saying, "I want every single row where the username is Malan." Now for the user's table that's only gonna be one row. But it can be the case that this be returned more than one row. So if we said, "Well, I wanna get everything from users where either I'm logged in as David or that person has less than 9,000 dollars." So this will return an array of rows not just necessarily 1. So if we don't wanna select every single column, we can actually list out the column names we wanna select. So we can say I wanna select the username in the hash, I don't really care about the ID, this is actually gonna be faster as far as [inaudible] is concerned 'cause we don't need to read every single column. So I just want these two things, again, I've [inaudible] users but this time instead of equal to Malan, I'm saying not equal to Malan. Make sense? And finally, if we don't wanna return the rows in cells but instead want to return how many rows were returned, I can say something like "Select Count." This count is gonna say, "Well, I don't actually want the column data, I just want how many of those--how many of the rows there are." So if we wanted to know inside of CS50 Finance, how many users right now have cash that's greater than 9,000? We could run some query like this. So sites like Facebook who say, you know, we have 750 million users, they probably just--well, not really, but they probably just said like a select count from users that said, well, how many rows are inside of my database? So now I can write Facebook. So that's the SQL. But SQL is not a programming language. PHP is our programing language that's going to utilize SQL. So here are one, two or three, six-step process for using it. So the first thing we need to do is to connect to our database. So this is where those constants come in that we saw on constants.php. We basically need to login to our database from PHP. So this is just another security measure, right. If we have some password in our database that's separate from the password on our server, that means that if our server gets hacked, that means that whoever hacked us might not necessarily have all of our database information which is really good if your database has like credit card numbers which hopefully your final project doesn't. So we're gonna connect to the database with this username and password that we specified and after we connect we're going to select a database. 

>> So a database can have multiple tables but we can also have multiple databases that contain multiple tables. So the purpose of this pset, you're just working with one single database called pset7. And everything inside the database pset7 has all the tables that you need for this entire pset. So this DB_NAME is just a constant. It's never gonna change for the purposes of this pset. So we're just gonna use pset7. So now we can start running these queries. So we just saw an example of the select queries. So if I say "SELECT star FROM users," that means I'm going to get my entire user's table. I want every column and I have no condition so this is gonna return every row. So it's not gonna actually execute that statement yet because we've just created the string. In order to actually run that query we wanna say my SQL query and then pass in whatever our query was. So because we stored this in a variable called dollar sign query, that's how it knows what query to run. So it's going to return this result object and this result object contains all of the rows that returned by this query. So to iterate through those rows, you can say something like mysql_fetch_array from a result. So this is very similar to using this wild [inaudible] kind of thing. We're gonna have just like inside of our file some cursor inside of our result object. So if I called mysql_fetch_array, the first time I call that, I'm gonna get back the first row that was returned from my query. It's gonna be stored inside of this PHP array row. So from that array I can say something like row zero or row username. And if I call mysql_fetch_array again, it's going to give the next row. So you can kind of see how it can iterate through all the results of our query. We can just say "well, mysql_fetch_array returned something, that means I must have another row to go through and iterate through it--you know, whatever it is that we need to do, print out the values or something like that." So questions on the structure? Yup? 

>> Do you ever have to say that something is SQL and--but then brackets are not? 

>>So do we have the same brackets for SQL? We don't actually we can just say that this entire string that you're passing to mysql query that entire thing is just a SQL thing. And so if you have a syntax error in your SQL or that query failed for some reason, then this function mysql_query is gonna return null. So that's a good if you're concerned about error checking in which you are, that's a good thing to check. What do this function actually returned, did it worked, did I have a syntax error, or something like that. Yup? 

>> You said that mysql_fetch_array [inaudible]? 

>> Yes, mysql_fetch_array will get one row at a time just like something like fgets would get one line at a time from a string. 

>> So what kind of index can you use to [inaudible]? 

>> So what kind of index can you use to kinda jump to a row? So this you can't really just jump to, say, the third row. There's kind of almost no need to do that really just like if you can think of about a problem that would need to jump like the third row to solve, doesn't really exist because you could probably just refine your SQL query to only get that row or something. So the most common thing that we're gonna do is either just get the single row, in much case we're just gonna call it once, or we need to iterate through some set of rows. And to do that, we're just gonna need to put that in to the loop. So because those are the two most common things you're gonna need to do with the mysql result, that's just the way this function was designed. Other questions on using SQL? Okay. So let's jump in to the five parts of this pset. So the first thing we need to do is allow our users to register. So to do that, we're gonna need to create some new page that's equivalent to login.php that displays a form to the user that allows them to choose a username and password. Now we need--from that form we need to pass it to some other file that's gonna do something like make sure our passwords match 'cause we're asking you to say type in the password, confirm your password. So make sure that those are valid, make sure the username doesn't already exist and that if all those [inaudible] check passed, then we actually need to create a new user. So to display a new form, if you like to practice your HTML then you can just start off with the new blank PHP file and start writing but if you're pressed for time like I'm sure you are then you probably just wanna start off with the copying login.php into register.php. And so now we just have this really nice framework we can start operating with because we only need to change a couple of things. The first thing we need to change is going to be this action, so where it send to. So no longer do we wanna send the data to login2.php, because login2 is gonna login the user and we're trying to register a user. Instead, we wanna send it to some file that we can call register2.php. So after we change that attribute, we also need to create a new password field. So just like we saw those input name equals username you just need to create a new one that has a name like password two or confirmed password or something like that. So that when the user enters data into that it gets passed along to this register2.php file. So that's pretty straightforward. Any questions on the HTML part of this? Okay. So now for the server side logic. So the first that you need to do is make sure that nothing is blank, because if the user typed in something blank then we can't allow them to register 'cause that's gonna be an error. So assuming all these things are not blank, we now just need to make sure that the password and the confirmed password match. In this--And at this point, this might be your favorite part of PHP because at long last you can just say if A equals B, and A and B are strings, this will actually work 'cause you no longer have to worry about memory addresses and pointers and whatever that was any way. So the first time you type this you should just be so excited that you can finally do this. So we just need to compare password and password2. If they're the same, we can keep going. If they're not the same, then we can invoke this apologize function that is especially written to display some error message. So we can just say, "apologize your passwords didn't match" or whatever it is that [inaudible] solution says. So questions on that bit? So similarly, we wanna make sure that what they've entered is a valid username. So if you set your database up the way the pset suggests with the unique key on username then SQL is gonna say, "Okay, everything inside of this username column needs to be unique. So if you try to insert something with the same username I'm going to fail. So if the query fails, we know that, well, mysql_query is gonna return null. So in the event that this query fails that might mean that they tried to do--the user tried to insert the same username as something that already existed. So if this query fails for this reason or some other reason, we just wanna apologize again saying there's some error like maybe you typed in a duplicate username. Make sense? Yup? 

[ Inaudible Remark ] 

>> Sure. So I was saying here is that mysql_query is gonna return null if you try to insert a non-unique value into a column that you've said this must be unique. My SQL is smart enough to do that for you. If you'd like you could also select it yourself and look for the username, if you don't find it, then insert it. But this unique key is kind of a nice shortcut way of doing that. So now, that we've done all of our error checking, we actually want to add our new user to the table. So we have a new SQL query, a new verb, and now we're going to say "insert." So the insert statement is going to create a new row inside of our database. So insert into the user's table and that user's table I wanna modify these columns. So I wanna give this user a username on some hash which is their password and some cash. So we're starting off with some default value for cash. So those are the columns I wanna modify. What I wanna insert into those columns I wanna say "values," and then this is a list of the values I wanna insert. So this Tommy corresponds to the username. This supersecret password corresponds to the hash of my password. And then finally the cash corresponds to that column. So you notice that because these are--the first two values are strings, I've enclosed them in quotes. But this last value is equivalent of a float or in SQL it's called the decimal, so I don't need to use quotes because it's just a number. So when I actually say "mysql_query" and I add this it's going to go into the database and modify it. So after you visit that PHP file from your browser that makes this call and go back into PHP myadmin and refresh, you're going to have a different table, hopefully, than you did before. Make sense? Alright, so remember that we're using the hash of the password and not the password itself. So we don't just wanna insert password, we wanna insert crypt of the password. So this function crypt, remember, is what's going to return the hash value, we want to insert that, not the password itself because login is looking at the hash. If you do insert the password itself then you're not gonna be able to login even if it's the right password. Make sense? Okay. And so after the user registers, we wanna be able for them to be logged in. And so to log them in, remember, it's really simple. Login the user, just corresponds to changing this session super global. So if we just change that variable that's equivalent to logging the user in. So any questions on registration? Alright, so now we need to worry about these stock quotes. So this stock information is coming from Yahoo Finance. But luckily, we don't really need to worry about how that data is actually being retrieved. So the first thing you need to do again is create some new HTML file that contains a form that's gonna send data to some other file. So following the convention that David setup, we're gonna say "quote. php is gonna display the form and quote2.php is where the data is going to be sent to." So we know how to do that. And our form this time only needs a single row. We're just looking up a single stock symbol. 

>> So we're just gonna send that data this time to quote2.php. Same thing that we're used to, we know how to do that. So now, one of these functions defined inside of helpers is this function called "lookup." And what this function is going to do for you is hit the Yahoo Finance API, it's gonna ask all the information about whatever stock symbol you pass it. So this data is going to be returned into a stock object. So as far as PHP is concerned, we have things that are a little more powerful than structs called objects. But for this pset, this S thing, the stock thing is really just a struct. So remember a struct just contain multiple members and member to be different types, whatever. So just like in C we have some struct, we wanna get at something inside of it we're gonna use this arrow notation. So the thing--some of the things we were concerned about is the stock symbol, stock name, stock price. So from that variable we just need to display this information. So we see this a little bit earlier but we have a lot of options for displaying data 'cause again PHP is a function for everything. So what we can do is we can say something like prints, price, and then you notice this dot is the string concatenation operator. So if I have a string on the left and a string on the right, this is like saying add those two strings together, combine them into a single string. So if I print out the concatenation of the string price and then whatever value s that price is I'm just gonna get a single string that's displayed out. If you really like typing percent D then printf is still available to you. And finally, if you want to instead of concatenating or create some string that PHP is gonna say, "Okay, I'm gonna jump in to this string and I'm gonna substitute whatever is inside of these brackets for the code that's inside of it." So this code here is actually going to be executed." PHP is gonna say, I want you to look at S price and this is going to effectively return into the string whatever S price was. So this notation is just a little bit cleaner than using the dot but it just says, "Well, everything inside of these two braces just actually run that code and returned that value." Make sense? So questions on actually displaying the stock information or how are we gonna get that information? Alright. Next we're gonna worry about buying stocks. So in order to buy stocks, we need to do a little bit of change in the design of our application. So right now we just have a single table called users. So inside of that user's table, we have a username, a password we have how much money they have. So what we can't really do is have that table keep track of what stocks you own, right. Having another column that's like a comma separated list of stocks is pretty inelegant 'cause we need to keep track of how many stocks they have and that's just kind of lame. So the right way to do this is to create a new table that's dedicated just to portfolios. So inside of that table, we probably wanna keep track of some the ID of the user, we wanna keep track of what stock they bought, so the symbol that corresponds to it or maybe the name of the stock. And finally how many shares that were purchased. So if our first SQL table have those columns, we're gonna create some new tables maybe called portfolio or whatever it is you wanna call it and these are gonna be the columns of that table. So here's just a diagram of what this looks like. So if I'm a user and I buy a new stock then I'm gonna create a new--maybe a new row inside of my portfolio's table. Now the ID of this row can correspond to the ID of the user that purchased the stock. So in this case, this ID is not unique, right. Because as one user I have an ID like seven and if I own like 10 stocks I'm gonna have 10 rows inside of this portfolio's table. So that's how we're kind of connecting these two things. We're associating a row in the portfolio with the user. And the way we do that is just have some column that's gonna be the same value in both of the tables. So then we can just store the symbol as a string and the number of shares that I bought as an integer. Questions on the table structure? Alright. So HTML forms are so fun 'cause we love making them. So now again, we need a symbol, a form that's gonna ask you for what symbol you wanna buy. So if you look at the staff solution which is at cs50.net/finance, I'm gonna login as--yes. So now, if we do something like Get Quote, so Get Quote, and here's our form that's gonna go to quote2, we have this link that say's perhaps you like to buy it. Now if I click buy, you'll notice that this AAPL was pre populated for me, right. In the way that came from was I said well, I wanna quote for apple. And when I said buy, our handy-dandy staff solution was smart enough to recognize that you don't have to type in AAPL again, I'm just gonna type it in for you. So if you look at the URL of this page, you might notice that I'm in the wrong tab--that we're still at buy.php but after that we're saying symbol equals AAPL. So that looks kind of fishy. So let's change the symbol to something like GOOG. You'll notice that I've just by changing the URL I've changed what's inside of this text box. So this is using a different super global this time called "GET." So POST is populated from any form that has a method of POST. So as a corollary to POST we also have this global called GET. So GET is gonna be populated from any form that has a method equal to GET or from the URL. So if you submit a form in which you've said method get, that form is actually going to inside of the URL whatever values you've typed in to the form. So the way it's gonna parsed is by saying, we're gonna have a list of key value pairs. So I'm gonna say key1 is gonna be some value but we're gonna have the ampersand that's gonna separate this key value pairs then I can have another one, and so I can go on and on and on. So if I say get any one of these keys I can get the value that was passed in from the URL. Make sense? So now how did we prepopulate this data? Well, if we go back into our login, so there's an attribute of this input element called value. So if I say value equals Tommy and save this form. So let's go back to the web browser this is my implementation. We're gonna go back to login.php pset7. So now you'll notice that Tommy was prepopulated. Now remember that we don't necessarily have to have all of our PHP at the top of our page. So let's--instead of saying Tommy, let's say something like equals--no, let's say Tommy 'cause [inaudible]. So now, we have some PHP code. So you notice that this is definitely PHP because we have inside of it some bracket question mark and so now we're evaluating this variable. So if I go back into my web browser, this is gonna be the same thing. Well, out of practice, killed me. So it's gonna be the same thing. So we've evaluated this PHP code and we've put it into our HTML. So this construction looks a little weird, right, because we're evaluating PHP in middle of the double quote but that's totally okay. Because as the server is evaluating this PHP file, it's gonna look for everywhere it sees any of these bracket question marks whether they be either HTML or your JavaScript or anywhere, and it's going to evaluate them. So after the server runs this PHP file, it's gonna get some static HTML file where this PHP is going to have been replaced by with whatever it evaluated to. Make sense? Yeah. Alright, so now we need to worry about actually adding the stock that we just bought to our portfolio. So again, don't forget that we're checking, make sure that we're buying, you know, some valid number of stocks, make sure that we're actually buying a stock that exists. So don't forget about all of our checking. So now if have some valid stock, we wanna make sure that the user can afford it. And so that the user affording it means that the amount of cash that they currently have is more than the cost of buying that many shares of stock. So the way we could do that, we can get the amount of cash the user has, we can say, "Well, I wanna select just the cash, I don't really care about my password from the user's table where this ID is equal to whatever the ID of the currently logged in user is." So remember that's just stored in our session. And so we wanna look at session to determine what value we wanna use for our ID in our query. So now what if I'm a user and I buy Apple and I decided, "Well, I wanna buy Apple again." There're a couple of things I can do. So the first is I can say, "Well, if I buy Apple twice then I'm gonna have two rows in my portfolio table that say Apple." And when you display a portfolio you just wanna make sure that you combine those two rows. So inside of my portfolio I don't see Apple twice. I see the sum of all my Apple quotes. Or we can use this other SQL verb called update. So update, unlike insert, is going to only operate on an existing row. So we're gonna say update the user's table and I wanna set the column username equal to n00b wherever, inside of the same row, the username column already has a value Malan. 

>> So what this says is throw out my table inside of a row, every row that used to have a username of Malan now has a username of n00b. So these two column names don't necessarily have to match. I could change Malan's cash by saying, "Set cash equals 0 where username equals Malan." So after that, that's one way of inserting--of updating the amount of stocks you have. So, the pset mentions it even fancier way that says, "insert" and then "on duplicate key" or something like, "update values." And so that's just a way of invoking this update verb in the event that the stock already exists. So all three of those approaches are totally valid, some a little more straightforward than others. Questions on SQL updates? Yup> 

[ Inaudible Remark ] 

>> Yeah. 

[ Inaudible Remark ] 

>> So how would you actually type it? So this is going to be a string that we're gonna wanna pass to mysql_query. So inside of our PHP file, we wanna have some string that looks like this, except you wanna substitute into that string probably some--the current username or whatever it is we're changing. So we can substitute the ID to whatever our current logged in ID is or whatever it is we're doing with our query. We don't necessarily have to hard code in those values. So from that string, you just wanna call the function mysql_query on that string and that's how this is gonna gets executed. Other questions? Okay. So finally, we just want to subtract the amount of cash. So if I just bought a bunch of stocks and that price total to a hundred dollars, then we wanna make sure that I now subtract a hundred dollars from the amount of money that that user has. So now unlike--so our cash is not stored in the portfolio table anymore but we're back into the user's table. And so again, we wanna use an update statement. Now, what's cool is we can actually say, "We don't need two separate queries to say, select the amount of cash, get that value, and then update it and use that value." You can actually just say, "Set cash equals cash minus" and then however much the stocks cost. So this is just a handy way of not having to query twice, we can just say "cash." That's gonna be the current value of the cash column for the row in which user name equals Malan. So remember that the price of the stock which we need to calculate the number of shares times the price of one share, that's coming from that lookup function. So before you buy stock, we're gonna call Lookup on the symbol. If we get back a valid stock object, then we can determine how much it cost based on the price attribute of that object. Make sense? So finally, selling stocks is gonna be very similar to buying stocks 'cause again, we just need to operate on a portfolio and then corresponding row in the user's table. So, in order to allow the user to remove stocks, we need to display some list of what stocks they currently own so they can select a stock to delete. So in the staff solution, this is kind of a dropdown menu. And how we got that was we wanna say, "Select star" from portfolio where user name equals Malan. So when you say, "I wanna get every stock that this one user owns." So from those stocks, I can choose to delete all of the stocks that that user owns or sell them. So to remove from the portfolio, we could just say something like--delete, a new SQL verb--"Delete from portfolio where username equals Malan and symbol equals Apple." So now this is saying every Apple stock that Malan has is now deleted. Pretty straightforward, any questions? Alright. So after we've removed the stock from the portfolio, we need to update the amount of cash that that user has. So one thing that I really don't understand about this pset when I did it is that when you sell the stock, it sells for the current price of the stock, not for the price it was when you bought it or else you wouldn't actually ever make any money. So I totally do not understand the stock market when I took this. So when you sell the cash--I mean when you sell stocks, this new cash value is going to come from the current price of the stock which you can get from the lookup function, and just like before, we said "cash minus something" we can just say, "cash equals cash plus something." We just need to give that user back the amount of money that the stock sold for. Make sense? So that's it for selling stocks. And so finally, the pset mentions that we need to keep track of our transaction history. And so, if you look at the staff implementation, you can see the time at which you bought the stock. So if I come over here back to portfolio to history, you can see my entire history and you can see both the date and the time at which I performed each action. So you notice here that I bought 12 shares of A and how much that symbol went for. So I bought 12 shares of A and then I sold and bought some INTC. So if I go back to my portfolio, I only have this A. But the history still needs to keep track. So even if we delete this from the portfolio like it's gone now from that table, the history needs to remember that you had that stock at one point. So one way of doing that is to create another table and call this "Table History." And anytime you do anything whether it'd be buy or sell a stock, that's gonna correspond to a new row inside of that table. So when you sell a stock, you're never deleting any rows. You're gonna create a new row that says, "You sold the stock." So we're never actually losing any information but we have another table that's dedicated to only remembering the information--to only remembering information. Where the portfolio is gonna have rows coming and going as you buy and sell stocks. The history is just going to be a big log, you're only adding rows. So another approach could be, well, I have inside of my portfolio, I'm gonna add another column and that column is either true or false depending upon if I still own that stock. So if I sold all my Apple stocks instead of deleting them, I'm just gonna set all those rows to false. So when I look at my portfolio, I'm gonna get every row that has a value of true, and when I look at my history, I'm gonna get every row that a value of true or false and display bought or sold based on what that column is. So, just two different approaches, there are bunch of other ways you can do this. These are just two ways that make sense to me. And so additionally, in order to keep track of the timestamp at which the action occurred, SQL has a function NOW that's going to return the current time stamp. So if you could say something like insert into history, timestamp, and then just now. You can also set a default value for the column so you can say whenever a new row is inserted, I want this column to have a default value of CURRENT_TIMESTAMP. Then, you don't have to worry at all about actually setting the timestamp yourself. SQL is just gonna say, "Okay, well, because this has a default value of the current time stamp and you didn't tell me what this column should be, I'm just gonna make it the current time." So finally, one more option is PHP's date function which is really, really handy. You can just pass in some generated string of the current date. Any questions on keeping track of history? So finally, just some extra features, you need to pick one of these features and implement it. Allow the users to change their passwords, email receipts of stocks. Every time I buy something I get an email sent to me, deposit additional money so allow the user to increment their cash without selling stocks. And then another one that was just added was allow the user to reset their password. So that last one being kind of difficult because it's an interesting security challenge whereas something like allowing the user change their password, emailing receipts is a little bit on the simpler side. So you're certainly free to implement all of them as long as you implement at least one of them. So any questions on pset7 at all? Yup? So what's the difference between changing your password and resetting your password? 

>> Sure. So what's the difference between changing your password and resetting your password? So this is a clarification that was literally just made on the spec. But changing your password is for I'm a user and I'm currently logged in and I wanna be able to change my password. Where resetting is like I'm logged out and I forgot what my password is so you need to devise some secure mechanism of allowing user to like click some link and be taken to a page where their password can be reset. So that's definitely the hardest one of these four 'cause it's an interesting challenge. Well, how do I give a user a link that I find someone else and I can't just guess a link and change someone else's password? Other questions? If not, then good luck on pset7. 

[ Noise ]