[ Silence ] All right. Welcome back this is Computer Science S75. I'm Chris Gerber. And tonight I'll be covering Section 5. We're going to talk about PDO, some more sql commands, talk to you about join, index and commit and well get into a little bit of more of the detail around project one. Hopefully that everyone is doing well with project zero, you're moving along, you're ready to turn things in Wednesday. We'll give you the instructions Tuesday night how to actually proceed of that. Well, let's get started. To kick things off, I'd actually like to do a little refresher about how we have been working. So, let me bring up bit of code. All right, so what we're going to look at first is just a little bit of how we have been working with databases in php. So you'll see, this is the model that I'm been working on as I begin to work on my implementation of project one. I've got a few definitions upfront just to make things easy. Ans so, I have a single plate were I can configure things. I've define my host, my user, my password and my database all with the defined commands. So I've basically set up, it's not quite available-- a variable, it's actually a constant, so I can use this later in my code. So, if down the road I actually changed the account name or the password, I'm just changing in one spot. So, let's move a little further down the code. Yeah. So, this is basically the approach we were taking before. We've got-- We could skip the first few lines but we set up a connection, we used Mysql connect. I had host name, user name and password. We would then select the database with Mysql select DB. And then, we were using things like, S print F to take a query, fill in the blanks automatically and build up the statements so that we could then do, for example in that Mysql query to actually get back some data. So, this is where we were just a few days ago. This is all basically seemed familiar so far? All right, so looking at this rough idea, what can tell me-- who can tell me what's potentially good or bad about what I've done so far here? So, anything that jumps out that seems promising, disappointing. Sure. 

One thing that's good is that you've encrypted your passwords with the SHA1 in the database. And yeah. 

Yup. So, the first thing pointed out was that we actually encrypted the password, we're using a password hash, were using SHA1. So, that was something that had come up in lecture and certainly that's one of the things I want incorporate, so, that's a great one. What else did people noticed from my code so far? [ Pause ] All right. So, one thing I might throw out there, I've got this function log in user and I'm connecting to the database and selecting the database, and that's probably something I'm going to do a lot. So, that's something where I might want to consider, does it make sense to put it in this individual functions and repeat that code over and over? Or do maybe want to factor that out to some higher point in the code? So, that's something to consider. Anything else? I'll point out that we also use the Mysql escapes string on the email because I didn't really know where that was coming from and I just want to make sure its safe when it's coming in. All right, and then one of the other things is something that David just started talked about tonight. When we start looking at this code, I've got all this Mysql specific commands. So, What's the problem with this Mysql specific commands? Yep 

Well, we did mention that if you might read your database from another system, like get another new database or something, it would be beneficial to use something that for some humbled multiple databases, so you just might as well. 

Right, so the answer was and it's great answer. If in the future I want to use something other than Mysql, I've pretty much locked my self in. So, that's why we're going to start talking about PDO tonight. The other thing I'd like you to keep in mind as we look at this is that S print F command where I've basically build this query once. I take the parameters and I build a unique string and I'm going to pass that one string into the database. So, let's switch over to the slides. So, actually I have one other thing I want to talk about related to this. We've talked about indexes. You know, one of the key reason we use indexes are to improve performance at the cost of using some additional storage. So, just as a refresher, we've got the create index command, you can specify that they're unique, specify the table and the columns that are going to be used. When we look at our code, we've talked about this in the example before, we've got the email and the password, and were going to be querying this over and over. But really if you start looking at the statement I build up here, it's probably more likely that ID is actually the field that I've set up as a primary key. The fact that we're going to continually query against email and password, it actually implies to me that I might want to create an index that uses both those fields so that every time I'm authenticating someone against the database, that performance is going to be improved. So, something to consider as you're working through your project. All right, so I want to jump over and talk about PDO. We want to start leaving behind this idea of this Mysql statement, it's not that they're bad but we can do better. So PDO is PHP data objects, it provides a data access obstruction layer but it does not provide a database obstruction layer. So basically the difference there is, we don't have to learn a new set of PHP commands to talk to our databases. No matter what we change on the back end, if it's Mysql, oracle whatever, were still using the same PHP commands. But whatever we pass in through the PHP commands, a select statement, an insert, an update, a delete, those commands are not going to get translated. So, if your using a specific set of sql commands that are specific to your database, those do not change. So, if you start using custom functionality, for example, if I was going to do a create table through PHP and I'm going to use Mysql, we talked tonight about using NODB, it could be one of your database options. Oracle is not going to know anything about that. So, if I remap the commands and use oracle as the back end, the PHP part will stay the same, those commands will be the same. But the actual create table command will have to be edited because this function or the PDO doesn't know how to automatically make those translations. One of the other nice things about PDO, we mentioned some of the different drivers that are available, some of platforms out there are Informix, MS SQL, MySQL, Oracle, SQLite. So certainly plenty of options which is really nice as well. All right. So, let's actually see what some of these starts to look like. Get out of my code. [ Pause ] So, I created a very simple example, basic idea of a select statement using PDO. So, we can start. I have to sort of spelled it out on one big command to get started, but I'm creating a database handle, dollar sign DVH, so, starting here, equals new PDO. So, I'm actually creating an object and it's a PDO object. And I'm passing in some parameters. The first parameter is the DSN, which I don't of the top of my head remember what the acronym is for. But basically what it tells it is, the type of database you're connecting to, the host that it's on, and which database in that host you're going to use. So, I've specified Mysql for the database type, I've specified local host as my host, and I've specified the database name of jharvard_section5 which should feel familiar from all the work we've seen in PHP, Mysql so forth-- PHP in my app. Then the next two parameters are the user and the password. And we've certainly seen jharvard as the user and crimson is the password plenty of times this semester. The next thing I'm going to do is just a simple query, I take the database handle and I tell I'm going to do a query. And I've got a very simple query statement here, select star from the students. We all know what that does straightforward. I'm going to take the results and put it into a variable called $students. As you might guess, this is going to be an array. So I can, as we go to the bottom of the screen here, use a four each to iterate across that, taking each element out of students and calling it a student. And then, I can print the various fields. So, one thing you'll see here is I suddenly have ID first and last available, and what's happening is PDO is actually looking at the column names in the table and making those the keys for my associative array so that I can pull the values out. So, student is the row, id is the column, it's mapped at into an associative ray of element and we'll automatically pull out the value. And then when we're done, just for good measure of set, the database handled to null which essentially close is the connection. So, we can actually see what this does. [ Pause ] Before I actually kick off this command, one thing I want to point out is for the bulk of this course, we've actually been running PHP through the web server, but PHP is just a standard scripting language. When you execute it, you can specify that the output is going out through, for example, through apache out through the web server, but you can actually run this commands from the command line as well and just see the output. So I've actually, I'm going to do that right here. And you can see that it's going into my database, previously, set up a small table. I've got a column ID, it's the primary key. It's just in 1, 2, 3, auto in command data. I've got a first name and a last name. And it's pulled out to those records and printed in the screen for me. So, just as a very quick overview of PDO, does that make basically make sense so far? OK, great. [ Pause ] So, as we start looking into PDO, you'll see a few standard things, we've got the dsn which we mentioned and the dbh, database handle that we mention. There are basically five statements that are sort of my wheelhouse when I'm using PDO, query executes the statement and return the result set, that's what we just saw. Exec executes the statement. But instead of returning the results, it just tells you the number of rows that were impacted. So, if you were doing an update operation and you expected three rows to be updated, you could determine if that actually happened if for example zero were updated, you could start to get a sense as to was the command successful based on what you expected the account to be. Prepare, bindValue and execute, get a little more interesting. So what prepared us is it let's us pre-prepare a statement for execution on the database. So, that actually gives us several advantages. First of all, it's letting the database know up front. We're going to do this work and let it do a bulk of the work up front to get ready. It knows what tables it's going to need. It knows basically the approach that it's going to take query the data. And then, we can specify parameters and we can use those parameters to then tweak the query, so we might be selecting with the parameter of ID equals fill in the blank, and then I can say, "Run this query with a 1. Run this query with a 2. Run this query with a 3." And the database doesn't have to go through the full process of building the instructions each time, it just has to rerun the same query with the new value. To do that, we're going to use the bind value command which takes one of the parameters and inserts the value that we want to use into it. And then, once we've prepared it and bound it, we actually use execute as to oppose to exec, execute spelled out to actually run the statement. So, why do we do this? Basically, there two really good reasons, performance first off, the preparations perform once, so the database can do that work and it doesn't have to repeat it every time we're going to execute the command. And then, you know, parameters can have new values bound at ease executions well which is great. The other advantage is security. As David mentioned earlier tonight, by using this parameters, we actually get the work of escaping the parameters done for us up front. So, we don't have to remember to do Mysql, real, prepare, whatever the whole statement was. You don't have to remember that even. You just have to know that as long as you're using these parameters it's going to take care that for you. [ Pause ] So, let's see what that looks like. [ Pause ] So, I'd shown you my table of students with my self and David and Ellen. I've also got a table of grades that I had pre-created. And what I want to do is fill in a number of values. So, I've created this array that has the data that I want to load into the database. The first value is the ID number of the student, the second value is the project number they were working on, and the third value is the grade that they got on the project. And I just want to bulk load this data into the database. You can see I've set up my connection to the database as the last line here. Well, let's see how we might actually go through this process using parameters. All right, just to make it clearer for my self, I started off by deleting from grades. So, anything that wasn't there, I'm getting rid off. Next, I can prepare this statement. We want to insert into grades, grades was the table, the columns are going to be student, project and grade, and we want to fill in values. And you can see what I've done here is I've got :student, :project, and :grade. So basically, what I've done is name the parameters. These are the blanks that I want to fill in and these are the names I want to use to fill them in with. Now, you could actually use question mark for each one and then just remember the position of each question mark. But I find using the full name as actually little more clear. Then, I've created a loop here to insert all this grades. At each point, I'm going to bind a value to the appropriate parameter and then I can execute the query. So the first time through loop, I take those first elements of the array and I bind them to student, project, and grade and then I can execute the command and loop around to the next item. The other thing that's interesting here is this last set of parameters. Now, what does that look like to folks? Sure. 

Integer and strings. 

Exactly. So, what I can do is I can actually-- sorry, it was integers and strings. What I can actually do is tell the database in advance what types of fields this are as well which will help it with the process of making sure that the data that's being bound in is appropriate for the fields, so that I don't try to take a string and cram it into an integer or it will know that it actually has to do formatting appropriately to make that happen. Those parameter types are actually optional, but I certainly recommend it as a-- if nothing else, good clear it to yourself as to what you're expecting to be happening. And at the very end of this example, I just do another query of the database, the same code as before just so we can display the results. I'm using the grades table instead of the students table but the concept is the same. So, let's see how this actually works. [ Pause ] So you see it's gone through and it's entered the data from that array directly into the table of query to back out, relatively straightforward. So, you can select a series of columns from a table name, there's a number of options here and I'll come back to that, joined to a second table name based on some conditions. So we've got left, right, outer, inner, what does all this mean? Basically, we have inner joins where we have left or right outer joins, there's also full outer joins which we'll hint at but we won't go into a lot of details there. So, let's see one of this. So, starting with inner join, I've got two tables and this are the tables that we've been talking about. We've got students, Chris, David, Alain, we've got grades with the student number of project and grade. I moved the ID column in the first table to the right just so you can see that ID and student go together and you can sort of see how things might line up there. With an inner join, what we're going to see is a result like this. It's basically going to look for every place on the left where it can find an ID number that has a matching number on the right and display those records. So for an ID of one, it goes over. So, see a student is in the first and second rows and it brings that combination of data together, 1, 1, 1, 1. It goes on to number two, it finds the third and fourth row in the second table and brings those rows together. For Alain, his ID 3, there are no grade, so it doesn't bring anything back there. Likewise in the grades column, there's a student 4 who we haven't define anywhere and it doesn't bring that back either. The inner being, it's all the places where only there is a direct match. Code was, this is what it would look like. The table that I actually brought back was select star from students, join grades and students.id=grades.student. And we talked about that earlier where when you're using database columns but you have multiple tables to work with, as long as the fields are unique you don't necessarily have to specify the table name but it's good practice to always specify table name dot column name just to be explicit about where those columns are actually coming from. So, we can see this in code. [ Pause ] I'll skip that the part of the codes that's repetitive. And you can see I've plugged in exactly the sql statement that we just saw at the very top, select*, FROM students, JOIN grades, ON students.id=grades.student. And we can just go ahead and run. This is relatively straightforward here [ Pause ] And you'll see that we actually get the same data set, the one difference between the ID column is back in its first position rather than in the middle of the table. So, what about this case where the inner join isn't giving us all the data that we need? Now, we can start looking at left and right outer joins. [ Pause ] So the left join, we're starting with the same two tables of left, than physically in the same space. The students is the left table, grades is the right table. Now, what do you folks think the difference is here versus what we talked about with inner joins? OK. So, the difference is what we're saying when we specify a left join is that every record in the left table, whether it has a corresponding record in the right table, it needs to be displayed. So in the last example, Alain didn't get listed because he didn't have any grades. But now, we're saying that absolutely has to happen. So, that data set will come back looking like this. It will suddenly have this null fields because there is no record to go with it. Now, who can think of a situation where this might be useful? Sure 

In this very ideas right here where some person comes in and doesn't have all the information that needs to be displayed to see all the students. 

Exactly. So, as Wester [assumed spelling] said, in the case where we still need to see all the students, so if I have a class list and I need to see who hasn't been turning in their homework, I need to know who all my students are whether or not they've actually earned any grades. So, let's go head and jump over to the sql statement. Nearly the same as the last example, the one subtle difference is the addition of the word LEFT right before the word JOIN to explicitly say that, "Yes, we're doing this left outer join." As a side note, you could actually include the word outer but it's optional and I didn't feel like the extra type of thing. So, coming back to real code. [ Pause ] The same idea, we've just added the left to the sql statement. And as I prematurely ran it, you'll see that the actual output is just as we had predicted it would be. I think I'm actually going to for times sake, skip PDO5. As you might guess, PDO5 is the right join which is essentially the same as the left join except your saying that the right table is the dominant table. And no matter what you want to include the records from that table regardless of whether there's data that corresponds in the left table. So, I can actually run that just to demonstrate the output that we would expect there. Where we get this mystery student 4, we see their grades come through but we don't actually see any name information because they're not in our students table. [ Pause ] The other type of join that's out there is this full outer join. And basically the idea is, we're saying that both tables are equally important and we need every record from both tables no matter what. So, quite simply, we want this output which is the combination of both, we have the student that doesn't have any grades, we've got the grades that don't seem to have any student and we need all the data to come back. The reason I put a little asterisk on this one full outer join is it's not actually supported by Mysql. So, although it is an available option in certain database, is it's not one that we'll be working with. So, this is an example where if we're using PDO and we've, you know, we've got this standardize PHP statements now, we might be using a database at one point that supports a function, but then we switch to another database and that functionality is no longer available. So, something to be in the back of your mind when you do change databases, if you do change. All right. The next new sql feature that I wanted to talk about is commit. So basically the idea of commit or transactions in general, is that we want to specify that either all the commands need to happen or none of them should happen, these whole atomic operations. Specifically in Mysql, we need to specify InnoDB as our database type to get this functionality. But then, we can start doing commands like start transaction and then either commit or roll back. In everything that happens between those two points, it will happen as one complete operation. So, this is very rough code. This is not tested. This is just to give you an idea of what something might look like. So, we've got a foreach where we're looking at balances for user ID equals one, not my cleanest code. I wouldn't recommend putting this in production anywhere. But I'm taking the balance by getting the balance out of the row and I'm going to add a hundred to it. And then, I want to execute this, update the user, set the balance to this new value. Now, what's potentially bad about this? Sure. 

Same user goes and runs this twice and try and get their balance up to 200. 

Right, so the problem is the case where the user tries to run this twice in parallel. And then, you know, what's the right value? If I'm suppose to add a 100 in both cases, which update gets put in when? Do they only get $100? Do they end up with their full $200? We don't really know because it's-- there's an opportunity between each statement for execution to pause and move on to another process. So, what we wind up doing is using these commands. So because this is so well-known, this idea of transactions, PDO actually supports it native ways. So, it's got a begin transaction command that you can send to your database handle and a commit and also rollback as my guess commands that you can put in directly into your code. So, let's see that with some real code [ Pause ] All right. So one thing I-- just as a quick note, as I've packaged up this little function at the top here, just so I can run it multiple times, what it's going to do is show me the results or the current state of my grades table so that we can see how things are going along as make changes. So, I'll start off at the beginning. Before the update I'll just put the grades, so we have a base line. Then, I can begin a transaction and I can update the grades and I can say, for student 1, I'm going to bump up my grades to A minus, you know, I only had B and B+ before but, you know, I got the curves so I'm excited now I got my A minuses. And I'm going to commit those changes to the database. Then using my powers as a teaching fellow, I'm going to say that, you know, "I don't think David necessarily did as well as he thought he did. And I'm going to bring David Malon's [assumed spelling] grades down to an A minus from the As that he had previously." But I'm going to wrap that in a transaction as well. So as soon as I update the values, I'm going to query the database and see what things look like. But then, I'm going to have regrets. I'm going to actually roll back that transaction and then look at the database again. So, we've got three points. Before I do anything, after I've manipulated my grades but committed them, and then updated David's grades but not yet determined whether to commit a rollback in the very end of the application. So, let's see what happens there. [ Pause ] So, we've got a whole full screen of stuff here. But you can see before we started, the grades were, B, B+, A, A, C as we had see before. I had my two updates, the one that I committed for my self that updated the first two lines to be A-, and then the uncommitted or rolled back changes that updated lines three and four and left those at A-. But then I had my regrets, I rolled back and David was once again returned to his As. So, all this code is actually up on bit bucket for you, I'll make sure I get the URL out as part of getting it posted with our standard location in the section code. Yeah. So, any basic questions about the basic ideas of PDO so far? All right, great let's move on to the walkthrough. [ Pause ] So, our new project is CS 75 finance, I'm going to talked a little bit about the architecture, a little bit what you might think about for database design, show you a little bit about how we actually do this Yahoo stock quotes. And then, talk about other considerations that you might want to think about as you work on a project. Just as a-- yeah, Just as a quick over view of the Yahoo stock quotes, you can actually go out to Yahoo with this URLs going to quotes.csv and do query. So in this case, I'm searching for Goggle and Yahoo quotes with this little magical snl1. And I've broken that out, there's actually if you go to this gummy-stuff.org site, they've got must be about 30 different parameters that you can pass in here. S specifically says show the symbol, N says specifically show me the name, and L1 says show me the last trade for the stock. So, this query would actually bring back a CSV file that has that information for both Goggle and Yahoo. But let's with that background get on to the live demo. All right [ Pause ] So, let's start at the beginning. I'm sticking with this whole MVC concept in sort of expanding on what David had done. So I've got a dot HT access file that looks a little bit different than the one we had used before. But some basic ideas really haven't changed. I'm still turning the rewrite engine on. I'm specifically using a rewrite base of just slash now because I'm actually going to work in our project one directory as oppose to one of the MVC slash 7 slash, et cetera directories that David's been working with. The third line is just going to say if the URI is index.php, ignore it. Otherwise, I do this very right. For simplicity tonight, all you really need to know is I'm capturing two parameters that end up being called one and three, and its getting turned into index.php?page equals the first parameter and param equals the second parameter. So, if I have a URL that's project 1/home, it's going to go to index.php?page=home and param equals nothing. But if I want to a page that's project1/"/GOOG, I can say I go to the index.php?page="param=GOOG and then presumably actually get a stock quote for Goggle base on the URL. From dot HT access, we jump over to index.php, which is just taking this parameters and deciding what controller it's load. So, if the page is set, it's going to use that as the name of my controller and it's actually going to load that file, ultimately, through this require of statement. If it's not set, it's going to default to home as the page. So no matter what, people end up at my home page unless they know where they're going. Leave it to as an exercise to a sort through the rest of that, but it's basically just the dispatcher for MVC at this point. The other magical piece is actually code that I took pretty much straight from David. [ Pause ] And that's the surrender function that he had, which basically is for rendering my views. So, a controller can say render with this template and this data. The data is automatically expanded from an associative array into a series of actual variables using the extract command. And although my name is on the top of this, this really is essentially David's work, almost unchanged. [ Pause ] So, given all of that, I've come to this MVC model again. This is my controller for home. This is the base page that I go to. It includes that helper functions, so it knows how to actually render views after work. And basically, all its going to do is look at my current session, see if a user ID has been set and then decide which view does the user get to see. Do they get to see the home page or do they actually have to go to a log in page? And you can see where we're actually getting to the point where using this model view control or architecture, potentially, things are very straightforward to look at. I've got all about five functioning lines of code that handles the entire controller for this page. All right. And let's see what some of those view components look like so we can put the pieces together. [ Pause ] So, one of the ones that we just saw referred to as this login.php, and this is where things are actually getting a little more interesting. Who knows? We hadn't really seen any html yet, but now that we're in the view, it's the right time to start seeing the html code appear. So, I'm going to render a header and look at that shortly. Then, I'm going to start putting things together. So, I want people to actually log in. So, it sounds like I need a form. I've just created two fields for now, an email address and a password, but that sort of fits with the spec that were working with for this project. And then, I've started adding a little more. So, we haven't really started talking about JavaScript yet but I wanted to have some place holders in the example as we get to that in the coming days here. So, I've done two little things, this validate form which just a short explanation. Basically, what I'd like to do is before the user even takes a trip out to the web server to do any work, I want to start doing some work on the client side to make sure that things are OK before you even go through the effort. So, this very, very short example of validate form, all it's going to do is look at the fields that have been filled in, it's only going to look at the email field. And what I want to do is just check that at least 6 characters have been entered, the idea of being-- if you were able to get the address, x@x.to, that was the shortest email address I could think of that was possibly going to be valid. So, if you haven't put in at least 6 characters, I didn't want to worry about it. The explanation of this will start coming Wednesday as we get in to JavaScript jQuery. This is just here for as you move forward at the project. The other little tweak that you'll see in here is the statement here, input name equals email dot focus. Basically when the page first loads, the cursor doesn't have any focus, this is ultimately going to say move that cursor into the email fields so the person can start typing. Again, another one of those details, you don't have to worry about tonight but a little preview for what you might start seeing on Wednesday. And then lastly, will include the footer. So basically, all this template has done is created a form on the screen. And I can actually show you what that looks like just so you have a little sample. This is actually that page. We saw the email address, you see it has actually right now. We saw the password. We saw the log in button as part of the form. There's also this little link home page at the top which we haven't seen yet, that's actually going to come out of the standard header. So, this should look relatively straightforward except for the one line that says script. And again, as we start getting into JavaScript on Wednesday, that line will make sense. But otherwise you'll see, I'm opening my html in my head, plug in the title using the same approach that David had use the last time he did MVC. And I've created one link at the top of the body there that says if you click on home page it just going to go to slash on the site which translates to http://project1/. All this code is also up on my bit bucket account. So, when that link is published on the home page for the class, you'll also have access to all of this to review as well. And the footer is even less convoluted. All I'm doing is closing my basic text. [ Pause ] The other option, if the user was already logged in to the site, I want to show them a little information on the site. So, I've just got a few sample links just to get things started here. You might think about how you might build out this project later to include more details here. I've specifically card quoted that you can request a quote for Goggle. Obviously for your project, do you want some more flexibility than just one stock symbol? I've provided a link so I can actually view my portfolio. And I've provided the opportunity to log out. You'll want to start thinking about things like how will they buy a stock, how will they sell a stock, do you need separate pages for those. If some ones adequate page already, is that the time to buy or do you want a separate purchase page? If they're already looking at their portfolio, is that the time to sell stocks or should there be a separate page for selling? Some design considerations you can start thinking about there. [ Pause ] All right. So, let's start looking at something real. Let's get a quick view of what the start of the portfolio might look like. [ Pause ] All right. So the portfolio control, if I click at my portfolio link, what is the controller going to do? The controller really just wants to talk to the model and pass any information out to the view controller. So basically, the sense of being relatively straightforward, if the users logged in with session users ID as set, then. I'm going to get the user ID from the session so you can think about how we've been the past use the session and stored a variable in there, somewhere I must be storing this value. Then, I'm going to get the users holdings. And this is just one call out to the model in this approach. Your holding is just get users shares for your user ID, and then we use render to throw it out to the view. So, the controller ends up just being the glue in this case. All it knows is I want to talk to the model to get the, and as soon as I have it, I don't want to think anything about html, I just want to get it off to view to deal with. So, this ends up being the entire controller relatively straightforward. One thing I'll point out here, you know, we've already seen one other place in my code were I check if the session user ID is set, perhaps I'm not doing the right thing here. Do I really want to check that on every single page? Is that a helper function that should be caught at a higher level? Things like that that you can consider. I don't know that I want to-- have to remember to in every file cut and paste the same in session user ID. And if not, render log in. All right, so to get to the real meat of all this, we finally make it down to the model again. This is the same model that we've have looked just shortly ago when we we're looking at an example of how we used to do things, this Mysql statements. I've left this in there. This is basically one of the approaches that we've used in class for authenticating a user against your database where you take the idea of selecting by email and password. If you get a row, the users have the right password and has a valid account and you can use that to log them in. But at the moment, we're talking about portfolio. So, let's actually go forward a little further and get a sense of what something like this might start to look like. So the portfolio, a user has some collection of stocks, they have some cash balance. I'm going down to a very basic example here and I'm only going to look at what stocks they have. So, you'll want to think about how do you expand this as you start developing the project to include everything that truly belongs in a user's portfolio. So, we got our new PDO syntax. We're connecting to the host, selecting the database, log in with user name and password, all of basically in those first two lines of code. Then, we're going to prepare a statement. Because the user ID is coming from someone else, I like the idea of the prepared statement here, it's going to protect me, it's going to do that wrapper of making sure that anything that needs to be escaped is escaped. So I'm going to buy in that value in and then I can execute the query. Once the query executes, I'm going to get back a result set. For each of the rows that I get, I'm going to do-- instead of doing the 4H approach like last time, I actually took a different approach. I'm using the statement and a fetch to get each row that was returned. And as I get them, I'm just going to push those results into an array so I can return it. So, what is this going to look like?. We're selecting symbols in shares, so we're basically going to end up with an array set that is for each row in the array, I'm going to have the symbol and the value, the shares and the value in an associated array, so an array of associative arrays. And I'm just going to return that to the users. If the statement didn't execute my database handle, just goes to null and you'll see a return no data. So, this is basic idea of how I might go out to the database using PDO, select a few rows and return an array back to the controller vaguely make sense? Are there any questions about it? All right, no questions yet. So once we've gotten this, we already saw that the controller is really just passing along, so we can jump right over to the view. [ Pause ] So, what are we doing with all this data that we've got? I've decided that I'm actually going to make a small table. It's going to have two columns. I'm using that table heading to bold those two-- the headers on the two columns, so it's just symbols and shares. I'm going to loop over the array that have been passed back directly as it come right out of the model. And for each one, I'm going to html special care as to clean it up, but basically put it in a table data within the table rows, pulling things out by the names because I've got this associative arrays inside there. And that is roughly it, relatively straightforward here before I put the footer on. So, let me show you what that actually looks like just to give you some context. I'm going to jump ahead and actually log in so I can do view portfolio. So apparently in the database, this user had two actual holdings, one was the symbol GOOG for 10 shares, one was the symbol for Yahoo with a 100 shares. In fact, that's not the real symbol for Yahoo. That was a typo when I created the data. But basically very straightforward, and if we look at the page source, you'll see that the page-- [ Pause ] -- I really haven't gotten too complex with my code here. All right. So, the one other thing I want to just show you was what David just started alluding to at the end of class as well, and that is the quotes. So, we can look at the controller for quotes. And this is again very short quote because all the work is really being done by the model and the view. Basically, I'm going to say if a param was passed in, I'm going to call this get quote data with the parameter, urlencoded. And then whatever comes back, I'm passing directly out to the view for rendering. So, let's see what's really going on in the model here. [ Pause ] All right. So, this code probably looks very familiar, it's very similar to what David just showed a few minutes ago. I'm being passed and assembled. I'm creating a result array just to store things in, nothing in their yet. And I've got this URL to actually connect to Yahoo. The one special piece of data that I've plugged in is the symbol that was actually passed in. I'll get the pointer. So I've got the symbol here. I've also specified this which we saw in the slides earlier that sl1&n, s being the symbol, l1 being the last trade value and n being the name. What I want to do then is I can actually open this URL as if it was a file directly with be an F open command. Once I've a handle to that file, CSV is one of the things that PHP knows how to work with, so I can one row at a time use fgetcsv to pull the CSV data directly into rows that I can work with. If the row, row value one in this case is set, then, I know I've got data, I found the last traded value. So what I'm going to do is I'm actually going to create an associative array with the symbol set to the first parameter which was always the symbol, the last trade set to the second parameter was the l1 is the last trade, and the name set to the third parameter which was the n from the URL that we used. I set this us specifically so it just pulls down a single symbol at a time, although the APIs do allow us to pull down multiples, and that's something that you want to consider as well. In this case I really-- I was only concern about returning one result. But when your working later, say for example you're going to be generating the value of someone's portfolio, does it make sense for each stock symbol to take another trip out to the Yahoo server to get that quotes-- that symbols phrase, or do you want to bunch them up and get a number of symbols back at one shot so you might get 5 or 10 current stock raises from Yahoo in a single transaction rather than doing multiple transactions? So things to consider their. Once I've got the data, I close my handle and I return it. And that data can then be pass off to the view. [ Pause ] So, one little tweak you'll see at the top of this file that I haven't done in other files, is it possible to call this page and not have valid data come back? So, either someone picked a stock symbol that didn't exist or Yahoo service was unavailable and I just couldn't get data at the moment. It's possible that things will go wrong. So, I've got some notification to the user if no data has come in to the view that either the symbol wasn't provided or the quote data wasn't found. But assuming that I do actually get my data back, I've done a short table again scrolling off the bottom in the screen here, and following the same templets as I did with portfolio. I'm just taking the three pieces of data and put them into that table row and closing up the table on the screen and I can show you what that looks like as well. [ Pause ] It's a very straightforward little table, just a symbol name and last trade and the data's pulled directly from the model, pass through the controller and displayed on the view here. So, that was the code I was going to review tonight. There's one other file in there, you'll see it's called pwd hash dot php. If you try to log in and your log in credentials aren't valid, it will actually show you the password hash or whatever you just try to log in with. So in that, until you actually write your register function, you can find out what the password is and you can push them with the database, you can actually keep moving along with your code and before you finish all your code. So, a few things that I want to make sure that I got some design considerations out there for you. I've have mention a few as we went. We talked about how many connections should you make out to yahoo when you're trying to calculate the valuable portfolio, so something to think about there as a multiple connection, single connections. What happens if the user actually try to sell stocks from two different browser simultaneously? What do you want to do about that? What happens if the user buys additional shares of a stock that they were already own? And David hinted strongly at how you might consider that earlier in the lecture tonight? As we get into the JavaScript, you want to start thinking about what validation do you want to do on the browser and what validation do you want to do on the server. If the user hasn't entered a password at all, you can probably save yourself a trip to the server and validate on the browser. But if you manage to get pass that check or the user doesn't run JavaScript, you probably still need to confirm some things on the server side as well, possibly the same things that you thought you were confirming on the browser side. So, something to think about is we're going to Wednesday there. And one other thing that isn't obvious, but if you look at my code-- this page is available right now, this quote slash GOOG. But if I logged out of the application, the page is still actually visible. So, think about things like this, does it make sense for me to be able to still look up quotes even if I'm not logged in to the application or should all the pages? Or what subset of the pages should be restricted to just authenticated users? So, those are the few thoughts I had to inspire you as you work through this. Are there any questions on the project so far? Just as a quick reminder, I'll be sticking around for office hours for as long as folks need tonight. And one of the TFs will be online office hours at 8. But certainly, any questions right now, I'm happy to answer. All right. Well I thank every one for their time and thanks to the folks watching this remotely. And good luck. Remember to watch for our posting on how to submit you're project zero tomorrow so that we can get those submitted Wednesday. Thanks. Good night everyone. [ Applause ] [ Silence ]