1 00:00:00,000 --> 00:00:00,500 2 00:00:00,500 --> 00:00:02,670 ZAMYLA CHAN: For buy, we'll also want to display 3 00:00:02,670 --> 00:00:06,870 a form for users to specify which stock they want to buy 4 00:00:06,870 --> 00:00:10,200 and the number of shares of that stock they'd like to purchase. 5 00:00:10,200 --> 00:00:14,880 So again, it sounds like it's time to add another template to our directory. 6 00:00:14,880 --> 00:00:17,880 Then we'll want to add that stock to the user's portfolio 7 00:00:17,880 --> 00:00:22,650 if they have enough cash and update their cash as appropriate. 8 00:00:22,650 --> 00:00:25,950 For the display form, let's ask for the symbol of the stock 9 00:00:25,950 --> 00:00:30,600 and the number of shares-- of course checking if this is valid input. 10 00:00:30,600 --> 00:00:33,570 Now that we know how many shares of a particular stock 11 00:00:33,570 --> 00:00:37,410 the user wants to purchase, let's see if they can actually afford it. 12 00:00:37,410 --> 00:00:43,800 Select the cash column from the table users where the ID number equals 1. 13 00:00:43,800 --> 00:00:47,760 And if that cash exceeds the total value of the shares 14 00:00:47,760 --> 00:00:51,930 times the price of that stock, then I can go ahead and buy that stock. 15 00:00:51,930 --> 00:00:57,630 If they can indeed afford it, then let's add that stock to the user's portfolio. 16 00:00:57,630 --> 00:01:02,070 In the portfolio we'll want to keep track of how many shares of each stock 17 00:01:02,070 --> 00:01:03,600 the user has. 18 00:01:03,600 --> 00:01:07,680 Now the user's table that we have within our SQL database 19 00:01:07,680 --> 00:01:11,190 doesn't have the capability to store that information yet 20 00:01:11,190 --> 00:01:14,220 because our user's table simply has the user name, the user 21 00:01:14,220 --> 00:01:16,990 ID, and the hash of their password. 22 00:01:16,990 --> 00:01:21,570 So in addition to our users table we're going to want to add another one, 23 00:01:21,570 --> 00:01:25,020 whether we call that "portfolio" or "transactions" or "history" 24 00:01:25,020 --> 00:01:30,210 or any other appropriate name that reflects the type of table it is. 25 00:01:30,210 --> 00:01:33,360 Within this table or perhaps combination of tables-- 26 00:01:33,360 --> 00:01:36,210 you could have to if you so choose-- we want 27 00:01:36,210 --> 00:01:38,610 to keep track of who bought what at what price 28 00:01:38,610 --> 00:01:42,660 and when using the appropriate SQLite types for the values 29 00:01:42,660 --> 00:01:46,500 that we're inserting, defining unique indices on any fields that should 30 00:01:46,500 --> 00:01:52,050 be unique, and then non-unique indices on any fields that we may be searching. 31 00:01:52,050 --> 00:01:55,440 After successfully updating the user's virtual portfolio, 32 00:01:55,440 --> 00:01:59,950 let's also update the cash to reflect the purchase that they just made. 33 00:01:59,950 --> 00:02:03,850 Remember that a user's cash is stored in the user's table. 34 00:02:03,850 --> 00:02:07,770 So, if I have my user with ID number one and they just 35 00:02:07,770 --> 00:02:13,500 bought \$50 worth of shares, then I'll execute the update query. 36 00:02:13,500 --> 00:02:16,740 Update the user's table, setting cash equal 37 00:02:16,740 --> 00:02:22,290 to cash minus 50 where my user is user with ID 1. 38 00:02:22,290 --> 00:02:25,760 Now let's edit the landing page. 39 00:02:25,760 --> 00:02:26,819