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!

3 comments

  1. Heya I found your blog on google 🙂 I’m also doing the cs50 course. Only on week 1 though I thought it would be cool to reach out and connect to you as I’m documenting my progress too! How are you finding it? Your blog posts all seem quite factual. Like you’re the one explaining to the reader. Are you learning in the process too or do you know how to do each pset with ease? Are you coming accross any problems? How long have you been doing the cs50 for? Is there a reason why you are doing it? You’ve probably written these in previous posts but yano cbb to research through and just making convo with a follow blogger!

    Like

    1. I am glad that you reached out to me. The whole purpose of this blog is to help others, if I can, and meet new people on their coding journey. I had some prior programming experience before starting CS50, but I am by no means an expert and I found it difficult to use C at first. I came into CS50 with more knowledge of higher level programming languages such as C# and Python. I started CS50 back in May. I am currently a student at the University of Nevada Reno, and I am majoring in Information Systems and Business Management. I began the course because I wanted to keep coding over the summer while I was strictly taking management courses. I have finished all the problem sets now and currently working on my final project. Why did you start taking CS50? I hope that you stick with it and let me know if there is anything I can do to help. I will be sure to check out your blog. Thanks for checking mine out.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s