1 00:00:00,000 --> 00:00:12,800 >> [MUSIC PLAYING] 2 00:00:12,800 --> 00:00:13,410 >> ROB BOWDEN: Hi. 3 00:00:13,410 --> 00:00:17,150 I'm Rob, and I hope you're ready to put stock in this solution. 4 00:00:17,150 --> 00:00:19,740 First, let's take a look at register. 5 00:00:19,740 --> 00:00:24,400 So remember that here we're checking to see if a form was 6 00:00:24,400 --> 00:00:26,200 posted to this page. 7 00:00:26,200 --> 00:00:29,170 >> So first thing we're going to do is go to the else. 8 00:00:29,170 --> 00:00:31,650 And we're going to render the register form. 9 00:00:31,650 --> 00:00:37,320 So the register form is going to post to register.PHP. 10 00:00:37,320 --> 00:00:38,760 And what is it going to send? 11 00:00:38,760 --> 00:00:44,510 It's going to send a username that the user is going to fill out, a password, 12 00:00:44,510 --> 00:00:46,050 and a confirmation-- 13 00:00:46,050 --> 00:00:48,350 the password typed again. 14 00:00:48,350 --> 00:00:54,020 >> So now when that form is posted to register.PHP we'll execute this if. 15 00:00:54,020 --> 00:00:57,260 Looking at this if, we're first going to validate the input. 16 00:00:57,260 --> 00:01:00,650 We want to make sure that the username and password weren't empty and that 17 00:01:00,650 --> 00:01:03,860 the confirmation actually matches the password. 18 00:01:03,860 --> 00:01:07,820 Once we've verified that we can actually register the user. 19 00:01:07,820 --> 00:01:08,750 >> What does that mean? 20 00:01:08,750 --> 00:01:12,120 Well, we want to insert the user into our database. 21 00:01:12,120 --> 00:01:13,600 And this is how we'll do that. 22 00:01:13,600 --> 00:01:19,250 So we're going to insert into the users table the fields user name, 23 00:01:19,250 --> 00:01:21,230 hash, and cash. 24 00:01:21,230 --> 00:01:24,920 >> The default value of cash is going to be 10,000. 25 00:01:24,920 --> 00:01:29,630 And we're going to pass as username, username, through the POST super 26 00:01:29,630 --> 00:01:32,070 global that's submitted from the form. 27 00:01:32,070 --> 00:01:35,370 And we're going to encrypt the password. 28 00:01:35,370 --> 00:01:40,780 >> So if that succeeded, then results will be non-false. 29 00:01:40,780 --> 00:01:42,990 If it failed, then we want to apologize. 30 00:01:42,990 --> 00:01:44,100 Something went wrong. 31 00:01:44,100 --> 00:01:45,330 And what could have gone wrong? 32 00:01:45,330 --> 00:01:48,610 >> Well, there needs to be a unique username. 33 00:01:48,610 --> 00:01:51,900 And so the query could have failed if the username already 34 00:01:51,900 --> 00:01:53,700 existed in the table. 35 00:01:53,700 --> 00:01:58,210 So assuming it was a unique username, then we're going to query to grab the 36 00:01:58,210 --> 00:01:59,640 ID of that user. 37 00:01:59,640 --> 00:02:02,300 Remember that the ID is auto-incrementing. 38 00:02:02,300 --> 00:02:06,730 >> And so if that happens to fail for some reason, then we want to apologize 39 00:02:06,730 --> 00:02:08,520 that we couldn't grab the ID. 40 00:02:08,520 --> 00:02:13,450 But assuming that it didn't fail, then we grab the ID from what the query 41 00:02:13,450 --> 00:02:18,400 returned, store that in our session-- so we want to log this user in by 42 00:02:18,400 --> 00:02:21,770 storing the ID in the session super global, and finally 43 00:02:21,770 --> 00:02:23,820 redirect to our portfolio. 44 00:02:23,820 --> 00:02:26,040 And that's it for register. 45 00:02:26,040 --> 00:02:28,680 >> Now we're going to move on to quote. 46 00:02:28,680 --> 00:02:31,770 So quote is going to have a really similar set up. 47 00:02:31,770 --> 00:02:35,220 We see here that this is the code that we're going to execute when a form is 48 00:02:35,220 --> 00:02:36,850 posted to this page. 49 00:02:36,850 --> 00:02:40,500 But first we actually have to render that form. 50 00:02:40,500 --> 00:02:44,140 So taking a look at quote form, what fields are there? 51 00:02:44,140 --> 00:02:50,710 >> We see that all quote has is a single text box with the name symbol. 52 00:02:50,710 --> 00:02:55,350 And so when quote form is posted to quote.PHP we're now going to 53 00:02:55,350 --> 00:02:56,950 execute this code. 54 00:02:56,950 --> 00:03:01,630 And the only variable in our POST super global is going to be symbol. 55 00:03:01,630 --> 00:03:05,910 >> We validate that to make sure that they actually posted the symbol. 56 00:03:05,910 --> 00:03:08,760 And if they didn't, we say you must provide a symbol. 57 00:03:08,760 --> 00:03:12,670 Assuming they did provide a symbol, we look up that symbol. 58 00:03:12,670 --> 00:03:16,750 >> Now remember that look up might have failed since, well, maybe it wasn't a 59 00:03:16,750 --> 00:03:18,460 valid symbol to begin with. 60 00:03:18,460 --> 00:03:22,690 So if this look up return false, we want to apologize that the 61 00:03:22,690 --> 00:03:24,850 symbol was not found. 62 00:03:24,850 --> 00:03:29,450 Once we've found the symbol, now we can render the quote.PHP template. 63 00:03:29,450 --> 00:03:31,220 >> What does that look like? 64 00:03:31,220 --> 00:03:36,580 That's just going to print that a share of whatever the stocks name was 65 00:03:36,580 --> 00:03:41,540 is worth whatever the stock price is. 66 00:03:41,540 --> 00:03:45,490 Now why do we use this htmlspecialchars function? 67 00:03:45,490 --> 00:03:49,280 That's because the stock name and symbol might actually contain special 68 00:03:49,280 --> 00:03:54,410 characters that should not be interpreted as HTML. 69 00:03:54,410 --> 00:03:56,960 >> All right, so that's it for quote. 70 00:03:56,960 --> 00:04:00,700 Now we want to look at index.PHP and portfolio. 71 00:04:00,700 --> 00:04:04,730 But first we actually need to construct the portfolios table. 72 00:04:04,730 --> 00:04:07,310 Here's how we're going to do that. 73 00:04:07,310 --> 00:04:09,390 >> So let's take a look at the structure. 74 00:04:09,390 --> 00:04:13,530 And we see that the portfolios table is going to have an ID. 75 00:04:13,530 --> 00:04:17,760 So that's going to be the user's ID that's inserting the shares. 76 00:04:17,760 --> 00:04:21,839 We have a symbol, which is going to be the symbol of the company that we're 77 00:04:21,839 --> 00:04:23,610 inserting the shares for. 78 00:04:23,610 --> 00:04:27,940 And then shares is the number of shares that is being inserted. 79 00:04:27,940 --> 00:04:33,490 >> So remember that per the pset spec, we specify that ID and symbol-- 80 00:04:33,490 --> 00:04:37,140 we check out indexes, ID and symbol is the primary key. 81 00:04:37,140 --> 00:04:41,240 So a user ID and symbol pairing should only appear a single 82 00:04:41,240 --> 00:04:44,010 time in this table. 83 00:04:44,010 --> 00:04:46,050 Now let's look at the code. 84 00:04:46,050 --> 00:04:50,950 >> So now index.PHP is going to grab all of our portfolio information and 85 00:04:50,950 --> 00:04:52,700 display it to the user. 86 00:04:52,700 --> 00:04:56,230 So first, we're going to grab the cash that the user currently has from the 87 00:04:56,230 --> 00:04:57,160 cash table. 88 00:04:57,160 --> 00:05:02,070 Remember, that query is always going to return an array of arrays. 89 00:05:02,070 --> 00:05:06,070 So even though we only selected cash from a single row, we still need to 90 00:05:06,070 --> 00:05:10,850 grab that cash by indexing into the zeroth index of rows and grabbing the 91 00:05:10,850 --> 00:05:13,720 cash index. 92 00:05:13,720 --> 00:05:17,810 >> So now we want to select all of the information from the portfolios table 93 00:05:17,810 --> 00:05:21,640 that's relevant to the currently logged in user. 94 00:05:21,640 --> 00:05:25,070 We of course need to validate that that actually succeeded, which we 95 00:05:25,070 --> 00:05:28,120 should always do whenever we query. 96 00:05:28,120 --> 00:05:31,890 Once we have all of that information, the pset spec informs us that we 97 00:05:31,890 --> 00:05:36,298 should do this in order to nicely store all of the information in this 98 00:05:36,298 --> 00:05:37,850 positions array. 99 00:05:37,850 --> 00:05:42,250 >> So we're looping over all of the portfolio information, looking up the 100 00:05:42,250 --> 00:05:47,030 stock associated with each row in the portfolio information, and then 101 00:05:47,030 --> 00:05:52,510 storing in the position array the name, price, shares and symbol all 102 00:05:52,510 --> 00:05:54,790 associated with that stock. 103 00:05:54,790 --> 00:05:59,040 And finally, we're going to render portfolio.PHP, passing in the amount 104 00:05:59,040 --> 00:06:02,290 of cash we currently have, the positions array that we just 105 00:06:02,290 --> 00:06:06,510 constructed, and the title of this page which will be portfolio. 106 00:06:06,510 --> 00:06:09,166 >> Let's take a look at portfolio.PHP. 107 00:06:09,166 --> 00:06:13,570 And we see that the major interesting part is this loop. 108 00:06:13,570 --> 00:06:17,410 So we're looping over the positions array, creating a 109 00:06:17,410 --> 00:06:19,420 table, where that table-- 110 00:06:19,420 --> 00:06:23,220 we're populating each row with the information that we put inside the 111 00:06:23,220 --> 00:06:24,470 positions array. 112 00:06:24,470 --> 00:06:28,250 >> Again, we need to use htmlspecialchars in case this symbol or the name 113 00:06:28,250 --> 00:06:31,020 contain HTML characters. 114 00:06:31,020 --> 00:06:36,690 And here we're multiplying the price and the amount of shares that we have 115 00:06:36,690 --> 00:06:40,880 in order to get how much that's currently worth to the user. 116 00:06:40,880 --> 00:06:42,880 And that's it for portfolio. 117 00:06:42,880 --> 00:06:45,500 >> Now we'll take a look at sell. 118 00:06:45,500 --> 00:06:50,190 So sell is going to go back to the format that we had in register.PHP. 119 00:06:50,190 --> 00:06:53,200 We see that a form is going to be posted to this page. 120 00:06:53,200 --> 00:06:57,270 But first, when we load the page, we're going to do this. 121 00:06:57,270 --> 00:06:58,880 >> So what is this doing? 122 00:06:58,880 --> 00:07:03,660 Well, we could just have the sell page have a single text box that the user 123 00:07:03,660 --> 00:07:06,020 enters this symbol that we want to sell. 124 00:07:06,020 --> 00:07:08,890 But we're going to be a bit more clever and we're going to have a drop 125 00:07:08,890 --> 00:07:14,690 down that allows the user to select actual symbols that they already have. 126 00:07:14,690 --> 00:07:17,940 >> So we're getting the user's portfolio. 127 00:07:17,940 --> 00:07:22,630 We're going to select from portfolios all of the symbols that the user 128 00:07:22,630 --> 00:07:26,180 currently has, the currently logged in user. 129 00:07:26,180 --> 00:07:28,150 Make sure that that succeeded. 130 00:07:28,150 --> 00:07:31,960 And now we're going to loop over the returned information, just grabbing 131 00:07:31,960 --> 00:07:35,610 each symbol, and storing it in this symbols array. 132 00:07:35,610 --> 00:07:37,570 >> And now we're going to render the sell form. 133 00:07:37,570 --> 00:07:43,380 So the sell form is going to just be a drop down menu, a select. 134 00:07:43,380 --> 00:07:49,140 And each option in the sell form is going to just print out the symbol 135 00:07:49,140 --> 00:07:52,380 that we grabbed from the portfolios table. 136 00:07:52,380 --> 00:07:58,390 >> So the sell form is going to submit back to sell.PHP. 137 00:07:58,390 --> 00:08:04,340 Looking at sell.PHP, this is the code that's going to execute when we submit 138 00:08:04,340 --> 00:08:05,555 to this page. 139 00:08:05,555 --> 00:08:10,320 We want to validate that the user actually entered a symbol. 140 00:08:10,320 --> 00:08:12,730 >> Now assuming that they did-- 141 00:08:12,730 --> 00:08:16,930 so now we want to determine how many shares the user is actually selling 142 00:08:16,930 --> 00:08:20,830 and how much cash the user should get for selling that many shares. 143 00:08:20,830 --> 00:08:25,210 So we grab the number of shares that the user has for this symbol. 144 00:08:25,210 --> 00:08:30,690 We're looking up in portfolios for the given user and the given symbol. 145 00:08:30,690 --> 00:08:34,289 >> Now make sure that that actually returned a row. 146 00:08:34,289 --> 00:08:39,440 Because if it didn't, the user doesn't actually have that symbol to sell. 147 00:08:39,440 --> 00:08:42,890 Assuming they do have that symbol, we want to grab the number of 148 00:08:42,890 --> 00:08:44,530 shares that they have. 149 00:08:44,530 --> 00:08:47,640 And now we want to look up how much each share is worth. 150 00:08:47,640 --> 00:08:49,450 >> So we used the look up function. 151 00:08:49,450 --> 00:08:52,910 We're looking up the value of the symbol. 152 00:08:52,910 --> 00:08:56,700 Assuming that the look up succeeded, now we're going to actually update all 153 00:08:56,700 --> 00:08:57,830 the information. 154 00:08:57,830 --> 00:09:02,285 So we want to delete from portfolios the shares that we're selling. 155 00:09:02,285 --> 00:09:05,610 We want to update the user's amount of cash. 156 00:09:05,610 --> 00:09:10,480 >> And we're updating it by shares times the stock price-- 157 00:09:10,480 --> 00:09:13,050 so that's how much money the user just made. 158 00:09:13,050 --> 00:09:15,150 And now we want to update our history. 159 00:09:15,150 --> 00:09:17,390 So we haven't taken a look at the history table yet. 160 00:09:17,390 --> 00:09:19,440 So we'll come back to this. 161 00:09:19,440 --> 00:09:23,690 >> Now finally we're going to redirect back to the portfolio. 162 00:09:23,690 --> 00:09:25,300 Now let's take a look at buy. 163 00:09:25,300 --> 00:09:28,910 So, buy should be pretty similar to sell. 164 00:09:28,910 --> 00:09:32,300 >> We see that we're again going to check to see if we're 165 00:09:32,300 --> 00:09:33,900 submitting to this page. 166 00:09:33,900 --> 00:09:37,470 Assuming that we're not, we're going to load the buy form. 167 00:09:37,470 --> 00:09:40,210 So what does the buy form look like? 168 00:09:40,210 --> 00:09:42,510 >> We see here it's just a regular form that's going to 169 00:09:42,510 --> 00:09:44,450 submit back to buy.PHP. 170 00:09:44,450 --> 00:09:49,230 And it's going to have a symbol that the user is entering, the number of 171 00:09:49,230 --> 00:09:53,330 shares that the user wants to buy of that symbol, and that's it. 172 00:09:53,330 --> 00:09:59,890 So when we submit back to buy.PHP, we're now going to execute this code. 173 00:09:59,890 --> 00:10:04,250 >> We again want to validate that the user entered something valid. 174 00:10:04,250 --> 00:10:06,730 So here we're making sure they actually entered a symbol. 175 00:10:06,730 --> 00:10:09,190 Here we're making sure that they actually entered shares. 176 00:10:09,190 --> 00:10:12,840 And here we're making sure that they entered an integer for shares, so 177 00:10:12,840 --> 00:10:15,095 they're not trying to buy ABC shares. 178 00:10:15,095 --> 00:10:17,630 179 00:10:17,630 --> 00:10:23,130 >> Now we want to look up the price of the symbol so we know how much cash we 180 00:10:23,130 --> 00:10:26,590 should subtract from the user. 181 00:10:26,590 --> 00:10:31,700 Now we'll select how much cash the user actually has and make sure that 182 00:10:31,700 --> 00:10:33,960 that succeeded. 183 00:10:33,960 --> 00:10:35,600 Here we're grabbing the cash. 184 00:10:35,600 --> 00:10:39,660 And now here, we're making sure that the user has enough cash. 185 00:10:39,660 --> 00:10:44,950 >> So if the number of shares the user wants to buy times the price of each 186 00:10:44,950 --> 00:10:49,100 of those shares is greater than the amount of cash that we have, then the 187 00:10:49,100 --> 00:10:50,950 user can't afford that. 188 00:10:50,950 --> 00:10:54,500 Assuming that the user has enough cash, now we want to insert into the 189 00:10:54,500 --> 00:10:55,980 user's portfolio. 190 00:10:55,980 --> 00:10:59,900 Well, we'll insert into the user's portfolio if this happens to be the 191 00:10:59,900 --> 00:11:02,770 first time the user is buying that particular symbol. 192 00:11:02,770 --> 00:11:06,630 >> But what if they already happen to have some Apple stock? 193 00:11:06,630 --> 00:11:10,930 Well, now we're making use of the on duplicate key update statement. 194 00:11:10,930 --> 00:11:15,750 So this is why earlier we specified that ID and symbol should be a joint 195 00:11:15,750 --> 00:11:19,620 primary key, so that if we try to insert an ID and symbol that are 196 00:11:19,620 --> 00:11:24,670 already there, we'll just update the shares to include the new shares that 197 00:11:24,670 --> 00:11:26,720 the user is buying. 198 00:11:26,720 --> 00:11:30,390 >> Now we want to update the amount of cash that the user has, since they 199 00:11:30,390 --> 00:11:33,390 just spent some money on those shares. 200 00:11:33,390 --> 00:11:36,060 And finally, we'll update the history table again. 201 00:11:36,060 --> 00:11:38,590 Which, again, we'll look at in a second. 202 00:11:38,590 --> 00:11:43,230 And finally we'll redirect back to portfolio.PHP. 203 00:11:43,230 --> 00:11:45,440 >> So let's take a look at the history table. 204 00:11:45,440 --> 00:11:48,770 205 00:11:48,770 --> 00:11:53,430 Now remember that the history table is supposed to keep track of all buys and 206 00:11:53,430 --> 00:11:57,580 sells that all users make, not just the current number of shares that the 207 00:11:57,580 --> 00:12:00,500 users have, which is what portfolio is for. 208 00:12:00,500 --> 00:12:06,350 So we're keeping track of the user that is buying or selling, whether 209 00:12:06,350 --> 00:12:11,740 this particular transaction was a buy or a sell, the symbol that's being 210 00:12:11,740 --> 00:12:17,540 bought or sold, the number of shares that we're buying or selling, the 211 00:12:17,540 --> 00:12:22,840 price of a single share that's being bought or sold, and finally, the time 212 00:12:22,840 --> 00:12:25,550 that this buying or selling is occurring. 213 00:12:25,550 --> 00:12:27,650 >> And that's all of the history information that we need 214 00:12:27,650 --> 00:12:29,850 to keep track of. 215 00:12:29,850 --> 00:12:37,340 So when we looked at sell, we saw that we were inserting into history sell, 216 00:12:37,340 --> 00:12:42,060 as whether we're buying or selling, the current time stamp, and the 217 00:12:42,060 --> 00:12:46,070 current user, the symbol that's being sold, the number of shares that are 218 00:12:46,070 --> 00:12:50,010 being sold, and the price of the stock at this time. 219 00:12:50,010 --> 00:12:52,740 Similarly, in buy, it'll look almost the same. 220 00:12:52,740 --> 00:12:56,820 The only difference is instead of selling, we're buying. 221 00:12:56,820 --> 00:13:01,243 >> So in sell and buy, we're inserting into the histories table all of the 222 00:13:01,243 --> 00:13:02,960 buys and sells that are happening. 223 00:13:02,960 --> 00:13:08,680 So all history.PHP needs to do is grab the information from the history 224 00:13:08,680 --> 00:13:13,960 table, make sure it succeeded, and render that information. 225 00:13:13,960 --> 00:13:18,770 So looking at the history.PHP template, the interesting information 226 00:13:18,770 --> 00:13:19,940 is right here. 227 00:13:19,940 --> 00:13:23,935 >> We're looping over all of the transactions, printing whether this 228 00:13:23,935 --> 00:13:29,580 was a buy or sell, formatting date time that we made this transaction. 229 00:13:29,580 --> 00:13:31,520 Remember we need to use htmlspecialchars on the 230 00:13:31,520 --> 00:13:33,270 symbol, just in case. 231 00:13:33,270 --> 00:13:37,150 And finally, formatting the number of shares that were bought and the price 232 00:13:37,150 --> 00:13:39,310 of a single share at that time. 233 00:13:39,310 --> 00:13:42,570 >> And that displays all of the history information that we need. 234 00:13:42,570 --> 00:13:44,220 And that's it for this pset. 235 00:13:44,220 --> 00:13:47,820 My name is Rob, and this was CS50 Finance. 236 00:13:47,820 --> 00:13:52,171