PHP and MySQL syntax: Difference between revisions
Jump to navigation
Jump to search
(add text match) |
|||
Line 80: | Line 80: | ||
* SELECT DISTINCT column FROM table<ref>[http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html MySQL :: MySQL 5.0 Reference Manual :: 8.3.1.13 DISTINCT Optimization]</ref> | * SELECT DISTINCT column FROM table<ref>[http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html MySQL :: MySQL 5.0 Reference Manual :: 8.3.1.13 DISTINCT Optimization]</ref> | ||
* SELECT column FROM table GROUP BY column | * SELECT column FROM table GROUP BY column | ||
== text match == | |||
* in_array | |||
* preg_match... | |||
== ideas == | == ideas == |
Revision as of 16:18, 4 January 2013
Convert time stamp <--> the human readable time format
timestamp
now
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'
- mysql:
- SELECT NOW(); /*return the human readable current time: 2011-04-01 12:19:43 */
- SELECT NOW()+0; //2011-04-01 12:19:43 returns 20110401122023.000000
- SELECT CURRENT_TIMESTAMP; /*return the human readable current time: 2011-04-01 12:19:43 */
the current timestamp ex: 1292897201
- php: echo time();
- mysql: SELECT UNIX_TIMESTAMP(); //2011-05-30 01:56:38 returns 1306720622
- javascript: datetime - How do you get a timestamp in JavaScript? - Stack Overflow [Last visited: 20110323]
- EXCEL: Excel date to Unix timestamp - Stack Overflow
compare the timestamp and human readable time format using MySQL
SELECT UNIX_TIMESTAMP( ) , FROM_UNIXTIME( UNIX_TIMESTAMP( ) , '%Y-%m-%d %H:%i:%S' );
specified time
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
- mysql:
- SELECT FROM_UNIXTIME( 1306311155 ); //convert the time stamp 1306311155 to the human readable time format 2011-05-25 08:12:11
- SELECT FROM_UNIXTIME( 1306311155, '%Y-%m-%d %H:%i:%S' ); //convert the time stamp 1306311155 to the human readable time format 2011-05-25 08:12:11
convert human-readable time to timestamp
- php: using strtotime() function ex: strtotime('2010-12-21 10:05:06') [1]
- mysql: SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57'); /* return timestamp: 1300186437 */
time difference of two time values
- php: using mktime() function
- mysql: SELECT TIMEDIFF('2010:01:01 00:00:00', '2010:02:01 00:00:00'); /* return -744:00:00 */
further reading
- PHP Date and Time functions
- MySQL The DATETIME, DATE, and TIMESTAMP Types
- MySQL Date and Time Functions
- PHP Date / Time Functions / PHP Date / Time 函数
Format a number
- PHP: PHP: number_format - Manual
- MySQL: LPAD(number,3,0) will output 007 if the value of number is 7
before delete trigger to make backup of deleted data
MySQL :: Re: before delete trigger to make backup of deleted data
step 1: create table hello_deleted_data with the same structure of table hello
step 2: add a trigger
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);
tested on version: mysqlnd 5.0.7-dev - 091210
redirect/reload to (another) page
redirect
- HTML: html redirect
- PHP:
- header() to new location PHP: header - Manual
- echo HTML redirect
- Javascript
- Javascript Tutorial - Redirect ex: window.location = "http://www.google.com/"
- iframe - Changing parent window's URL from IFRAME content - AV5
reload
- Javascript
- iframe (reload) - parent.location.reload();
find unique (non duplicated) data
- SELECT DISTINCT column FROM table[2]
- SELECT column FROM table GROUP BY column
text match
- in_array
- preg_match...
ideas
filter some records
- approach 1: (1) select * under some condition, (2) collect the $identifiers which matched the condition. and then (3) select some records NOT IN ( $identifiers )
- approach 2: (1) select * and then (2) unset some records which matched the condition.