1 00:00:00,506 --> 00:00:09,886 [ Silence ] 2 00:00:10,386 --> 00:00:10,716 >> All right. 3 00:00:10,716 --> 00:00:13,576 Welcome back this is Computer Science S75. 4 00:00:13,656 --> 00:00:15,366 I'm Chris Gerber. 5 00:00:15,366 --> 00:00:17,106 And tonight I'll be covering Section 5. 6 00:00:17,456 --> 00:00:21,276 We're going to talk about PDO, some more sql commands, 7 00:00:21,276 --> 00:00:23,666 talk to you about join, index and commit and well get 8 00:00:23,666 --> 00:00:26,806 into a little bit of more of the detail around project one. 9 00:00:27,206 --> 00:00:29,266 Hopefully that everyone is doing well with project zero, 10 00:00:29,266 --> 00:00:31,496 you're moving along, you're ready 11 00:00:31,496 --> 00:00:33,276 to turn things in Wednesday. 12 00:00:33,616 --> 00:00:35,716 We'll give you the instructions Tuesday night how 13 00:00:35,716 --> 00:00:36,746 to actually proceed of that. 14 00:00:37,636 --> 00:00:39,156 Well, let's get started. 15 00:00:39,606 --> 00:00:42,596 To kick things off, I'd actually like to do a little refresher 16 00:00:42,596 --> 00:00:43,736 about how we have been working. 17 00:00:44,266 --> 00:00:47,896 So, let me bring up bit of code. 18 00:00:48,096 --> 00:00:54,206 All right, so what we're going to look 19 00:00:54,206 --> 00:00:57,136 at first is just a little bit of how we have been working 20 00:00:57,136 --> 00:00:58,346 with databases in php. 21 00:00:58,916 --> 00:01:03,436 So you'll see, this is the model that I'm been working 22 00:01:03,436 --> 00:01:07,166 on as I begin to work on my implementation of project one. 23 00:01:07,926 --> 00:01:10,116 I've got a few definitions upfront just 24 00:01:10,116 --> 00:01:11,146 to make things easy. 25 00:01:11,146 --> 00:01:13,556 Ans so, I have a single plate were I can configure things. 26 00:01:13,956 --> 00:01:17,996 I've define my host, my user, my password and my database all 27 00:01:17,996 --> 00:01:18,966 with the defined commands. 28 00:01:18,966 --> 00:01:22,426 So I've basically set up, it's not quite available-- 29 00:01:22,426 --> 00:01:24,106 a variable, it's actually a constant, 30 00:01:24,646 --> 00:01:26,376 so I can use this later in my code. 31 00:01:26,686 --> 00:01:29,466 So, if down the road I actually changed the account name 32 00:01:29,466 --> 00:01:32,016 or the password, I'm just changing in one spot. 33 00:01:32,536 --> 00:01:35,566 So, let's move a little further down the code. 34 00:01:40,996 --> 00:01:44,106 Yeah. So, this is basically the approach we were taking before. 35 00:01:44,786 --> 00:01:48,146 We've got-- We could skip the first few lines but we set 36 00:01:48,146 --> 00:01:50,136 up a connection, we used Mysql connect. 37 00:01:51,196 --> 00:01:52,996 I had host name, user name and password. 38 00:01:53,316 --> 00:01:56,716 We would then select the database with Mysql select DB. 39 00:01:56,716 --> 00:02:03,826 And then, we were using things like, S print F to take a query, 40 00:02:04,226 --> 00:02:07,526 fill in the blanks automatically and build up the statements 41 00:02:07,526 --> 00:02:11,316 so that we could then do, for example in that Mysql query 42 00:02:11,406 --> 00:02:12,686 to actually get back some data. 43 00:02:13,566 --> 00:02:15,666 So, this is where we were just a few days ago. 44 00:02:16,416 --> 00:02:18,636 This is all basically seemed familiar so far? 45 00:02:19,216 --> 00:02:26,136 All right, so looking at this rough idea, what can tell me-- 46 00:02:26,456 --> 00:02:29,076 who can tell me what's potentially good or bad 47 00:02:29,236 --> 00:02:31,376 about what I've done so far here? 48 00:02:33,376 --> 00:02:36,966 So, anything that jumps out that seems promising, disappointing. 49 00:02:37,416 --> 00:02:37,506 Sure. 50 00:02:37,506 --> 00:02:43,786 >> One thing that's good is that you've encrypted your passwords 51 00:02:44,176 --> 00:02:46,896 with the SHA1 in the database. 52 00:02:47,146 --> 00:02:48,286 And yeah. 53 00:02:48,646 --> 00:02:50,456 >> Yup. So, the first thing pointed out was 54 00:02:50,456 --> 00:02:52,886 that we actually encrypted the password, 55 00:02:52,886 --> 00:02:55,596 we're using a password hash, were using SHA1. 56 00:02:55,596 --> 00:02:58,776 So, that was something that had come up in lecture 57 00:02:58,996 --> 00:03:01,486 and certainly that's one of the things I want incorporate, 58 00:03:01,486 --> 00:03:02,186 so, that's a great one. 59 00:03:03,176 --> 00:03:05,616 What else did people noticed from my code so far? 60 00:03:06,516 --> 00:03:12,656 [ Pause ] 61 00:03:13,156 --> 00:03:13,406 All right. 62 00:03:13,406 --> 00:03:15,086 So, one thing I might throw out there, 63 00:03:16,166 --> 00:03:19,566 I've got this function log in user and I'm connecting 64 00:03:19,566 --> 00:03:22,426 to the database and selecting the database, 65 00:03:22,426 --> 00:03:25,006 and that's probably something I'm going to do a lot. 66 00:03:26,056 --> 00:03:28,326 So, that's something where I might want to consider, 67 00:03:28,326 --> 00:03:31,806 does it make sense to put it in this individual functions 68 00:03:31,806 --> 00:03:33,556 and repeat that code over and over? 69 00:03:33,936 --> 00:03:35,486 Or do maybe want to factor that out 70 00:03:35,486 --> 00:03:36,846 to some higher point in the code? 71 00:03:37,306 --> 00:03:38,436 So, that's something to consider. 72 00:03:38,436 --> 00:03:39,836 Anything else? 73 00:03:39,916 --> 00:03:48,616 I'll point out that we also use the Mysql escapes string 74 00:03:49,426 --> 00:03:51,126 on the email because I didn't really know 75 00:03:51,126 --> 00:03:52,566 where that was coming from and I just want 76 00:03:52,566 --> 00:03:54,046 to make sure its safe when it's coming in. 77 00:03:54,306 --> 00:04:03,516 All right, and then one of the other things is something 78 00:04:03,516 --> 00:04:05,276 that David just started talked about tonight. 79 00:04:05,276 --> 00:04:07,446 When we start looking at this code, 80 00:04:08,226 --> 00:04:11,046 I've got all this Mysql specific commands. 81 00:04:11,796 --> 00:04:14,846 So, What's the problem with this Mysql specific commands? 82 00:04:15,446 --> 00:04:15,546 Yep 83 00:04:16,436 --> 00:04:19,206 >> Well, we did mention that if you might read your database 84 00:04:19,296 --> 00:04:22,046 from another system, like get another new database 85 00:04:22,256 --> 00:04:25,086 or something, it would be beneficial to use something 86 00:04:25,186 --> 00:04:28,076 that for some humbled multiple databases, 87 00:04:28,756 --> 00:04:30,216 so you just might as well. 88 00:04:30,366 --> 00:04:32,376 >> Right, so the answer was and it's great answer. 89 00:04:33,066 --> 00:04:35,946 If in the future I want to use something other than Mysql, 90 00:04:35,946 --> 00:04:37,456 I've pretty much locked my self in. 91 00:04:38,096 --> 00:04:40,286 So, that's why we're going to start talking about PDO tonight. 92 00:04:40,836 --> 00:04:46,176 The other thing I'd like you to keep in mind as we look 93 00:04:46,176 --> 00:04:48,256 at this is that S print F command 94 00:04:48,876 --> 00:04:53,066 where I've basically build this query once. 95 00:04:53,606 --> 00:04:58,116 I take the parameters and I build a unique string 96 00:04:58,116 --> 00:05:00,606 and I'm going to pass that one string into the database. 97 00:05:01,146 --> 00:05:05,826 So, let's switch over to the slides. 98 00:05:07,246 --> 00:05:09,146 So, actually I have one other thing I want to talk 99 00:05:09,146 --> 00:05:10,746 about related to this. 100 00:05:11,506 --> 00:05:12,886 We've talked about indexes. 101 00:05:12,996 --> 00:05:15,616 You know, one of the key reason we use indexes are 102 00:05:15,616 --> 00:05:18,316 to improve performance at the cost 103 00:05:18,316 --> 00:05:19,956 of using some additional storage. 104 00:05:20,296 --> 00:05:23,526 So, just as a refresher, we've got the create index command, 105 00:05:23,786 --> 00:05:26,866 you can specify that they're unique, specify the table 106 00:05:26,866 --> 00:05:28,726 and the columns that are going to be used. 107 00:05:29,216 --> 00:05:35,496 When we look at our code, we've talked about this 108 00:05:35,496 --> 00:05:42,176 in the example before, we've got the email and the password, 109 00:05:42,176 --> 00:05:45,176 and were going to be querying this over and over. 110 00:05:46,216 --> 00:05:49,326 But really if you start looking at the statement I build 111 00:05:49,326 --> 00:05:51,546 up here, it's probably more likely 112 00:05:51,546 --> 00:05:54,156 that ID is actually the field 113 00:05:54,156 --> 00:05:56,736 that I've set up as a primary key. 114 00:05:57,616 --> 00:06:02,516 The fact that we're going to continually query against email 115 00:06:02,516 --> 00:06:05,476 and password, it actually implies to me that I might want 116 00:06:05,476 --> 00:06:07,976 to create an index that uses both those fields 117 00:06:08,296 --> 00:06:10,976 so that every time I'm authenticating someone 118 00:06:10,976 --> 00:06:11,856 against the database, 119 00:06:12,546 --> 00:06:14,456 that performance is going to be improved. 120 00:06:15,136 --> 00:06:16,076 So, something to consider 121 00:06:16,076 --> 00:06:17,296 as you're working through your project. 122 00:06:17,296 --> 00:06:26,746 All right, so I want to jump over and talk about PDO. 123 00:06:27,016 --> 00:06:28,886 We want to start leaving behind this idea 124 00:06:28,886 --> 00:06:30,506 of this Mysql statement, 125 00:06:30,506 --> 00:06:32,906 it's not that they're bad but we can do better. 126 00:06:33,766 --> 00:06:36,736 So PDO is PHP data objects, 127 00:06:37,906 --> 00:06:41,586 it provides a data access obstruction layer 128 00:06:42,416 --> 00:06:45,656 but it does not provide a database obstruction layer. 129 00:06:46,376 --> 00:06:51,036 So basically the difference there is, we don't have 130 00:06:51,036 --> 00:06:53,366 to learn a new set of PHP commands 131 00:06:53,736 --> 00:06:55,096 to talk to our databases. 132 00:06:55,536 --> 00:06:58,166 No matter what we change on the back end, if it's Mysql, 133 00:06:58,166 --> 00:07:01,166 oracle whatever, were still using the same PHP commands. 134 00:07:01,966 --> 00:07:05,136 But whatever we pass in through the PHP commands, 135 00:07:05,556 --> 00:07:08,036 a select statement, an insert, an update, a delete, 136 00:07:08,756 --> 00:07:12,196 those commands are not going to get translated. 137 00:07:12,546 --> 00:07:16,276 So, if your using a specific set of sql commands 138 00:07:16,436 --> 00:07:21,566 that are specific to your database, those do not change. 139 00:07:21,756 --> 00:07:24,256 So, if you start using custom functionality, for example, 140 00:07:24,256 --> 00:07:28,856 if I was going to do a create table through PHP and I'm going 141 00:07:28,856 --> 00:07:31,556 to use Mysql, we talked tonight about using NODB, 142 00:07:31,556 --> 00:07:33,766 it could be one of your database options. 143 00:07:34,516 --> 00:07:36,816 Oracle is not going to know anything about that. 144 00:07:37,306 --> 00:07:40,976 So, if I remap the commands and use oracle as the back end, 145 00:07:40,976 --> 00:07:44,936 the PHP part will stay the same, those commands will be the same. 146 00:07:45,476 --> 00:07:48,556 But the actual create table command will have to be edited 147 00:07:49,596 --> 00:07:51,756 because this function or the PDO doesn't know how 148 00:07:51,756 --> 00:07:53,556 to automatically make those translations. 149 00:07:53,556 --> 00:07:58,576 One of the other nice things about PDO, we mentioned some 150 00:07:58,576 --> 00:08:01,816 of the different drivers that are available, some of platforms 151 00:08:01,816 --> 00:08:06,026 out there are Informix, MS SQL, MySQL, Oracle, SQLite. 152 00:08:06,026 --> 00:08:09,016 So certainly plenty of options which is really nice as well. 153 00:08:09,576 --> 00:08:13,246 All right. 154 00:08:14,396 --> 00:08:17,896 So, let's actually see what some of these starts to look like. 155 00:08:18,506 --> 00:08:21,926 Get out of my code. 156 00:08:22,516 --> 00:08:34,316 [ Pause ] 157 00:08:34,816 --> 00:08:38,396 So, I created a very simple example, 158 00:08:39,386 --> 00:08:42,316 basic idea of a select statement using PDO. 159 00:08:42,316 --> 00:08:44,916 So, we can start. 160 00:08:44,916 --> 00:08:48,266 I have to sort of spelled it out on one big command 161 00:08:48,266 --> 00:08:50,696 to get started, but I'm creating a database handle, 162 00:08:51,026 --> 00:08:56,536 dollar sign DVH, so, starting here, equals new PDO. 163 00:08:56,536 --> 00:08:59,806 So, I'm actually creating an object and it's a PDO object. 164 00:09:00,386 --> 00:09:01,926 And I'm passing in some parameters. 165 00:09:02,946 --> 00:09:11,336 The first parameter is the DSN, which I don't of the top 166 00:09:11,336 --> 00:09:12,986 of my head remember what the acronym is for. 167 00:09:12,986 --> 00:09:14,766 But basically what it tells it is, 168 00:09:15,666 --> 00:09:17,476 the type of database you're connecting to, 169 00:09:17,846 --> 00:09:20,326 the host that it's on, and which database 170 00:09:20,326 --> 00:09:21,786 in that host you're going to use. 171 00:09:22,366 --> 00:09:25,816 So, I've specified Mysql for the database type, 172 00:09:26,486 --> 00:09:29,046 I've specified local host as my host, 173 00:09:29,956 --> 00:09:35,346 and I've specified the database name of jharvard_section5 174 00:09:35,766 --> 00:09:38,216 which should feel familiar from all the work we've seen in PHP, 175 00:09:38,216 --> 00:09:40,796 Mysql so forth-- PHP in my app. 176 00:09:41,996 --> 00:09:45,036 Then the next two parameters are the user and the password. 177 00:09:45,736 --> 00:09:47,616 And we've certainly seen jharvard as the user 178 00:09:47,616 --> 00:09:49,896 and crimson is the password plenty of times this semester. 179 00:09:49,896 --> 00:09:54,286 The next thing I'm going to do is just a simple query, 180 00:09:54,446 --> 00:09:58,286 I take the database handle and I tell I'm going to do a query. 181 00:09:58,876 --> 00:10:00,726 And I've got a very simple query statement here, 182 00:10:00,946 --> 00:10:01,976 select star from the students. 183 00:10:03,446 --> 00:10:05,406 We all know what that does straightforward. 184 00:10:05,406 --> 00:10:06,846 I'm going to take the results and put it 185 00:10:06,846 --> 00:10:10,826 into a variable called $students. 186 00:10:11,296 --> 00:10:14,376 As you might guess, this is going to be an array. 187 00:10:15,256 --> 00:10:17,466 So I can, as we go to the bottom of the screen here, 188 00:10:17,826 --> 00:10:22,146 use a four each to iterate across that, taking each element 189 00:10:22,146 --> 00:10:23,956 out of students and calling it a student. 190 00:10:28,396 --> 00:10:31,826 And then, I can print the various fields. 191 00:10:32,216 --> 00:10:35,976 So, one thing you'll see here is I suddenly have ID first 192 00:10:35,976 --> 00:10:40,446 and last available, and what's happening is PDO is actually 193 00:10:40,446 --> 00:10:43,196 looking at the column names in the table 194 00:10:43,826 --> 00:10:46,756 and making those the keys for my associative array 195 00:10:47,016 --> 00:10:48,456 so that I can pull the values out. 196 00:10:49,776 --> 00:10:53,666 So, student is the row, id is the column, 197 00:10:54,096 --> 00:10:56,216 it's mapped at into an associative ray of element 198 00:10:56,336 --> 00:10:57,976 and we'll automatically pull out the value. 199 00:10:58,536 --> 00:11:02,636 And then when we're done, just for good measure of set, 200 00:11:02,636 --> 00:11:04,396 the database handled to null 201 00:11:04,396 --> 00:11:05,936 which essentially close is the connection. 202 00:11:07,226 --> 00:11:08,586 So, we can actually see what this does. 203 00:11:09,516 --> 00:11:18,016 [ Pause ] 204 00:11:18,516 --> 00:11:20,846 Before I actually kick off this command, one thing I want 205 00:11:20,846 --> 00:11:23,736 to point out is for the bulk of this course, 206 00:11:23,736 --> 00:11:26,336 we've actually been running PHP through the web server, 207 00:11:26,906 --> 00:11:29,576 but PHP is just a standard scripting language. 208 00:11:30,746 --> 00:11:34,556 When you execute it, you can specify that the output is going 209 00:11:34,556 --> 00:11:36,946 out through, for example, through apache 210 00:11:36,946 --> 00:11:37,816 out through the web server, 211 00:11:37,816 --> 00:11:39,906 but you can actually run this commands from the command line 212 00:11:39,906 --> 00:11:41,186 as well and just see the output. 213 00:11:41,816 --> 00:11:43,966 So I've actually, I'm going to do that right here. 214 00:11:48,016 --> 00:11:49,986 And you can see that it's going into my database, 215 00:11:50,756 --> 00:11:52,746 previously, set up a small table. 216 00:11:53,876 --> 00:11:57,596 I've got a column ID, it's the primary key. 217 00:11:57,866 --> 00:12:01,426 It's just in 1, 2, 3, auto in command data. 218 00:12:01,936 --> 00:12:03,606 I've got a first name and a last name. 219 00:12:04,636 --> 00:12:06,086 And it's pulled out to those records 220 00:12:06,086 --> 00:12:08,106 and printed in the screen for me. 221 00:12:08,796 --> 00:12:11,436 So, just as a very quick overview of PDO, 222 00:12:11,436 --> 00:12:14,946 does that make basically make sense so far? 223 00:12:15,106 --> 00:12:15,976 OK, great. 224 00:12:16,516 --> 00:12:26,296 [ Pause ] 225 00:12:26,796 --> 00:12:28,676 So, as we start looking into PDO, 226 00:12:29,806 --> 00:12:32,536 you'll see a few standard things, we've got the dsn 227 00:12:32,786 --> 00:12:36,766 which we mentioned and the dbh, database handle that we mention. 228 00:12:37,656 --> 00:12:40,946 There are basically five statements that are sort 229 00:12:40,946 --> 00:12:42,766 of my wheelhouse when I'm using PDO, 230 00:12:43,766 --> 00:12:47,166 query executes the statement and return the result set, 231 00:12:47,346 --> 00:12:49,506 that's what we just saw. 232 00:12:49,746 --> 00:12:52,366 Exec executes the statement. 233 00:12:52,366 --> 00:12:54,036 But instead of returning the results, 234 00:12:54,176 --> 00:12:56,636 it just tells you the number of rows that were impacted. 235 00:12:57,526 --> 00:13:00,476 So, if you were doing an update operation 236 00:13:00,476 --> 00:13:04,516 and you expected three rows to be updated, you could determine 237 00:13:04,516 --> 00:13:07,896 if that actually happened if for example zero were updated, 238 00:13:07,896 --> 00:13:09,266 you could start to get a sense 239 00:13:09,266 --> 00:13:11,746 as to was the command successful based 240 00:13:11,746 --> 00:13:16,726 on what you expected the account to be. 241 00:13:16,976 --> 00:13:20,946 Prepare, bindValue and execute, get a little more interesting. 242 00:13:21,136 --> 00:13:24,586 So what prepared us is it let's us pre-prepare a statement 243 00:13:24,586 --> 00:13:27,116 for execution on the database. 244 00:13:28,056 --> 00:13:30,376 So, that actually gives us several advantages. 245 00:13:30,376 --> 00:13:33,596 First of all, it's letting the database know up front. 246 00:13:34,076 --> 00:13:38,916 We're going to do this work and let it do a bulk of the work 247 00:13:38,916 --> 00:13:40,146 up front to get ready. 248 00:13:40,146 --> 00:13:41,976 It knows what tables it's going to need. 249 00:13:42,256 --> 00:13:45,136 It knows basically the approach that it's going 250 00:13:45,136 --> 00:13:46,186 to take query the data. 251 00:13:46,186 --> 00:13:48,926 And then, we can specify parameters 252 00:13:48,926 --> 00:13:52,296 and we can use those parameters to then tweak the query, 253 00:13:52,516 --> 00:13:56,936 so we might be selecting with the parameter of ID equals fill 254 00:13:56,936 --> 00:14:00,076 in the blank, and then I can say, "Run this query with a 1. 255 00:14:00,076 --> 00:14:01,056 Run this query with a 2. 256 00:14:01,146 --> 00:14:02,146 Run this query with a 3." 257 00:14:02,616 --> 00:14:05,236 And the database doesn't have to go through the full process 258 00:14:05,236 --> 00:14:06,926 of building the instructions each time, 259 00:14:06,926 --> 00:14:09,916 it just has to rerun the same query with the new value. 260 00:14:11,206 --> 00:14:13,716 To do that, we're going to use the bind value command 261 00:14:14,346 --> 00:14:16,856 which takes one of the parameters and inserts the value 262 00:14:16,856 --> 00:14:19,006 that we want to use into it. 263 00:14:19,236 --> 00:14:21,046 And then, once we've prepared it and bound it, 264 00:14:21,046 --> 00:14:24,616 we actually use execute as to oppose to exec, execute spelled 265 00:14:24,616 --> 00:14:27,436 out to actually run the statement. 266 00:14:31,856 --> 00:14:37,106 So, why do we do this? 267 00:14:37,306 --> 00:14:41,766 Basically, there two really good reasons, performance first off, 268 00:14:42,946 --> 00:14:46,646 the preparations perform once, so the database can do that work 269 00:14:46,646 --> 00:14:48,496 and it doesn't have to repeat it every time we're going 270 00:14:48,496 --> 00:14:49,506 to execute the command. 271 00:14:50,036 --> 00:14:53,656 And then, you know, parameters can have new values bound 272 00:14:53,656 --> 00:14:55,596 at ease executions well which is great. 273 00:14:56,306 --> 00:14:58,556 The other advantage is security. 274 00:14:58,716 --> 00:15:00,156 As David mentioned earlier tonight, 275 00:15:01,636 --> 00:15:05,906 by using this parameters, we actually get the work 276 00:15:05,906 --> 00:15:09,556 of escaping the parameters done for us up front. 277 00:15:09,556 --> 00:15:14,386 So, we don't have to remember to do Mysql, real, prepare, 278 00:15:14,386 --> 00:15:15,556 whatever the whole statement was. 279 00:15:15,556 --> 00:15:16,746 You don't have to remember that even. 280 00:15:17,146 --> 00:15:18,426 You just have to know that as long 281 00:15:18,426 --> 00:15:19,746 as you're using these parameters it's going 282 00:15:19,746 --> 00:15:20,726 to take care that for you. 283 00:15:21,516 --> 00:15:32,296 [ Pause ] 284 00:15:32,796 --> 00:15:35,576 So, let's see what that looks like. 285 00:15:36,516 --> 00:15:46,426 [ Pause ] 286 00:15:46,926 --> 00:15:48,866 So, I'd shown you my table of students 287 00:15:49,626 --> 00:15:51,886 with my self and David and Ellen. 288 00:15:53,256 --> 00:15:55,446 I've also got a table of grades that I had pre-created. 289 00:15:55,826 --> 00:15:58,856 And what I want to do is fill in a number of values. 290 00:15:58,976 --> 00:16:01,796 So, I've created this array that has the data that I want 291 00:16:01,796 --> 00:16:02,906 to load into the database. 292 00:16:03,776 --> 00:16:08,636 The first value is the ID number of the student, 293 00:16:09,226 --> 00:16:11,626 the second value is the project number they were working on, 294 00:16:11,626 --> 00:16:13,976 and the third value is the grade that they got on the project. 295 00:16:14,876 --> 00:16:17,406 And I just want to bulk load this data into the database. 296 00:16:18,776 --> 00:16:21,186 You can see I've set up my connection to the database 297 00:16:21,186 --> 00:16:22,696 as the last line here. 298 00:16:23,946 --> 00:16:25,346 Well, let's see how we might actually go 299 00:16:25,346 --> 00:16:27,036 through this process using parameters. 300 00:16:32,836 --> 00:16:36,156 All right, just to make it clearer for my self, 301 00:16:36,156 --> 00:16:38,376 I started off by deleting from grades. 302 00:16:38,376 --> 00:16:42,506 So, anything that wasn't there, I'm getting rid off. 303 00:16:42,776 --> 00:16:45,136 Next, I can prepare this statement. 304 00:16:45,926 --> 00:16:50,466 We want to insert into grades, grades was the table, 305 00:16:50,816 --> 00:16:53,546 the columns are going to be student, project and grade, 306 00:16:53,546 --> 00:16:56,056 and we want to fill in values. 307 00:16:56,506 --> 00:16:59,986 And you can see what I've done here is I've got :student, 308 00:16:59,986 --> 00:17:01,476 :project, and :grade. 309 00:17:02,046 --> 00:17:04,596 So basically, what I've done is name the parameters. 310 00:17:04,596 --> 00:17:06,186 These are the blanks that I want to fill in 311 00:17:06,186 --> 00:17:08,786 and these are the names I want to use to fill them in with. 312 00:17:09,956 --> 00:17:12,346 Now, you could actually use question mark for each one 313 00:17:12,796 --> 00:17:15,066 and then just remember the position of each question mark. 314 00:17:15,556 --> 00:17:20,296 But I find using the full name as actually little more clear. 315 00:17:20,456 --> 00:17:24,066 Then, I've created a loop here to insert all this grades. 316 00:17:25,156 --> 00:17:27,886 At each point, I'm going to bind a value 317 00:17:28,126 --> 00:17:32,186 to the appropriate parameter and then I can execute the query. 318 00:17:33,016 --> 00:17:36,546 So the first time through loop, I take those first elements 319 00:17:36,546 --> 00:17:40,456 of the array and I bind them to student, project, and grade 320 00:17:40,456 --> 00:17:43,726 and then I can execute the command and loop 321 00:17:43,726 --> 00:17:44,656 around to the next item. 322 00:17:45,946 --> 00:17:49,936 The other thing that's interesting here is this last 323 00:17:49,936 --> 00:17:50,846 set of parameters. 324 00:17:52,066 --> 00:17:53,616 Now, what does that look like to folks? 325 00:17:54,776 --> 00:17:55,036 Sure. 326 00:17:56,106 --> 00:17:57,666 >> Integer and strings. 327 00:17:57,876 --> 00:17:58,536 >> Exactly. 328 00:17:58,706 --> 00:18:00,406 So, what I can do is I can actually-- 329 00:18:00,656 --> 00:18:01,936 sorry, it was integers and strings. 330 00:18:02,496 --> 00:18:04,446 What I can actually do is tell the database 331 00:18:04,446 --> 00:18:07,956 in advance what types of fields this are as well 332 00:18:07,956 --> 00:18:09,996 which will help it with the process of making sure 333 00:18:09,996 --> 00:18:13,356 that the data that's being bound in is appropriate 334 00:18:13,356 --> 00:18:18,846 for the fields, so that I don't try to take a string and cram it 335 00:18:18,846 --> 00:18:21,126 into an integer or it will know that it actually has 336 00:18:21,126 --> 00:18:23,496 to do formatting appropriately to make that happen. 337 00:18:24,546 --> 00:18:26,986 Those parameter types are actually optional, 338 00:18:26,986 --> 00:18:30,856 but I certainly recommend it as a-- if nothing else, 339 00:18:31,316 --> 00:18:32,416 good clear it to yourself 340 00:18:32,416 --> 00:18:33,906 as to what you're expecting to be happening. 341 00:18:34,446 --> 00:18:40,476 And at the very end of this example, I just do another query 342 00:18:40,476 --> 00:18:42,466 of the database, the same code as before just 343 00:18:42,466 --> 00:18:44,186 so we can display the results. 344 00:18:44,186 --> 00:18:46,766 I'm using the grades table instead of the students table 345 00:18:46,766 --> 00:18:49,206 but the concept is the same. 346 00:18:49,356 --> 00:18:51,556 So, let's see how this actually works. 347 00:18:52,516 --> 00:19:03,706 [ Pause ] 348 00:19:04,206 --> 00:19:06,976 So you see it's gone through and it's entered the data 349 00:19:06,976 --> 00:19:10,056 from that array directly into the table of query to back out, 350 00:19:10,696 --> 00:19:11,716 relatively straightforward. 351 00:19:12,286 --> 00:19:19,556 So, you can select a series of columns from a table name, 352 00:19:20,806 --> 00:19:23,096 there's a number of options here and I'll come back to that, 353 00:19:24,096 --> 00:19:28,526 joined to a second table name based on some conditions. 354 00:19:29,066 --> 00:19:34,286 So we've got left, right, outer, inner, what does all this mean? 355 00:19:35,706 --> 00:19:38,986 Basically, we have inner joins where we have left 356 00:19:38,986 --> 00:19:42,356 or right outer joins, there's also full outer joins 357 00:19:42,356 --> 00:19:46,106 which we'll hint at but we won't go into a lot of details there. 358 00:19:47,356 --> 00:19:49,326 So, let's see one of this. 359 00:19:49,916 --> 00:19:54,836 So, starting with inner join, I've got two tables 360 00:19:54,836 --> 00:19:56,646 and this are the tables that we've been talking about. 361 00:19:57,166 --> 00:20:00,966 We've got students, Chris, David, Alain, we've got grades 362 00:20:01,846 --> 00:20:03,676 with the student number of project and grade. 363 00:20:04,636 --> 00:20:07,696 I moved the ID column in the first table to the right just 364 00:20:07,696 --> 00:20:10,886 so you can see that ID and student go together 365 00:20:10,886 --> 00:20:15,816 and you can sort of see how things might line up there. 366 00:20:16,056 --> 00:20:18,066 With an inner join, what we're going 367 00:20:18,066 --> 00:20:22,566 to see is a result like this. 368 00:20:23,396 --> 00:20:25,836 It's basically going to look for every place on the left 369 00:20:27,116 --> 00:20:29,866 where it can find an ID number that has a matching number 370 00:20:29,866 --> 00:20:33,016 on the right and display those records. 371 00:20:33,916 --> 00:20:36,096 So for an ID of one, it goes over. 372 00:20:36,096 --> 00:20:38,856 So, see a student is in the first and second rows 373 00:20:38,856 --> 00:20:43,356 and it brings that combination of data together, 1, 1, 1, 1. 374 00:20:44,226 --> 00:20:47,726 It goes on to number two, it finds the third and fourth row 375 00:20:47,726 --> 00:20:50,516 in the second table and brings those rows together. 376 00:20:51,116 --> 00:20:55,616 For Alain, his ID 3, there are no grade, 377 00:20:55,616 --> 00:20:57,146 so it doesn't bring anything back there. 378 00:20:58,356 --> 00:21:01,256 Likewise in the grades column, there's a student 4 379 00:21:01,256 --> 00:21:02,596 who we haven't define anywhere 380 00:21:02,716 --> 00:21:04,046 and it doesn't bring that back either. 381 00:21:04,656 --> 00:21:07,456 The inner being, it's all the places 382 00:21:07,456 --> 00:21:09,276 where only there is a direct match. 383 00:21:14,936 --> 00:21:17,796 Code was, this is what it would look like. 384 00:21:18,866 --> 00:21:22,176 The table that I actually brought back was select star 385 00:21:22,386 --> 00:21:28,856 from students, join grades and students.id=grades.student. 386 00:21:29,246 --> 00:21:30,386 And we talked about that earlier 387 00:21:30,386 --> 00:21:35,126 where when you're using database columns 388 00:21:35,706 --> 00:21:38,196 but you have multiple tables to work with, 389 00:21:38,736 --> 00:21:46,306 as long as the fields are unique you don't necessarily have 390 00:21:46,496 --> 00:21:48,976 to specify the table name but it's good practice 391 00:21:48,976 --> 00:21:52,756 to always specify table name dot column name just to be explicit 392 00:21:52,756 --> 00:21:55,656 about where those columns are actually coming from. 393 00:21:56,556 --> 00:21:57,976 So, we can see this in code. 394 00:21:58,516 --> 00:22:05,636 [ Pause ] 395 00:22:06,136 --> 00:22:09,456 I'll skip that the part of the codes that's repetitive. 396 00:22:10,086 --> 00:22:15,686 And you can see I've plugged in exactly the sql statement 397 00:22:15,686 --> 00:22:18,316 that we just saw at the very top, select*, FROM students, 398 00:22:18,316 --> 00:22:21,966 JOIN grades, ON students.id=grades.student. 399 00:22:22,476 --> 00:22:27,386 And we can just go ahead and run. 400 00:22:27,536 --> 00:22:30,286 This is relatively straightforward here 401 00:22:31,516 --> 00:22:42,346 [ Pause ] 402 00:22:42,846 --> 00:22:45,236 And you'll see that we actually get the same data set, 403 00:22:45,236 --> 00:22:47,786 the one difference between the ID column is back 404 00:22:47,786 --> 00:22:54,596 in its first position rather than in the middle of the table. 405 00:22:54,596 --> 00:22:56,236 So, what about this case 406 00:22:56,236 --> 00:22:58,756 where the inner join isn't giving us all the data 407 00:22:58,756 --> 00:22:59,296 that we need? 408 00:22:59,356 --> 00:23:02,976 Now, we can start looking at left and right outer joins. 409 00:23:03,516 --> 00:23:14,236 [ Pause ] 410 00:23:14,736 --> 00:23:19,346 So the left join, we're starting with the same two tables 411 00:23:20,676 --> 00:23:23,426 of left, than physically in the same space. 412 00:23:23,426 --> 00:23:27,116 The students is the left table, grades is the right table. 413 00:23:28,296 --> 00:23:31,276 Now, what do you folks think the difference is here 414 00:23:31,526 --> 00:23:33,106 versus what we talked about with inner joins? 415 00:23:39,636 --> 00:23:44,016 OK. So, the difference is what we're saying 416 00:23:44,016 --> 00:23:48,056 when we specify a left join is that every record 417 00:23:48,276 --> 00:23:51,476 in the left table, whether it has a corresponding record 418 00:23:51,476 --> 00:23:53,966 in the right table, it needs to be displayed. 419 00:23:55,626 --> 00:24:00,246 So in the last example, Alain didn't get listed 420 00:24:00,826 --> 00:24:03,366 because he didn't have any grades. 421 00:24:03,446 --> 00:24:06,836 But now, we're saying that absolutely has to happen. 422 00:24:07,046 --> 00:24:10,706 So, that data set will come back looking like this. 423 00:24:10,706 --> 00:24:13,296 It will suddenly have this null fields 424 00:24:13,356 --> 00:24:15,706 because there is no record to go with it. 425 00:24:17,206 --> 00:24:18,866 Now, who can think of a situation 426 00:24:18,866 --> 00:24:20,536 where this might be useful? 427 00:24:20,536 --> 00:24:20,626 Sure 428 00:24:24,516 --> 00:24:29,216 >> In this very ideas right here where some person comes in 429 00:24:29,216 --> 00:24:31,606 and doesn't have all the information that needs 430 00:24:31,756 --> 00:24:34,306 to be displayed to see all the students. 431 00:24:34,466 --> 00:24:35,046 >> Exactly. 432 00:24:35,046 --> 00:24:36,836 So, as Wester [assumed spelling] said, 433 00:24:37,086 --> 00:24:39,726 in the case where we still need to see all the students, 434 00:24:40,196 --> 00:24:41,956 so if I have a class list and I need to see 435 00:24:41,956 --> 00:24:44,536 who hasn't been turning in their homework, I need to know 436 00:24:44,536 --> 00:24:46,106 who all my students are whether 437 00:24:46,106 --> 00:24:48,186 or not they've actually earned any grades. 438 00:24:48,186 --> 00:24:55,496 So, let's go head and jump over to the sql statement. 439 00:24:56,776 --> 00:24:58,946 Nearly the same as the last example, 440 00:24:59,646 --> 00:25:01,366 the one subtle difference is the addition 441 00:25:01,366 --> 00:25:03,226 of the word LEFT right before the word JOIN 442 00:25:03,946 --> 00:25:05,596 to explicitly say that, "Yes, 443 00:25:05,596 --> 00:25:07,236 we're doing this left outer join." 444 00:25:07,726 --> 00:25:11,556 As a side note, you could actually include the word outer 445 00:25:11,556 --> 00:25:13,816 but it's optional and I didn't feel 446 00:25:13,816 --> 00:25:20,846 like the extra type of thing. 447 00:25:21,056 --> 00:25:24,526 So, coming back to real code. 448 00:25:25,516 --> 00:25:33,156 [ Pause ] 449 00:25:33,656 --> 00:25:40,106 The same idea, we've just added the left to the sql statement. 450 00:25:41,146 --> 00:25:43,706 And as I prematurely ran it, 451 00:25:43,706 --> 00:25:45,616 you'll see that the actual output is just 452 00:25:45,616 --> 00:25:51,166 as we had predicted it would be. 453 00:25:51,416 --> 00:25:54,666 I think I'm actually going to for times sake, skip PDO5. 454 00:25:55,256 --> 00:25:58,306 As you might guess, PDO5 is the right join 455 00:25:58,836 --> 00:26:01,176 which is essentially the same as the left join except your saying 456 00:26:01,176 --> 00:26:03,136 that the right table is the dominant table. 457 00:26:04,066 --> 00:26:07,446 And no matter what you want to include the records 458 00:26:07,566 --> 00:26:11,786 from that table regardless of whether there's data 459 00:26:11,786 --> 00:26:13,436 that corresponds in the left table. 460 00:26:14,246 --> 00:26:18,186 So, I can actually run that just to demonstrate the output 461 00:26:18,186 --> 00:26:22,376 that we would expect there. 462 00:26:27,246 --> 00:26:30,106 Where we get this mystery student 4, 463 00:26:30,106 --> 00:26:31,426 we see their grades come through 464 00:26:31,426 --> 00:26:34,246 but we don't actually see any name information 465 00:26:34,246 --> 00:26:35,876 because they're not in our students table. 466 00:26:36,516 --> 00:26:49,386 [ Pause ] 467 00:26:49,886 --> 00:26:52,076 The other type of join that's 468 00:26:52,076 --> 00:26:54,766 out there is this full outer join. 469 00:26:56,386 --> 00:26:59,896 And basically the idea is, we're saying 470 00:26:59,896 --> 00:27:02,476 that both tables are equally important 471 00:27:03,306 --> 00:27:07,546 and we need every record from both tables no matter what. 472 00:27:08,706 --> 00:27:14,496 So, quite simply, we want this output which is the combination 473 00:27:14,496 --> 00:27:18,786 of both, we have the student that doesn't have any grades, 474 00:27:19,346 --> 00:27:21,506 we've got the grades that don't seem to have any student 475 00:27:22,666 --> 00:27:24,416 and we need all the data to come back. 476 00:27:25,266 --> 00:27:26,546 The reason I put a little asterisk 477 00:27:26,546 --> 00:27:29,336 on this one full outer join is it's not actually supported 478 00:27:29,476 --> 00:27:30,106 by Mysql. 479 00:27:30,436 --> 00:27:32,796 So, although it is an available option in certain database, 480 00:27:32,796 --> 00:27:34,906 is it's not one that we'll be working with. 481 00:27:35,736 --> 00:27:39,626 So, this is an example where if we're using PDO and we've, 482 00:27:40,076 --> 00:27:42,876 you know, we've got this standardize PHP statements now, 483 00:27:43,476 --> 00:27:47,196 we might be using a database at one point 484 00:27:47,196 --> 00:27:49,146 that supports a function, but then we switch 485 00:27:49,146 --> 00:27:50,976 to another database and that functionality is no 486 00:27:50,976 --> 00:27:51,766 longer available. 487 00:27:51,766 --> 00:27:54,336 So, something to be in the back of your mind 488 00:27:54,706 --> 00:27:56,966 when you do change databases, if you do change. 489 00:27:57,306 --> 00:27:57,466 All right. 490 00:28:02,376 --> 00:28:07,846 The next new sql feature that I wanted to talk about is commit. 491 00:28:08,896 --> 00:28:12,826 So basically the idea of commit or transactions in general, 492 00:28:13,806 --> 00:28:16,956 is that we want to specify that either all the commands need 493 00:28:16,956 --> 00:28:18,836 to happen or none of them should happen, 494 00:28:19,196 --> 00:28:22,626 these whole atomic operations. 495 00:28:24,176 --> 00:28:27,096 Specifically in Mysql, we need to specify InnoDB 496 00:28:27,096 --> 00:28:29,346 as our database type to get this functionality. 497 00:28:29,896 --> 00:28:33,456 But then, we can start doing commands like start transaction 498 00:28:33,456 --> 00:28:35,376 and then either commit or roll back. 499 00:28:35,616 --> 00:28:38,056 In everything that happens between those two points, 500 00:28:39,776 --> 00:28:43,336 it will happen as one complete operation. 501 00:28:48,576 --> 00:28:51,806 So, this is very rough code. 502 00:28:52,096 --> 00:28:53,876 This is not tested. 503 00:28:53,876 --> 00:28:56,126 This is just to give you an idea 504 00:28:56,126 --> 00:28:57,456 of what something might look like. 505 00:28:58,296 --> 00:29:01,606 So, we've got a foreach where we're looking at balances 506 00:29:01,656 --> 00:29:04,846 for user ID equals one, not my cleanest code. 507 00:29:04,846 --> 00:29:07,126 I wouldn't recommend putting this in production anywhere. 508 00:29:08,426 --> 00:29:12,846 But I'm taking the balance by getting the balance 509 00:29:12,846 --> 00:29:14,996 out of the row and I'm going to add a hundred to it. 510 00:29:14,996 --> 00:29:18,886 And then, I want to execute this, update the user, 511 00:29:19,236 --> 00:29:21,066 set the balance to this new value. 512 00:29:22,196 --> 00:29:24,546 Now, what's potentially bad about this? 513 00:29:26,346 --> 00:29:26,566 Sure. 514 00:29:26,986 --> 00:29:29,866 >> Same user goes and runs this twice and try 515 00:29:29,866 --> 00:29:32,666 and get their balance up to 200. 516 00:29:32,746 --> 00:29:35,376 >> Right, so the problem is the case where the user tries 517 00:29:35,376 --> 00:29:37,046 to run this twice in parallel. 518 00:29:38,516 --> 00:29:41,846 And then, you know, what's the right value? 519 00:29:41,846 --> 00:29:44,176 If I'm suppose to add a 100 in both cases, 520 00:29:44,496 --> 00:29:46,276 which update gets put in when? 521 00:29:46,656 --> 00:29:48,326 Do they only get $100? 522 00:29:48,326 --> 00:29:50,216 Do they end up with their full $200? 523 00:29:50,216 --> 00:29:51,536 We don't really know because it's-- 524 00:29:52,736 --> 00:29:56,586 there's an opportunity between each statement for execution 525 00:29:56,706 --> 00:29:58,856 to pause and move on to another process. 526 00:30:00,126 --> 00:30:05,876 So, what we wind up doing is using these commands. 527 00:30:05,876 --> 00:30:08,216 So because this is so well-known, 528 00:30:08,216 --> 00:30:09,426 this idea of transactions, 529 00:30:09,426 --> 00:30:11,376 PDO actually supports it native ways. 530 00:30:12,126 --> 00:30:15,186 So, it's got a begin transaction command that you can send 531 00:30:15,186 --> 00:30:18,836 to your database handle and a commit and also rollback 532 00:30:18,836 --> 00:30:21,216 as my guess commands that you can put 533 00:30:21,216 --> 00:30:23,066 in directly into your code. 534 00:30:24,096 --> 00:30:25,976 So, let's see that with some real code 535 00:30:26,516 --> 00:30:37,876 [ Pause ] 536 00:30:38,376 --> 00:30:38,986 All right. 537 00:30:38,986 --> 00:30:44,096 So one thing I-- just as a quick note, as I've packaged 538 00:30:44,096 --> 00:30:45,666 up this little function at the top here, 539 00:30:46,216 --> 00:30:50,756 just so I can run it multiple times, what it's going 540 00:30:50,756 --> 00:30:54,266 to do is show me the results or the current state 541 00:30:54,476 --> 00:30:58,576 of my grades table so that we can see how things are going 542 00:30:58,576 --> 00:30:59,876 along as make changes. 543 00:31:00,446 --> 00:31:02,706 So, I'll start off at the beginning. 544 00:31:04,076 --> 00:31:05,906 Before the update I'll just put the grades, 545 00:31:06,186 --> 00:31:07,606 so we have a base line. 546 00:31:08,276 --> 00:31:11,856 Then, I can begin a transaction and I can update the grades 547 00:31:11,896 --> 00:31:16,746 and I can say, for student 1, I'm going to bump up my grades 548 00:31:16,746 --> 00:31:19,766 to A minus, you know, I only had B and B+ before but, you know, 549 00:31:19,766 --> 00:31:22,206 I got the curves so I'm excited now I got my A minuses. 550 00:31:23,066 --> 00:31:30,156 And I'm going to commit those changes to the database. 551 00:31:34,126 --> 00:31:36,536 Then using my powers as a teaching fellow, 552 00:31:36,536 --> 00:31:37,986 I'm going to say that, you know, 553 00:31:38,486 --> 00:31:41,066 "I don't think David necessarily did 554 00:31:41,066 --> 00:31:42,226 as well as he thought he did. 555 00:31:42,436 --> 00:31:45,636 And I'm going to bring David Malon's [assumed spelling] 556 00:31:45,636 --> 00:31:48,536 grades down to an A minus from the As that he had previously." 557 00:31:48,536 --> 00:31:51,956 But I'm going to wrap that in a transaction as well. 558 00:31:52,646 --> 00:31:54,516 So as soon as I update the values, 559 00:31:54,886 --> 00:31:57,036 I'm going to query the database and see what things look like. 560 00:31:58,106 --> 00:31:59,656 But then, I'm going to have regrets. 561 00:31:59,656 --> 00:32:03,116 I'm going to actually roll back that transaction and then look 562 00:32:03,116 --> 00:32:04,076 at the database again. 563 00:32:04,356 --> 00:32:05,336 So, we've got three points. 564 00:32:05,376 --> 00:32:08,566 Before I do anything, after I've manipulated my grades 565 00:32:08,566 --> 00:32:13,566 but committed them, and then updated David's grades but not 566 00:32:13,626 --> 00:32:15,696 yet determined whether to commit a rollback 567 00:32:16,226 --> 00:32:17,596 in the very end of the application. 568 00:32:18,336 --> 00:32:21,526 So, let's see what happens there. 569 00:32:22,516 --> 00:32:30,576 [ Pause ] 570 00:32:31,076 --> 00:32:33,756 So, we've got a whole full screen of stuff here. 571 00:32:34,446 --> 00:32:37,686 But you can see before we started, the grades were, B, B+, 572 00:32:37,686 --> 00:32:39,486 A, A, C as we had see before. 573 00:32:39,486 --> 00:32:44,846 I had my two updates, the one that I committed for my self 574 00:32:44,846 --> 00:32:47,396 that updated the first two lines to be A-, 575 00:32:47,396 --> 00:32:51,416 and then the uncommitted or rolled back changes 576 00:32:51,486 --> 00:32:56,416 that updated lines three and four and left those at A-. 577 00:32:57,586 --> 00:33:00,866 But then I had my regrets, I rolled back 578 00:33:01,266 --> 00:33:03,106 and David was once again returned to his As. 579 00:33:04,386 --> 00:33:07,266 So, all this code is actually up on bit bucket for you, 580 00:33:07,526 --> 00:33:12,296 I'll make sure I get the URL out as part of getting it posted 581 00:33:12,296 --> 00:33:18,196 with our standard location in the section code. 582 00:33:19,646 --> 00:33:21,366 Yeah. So, any basic questions 583 00:33:21,366 --> 00:33:24,266 about the basic ideas of PDO so far? 584 00:33:24,266 --> 00:33:29,656 All right, great let's move on to the walkthrough. 585 00:33:30,516 --> 00:33:37,636 [ Pause ] 586 00:33:38,136 --> 00:33:41,616 So, our new project is CS 75 finance, 587 00:33:41,616 --> 00:33:43,696 I'm going to talked a little bit about the architecture, 588 00:33:43,696 --> 00:33:46,556 a little bit what you might think about for database design, 589 00:33:47,216 --> 00:33:49,816 show you a little bit about how we actually do this Yahoo 590 00:33:49,816 --> 00:33:50,646 stock quotes. 591 00:33:51,416 --> 00:33:53,986 And then, talk about other considerations 592 00:33:53,986 --> 00:33:55,976 that you might want to think about as you work on a project. 593 00:33:56,016 --> 00:34:04,056 Just as a-- yeah, Just as a quick over view 594 00:34:04,056 --> 00:34:07,886 of the Yahoo stock quotes, you can actually go out to Yahoo 595 00:34:07,886 --> 00:34:12,686 with this URLs going to quotes.csv and do query. 596 00:34:12,686 --> 00:34:15,846 So in this case, I'm searching for Goggle and Yahoo quotes 597 00:34:16,446 --> 00:34:19,826 with this little magical snl1. 598 00:34:19,826 --> 00:34:23,426 And I've broken that out, there's actually if you go 599 00:34:23,426 --> 00:34:27,306 to this gummy-stuff.org site, they've got must be 600 00:34:27,306 --> 00:34:29,496 about 30 different parameters that you can pass in here. 601 00:34:30,666 --> 00:34:32,836 S specifically says show the symbol, 602 00:34:33,296 --> 00:34:35,196 N says specifically show me the name, 603 00:34:35,666 --> 00:34:38,506 and L1 says show me the last trade for the stock. 604 00:34:39,436 --> 00:34:42,616 So, this query would actually bring back a CSV file that has 605 00:34:42,616 --> 00:34:44,806 that information for both Goggle and Yahoo. 606 00:34:45,266 --> 00:34:50,586 But let's with that background get on to the live demo. 607 00:34:51,096 --> 00:34:52,706 All right 608 00:34:53,516 --> 00:35:08,606 [ Pause ] 609 00:35:09,106 --> 00:35:11,826 So, let's start at the beginning. 610 00:35:11,826 --> 00:35:15,466 I'm sticking with this whole MVC concept in sort of expanding 611 00:35:15,466 --> 00:35:16,686 on what David had done. 612 00:35:17,326 --> 00:35:19,876 So I've got a dot HT access file 613 00:35:20,886 --> 00:35:22,146 that looks a little bit different 614 00:35:22,146 --> 00:35:23,516 than the one we had used before. 615 00:35:24,006 --> 00:35:27,266 But some basic ideas really haven't changed. 616 00:35:28,156 --> 00:35:29,856 I'm still turning the rewrite engine on. 617 00:35:30,766 --> 00:35:33,626 I'm specifically using a rewrite base of just slash now 618 00:35:33,626 --> 00:35:35,076 because I'm actually going to work 619 00:35:35,076 --> 00:35:37,346 in our project one directory as oppose to one 620 00:35:37,666 --> 00:35:40,636 of the MVC slash 7 slash, et cetera directories 621 00:35:40,636 --> 00:35:41,736 that David's been working with. 622 00:35:42,296 --> 00:35:44,956 The third line is just going to say 623 00:35:44,956 --> 00:35:50,446 if the URI is index.php, ignore it. 624 00:35:51,416 --> 00:35:53,866 Otherwise, I do this very right. 625 00:35:55,176 --> 00:35:58,306 For simplicity tonight, all you really need 626 00:35:58,306 --> 00:36:01,106 to know is I'm capturing two parameters that end 627 00:36:01,106 --> 00:36:05,476 up being called one and three, and its getting turned 628 00:36:05,476 --> 00:36:10,356 into index.php?page equals the first parameter 629 00:36:10,806 --> 00:36:12,816 and param equals the second parameter. 630 00:36:13,506 --> 00:36:19,606 So, if I have a URL that's project 1/home, it's going to go 631 00:36:19,606 --> 00:36:24,736 to index.php?page=home and param equals nothing. 632 00:36:25,386 --> 00:36:31,686 But if I want to a page that's project1/"/GOOG, I can say I go 633 00:36:31,686 --> 00:36:37,786 to the index.php?page="param=GOOG 634 00:36:38,106 --> 00:36:40,416 and then presumably actually get a stock quote 635 00:36:40,466 --> 00:36:41,826 for Goggle base on the URL. 636 00:36:41,826 --> 00:36:52,806 From dot HT access, we jump over to index.php, 637 00:36:57,766 --> 00:36:59,476 which is just taking this parameters 638 00:36:59,576 --> 00:37:03,366 and deciding what controller it's load. 639 00:37:03,626 --> 00:37:07,066 So, if the page is set, it's going to use that as the name 640 00:37:07,066 --> 00:37:10,246 of my controller and it's actually going to load 641 00:37:10,246 --> 00:37:13,426 that file, ultimately, through this require of statement. 642 00:37:14,376 --> 00:37:18,136 If it's not set, it's going to default to home as the page. 643 00:37:18,516 --> 00:37:20,026 So no matter what, people end 644 00:37:20,026 --> 00:37:24,246 up at my home page unless they know where they're going. 645 00:37:24,416 --> 00:37:29,766 Leave it to as an exercise to a sort through the rest of that, 646 00:37:29,766 --> 00:37:32,646 but it's basically just the dispatcher 647 00:37:32,646 --> 00:37:34,406 for MVC at this point. 648 00:37:37,176 --> 00:37:41,196 The other magical piece is actually code 649 00:37:41,196 --> 00:37:43,976 that I took pretty much straight from David. 650 00:37:44,516 --> 00:37:54,566 [ Pause ] 651 00:37:55,066 --> 00:37:57,136 And that's the surrender function that he had, 652 00:37:58,126 --> 00:38:00,386 which basically is for rendering my views. 653 00:38:00,646 --> 00:38:02,436 So, a controller can say render 654 00:38:02,786 --> 00:38:04,486 with this template and this data. 655 00:38:04,486 --> 00:38:07,196 The data is automatically expanded 656 00:38:07,196 --> 00:38:09,416 from an associative array into a series 657 00:38:09,416 --> 00:38:11,806 of actual variables using the extract command. 658 00:38:12,726 --> 00:38:14,946 And although my name is on the top of this, 659 00:38:14,946 --> 00:38:18,476 this really is essentially David's work, almost unchanged. 660 00:38:19,516 --> 00:38:30,456 [ Pause ] 661 00:38:30,956 --> 00:38:34,546 So, given all of that, I've come to this MVC model again. 662 00:38:35,026 --> 00:38:37,476 This is my controller for home. 663 00:38:37,716 --> 00:38:39,786 This is the base page that I go to. 664 00:38:40,396 --> 00:38:43,386 It includes that helper functions, so it knows how 665 00:38:43,386 --> 00:38:45,116 to actually render views after work. 666 00:38:46,046 --> 00:38:47,966 And basically, all its going to do is look 667 00:38:47,966 --> 00:38:51,046 at my current session, see if a user ID has been set 668 00:38:51,046 --> 00:38:53,926 and then decide which view does the user get to see. 669 00:38:54,646 --> 00:38:57,036 Do they get to see the home page or do they actually have 670 00:38:57,036 --> 00:38:58,346 to go to a log in page? 671 00:38:59,266 --> 00:39:01,146 And you can see where we're actually getting to the point 672 00:39:01,146 --> 00:39:03,766 where using this model view control or architecture, 673 00:39:04,336 --> 00:39:06,726 potentially, things are very straightforward to look at. 674 00:39:06,866 --> 00:39:11,716 I've got all about five functioning lines of code 675 00:39:12,286 --> 00:39:19,116 that handles the entire controller for this page. 676 00:39:20,756 --> 00:39:21,156 All right. 677 00:39:21,196 --> 00:39:23,856 And let's see what some of those view components look 678 00:39:23,856 --> 00:39:25,516 like so we can put the pieces together. 679 00:39:26,516 --> 00:39:34,356 [ Pause ] 680 00:39:34,856 --> 00:39:37,036 So, one of the ones that we just saw referred 681 00:39:37,036 --> 00:39:39,166 to as this login.php, and this is 682 00:39:39,256 --> 00:39:40,596 where things are actually getting a little 683 00:39:40,596 --> 00:39:41,296 more interesting. 684 00:39:42,076 --> 00:39:42,436 Who knows? 685 00:39:42,436 --> 00:39:45,116 We hadn't really seen any html yet, but now that we're 686 00:39:45,116 --> 00:39:46,786 in the view, it's the right time 687 00:39:46,786 --> 00:39:48,446 to start seeing the html code appear. 688 00:39:49,026 --> 00:39:53,996 So, I'm going to render a header and look at that shortly. 689 00:39:54,746 --> 00:39:57,666 Then, I'm going to start putting things together. 690 00:39:57,666 --> 00:39:59,546 So, I want people to actually log in. 691 00:39:59,766 --> 00:40:00,976 So, it sounds like I need a form. 692 00:40:02,026 --> 00:40:04,316 I've just created two fields for now, an email address 693 00:40:04,316 --> 00:40:06,326 and a password, but that sort of fits with the spec 694 00:40:06,326 --> 00:40:07,826 that were working with for this project. 695 00:40:08,396 --> 00:40:13,916 And then, I've started adding a little more. 696 00:40:13,916 --> 00:40:16,256 So, we haven't really started talking about JavaScript yet 697 00:40:16,256 --> 00:40:19,956 but I wanted to have some place holders in the example as we get 698 00:40:19,956 --> 00:40:21,666 to that in the coming days here. 699 00:40:22,906 --> 00:40:28,256 So, I've done two little things, this validate form 700 00:40:29,366 --> 00:40:31,636 which just a short explanation. 701 00:40:32,106 --> 00:40:35,986 Basically, what I'd like to do is before the user even takes a 702 00:40:35,986 --> 00:40:37,786 trip out to the web server to do any work, 703 00:40:38,366 --> 00:40:41,546 I want to start doing some work on the client side to make sure 704 00:40:41,546 --> 00:40:43,486 that things are OK before you even go through the effort. 705 00:40:44,566 --> 00:40:47,776 So, this very, very short example of validate form, 706 00:40:47,836 --> 00:40:51,126 all it's going to do is look at the fields 707 00:40:51,186 --> 00:40:52,916 that have been filled in, it's only going 708 00:40:52,916 --> 00:40:53,946 to look at the email field. 709 00:40:54,396 --> 00:40:55,876 And what I want to do is just check 710 00:40:55,876 --> 00:40:59,046 that at least 6 characters have been entered, 711 00:40:59,916 --> 00:41:04,656 the idea of being-- if you were able to get the address, x@x.to, 712 00:41:04,656 --> 00:41:06,916 that was the shortest email address I could think 713 00:41:06,916 --> 00:41:08,246 of that was possibly going to be valid. 714 00:41:08,726 --> 00:41:10,576 So, if you haven't put in at least 6 characters, 715 00:41:10,966 --> 00:41:13,896 I didn't want to worry about it. 716 00:41:14,256 --> 00:41:17,516 The explanation of this will start coming Wednesday as we get 717 00:41:17,516 --> 00:41:19,146 in to JavaScript jQuery. 718 00:41:19,756 --> 00:41:22,746 This is just here for as you move forward at the project. 719 00:41:23,216 --> 00:41:27,576 The other little tweak that you'll see 720 00:41:27,576 --> 00:41:29,736 in here is the statement here, 721 00:41:30,386 --> 00:41:32,566 input name equals email dot focus. 722 00:41:33,806 --> 00:41:35,806 Basically when the page first loads, 723 00:41:36,806 --> 00:41:40,016 the cursor doesn't have any focus, this is ultimately going 724 00:41:40,016 --> 00:41:43,096 to say move that cursor into the email fields 725 00:41:43,096 --> 00:41:44,346 so the person can start typing. 726 00:41:45,036 --> 00:41:46,996 Again, another one of those details, you don't have to worry 727 00:41:46,996 --> 00:41:49,666 about tonight but a little preview 728 00:41:49,666 --> 00:41:51,196 for what you might start seeing on Wednesday. 729 00:41:51,196 --> 00:41:54,936 And then lastly, will include the footer. 730 00:41:55,786 --> 00:41:59,426 So basically, all this template has done is created a form 731 00:41:59,426 --> 00:41:59,946 on the screen. 732 00:42:00,296 --> 00:42:03,046 And I can actually show you what that looks like just 733 00:42:03,046 --> 00:42:04,536 so you have a little sample. 734 00:42:04,966 --> 00:42:07,976 This is actually that page. 735 00:42:09,106 --> 00:42:12,206 We saw the email address, you see it has actually right now. 736 00:42:12,666 --> 00:42:13,546 We saw the password. 737 00:42:13,546 --> 00:42:15,616 We saw the log in button as part of the form. 738 00:42:16,606 --> 00:42:18,976 There's also this little link home page at the top 739 00:42:18,976 --> 00:42:25,466 which we haven't seen yet, that's actually going to come 740 00:42:25,466 --> 00:42:26,656 out of the standard header. 741 00:42:32,696 --> 00:42:35,496 So, this should look relatively straightforward except 742 00:42:35,496 --> 00:42:37,256 for the one line that says script. 743 00:42:37,836 --> 00:42:43,206 And again, as we start getting into JavaScript on Wednesday, 744 00:42:43,296 --> 00:42:44,576 that line will make sense. 745 00:42:44,706 --> 00:42:47,526 But otherwise you'll see, I'm opening my html in my head, 746 00:42:48,146 --> 00:42:50,446 plug in the title using the same approach 747 00:42:50,446 --> 00:42:52,656 that David had use the last time he did MVC. 748 00:42:53,466 --> 00:42:58,226 And I've created one link at the top of the body there that says 749 00:42:58,316 --> 00:43:01,156 if you click on home page it just going to go to slash 750 00:43:01,156 --> 00:43:05,226 on the site which translates to http://project1/. 751 00:43:05,426 --> 00:43:13,666 All this code is also up on my bit bucket account. 752 00:43:13,666 --> 00:43:16,676 So, when that link is published on the home page for the class, 753 00:43:16,676 --> 00:43:18,916 you'll also have access to all of this to review as well. 754 00:43:19,466 --> 00:43:27,106 And the footer is even less convoluted. 755 00:43:27,106 --> 00:43:28,916 All I'm doing is closing my basic text. 756 00:43:29,516 --> 00:43:40,856 [ Pause ] 757 00:43:41,356 --> 00:43:45,126 The other option, if the user was already logged 758 00:43:45,126 --> 00:43:48,376 in to the site, I want to show them a little information 759 00:43:48,556 --> 00:43:49,146 on the site. 760 00:43:49,676 --> 00:43:51,836 So, I've just got a few sample links just 761 00:43:51,836 --> 00:43:52,906 to get things started here. 762 00:43:53,476 --> 00:43:54,766 You might think about how you might build 763 00:43:54,766 --> 00:43:57,926 out this project later to include more details here. 764 00:43:58,626 --> 00:44:00,316 I've specifically card quoted 765 00:44:00,316 --> 00:44:02,116 that you can request a quote for Goggle. 766 00:44:03,056 --> 00:44:05,316 Obviously for your project, do you want some more flexibility 767 00:44:05,316 --> 00:44:06,796 than just one stock symbol? 768 00:44:07,736 --> 00:44:10,816 I've provided a link so I can actually view my portfolio. 769 00:44:11,736 --> 00:44:13,886 And I've provided the opportunity to log out. 770 00:44:14,776 --> 00:44:16,706 You'll want to start thinking about things 771 00:44:17,116 --> 00:44:20,676 like how will they buy a stock, how will they sell a stock, 772 00:44:21,586 --> 00:44:23,416 do you need separate pages for those. 773 00:44:23,466 --> 00:44:27,116 If some ones adequate page already, is that the time to buy 774 00:44:27,116 --> 00:44:28,866 or do you want a separate purchase page? 775 00:44:29,336 --> 00:44:31,226 If they're already looking at their portfolio, 776 00:44:31,226 --> 00:44:32,986 is that the time to sell stocks 777 00:44:32,986 --> 00:44:34,646 or should there be a separate page for selling? 778 00:44:35,536 --> 00:44:37,956 Some design considerations you can start thinking about there. 779 00:44:38,516 --> 00:44:48,456 [ Pause ] 780 00:44:48,956 --> 00:44:49,416 All right. 781 00:44:49,416 --> 00:44:54,496 So, let's start looking at something real. 782 00:44:54,496 --> 00:44:58,546 Let's get a quick view of what the start 783 00:44:58,546 --> 00:44:59,976 of the portfolio might look like. 784 00:45:00,516 --> 00:45:07,286 [ Pause ] 785 00:45:07,786 --> 00:45:08,166 All right. 786 00:45:08,166 --> 00:45:11,006 So the portfolio control, if I click at my portfolio link, 787 00:45:11,606 --> 00:45:13,796 what is the controller going to do? 788 00:45:13,926 --> 00:45:16,296 The controller really just wants to talk to the model 789 00:45:16,296 --> 00:45:19,156 and pass any information out to the view controller. 790 00:45:19,686 --> 00:45:23,936 So basically, the sense of being relatively straightforward, 791 00:45:24,396 --> 00:45:30,796 if the users logged in with session users ID as set, then. 792 00:45:30,796 --> 00:45:32,906 I'm going to get the user ID from the session 793 00:45:33,596 --> 00:45:35,956 so you can think about how we've been the past use the session 794 00:45:35,956 --> 00:45:37,326 and stored a variable in there, 795 00:45:37,836 --> 00:45:39,586 somewhere I must be storing this value. 796 00:45:40,146 --> 00:45:43,396 Then, I'm going to get the users holdings. 797 00:45:43,876 --> 00:45:47,086 And this is just one call out to the model in this approach. 798 00:45:47,566 --> 00:45:50,976 Your holding is just get users shares for your user ID, 799 00:45:50,976 --> 00:45:53,556 and then we use render to throw it out to the view. 800 00:45:54,466 --> 00:45:57,186 So, the controller ends up just being the glue in this case. 801 00:45:57,696 --> 00:46:00,386 All it knows is I want to talk to the model to get the, 802 00:46:00,446 --> 00:46:02,926 and as soon as I have it, I don't want to think anything 803 00:46:02,926 --> 00:46:05,526 about html, I just want to get it off to view to deal with. 804 00:46:06,066 --> 00:46:08,916 So, this ends up being the entire controller 805 00:46:08,976 --> 00:46:10,576 relatively straightforward. 806 00:46:11,026 --> 00:46:14,766 One thing I'll point out here, you know, 807 00:46:15,396 --> 00:46:18,026 we've already seen one other place in my code were I check 808 00:46:18,136 --> 00:46:21,376 if the session user ID is set, 809 00:46:22,516 --> 00:46:24,676 perhaps I'm not doing the right thing here. 810 00:46:24,676 --> 00:46:27,236 Do I really want to check that on every single page? 811 00:46:27,236 --> 00:46:28,296 Is that a helper function 812 00:46:28,296 --> 00:46:30,416 that should be caught at a higher level? 813 00:46:30,596 --> 00:46:32,086 Things like that that you can consider. 814 00:46:32,306 --> 00:46:33,836 I don't know that I want to-- 815 00:46:34,096 --> 00:46:36,746 have to remember to in every file cut 816 00:46:36,746 --> 00:46:39,656 and paste the same in session user ID. 817 00:46:39,656 --> 00:46:42,056 And if not, render log in. 818 00:46:44,876 --> 00:46:48,936 All right, so to get to the real meat of all this, 819 00:46:49,056 --> 00:46:52,916 we finally make it down to the model again. 820 00:46:53,776 --> 00:46:54,976 This is the same model 821 00:46:54,976 --> 00:46:56,816 that we've have looked just shortly ago 822 00:46:56,816 --> 00:47:01,726 when we we're looking at an example of how we used 823 00:47:01,726 --> 00:47:03,806 to do things, this Mysql statements. 824 00:47:04,276 --> 00:47:07,236 I've left this in there. 825 00:47:07,236 --> 00:47:10,626 This is basically one of the approaches that we've used 826 00:47:10,626 --> 00:47:13,826 in class for authenticating a user against your database 827 00:47:13,866 --> 00:47:17,586 where you take the idea of selecting by email and password. 828 00:47:18,676 --> 00:47:22,196 If you get a row, the users have the right password 829 00:47:22,196 --> 00:47:24,546 and has a valid account and you can use that to log them in. 830 00:47:25,086 --> 00:47:30,126 But at the moment, we're talking about portfolio. 831 00:47:30,126 --> 00:47:36,616 So, let's actually go forward a little further and get a sense 832 00:47:36,616 --> 00:47:39,316 of what something like this might start to look like. 833 00:47:40,206 --> 00:47:45,026 So the portfolio, a user has some collection of stocks, 834 00:47:45,496 --> 00:47:47,786 they have some cash balance. 835 00:47:48,806 --> 00:47:51,706 I'm going down to a very basic example here and I'm only going 836 00:47:51,706 --> 00:47:53,516 to look at what stocks they have. 837 00:47:53,766 --> 00:47:55,556 So, you'll want to think about how do you expand this 838 00:47:55,556 --> 00:47:59,146 as you start developing the project to include everything 839 00:47:59,146 --> 00:48:01,156 that truly belongs in a user's portfolio. 840 00:48:01,726 --> 00:48:05,366 So, we got our new PDO syntax. 841 00:48:05,936 --> 00:48:09,826 We're connecting to the host, selecting the database, 842 00:48:11,066 --> 00:48:13,166 log in with user name and password, all of basically 843 00:48:13,166 --> 00:48:14,676 in those first two lines of code. 844 00:48:16,126 --> 00:48:17,486 Then, we're going to prepare a statement. 845 00:48:18,936 --> 00:48:21,206 Because the user ID is coming from someone else, 846 00:48:21,206 --> 00:48:23,116 I like the idea of the prepared statement here, 847 00:48:23,116 --> 00:48:25,536 it's going to protect me, it's going to do that wrapper 848 00:48:25,536 --> 00:48:27,616 of making sure that anything that needs 849 00:48:27,616 --> 00:48:28,826 to be escaped is escaped. 850 00:48:28,826 --> 00:48:31,396 So I'm going to buy in that value in 851 00:48:32,496 --> 00:48:34,116 and then I can execute the query. 852 00:48:34,556 --> 00:48:39,666 Once the query executes, I'm going to get back a result set. 853 00:48:41,286 --> 00:48:48,146 For each of the rows that I get, I'm going to do-- 854 00:48:48,396 --> 00:48:50,546 instead of doing the 4H approach like last time, 855 00:48:50,546 --> 00:48:51,696 I actually took a different approach. 856 00:48:51,746 --> 00:48:54,156 I'm using the statement and a fetch 857 00:48:54,216 --> 00:48:56,266 to get each row that was returned. 858 00:48:56,766 --> 00:49:00,496 And as I get them, I'm just going to push those results 859 00:49:00,496 --> 00:49:01,976 into an array so I can return it. 860 00:49:01,976 --> 00:49:04,836 So, what is this going to look like?. 861 00:49:05,556 --> 00:49:11,946 We're selecting symbols in shares, so we're basically going 862 00:49:11,946 --> 00:49:16,286 to end up with an array set that is for each row in the array, 863 00:49:16,336 --> 00:49:22,156 I'm going to have the symbol and the value, the shares 864 00:49:22,156 --> 00:49:23,866 and the value in an associated array, 865 00:49:24,186 --> 00:49:26,116 so an array of associative arrays. 866 00:49:26,636 --> 00:49:29,596 And I'm just going to return that to the users. 867 00:49:30,676 --> 00:49:34,296 If the statement didn't execute my database handle, 868 00:49:35,126 --> 00:49:41,216 just goes to null and you'll see a return no data. 869 00:49:42,006 --> 00:49:43,916 So, this is basic idea of how I might go 870 00:49:43,916 --> 00:49:47,046 out to the database using PDO, select a few rows 871 00:49:47,706 --> 00:49:52,826 and return an array back to the controller vaguely make sense? 872 00:49:53,516 --> 00:49:57,346 Are there any questions about it? 873 00:49:58,896 --> 00:50:01,306 All right, no questions yet. 874 00:50:06,776 --> 00:50:08,406 So once we've gotten this, we already saw 875 00:50:08,406 --> 00:50:10,666 that the controller is really just passing along, 876 00:50:10,666 --> 00:50:12,436 so we can jump right over to the view. 877 00:50:13,516 --> 00:50:19,916 [ Pause ] 878 00:50:20,416 --> 00:50:23,746 So, what are we doing with all this data that we've got? 879 00:50:24,046 --> 00:50:26,706 I've decided that I'm actually going to make a small table. 880 00:50:27,436 --> 00:50:28,836 It's going to have two columns. 881 00:50:29,196 --> 00:50:32,826 I'm using that table heading to bold those two-- 882 00:50:33,066 --> 00:50:36,426 the headers on the two columns, so it's just symbols and shares. 883 00:50:37,376 --> 00:50:38,906 I'm going to loop over the array 884 00:50:38,906 --> 00:50:41,346 that have been passed back directly 885 00:50:41,346 --> 00:50:42,686 as it come right out of the model. 886 00:50:43,786 --> 00:50:47,346 And for each one, I'm going to html special care 887 00:50:47,346 --> 00:50:51,036 as to clean it up, but basically put it in a table data 888 00:50:51,036 --> 00:50:54,676 within the table rows, pulling things out by the names 889 00:50:54,716 --> 00:50:57,556 because I've got this associative arrays inside there. 890 00:50:59,676 --> 00:51:02,886 And that is roughly it, 891 00:51:02,886 --> 00:51:07,296 relatively straightforward here before I put the footer on. 892 00:51:07,716 --> 00:51:10,246 So, let me show you what that actually looks like just 893 00:51:10,246 --> 00:51:11,316 to give you some context. 894 00:51:11,856 --> 00:51:17,566 I'm going to jump ahead and actually log 895 00:51:20,096 --> 00:51:21,626 in so I can do view portfolio. 896 00:51:23,616 --> 00:51:25,306 So apparently in the database, 897 00:51:25,306 --> 00:51:30,036 this user had two actual holdings, 898 00:51:30,756 --> 00:51:33,576 one was the symbol GOOG for 10 shares, one was the symbol 899 00:51:33,576 --> 00:51:35,406 for Yahoo with a 100 shares. 900 00:51:36,516 --> 00:51:38,266 In fact, that's not the real symbol for Yahoo. 901 00:51:38,266 --> 00:51:43,336 That was a typo when I created the data. 902 00:51:43,506 --> 00:51:46,496 But basically very straightforward, and if we look 903 00:51:46,496 --> 00:51:48,526 at the page source, you'll see that the page-- 904 00:51:49,516 --> 00:51:57,706 [ Pause ] 905 00:51:58,206 --> 00:52:02,306 -- I really haven't gotten too complex with my code here. 906 00:52:02,876 --> 00:52:10,066 All right. 907 00:52:10,506 --> 00:52:14,196 So, the one other thing I want 908 00:52:14,196 --> 00:52:18,486 to just show you was what David just started alluding 909 00:52:18,486 --> 00:52:22,636 to at the end of class as well, and that is the quotes. 910 00:52:23,016 --> 00:52:25,366 So, we can look at the controller for quotes. 911 00:52:25,876 --> 00:52:30,856 And this is again very short quote 912 00:52:31,826 --> 00:52:33,366 because all the work is really being done 913 00:52:33,366 --> 00:52:35,686 by the model and the view. 914 00:52:35,906 --> 00:52:39,046 Basically, I'm going to say if a param was passed in, 915 00:52:39,636 --> 00:52:43,196 I'm going to call this get quote data 916 00:52:44,466 --> 00:52:48,886 with the parameter, urlencoded. 917 00:52:49,976 --> 00:52:55,436 And then whatever comes back, I'm passing directly 918 00:52:55,436 --> 00:52:56,616 out to the view for rendering. 919 00:52:56,956 --> 00:52:59,976 So, let's see what's really going on in the model here. 920 00:53:00,516 --> 00:53:13,396 [ Pause ] 921 00:53:13,896 --> 00:53:15,046 All right. 922 00:53:15,956 --> 00:53:19,176 So, this code probably looks very familiar, it's very similar 923 00:53:19,176 --> 00:53:21,616 to what David just showed a few minutes ago. 924 00:53:22,506 --> 00:53:25,266 I'm being passed and assembled. 925 00:53:26,756 --> 00:53:28,486 I'm creating a result array just 926 00:53:28,486 --> 00:53:30,876 to store things in, nothing in their yet. 927 00:53:32,176 --> 00:53:34,886 And I've got this URL to actually connect to Yahoo. 928 00:53:35,476 --> 00:53:38,836 The one special piece of data that I've plugged 929 00:53:38,836 --> 00:53:41,516 in is the symbol that was actually passed in. 930 00:53:42,266 --> 00:53:42,966 I'll get the pointer. 931 00:53:43,826 --> 00:53:47,476 So I've got the symbol here. 932 00:53:48,016 --> 00:53:53,026 I've also specified this which we saw in the slides earlier 933 00:53:53,976 --> 00:53:59,626 that sl1&n, s being the symbol, 934 00:53:59,656 --> 00:54:04,836 l1 being the last trade value and n being the name. 935 00:54:05,286 --> 00:54:09,916 What I want to do then is I can actually open this URL 936 00:54:09,916 --> 00:54:13,396 as if it was a file directly with be an F open command. 937 00:54:13,866 --> 00:54:19,366 Once I've a handle to that file, CSV is one of the things 938 00:54:19,366 --> 00:54:22,956 that PHP knows how to work with, so I can one row 939 00:54:22,956 --> 00:54:28,676 at a time use fgetcsv to pull the CSV data directly 940 00:54:28,926 --> 00:54:32,256 into rows that I can work with. 941 00:54:36,626 --> 00:54:42,596 If the row, row value one in this case is set, then, 942 00:54:42,596 --> 00:54:45,176 I know I've got data, I found the last traded value. 943 00:54:46,686 --> 00:54:49,216 So what I'm going to do is I'm actually going 944 00:54:49,216 --> 00:54:54,756 to create an associative array with the symbol set 945 00:54:54,756 --> 00:54:56,856 to the first parameter which was always the symbol, 946 00:54:57,216 --> 00:55:01,176 the last trade set to the second parameter was the l1 is the last 947 00:55:01,176 --> 00:55:05,906 trade, and the name set to the third parameter which was the n 948 00:55:06,556 --> 00:55:08,006 from the URL that we used. 949 00:55:09,196 --> 00:55:12,766 I set this us specifically so it just pulls down a single symbol 950 00:55:12,766 --> 00:55:16,066 at a time, although the APIs do allow us to pull down multiples, 951 00:55:16,066 --> 00:55:18,616 and that's something that you want to consider as well. 952 00:55:18,616 --> 00:55:19,746 In this case I really-- 953 00:55:20,026 --> 00:55:23,386 I was only concern about returning one result. 954 00:55:24,156 --> 00:55:28,136 But when your working later, say for example you're going 955 00:55:28,136 --> 00:55:30,406 to be generating the value of someone's portfolio, 956 00:55:31,036 --> 00:55:33,696 does it make sense for each stock symbol 957 00:55:34,096 --> 00:55:36,306 to take another trip out to the Yahoo server to get 958 00:55:36,306 --> 00:55:39,276 that quotes-- that symbols phrase, or do you want 959 00:55:39,276 --> 00:55:41,776 to bunch them up and get a number of symbols back 960 00:55:41,776 --> 00:55:46,056 at one shot so you might get 5 or 10 current stock raises 961 00:55:46,056 --> 00:55:47,876 from Yahoo in a single transaction rather 962 00:55:47,876 --> 00:55:49,376 than doing multiple transactions? 963 00:55:49,826 --> 00:55:51,006 So things to consider their. 964 00:55:52,526 --> 00:55:55,926 Once I've got the data, I close my handle and I return it. 965 00:55:59,776 --> 00:56:02,966 And that data can then be pass off to the view. 966 00:56:03,516 --> 00:56:16,256 [ Pause ] 967 00:56:16,756 --> 00:56:19,696 So, one little tweak you'll see at the top of this file 968 00:56:19,696 --> 00:56:23,726 that I haven't done in other files, is it possible 969 00:56:23,726 --> 00:56:27,736 to call this page and not have valid data come back? 970 00:56:27,966 --> 00:56:31,006 So, either someone picked a stock symbol that didn't exist 971 00:56:31,106 --> 00:56:33,106 or Yahoo service was unavailable 972 00:56:33,106 --> 00:56:34,596 and I just couldn't get data at the moment. 973 00:56:35,376 --> 00:56:37,856 It's possible that things will go wrong. 974 00:56:38,566 --> 00:56:41,466 So, I've got some notification to the user if no data has come 975 00:56:41,466 --> 00:56:43,896 in to the view that either the symbol wasn't provided 976 00:56:43,896 --> 00:56:45,196 or the quote data wasn't found. 977 00:56:46,036 --> 00:56:48,206 But assuming that I do actually get my data back, 978 00:56:49,436 --> 00:56:52,546 I've done a short table again scrolling off the bottom 979 00:56:52,546 --> 00:56:58,766 in the screen here, and following the same templets 980 00:56:58,766 --> 00:57:00,076 as I did with portfolio. 981 00:57:00,186 --> 00:57:03,306 I'm just taking the three pieces of data and put them 982 00:57:03,306 --> 00:57:07,926 into that table row and closing up the table on the screen 983 00:57:08,096 --> 00:57:10,396 and I can show you what that looks like as well. 984 00:57:11,516 --> 00:57:17,586 [ Pause ] 985 00:57:18,086 --> 00:57:21,866 It's a very straightforward little table, just a symbol name 986 00:57:21,866 --> 00:57:25,096 and last trade and the data's pulled directly from the model, 987 00:57:25,096 --> 00:57:27,836 pass through the controller and displayed on the view here. 988 00:57:28,446 --> 00:57:36,386 So, that was the code I was going to review tonight. 989 00:57:36,386 --> 00:57:38,036 There's one other file in there, 990 00:57:38,036 --> 00:57:42,026 you'll see it's called pwd hash dot php. 991 00:57:42,026 --> 00:57:46,276 If you try to log in and your log in credentials aren't valid, 992 00:57:46,276 --> 00:57:48,156 it will actually show you the password hash 993 00:57:48,156 --> 00:57:49,806 or whatever you just try to log in with. 994 00:57:50,466 --> 00:57:53,526 So in that, until you actually write your register function, 995 00:57:53,676 --> 00:57:55,526 you can find out what the password is 996 00:57:55,526 --> 00:57:57,196 and you can push them with the database, 997 00:57:57,196 --> 00:58:00,006 you can actually keep moving along with your code 998 00:58:00,006 --> 00:58:02,106 and before you finish all your code. 999 00:58:02,756 --> 00:58:06,726 So, a few things that I want to make sure 1000 00:58:06,726 --> 00:58:08,966 that I got some design considerations 1001 00:58:08,966 --> 00:58:09,706 out there for you. 1002 00:58:09,706 --> 00:58:11,106 I've have mention a few as we went. 1003 00:58:11,106 --> 00:58:15,046 We talked about how many connections should you make 1004 00:58:15,046 --> 00:58:16,356 out to yahoo when you're trying 1005 00:58:16,356 --> 00:58:19,376 to calculate the valuable portfolio, so something to think 1006 00:58:19,376 --> 00:58:22,176 about there as a multiple connection, single connections. 1007 00:58:23,466 --> 00:58:25,986 What happens if the user actually try to sell stocks 1008 00:58:26,276 --> 00:58:28,376 from two different browser simultaneously? 1009 00:58:28,866 --> 00:58:30,326 What do you want to do about that? 1010 00:58:31,096 --> 00:58:35,336 What happens if the user buys additional shares of a stock 1011 00:58:35,336 --> 00:58:36,466 that they were already own? 1012 00:58:36,736 --> 00:58:40,696 And David hinted strongly at how you might consider that earlier 1013 00:58:40,696 --> 00:58:41,536 in the lecture tonight? 1014 00:58:42,096 --> 00:58:47,736 As we get into the JavaScript, you want to start thinking 1015 00:58:47,736 --> 00:58:51,066 about what validation do you want to do on the browser 1016 00:58:51,356 --> 00:58:54,196 and what validation do you want to do on the server. 1017 00:58:55,486 --> 00:58:58,016 If the user hasn't entered a password at all, 1018 00:58:58,756 --> 00:59:00,976 you can probably save yourself a trip to the server 1019 00:59:01,456 --> 00:59:02,666 and validate on the browser. 1020 00:59:03,806 --> 00:59:05,936 But if you manage to get pass that check 1021 00:59:05,936 --> 00:59:07,456 or the user doesn't run JavaScript, 1022 00:59:07,456 --> 00:59:09,636 you probably still need to confirm some things 1023 00:59:09,636 --> 00:59:12,056 on the server side as well, possibly the same things 1024 00:59:12,056 --> 00:59:14,116 that you thought you were confirming on the browser side. 1025 00:59:15,006 --> 00:59:17,226 So, something to think about is we're going to Wednesday there. 1026 00:59:18,646 --> 00:59:22,766 And one other thing that isn't obvious, but if you look 1027 00:59:22,766 --> 00:59:26,636 at my code-- this page is available right now, 1028 00:59:26,636 --> 00:59:28,026 this quote slash GOOG. 1029 00:59:28,576 --> 00:59:33,606 But if I logged out of the application, 1030 00:59:34,296 --> 00:59:36,176 the page is still actually visible. 1031 00:59:36,416 --> 00:59:42,006 So, think about things like this, does it make sense for me 1032 00:59:42,006 --> 00:59:45,886 to be able to still look up quotes even if I'm not logged 1033 00:59:45,886 --> 00:59:48,636 in to the application or should all the pages? 1034 00:59:48,636 --> 00:59:51,096 Or what subset of the pages should be restricted 1035 00:59:51,096 --> 00:59:52,456 to just authenticated users? 1036 00:59:53,646 --> 00:59:56,626 So, those are the few thoughts I had to inspire you 1037 00:59:56,626 --> 00:59:57,506 as you work through this. 1038 00:59:57,546 --> 00:59:59,846 Are there any questions on the project so far? 1039 01:00:00,286 --> 01:00:01,986 Just as a quick reminder, I'll be sticking 1040 01:00:01,986 --> 01:00:04,306 around for office hours for as long as folks need tonight. 1041 01:00:04,346 --> 01:00:08,336 And one of the TFs will be online office hours at 8. 1042 01:00:08,466 --> 01:00:10,576 But certainly, any questions right now, I'm happy to answer. 1043 01:00:11,096 --> 01:00:14,636 All right. 1044 01:00:14,636 --> 01:00:16,536 Well I thank every one for their time and thanks 1045 01:00:16,606 --> 01:00:19,266 to the folks watching this remotely. 1046 01:00:19,766 --> 01:00:20,996 And good luck. 1047 01:00:21,206 --> 01:00:23,696 Remember to watch for our posting on how 1048 01:00:23,696 --> 01:00:26,636 to submit you're project zero tomorrow 1049 01:00:26,726 --> 01:00:28,566 so that we can get those submitted Wednesday. 1050 01:00:29,906 --> 01:00:33,846 Thanks. Good night everyone. 1051 01:00:34,516 --> 01:00:36,516 [ Applause ] 1052 01:00:37,016 --> 01:00:44,856 [ Silence ]