14,983
edits
PHP and MySQL syntax: Difference between revisions
Jump to navigation
Jump to search
add before delete trigger to make backup of deleted data
m (→specified time) |
(add before delete trigger to make backup of deleted data) |
||
| Line 1: | Line 1: | ||
Convert time stamp <--> the human readable time format | Convert time stamp <--> the human readable time format | ||
== now == | == timestamp == | ||
=== now === | |||
the human readable time format ex: 2010-12-21 10:05:06 | the human readable time format ex: 2010-12-21 10:05:06 | ||
* php: echo date("y-m-d H:i:s", time() ); //Convert the time stamp of current time to the human readable time format. Ex: return '2010-12-21 10:05:06' | * php: echo date("y-m-d H:i:s", time() ); //Convert the time stamp of current time to the human readable time format. Ex: return '2010-12-21 10:05:06' | ||
| Line 10: | Line 11: | ||
* javascript: [http://stackoverflow.com/questions/221294/how-do-you-get-a-timestamp-in-javascript datetime - How do you get a timestamp in JavaScript? - Stack Overflow] {{access | date = 20110323}} | * javascript: [http://stackoverflow.com/questions/221294/how-do-you-get-a-timestamp-in-javascript datetime - How do you get a timestamp in JavaScript? - Stack Overflow] {{access | date = 20110323}} | ||
== specified time == | === specified time === | ||
the human readable time format ex: 2010-12-21 10:05:06 | the human readable time format ex: 2010-12-21 10:05:06 | ||
* php: echo date("y-m-d H:i:s", 1292897201 ); // convert the time stamp 1292897201 to the human readable time format | * php: echo date("y-m-d H:i:s", 1292897201 ); // convert the time stamp 1292897201 to the human readable time format | ||
| Line 19: | Line 20: | ||
* mysql: select UNIX_TIMESTAMP('2011-03-15 18:53:57'); /* return timestamp: 1300186437 */ | * mysql: select UNIX_TIMESTAMP('2011-03-15 18:53:57'); /* return timestamp: 1300186437 */ | ||
== time difference of two time values == | === time difference of two time values === | ||
* php: using mktime() function | * php: using mktime() function | ||
* mysql: SELECT TIMEDIFF('2010:01:01 00:00:00', '2010:02:01 00:00:00'); /* return -744:00:00 */ | * mysql: SELECT TIMEDIFF('2010:01:01 00:00:00', '2010:02:01 00:00:00'); /* return -744:00:00 */ | ||
| Line 27: | Line 28: | ||
* [http://dev.mysql.com/doc/refman/5.0/en/datetime.html MySQL The DATETIME, DATE, and TIMESTAMP Types] | * [http://dev.mysql.com/doc/refman/5.0/en/datetime.html MySQL The DATETIME, DATE, and TIMESTAMP Types] | ||
* [http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html MySQL Date and Time Functions] | * [http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html MySQL Date and Time Functions] | ||
== before delete trigger to make backup of deleted data == | |||
[http://forums.mysql.com/read.php?99,88841,88856#msg-88856 MySQL :: Re: before delete trigger to make backup of deleted data] | |||
action1. create table hello_deleted_data with the same structure of table hello | |||
action2. add a trigger | |||
<pre> | |||
create trigger hello before delete on hello | |||
for each row | |||
insert into hello_deleted_data (x,y,z) | |||
values (OLD.x, OLD.y, OLD.z); | |||
</pre> | |||
[[Category:Programming]] | [[Category:Programming]] | ||