Welcome to Part 3 of my solution to Finance from Problem Set 7 of CS50. We will continue working through this implementation by looking at the buy function. Up until now, we have been able to get by with the users table from the finance database. Now, we need to create a table that keeps track of all the transactions for each user. So, we will start by creating the table using the following SQL statement.:
CREATE TABLE “transactions” (‘id’ INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ‘user_id’ INTEGER NOT NULL, ‘symbol’ TEXT NOT NULL,’shares’ INTEGER NOT NULL, ‘price’ NUMERIC NOT NULL, ‘timestamp’ INTEGER DEFAULT CURRENT_TIMESTAMP)
In the previous statement, we are setting a primary key called ‘id’ which will autoincrement for each transaction. The next item is the ‘user_id’, which will be the foreign key that we will use to relate the two tables of our database. The next three items will store the symbol, number of shares, and price for those shares for each transaction. The last item will create a timestamp for each transaction that is processed. You can create this table in phpLiteAdmin or by using the command line.
Now that we have our table created, we can start adding buy transactions for our users. We will start by writing the html file for the user input. Below is the html file that I will use to get the stock symbol and number of shares that the user wants to buy. This file should look very similar by now. We are setting up a form and form controls just as we did in previous weeks.
Now, let’s look at the buy function. The start of this function should look very similar to quote from last week. We are starting out by making sure the user arrived via post. We make sure that the user has entered in a valid symbol for the purchase. Next, we use an exception handler (in the try, except block) that checks to make sure that the user has input a number for shares. We also need to make sure that the user inputs a positive number. Though this measure is a little redundant, since we set a minimum amount of 1 in the html.
Next, we start getting into some SQL statements. First, we want to get the available cash from the current user. The SELECT statement for the rows variable will return a one column and one row table. In order to extract the amount of cash from that table, we set available_cash equal to the 0th row (remember the tables are 0 based) from the column “cash”. Next we get the price for each stock. Remember, that when we call the lookup function it returns a JSON object that includes the stock symbol and the price of that stock. So, we access the price of the stock by calling stock[“price”]. The total price of the transaction will be equal to the price of the stock multiplied by the number of shares. And, of course, if the available cash is less than the purchase price the user cannot make the purchase.
The next two SQL statements will execute to complete the transaction. First, we will deduct the total_price for the transaction from cash that the user has on hand. We do this by updating the field for cash to equal cash – total_price. Remember, we want to use :total_price to prevent a SQL injection attack. The next SQL statement will record the transaction to the transactions table that we just created. We use the INSERT statement to add a new item to our table. Notice, that we only need to worry about the user_id, symbol, shares, and price. Our table will automatically add the id and timestamp fields. Once our transaction is recorded, we will redirect the user to their home page (index.html).
And now our users can buy stock. Next week we will set up the index.html page that will show the user a snapshot of their account. I hope you enjoyed this tutorial. Let me know if you have any questions by commenting below. Remember to share on your favorite social media platform. As always…Happy Programming!