PHP and MySQL syntax

From LemonWiki共筆
Jump to: navigation, search

mulitple approaches to completing the same/similar task using PHP, MySQL and Excel

time / timestamp[edit]

terms of time format[edit]

format \ date 1900/1/1 1970/1/1 2016/1/1 notes
value of unix timestamp
(the number of seconds since 1970/01/01)
-2209075200 0 1451606400
value of Excel DATEVALUE function
(number of days since 1900/1/1)
1 25569 42370 5 digit numbers after 1970/1/1

now[edit]

human readable time format[edit]

Convert unix timestamp (the number of seconds since 1970/01/01) <--> to the human readable time format ex: 2017-06-27 07:03: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 '2017-06-27 07:03:06' Icon exclaim.gif server timezone dependent
  • mysql: Icon exclaim.gif default client timezone is server timezone[1]
    • SELECT NOW(); Returns 2017-06-27 07:03:06 which mapping to DATETIME type[2]
    • SELECT NOW()+0; //2011-04-01 12:19:43 returns 20110401122023.000000
    • SELECT CURRENT_TIMESTAMP; Returns 2017-06-27 07:03:06 Icon exclaim.gif Current time zone of MySQL is SYSTEM & client timezone dependent.
    • convert mysql datatype timestamp (UTC) to Taipei datetime (client timezone dependent) [3]
/* approach 1: */
SET time_zone='+8:00';
SELECT CURRENT_TIMESTAMP();

/* approach 2: Detect server timezone automatically! Note the precision of time difference between server timezone to your preferred timezone is hours NOT to minutes */
SELECT convert_tz(
   CURRENT_TIMESTAMP(),   
   CONCAT(
     ROUND(
      TIME_TO_SEC(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')))/3600),  ':00')
   , '+08:00');

/* approach 3: If you already know the server timezone ex: -05:00 */
SELECT convert_tz(CURRENT_TIMESTAMP,  '-05:00', '+08:00');
  • shell script with GUN date installed in Linux Os linux.png , Mac Os mac.png or Cygwin of Win Os windows.png :
    • echo `date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1 or date +\%Y-\%m-\%d\ %H:%M:%S Returns 2017-06-27 07:03:06 Icon exclaim.gif server timezone dependent
    • echo `TZ=Asia/Taipei date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1 or TZ=Asia/Taipei date +\%Y-\%m-\%d\ %H:%M:%S Returns 2017-06-27 07:03:06 Icon exclaim.gif force to use Taipei timezone

current timestamp[edit]

the number of seconds since 1970/01/01. UTC±0

  • php: echo time();
  • php: echo microtime(); PHP: microtime - Manual "Returns current Unix timestamp with microseconds" //ex: 0.45920500 1406776901
  • mysql: SELECT UNIX_TIMESTAMP(); //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 @@global.time_zone, @@session.time_zone;

SELECT UNIX_TIMESTAMP( ) , FROM_UNIXTIME( UNIX_TIMESTAMP( ) ,  '%Y-%m-%d %H:%i:%S' );
 /* 
 1st column: timezone of UNIX_TIMESTAMP( ) is UTC
 2nd column: 'FROM_UNIXTIME( UNIX_TIMESTAMP( ) ,  '%Y-%m-%d %H:%i:%S' )' is server timezone dependent
 */

today, yesterday or the day before yesterday[edit]

today

  • MySQL: SELECT CURRENT_DATE(); or SELECT CURDATE(); Returns 2017-06-27 [5]
  • EXCEL:
    • =TODAY() Returns 2017/06/27 [6]
    • =TEXT(TODAY(), "YYYY-MM-DD") Returns 2017-06-27 [7]
  • PHP: date('Y-m-d', TIME())
  • Shell script in Linux Os linux.png or Mac Os mac.png with GUN date installed.
    • echo $(date +"%Y-%m-%d") or echo `date +\%Y-\%m-\%d` 2>&1 or date +"%Y-%m-%d" Returns 2017-06-27[8]
    • specify timezone: echo $(TZ=":Asia/Taipei" date +"%Y-%m-%d") or echo $(TZ=":US/Eastern" date +"%Y-%m-%d")[9]


yesterday //Returns sample: 2017-06-26

  • MySQL:
    • SELECT DATE(CURRENT_TIMESTAMP - INTERVAL 1 DAY);
    • SELECT DATE_SUB(DATE( CURRENT_DATE() ), INTERVAL 1 DAY);
  • EXCEL: =TEXT(TODAY()-1, "YYYY-MM-DD") [10]
  • PHP: date('Y-m-d', strtotime("-1 days"))
  • Shell script in Linux Os linux.png with GUN date installed. Not work on Mac Os mac.png
    • echo $(date -d "yesterday" '+%Y-%m-%d')[11]
    • date -d '-1 day' '+%Y-%m-%d' [12]
    • specify timezone: echo $(TZ=":Asia/Taipei" date -d "yesterday" '+%Y-%m-%d') or echo $(TZ=":US/Eastern" date -d "yesterday" '+%Y-%m-%d')

the day before yesterday //Returns sample: 2017-06-25

  • MySQL
    • SELECT DATE(CURRENT_TIMESTAMP - INTERVAL 2 DAY);
    • SELECT DATE_SUB(DATE( CURRENT_DATE() ), INTERVAL 2 DAY);
  • EXCEL: =TEXT(TODAY()-2, "YYYY-MM-DD")
  • PHP: date('Y-m-d', strtotime("-2 days"))
  • Shell script in Linux Os linux.png with GUN date installed. Not work on Mac Os mac.png
    • date -d '-2 day' '+%Y-%m-%d' [13]

specified time[edit]

Convert the unix timestamp to human readable time format[edit]

ex: 2010-12-21 10:05:06

  • Epoch Converter - Unix Timestamp Converter
  • 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] Icon exclaim.gif server timezone dependent
  • MySQL:
    • SELECT FROM_UNIXTIME( 1306311155 ); //convert the time stamp 1306311155 to the human readable time format 2011-05-25 08:12:11 Icon exclaim.gif server timezone dependent
    • 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 Icon exclaim.gif server timezone dependent
    • convert mysql datatype timestamp (UTC) to Taipei datetime (client timezone dependent) [14]
/* approach1 */
SET time_zone='+8:00';
SELECT FROM_UNIXTIME( `column_of_timestamp` ,  '%Y-%m-%d %H:%i:%S' ) FROM `table`;

/* approach2: detect server timezone automatically. Note the precision of time difference between server timezone to your preferred timezone is hours NOT to minutes */
SELECT convert_tz(
   FROM_UNIXTIME( `column_of_timestamp` ,  '%Y-%m-%d %H:%i:%S' ),   
   CONCAT(
     ROUND(
      TIME_TO_SEC(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')))/3600),  ':00')
   , '+08:00')
FROM `table`

/*  approach3: If you already know the server timezone ex: -05:00 */
SELECT convert_tz(
   FROM_UNIXTIME( `column_of_timestamp` ,  '%Y-%m-%d %H:%i:%S' ),  '-05:00', '+08:00')
FROM `table`
  • Excel
    • =( unix_time_stamp /86400)+DATE(1970,1,1) // convert the unix time stamp 1421539200 to the human readable time format 2015/1/18 [15]

Convert human-readable time to timestamp[edit]

  • PHP: using strtotime() function ex: strtotime('2010-12-21 10:05:06') [16]
  • mysql:
    • SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57'); /* return timestamp: 1300186437 */ Icon exclaim.gif server timezone dependent
    • SELECT UNIX_TIMESTAMP(STR_TO_DATE('2011-03-15 18:53:57', '%Y-%m-%d %H:%i:%S')); /* return timestamp: 1300186437 */ Icon exclaim.gif server timezone dependent
  • Excel:
    • input data (A2 cell): YYYY/MM/DD (e.g. 2017/06/27 ) or YYYY/MM/DD HH:MM (e.g. 2017/06/27 07:03)
    • =(A2-DATE(1970,1,1))*86400 [17]

convert datevalue (number of days since 1900/1/1) ↔ to human-readable date

  • Excel: =DATEVALUE(Cell_of_date) // ex: =DATEVALUE("2015/1/27") returns 42031. DATEVALUE of 1900/1/1 is 1
  • Excel: =TEXT(Cell_of_datevalue,"yyyy/mm/dd") // ex: =TEXT(42031,"yyyy/mm/dd") returns 2015/1/27

convert month name to numeric date ex: June 27, 2017 -> 2017-06-27

Previous month based on current month[edit]

  • PHP: echo date("Y-m", strtotime("-1 month")); // Returns YYYY-MM [19]
  • MySQL
  • Excel: Using EOMONTH() function. =YEAR(EOMONTH(NOW(), -2)+1)&"-"&TEXT(MONTH(EOMONTH(NOW(), -2)+1), "00") // Returns YYYY-MM [20]

PHP microtime to MySQL timestamp[edit]

PHP microtime to MySQL timestamp

time difference of two time values[edit]

  • php: using mktime() function
  • mysql: using TIMEDIFF() function ex: SELECT TIMEDIFF('2016:02:01 00:00:00', '2016:01:01 00:00:00'); /* return 744:00:00 */ Icon exclaim.gif time difference between two DATETIME values

timer[edit]

Timer

date interval[edit]

When people said 2017-06-01 ~ 2017-06-03, it means 2017-06-01 00:00:00 ~ 2017-06-03 59:59:59 in the system.

Query by month[edit]

mysql ex: find the records in January, 2017[21] Data type of column `time` is datetime (ex: yy:mm:dd hh:mm:ss). Using one of sql queries as following. online demo

  • SELECT * FROM `table_name` WHERE YEAR(`time`) = '2017' AND MONTH(`time`) = '1' /*MONTH(`time`) = '1' or MONTH(`time`) = '01' are both ok*/
  • SELECT * FROM `table_name` WHERE DATE_FORMAT(`time`, '%Y-%m') = '2017-01'
  • SELECT * FROM `table_name` WHERE `time` LIKE '2017-01%' /*find the time records starting with the string 2017-01 */
  • SELECT * FROM `table_name` WHERE DATE(`time`) between '2017-01-01' AND '2017-01-31' Icon exclaim.gif add the DATE function to the `time` field
  • SELECT * FROM `table_name` WHERE `time` >= '2017-01-01 00:00:00' AND `time` < '2017-02-01 00:00:00'
  • SELECT * FROM `table_name` WHERE `time` >= '2017-01-01 00:00:00' AND `time` <= '2017-01-31 59:59:59'

date format[edit]

YYYY-MM-DD e.g. 2017-06-27 was converted from 2017-06-27 01:23:45.

YYYY-MM-01: First day of the specific month[22] e.g. 2017-06-01 was converted from 2017-06-27 01:23:45.

YYYYMM e.g. 201706 was converted from 2017-06-27 01:23:45.

further reading[edit]

Math[edit]

Format a number[edit]

Round a number downward to its nearest integer[edit]

other[edit]

  • a >0 OR b > 0 OR c > 0
  • a+b+c > 0


Text / String[edit]

length of characters[edit]

echo mb_strlen("《王大文 Dawen》", 'UTF-8'); // return 11
echo strlen("《王大文 Dawen》"); // return 21
SELECT CHAR_LENGTH("《王大文 Dawen》"); /* return 11 */
SELECT LENGTH("《王大文 Dawen》"); /* return 21 */
=LEN("《王大文 Dawen》") // return 11

text match[edit]

//PHP code snippet:
$array = array("AAA", "BBB", "CCC");

if(in_array($var, $array){
//do something
}
  • preg_match...
  • verbose approach: Icon exclaim.gif IF (0 == 'String') always be true?[23]
if($var =="AAA" OR $var =="BBB" OR $var =="CCC"){
//do something
}


text input filter[edit]

trigger to make backup of deleted data before deleting them[edit]

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[edit]

redirect


reload

  • Javascript
    • iframe (reload) - parent.location.reload();

find unique (non duplicated) data[edit]

  • SELECT DISTINCT column FROM table[24]
  • (1)SELECT * FROM table; (2)Using PHP: array_unique for data handling two or more columns
  • SELECT column FROM table GROUP BY column

export/import sql file[edit]

MySQL commands

reverse the boolean value[edit]

  • PHP:
$var = true;
print( !$var); //add ! symbol to reverse the boolean value

ideas[edit]

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[edit]

How to generate the table schema

  1. Using phpMyAdmin or other MySQL client
  2. Input the SQL query:
    EXPLAIN table_name;
  3. Print view (with full texts)
  4. Easy to copy the table to other work processor
  5. (optional) Adjust the text size to 10

debug[edit]

SQL syntax debug

references[edit]

  1. MySQL :: MySQL 5.5 Reference Manual :: 10.6 MySQL Server Time Zone Support
  2. MySQL :: MySQL 5.1 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types
  3. timezone - How do I get the current time zone of MySQL? - Stack Overflow
  4. JavaScript getTime() Method
  5. MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions
  6. How to change Excel date format and create custom formatting
  7. How to change Excel date format and create custom formatting
  8. Using the ‘date’ command in your crontab
  9. How can I have `date` output the time from a different timezone? - Unix & Linux Stack Exchange
  10. Formula yesterday's date
  11. Get yesterday's date in bash on Linux, DST-safe - Stack Overflow & unix - Get the date (a day before current time) in Bash - Stack Overflow
  12. shell - How to get yesterday and day before yesterday in linux? - Stack Overflow
  13. shell - How to get yesterday and day before yesterday in linux? - Stack Overflow
  14. timezone - How do I get the current time zone of MySQL? - Stack Overflow
  15. Excel Timestamp to Date ← Automate Everything
  16. How to convert date to timestamp in PHP? - Stack Overflow
  17. Excel date to Unix timestamp - Stack Overflow
  18. MySQL MONTHNAME() from numbers - Stack Overflow
  19. Getting last month's date in php - Stack Overflow
  20. Excel formula: Get first day of previous month | Exceljet
  21. sql - MySQL Query GROUP BY day / month / year - Stack Overflow
  22. mysql - How do I get the first day of the current month? - Stack Overflow
  23. Why does PHP consider 0 to be equal to a string? - Stack Overflow
  24. MySQL :: MySQL 5.0 Reference Manual :: 8.3.1.13 DISTINCT Optimization