Sometimes, it is the simple ideas that really get a project moving. Rather than try to remember the code each time, I thought it would be handy to save some little code snippets for easy reference, training and testing.
Here is a very simple way to create a simple database table with just an ID and timestamp. The timestamp auto updates with new ID entry. This would be helpful for tracking statistics, users or very simple data metrics.
The following creates a table (table_name) and adds in an id as an INT and (created_at datetime) timestamp.
create table table_name (id int, created_at datetime DEFAULT CURRENT_TIMESTAMP);Now that the table is created, it is easy to add in values for the integer. We can do this one at a time using the following insert statement where I am inserting the value “53” into the id column :
INSERT INTO table_name (id) VALUES (53)
I know this is very basic, that’s the point.
Now, let’s go ahead and use a SQL statement to add in multiple entries. Again, we are adding values for the id column and the timestamp is automatically adjusting to reflect the current time.
INSERT INTO hello (id) VALUES (97), (102), (103), (107), (118), (126)
Here is a snippet to add in a few columns. The main data I am adding is going to be the “number” whereas the index1 is essentially the auto_increment index and the timestamp is added to the entry by default. I used this to create the table in my visitor count project.
create table if not exists visitor_count ( index1 int not null primary key AUTO_INCREMENT, number int, my_timestamp timestamp not null default current_timestamp on update current_timestamp )
Again, I can add number values for the “number” column by using the isert statement above. This will add the number values 97,102,103,107,118,126 while the index (index1 column) and date will auto populate. The screenshot below shows all of the dates the same because I added these as a bulk add whereas individually, they will be added one at a time.
INSERT INTO visitor_count1 (number) VALUES (97), (102), (103), (107), (118), (126)
