|
| |
Last updating time Storing in MySQL field |
We can keep one date & time filed in a record of MySQL table which automatically gets updated with current time and date when the record is added. For example we are adding names to a user or member table. In the member table there will be more fields like name, email, password etc but for simplicity we will use three fields only. One filed is id which is a auto increment field, next one is name filed and last tm is a datetime field. Here once we add name to the table the id automatically gets generated as it is an auto increment filed and the field tm stores the date and time value showing the time of record insertion.
We are not inserting current date time by calculating through PHP to the table like given here.
While creating the table we will set the tm filed ( DATETIME) Attributes to ON UPDATE CURRENT_TIMESTAMP and Default value to NOW().
See the MySQL dump to create the table here.
CREATE TABLE `test_time2` (
`id` int(2) NOT NULL auto_increment,
`name` varchar(20) NOT NULL,
`tm` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
Now once the table is created use this command to add one record. Note that we are only adding the name field value.
insert into test_time2 (name) values('my_name')
After executing the query you can see tm filed has the current date and time value, as usual the id filed has the new unique id for the record.
Now let us try to update the same record by using this query.
update test_time2 set name='my_name1' where name='my_name'
After using the above query you can see the value of tm files also changed and now it has the new date and time value of when record was updated.
If you are giving option to change password in a member table then such a filed can be kept to keep the last updated time of the record.
Another application is if you are storing the login time of the members. You can update one filed and show the visitors their last login time. Here before updating the current login info you have to collect the previous record and display.
| |
|
| HOME |
| SQL Tutorial List |
| SQL (Home) |
| SQL Commands |
|
|
|
|
|
| Subscribe |
|
Submit your email address and receive
article and product notifications. Your email is safe with us.
|
|
|
|
