PHP and MySQL syntax: Difference between revisions
mNo edit summary |
|||
Line 53: | Line 53: | ||
* 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 */ | ||
=== Query by month === | === date interval === | ||
When people said {{CURRENTYEAR}}-{{CURRENTMONTH}}-01 ~ {{CURRENTYEAR}}-{{CURRENTMONTH}}-03, it means {{CURRENTYEAR}}-{{CURRENTMONTH}}-01 00:00:00 ~ {{CURRENTYEAR}}-{{CURRENTMONTH}}-03 59:59:59 in the system. | |||
==== Query by month ==== | |||
* mysql ex: find the records in January, 2014<ref>[http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year sql - MySQL Query GROUP BY day / month / year - Stack Overflow]</ref> Data type of column `time` is {{kbd | key = datetime}}. Using one of sql queries as following. [http://ideone.com/kJEgnV online demo] | * mysql ex: find the records in January, 2014<ref>[http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year sql - MySQL Query GROUP BY day / month / year - Stack Overflow]</ref> Data type of column `time` is {{kbd | key = datetime}}. Using one of sql queries as following. [http://ideone.com/kJEgnV online demo] | ||
** SELECT * FROM `table_name` WHERE YEAR(`time`) = '2014' AND MONTH(`time`) = '1' /*MONTH(`time`) = '1' or MONTH(`time`) = '01' are both ok*/ | ** SELECT * FROM `table_name` WHERE YEAR(`time`) = '2014' AND MONTH(`time`) = '1' /*MONTH(`time`) = '1' or MONTH(`time`) = '01' are both ok*/ | ||
Line 67: | Line 70: | ||
* Use [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function]: {{kbd | key = <nowiki>DATE_FORMAT( time, '%Y-%m-%d')</nowiki>}} | * Use [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function]: {{kbd | key = <nowiki>DATE_FORMAT( time, '%Y-%m-%d')</nowiki>}} | ||
=== further reading === | === further reading === |
Revision as of 12:07, 7 January 2016
mulitple approaches to completing the same/similar task using PHP, MySQL and Excel syntax
time / timestamp
Convert time stamp <--> to the human readable time format
now
the human readable time format ex: 2024-04-29 00:57: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 '2024-04-29 10:05:06'
- mysql:
- SELECT NOW(); /*return the human readable current time: 2024-04-29 12:19:43 */ which mapping to DATETIME type[1]
- SELECT NOW()+0; //2011-04-01 12:19:43 returns 20110401122023.000000
- SELECT CURRENT_TIMESTAMP; /*return the human readable current time: 2024-04-29 12:19:43 */ Current time zone of MySQL is SYSTEM.
- SELECT CURRENT_DATE; or SELECT CURDATE(); /* Return 2024-04-29 */[2]
- EXCEL: =TODAY() Returns the current date. ex: 2024/04/29 [3]
the current timestamp ex: 1292897201
- php: echo time(); //Return the number of seconds since 1970/01/01
- php: echo microtime(); PHP: microtime - Manual "Returns current Unix timestamp with microseconds" //ex: 0.45920500 1406776901
- mysql: SELECT UNIX_TIMESTAMP(); //Return the number of seconds since 1970/01/01 ex: 2011-05-30 01:56:38 returns 1306720622
- javascript: getTime() Method ex: new Date().getTime(); //Return the number of milliseconds since 1970/01/01 [4]
- 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 ); // Using date() function that converting the time stamp 1292897201 to the human readable time format. similar: gmdate() function [Last visited: 2014-04-30]
- 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
- Excel
convert human-readable time to timestamp
- PHP: using strtotime() function ex: strtotime('2010-12-21 10:05:06') [5]
- mysql: SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57'); /* return timestamp: 1300186437 */
- Excel:
- input data (A2 cell): YYYY/MM/DD (e.g. 2024/04/29 ) or YYYY/MM/DD HH:MM (e.g. 2024/04/29 00:57)
- =(A2-DATE(1970,1,1))*86400 [6]
convert month name to numeric date ex: April 29, 2024 -> 2024-04-29
- PHP:
- MySQL: STR_TO_DATE(str,format) ex: SELECT STR_TO_DATE('April 29, 2024','%M %d, %Y');[7]
- Excel
PHP microtime to MySQL timestamp
PHP microtime to MySQL timestamp
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 */
date interval
When people said 2024-04-01 ~ 2024-04-03, it means 2024-04-01 00:00:00 ~ 2024-04-03 59:59:59 in the system.
Query by month
- mysql ex: find the records in January, 2014[8] Data type of column `time` is datetime. Using one of sql queries as following. online demo
- SELECT * FROM `table_name` WHERE YEAR(`time`) = '2014' AND MONTH(`time`) = '1' /*MONTH(`time`) = '1' or MONTH(`time`) = '01' are both ok*/
- SELECT * FROM `table_name` WHERE DATE_FORMAT(`time`, '%Y-%m') = '2014-01'
- SELECT * FROM `table_name` WHERE `time` LIKE '2014-01%' /*find the time records starting with the string 2014-01 */
- SELECT * FROM `table_name` WHERE date(`time`) between '2014-01-01' AND '2014-01-31'
- SELECT * FROM `table_name` WHERE `time` >= '2014-01-01 00:00:00' AND `time` < '2014-02-01 00:00:00'
- SELECT * FROM `table_name` WHERE `time` >= '2014-01-01 00:00:00' AND `time` <= '2014-01-31 00:00:00'
date format
sample output: 2024-04-29
- Use MySQL DATE() Function: DATE( time )
- Use MySQL DATE_FORMAT() Function: DATE_FORMAT( time, '%Y-%m-%d')
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 函数
- Help:Magic words - MediaWiki
Math
Format a number
- PHP: PHP: number_format - Manual
- MySQL: LPAD(number,3,0) will output 007 if the value of number is 7
Round a number downward to its nearest integer
other
- a >0 OR b > 0 OR c > 0
- a+b+c > 0
trigger to make backup of deleted data before deleting them
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[9]
- (1)SELECT * FROM table; (2)Using PHP: array_unique for data handling two or more columns
- SELECT column FROM table GROUP BY column
text match
- PHP in_array; jQuery.inArray()
//PHP code snippet: $array = array("AAA", "BBB", "CCC"); if(in_array($var, $array){ //do something }
- preg_match...
- verbose approach: IF (0 == 'String') always be true?[10]
if($var =="AAA" OR $var =="BBB" OR $var =="CCC"){ //do something }
text input filter
export/import sql file
reverse the boolean value
- PHP:
$var = true; print( !$var); //add ! symbol to reverse the boolean value
- EXCEL: NOT 函數 - Excel ex: =NOT(TRUE) will return FALSE
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.
documentation
How to generate the table schema
- Using phpMyAdmin or other MySQL client
- Input the SQL query:
EXPLAIN table_name;
- Print view (with full texts)
- Easy to copy the table to other work processor
- (optional) Adjust the text size to 10
debug
references
- ↑ MySQL :: MySQL 5.1 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types
- ↑ MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions
- ↑ TODAY function - Excel - Office.com
- ↑ JavaScript getTime() Method
- ↑ How to convert date to timestamp in PHP? - Stack Overflow
- ↑ Excel date to Unix timestamp - Stack Overflow
- ↑ MySQL MONTHNAME() from numbers - Stack Overflow
- ↑ sql - MySQL Query GROUP BY day / month / year - Stack Overflow
- ↑ MySQL :: MySQL 5.0 Reference Manual :: 8.3.1.13 DISTINCT Optimization
- ↑ Why does PHP consider 0 to be equal to a string? - Stack Overflow