4

In CS50 pset 7, there is a part where you have to use SQL to insert the date and time of a transaction into a table.

According to the web, there is a function to do that called CURRENT_TIMESTAMP or NOW(). However, it's not clear to me exactly how to use these. I tried a few google searches but was still struggling to figure this out.

$result3=query("INSERT INTO History (Date/Time, Symbol, Shares, Buy/Sell) VALUES (CURRENT_TIMESTAMP, ?, ?, ?)", $stock["symbol"], $shares_to_buy, 1);

The above is apparently NOT how you do it.

2
  • Sorry for the late reply. I was away at a conference for the past week without good internet and no time to work on programming :( Apparently, even if I ignore timestamps, there is a fatal error in this line of SQL: ("INSERT INTO History (Date/Time, Symbol, Shares, Buy/Sell) VALUES (NULL, ?, ?, ?)", $stock["symbol"], $shares_to_buy, 1);
    – user1298
    Jul 14 '14 at 23:44
  • So I guess the timestamp function may not have been the problem, which makes me feel both better and worse. If I remove Date/Time entirely, I STILL have a fatal error.
    – user1298
    Jul 14 '14 at 23:46
4

You can set the automatic value in the table itself to use CURRENT_TIMESTAMP so that anytime you add to the table, the time value is set to that. Then you don't need to insert that field in your individual query.

I have this in my transactions table:

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
2
  • I was thinking to do that,actually, but is there ever a case where one can't? Where it's necessary to type it out?
    – user1298
    Jul 7 '14 at 1:32
  • @user1298 Yes, more complex statements exist where e.g. some date arithmetic is performed that would require explicit use of date and time functions. For example, SELECT ... FROM table WHERE some_date > CURDATE() - INTERVAL 1 WEEK to find records where some_date is less than a week old. When in doubt, read the documentation to understand what functions and synonyms are available, and how they might be used.
    – Air
    Jul 7 '14 at 15:20
1

This requires a little bit changing in the structure of your history table. When you register for a new account the id automatically gets assigned to the user. Similar is the case with specifying time and date.

In your history table's structure if you have a column date/time make a change to it's attributes. The attribute which comes in handy is on update CURRENT_TIMESTAMP and set it's default value to CURRENT_TIMESTAMP. Also, Change it's type to timestamp. You can do this all changing graphically.

Now whenever you do buy or sell and assign a new row in history table. You don't have to care about current_timestamp. It'll update automatically. Just specify the other columns information and the computer is now smart enough to store the current date and time in one column.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .