PHP and MySQL syntax: Difference between revisions

Jump to navigation Jump to search

PHP and MySQL syntax (edit)

Revision as of 21:00, 28 July 2019

2,183 bytes added ,  28 July 2019
m
no edit summary
mNo edit summary
(35 intermediate revisions by the same user not shown)
Line 2: Line 2:


== time / timestamp ==
== time / timestamp ==
=== terms of time format ===
<table border="1" class="wikitable sortable nowrap">
<tr>
  <th> format \ date </th>
  <th> 1900/1/1</th>
  <th> 1970/1/1</th>
  <th> 2016/1/1 </th>
  <th> notes </th>
</tr>
<tr>
  <td> value of unix timestamp <br /> (the number of seconds since 1970/01/01)</td>
  <td> -2209075200</td>
  <td> 0</td>
  <td> 1451606400</td>
  <td> </td>
</tr>
<tr>
  <td> value of Excel [https://support.office.com/zh-tw/article/DATEVALUE-%E5%87%BD%E6%95%B8-df8b07d4-7761-4a93-bc33-b7471bbff252 DATEVALUE] function <br />  (number of days since 1900/1/1) </td>
  <td> 1</td>
  <td> 25569</td>
  <td> 42370</td>
  <td> 5 digit numbers after 1970/1/1 </td>
</tr>
</table>


=== now ===
=== now ===
==== '''human readable''' time format ====
==== '''human readable''' time format ====
Convert '''unix timestamp''' (the number of seconds since 1970/01/01) <--> to the '''human readable''' time format ex: {{Template:Today}} {{CURRENTTIME}}:06
Convert '''unix timestamp''' (the number of seconds since 1970/01/01) <--> to the '''human readable''' time format ex: {{Template:Today}} {{CURRENTTIME}}:06
* php: {{kbd | key=<nowiki>echo date("Y-m-d H:i:s", time() );</nowiki>}} //Convert the time stamp of current time to the human readable time format. Ex: return '{{Template:Today}} {{CURRENTTIME}}:06' {{exclaim}} server timezone dependent
* php: {{kbd | key=<nowiki>echo date('Y-m-d H:i:s');</nowiki>}} or {{kbd | key=<nowiki>echo date('Y-m-d H:i:s', time() );</nowiki>}} //Convert the time stamp of current time to the human readable time format. Ex: return '{{Template:Today}} {{CURRENTTIME}}:06' {{exclaim}} server timezone dependent
* mysql: {{exclaim}} default client timezone is server timezone<ref>[https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html MySQL :: MySQL 5.5 Reference Manual :: 10.6 MySQL Server Time Zone Support]</ref>
* mysql: {{exclaim}} default client timezone is server timezone<ref>[https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html MySQL :: MySQL 5.5 Reference Manual :: 10.6 MySQL Server Time Zone Support]</ref>
** {{kbd | key=<nowiki>SELECT NOW();</nowiki>}} Returns {{Template:Today}} {{CURRENTTIME}}:06 which mapping to {{kbd | key=<nowiki>DATETIME</nowiki>}} type<ref>[http://dev.mysql.com/doc/refman/5.1/en/datetime.html MySQL :: MySQL 5.1 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types]</ref>
** {{kbd | key=<nowiki>SELECT NOW();</nowiki>}} Returns {{Template:Today}} {{CURRENTTIME}}:06 which mapping to {{kbd | key=<nowiki>DATETIME</nowiki>}} type<ref>[http://dev.mysql.com/doc/refman/5.1/en/datetime.html MySQL :: MySQL 5.1 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types]</ref>
Line 52: Line 28:
SELECT convert_tz(CURRENT_TIMESTAMP,  '-05:00', '+08:00');
SELECT convert_tz(CURRENT_TIMESTAMP,  '-05:00', '+08:00');
</pre>
</pre>
* shell script with GUN {{kbd | key=<nowiki>date</nowiki>}} installed in {{Linux}}, {{Mac}} or [https://www.cygwin.com/ Cygwin] of {{Win}}:  
* BASH (UNIX shell script) with GUN {{kbd | key=<nowiki>date</nowiki>}} installed in {{Linux}}, {{Mac}} or [https://www.cygwin.com/ Cygwin] of {{Win}}:  
** {{kbd | key=<nowiki>echo `date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1</nowiki>}} or  {{kbd | key=<nowiki>date +\%Y-\%m-\%d\ %H:%M:%S</nowiki>}} Returns {{Template:Today}} {{CURRENTTIME}}:06 {{exclaim}} server timezone dependent
** {{kbd | key=<nowiki>echo $(date '+%Y-%m-%d %H:%M:%S')</nowiki>}}<ref>[https://ss64.com/bash/date.html date Man Page - Linux - SS64.com]</ref> or {{kbd | key=<nowiki>echo `date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1</nowiki>}} or  {{kbd | key=<nowiki>date +\%Y-\%m-\%d\ %H:%M:%S</nowiki>}} Returns {{Template:Today}} {{CURRENTTIME}}:06 {{exclaim}} server timezone dependent
** {{kbd | key=<nowiki>echo `TZ=Asia/Taipei date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1</nowiki>}} or {{kbd | key=<nowiki>TZ=Asia/Taipei date +\%Y-\%m-\%d\ %H:%M:%S</nowiki>}} Returns {{Template:Today}} {{CURRENTTIME}}:06 {{exclaim}} force to use Taipei timezone
** {{kbd | key=<nowiki>echo `TZ=Asia/Taipei date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1</nowiki>}} or {{kbd | key=<nowiki>TZ=Asia/Taipei date +\%Y-\%m-\%d\ %H:%M:%S</nowiki>}} Returns {{Template:Today}} {{CURRENTTIME}}:06 {{exclaim}} force to use Taipei timezone


==== current '''timestamp''' ====
==== current '''timestamp''' ====
The number of seconds since 1970/01/01. [https://zh.wikipedia.org/wiki/UTC%C2%B10 UTC±0] e.g. 2011-05-30 01:56:38 returns 1306720622
The number of seconds since 1970/01/01. [https://zh.wikipedia.org/wiki/UTC%C2%B10 UTC±0] e.g. 2011-05-30 01:56:38 returns 1306720622
* BASH for {{Linux}} & {{Mac}}: {{kbd | key=<nowiki>echo $(date +%s)</nowiki>}} "%s - seconds since 1970-01-01 00:00:00 UTC"<ref>[https://linux.die.net/man/1/date date(1): print/set system date/time - Linux man page]</ref>
* BASH for {{Mac}}: (1) {{kbd | key=<nowiki>brew install coreutils</nowiki>}} (2) key-in {{kbd | key=<nowiki>gdate +%s</nowiki>}}<ref>[https://www.gsp.com/cgi-bin/man.cgi?section=1&topic=gdate gdate(1) - print or set the system date and time]</ref>
* php: {{kbd | key=<nowiki>echo time();</nowiki>}}
* php: {{kbd | key=<nowiki>echo time();</nowiki>}}
* php: {{kbd | key=<nowiki>echo microtime();</nowiki>}}  [http://php.net/manual/en/function.microtime.php PHP: microtime - Manual] "Returns current Unix timestamp with microseconds" //ex: 0.45920500 1406776901
* php: {{kbd | key=<nowiki>echo microtime();</nowiki>}}  [http://php.net/manual/en/function.microtime.php PHP: microtime - Manual] "Returns current Unix timestamp with microseconds" //ex: 0.45920500 1406776901
Line 74: Line 52:
  */
  */
</pre>
</pre>
How to print the number of nanoseconds or number of microseconds since 1970/01/01:
<div style="width:100%; min-height: .01%; overflow-x: auto;">
<table border="1" class="wikitable sortable nowrap">
<tr>
  <th>Unit </th>
  <th>Example </th>
  <th>Approach1: {{kbd | key=date}}</th>
  <th>Approach2: {{kbd | key=gdate}}<ref>[https://apple.stackexchange.com/questions/231224/how-to-have-gnus-date-in-os-x macos - How to have GNU's date in OS X? - Ask Different]</ref></th>
</tr>
<tr>
  <td>(Human readable)</td>
  <td>2018-11-04 13:55:52</td>
  <td>{{kbd | key=<nowiki>echo $(date '+%Y-%m-%d %H:%M:%S')</nowiki>}} for {{Linux}}, {{Mac}} & CygWin on {{Win}}</td>
  <td>{{kbd | key=<nowiki>echo $(gdate '+%Y-%m-%d %H:%M:%S')</nowiki>}} for {{Mac}}</td>
</tr>
<tr>
  <td>the number of seconds since 1970/01/01</td>
  <td>1541310952</td>
  <td>{{kbd | key=<nowiki>echo $(date '+%s')</nowiki>}} for {{Linux}}, {{Mac}} & CygWin on {{Win}}</td>
  <td>{{kbd | key=<nowiki>echo $(gdate '+%s')</nowiki>}} for {{Mac}}</td>
</tr>
<tr>
  <td>the number of microseconds since 1970/01/01</td>
  <td>1541310952.181870</td>
  <td>{{kbd | key=<nowiki>printf "%.6f\n" $(date '+%s.%N')</nowiki>}} for {{Linux}} & CygWin on {{Win}}</td>
  <td>{{kbd | key=<nowiki>printf "%.6f\n" $(gdate '+%s.%N')</nowiki>}} for {{Mac}}</td>
</tr>
<tr>
<tr>
  <td>the number of nanoseconds since 1970/01/01</td>
  <td>1541310952.181870383</td>
  <td>{{kbd | key=<nowiki>echo $(date '+%s.%N')</nowiki>}} for {{Linux}} & CygWin on {{Win}}</td>
  <td>{{kbd | key=<nowiki>echo $(gdate '+%s.%N')</nowiki>}} for {{Mac}}</td>
</tr>
<tr>
</table>
</div>
{{exclaim}} Since {{kbd | key=<nowiki>echo $(date '+%N')</nowiki>}} was not supported on {{Mac}}<ref>[https://superuser.com/questions/599072/how-to-get-bash-execution-time-in-milliseconds-under-mac-os-x macos - How to get Bash execution time in milliseconds under Mac OS X? - Super User]</ref>, alternative approach is the installation of {{kbd | key=gdate}}.


=== specified time ===
=== specified time ===
Line 106: Line 124:
** {{kbd | key==( unix_time_stamp /86400)+DATE(1970,1,1) }} // convert the unix time stamp 1421539200 to the human readable time format 2015/1/18 <ref>[http://www.bajb.net/2010/05/excel-timestamp-to-date/ Excel Timestamp to Date ← Automate Everything]</ref>
** {{kbd | key==( unix_time_stamp /86400)+DATE(1970,1,1) }} // convert the unix time stamp 1421539200 to the human readable time format 2015/1/18 <ref>[http://www.bajb.net/2010/05/excel-timestamp-to-date/ Excel Timestamp to Date ← Automate Everything]</ref>


==== Convert human-readable time to '''timestamp''' ====
==== Convert human-readable time to '''timestamp''' (string to timestamp) ====
* PHP: using [http://php.net/manual/en/function.strtotime.php strtotime()] function ex: strtotime('2010-12-21 10:05:06') <ref>[http://stackoverflow.com/questions/113829/how-to-convert-date-to-timestamp-in-php How to convert date to timestamp in PHP? - Stack Overflow]</ref>
* PHP: Using [http://php.net/manual/en/function.strtotime.php strtotime()] function ex: strtotime('2010-12-21 10:05:06') <ref>[http://stackoverflow.com/questions/113829/how-to-convert-date-to-timestamp-in-php How to convert date to timestamp in PHP? - Stack Overflow]</ref>
* mysql:  
* mysql: Using [https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_unix-timestamp UNIX_TIMESTAMP()] function
** {{kbd | key = SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57');}} /* return timestamp: 1300186437 */ {{exclaim}} server timezone dependent
** {{kbd | key = SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57');}} /* return timestamp: 1300186437 */ {{exclaim}} server timezone dependent
** {{kbd | key = SELECT UNIX_TIMESTAMP(STR_TO_DATE('2011-03-15 18:53:57', '%Y-%m-%d %H:%i:%S'));}} /* return timestamp: 1300186437 */ {{exclaim}} server timezone dependent
** {{kbd | key = SELECT UNIX_TIMESTAMP(STR_TO_DATE('2011-03-15 18:53:57', '%Y-%m-%d %H:%i:%S'));}} /* return timestamp: 1300186437 */ {{exclaim}} server timezone dependent
Line 140: Line 158:


* PHP: {{kbd | key =<nowiki>date('Y-m-d', TIME())</nowiki>}}
* PHP: {{kbd | key =<nowiki>date('Y-m-d', TIME())</nowiki>}}
* Javascript: [https://www.w3schools.com/jsref/jsref_obj_date.asp JavaScript Date Reference] {{access | date=2018-10-18}}


* Shell script in {{Linux}} or {{Mac}}  with GUN {{kbd | key=<nowiki>date</nowiki>}} installed.   
* Shell script in {{Linux}} or {{Mac}}  with GUN {{kbd | key=<nowiki>date</nowiki>}} installed.   
Line 145: Line 165:
** specify timezone: {{kbd | key=<nowiki>echo $(TZ=":Asia/Taipei" date +"%Y-%m-%d")</nowiki>}} or {{kbd | key=<nowiki>echo $(TZ=":US/Eastern" date +"%Y-%m-%d")</nowiki>}}<ref>[http://unix.stackexchange.com/questions/48101/how-can-i-have-date-output-the-time-from-a-different-timezone How can I have `date` output the time from a different timezone? - Unix & Linux Stack Exchange]</ref>
** specify timezone: {{kbd | key=<nowiki>echo $(TZ=":Asia/Taipei" date +"%Y-%m-%d")</nowiki>}} or {{kbd | key=<nowiki>echo $(TZ=":US/Eastern" date +"%Y-%m-%d")</nowiki>}}<ref>[http://unix.stackexchange.com/questions/48101/how-can-i-have-date-output-the-time-from-a-different-timezone How can I have `date` output the time from a different timezone? - Unix & Linux Stack Exchange]</ref>


<pre>
// JavaScript
var date = new Date();
date;
// output: Thu Oct 18 2018 16:10:33 GMT+0800 (台北標準時間)
</pre>


yesterday //Returns sample: {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{#time: d |now - 24 hours}}
yesterday //Returns sample: {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{#time: d |now - 24 hours}}
Line 154: Line 181:


* PHP: {{kbd | key =<nowiki>date('Y-m-d', strtotime("-1 days"))</nowiki>}}
* PHP: {{kbd | key =<nowiki>date('Y-m-d', strtotime("-1 days"))</nowiki>}}
* JavaScript: [https://stackoverflow.com/questions/5511323/calculate-the-date-yesterday-in-javascript Calculate the date yesterday in JavaScript - Stack Overflow] {{access | date=2018-10-18}}


* Shell script in {{Linux}} with GUN {{kbd | key=<nowiki>date</nowiki>}} installed. '''Not''' work on {{Mac}}   
* Shell script in {{Linux}} with GUN {{kbd | key=<nowiki>date</nowiki>}} installed. '''Not''' work on {{Mac}}   
Line 159: Line 188:
** {{kbd | key=<nowiki> date -d '-1 day' '+%Y-%m-%d' </nowiki>}}<ref>[http://stackoverflow.com/questions/22043088/how-to-get-yesterday-and-day-before-yesterday-in-linux shell - How to get yesterday and day before yesterday in linux? - Stack Overflow]</ref>
** {{kbd | key=<nowiki> date -d '-1 day' '+%Y-%m-%d' </nowiki>}}<ref>[http://stackoverflow.com/questions/22043088/how-to-get-yesterday-and-day-before-yesterday-in-linux shell - How to get yesterday and day before yesterday in linux? - Stack Overflow]</ref>
** specify timezone: {{kbd | key=<nowiki>echo $(TZ=":Asia/Taipei" date -d "yesterday" '+%Y-%m-%d')</nowiki>}} or {{kbd | key=<nowiki>echo $(TZ=":US/Eastern" date -d "yesterday" '+%Y-%m-%d')</nowiki>}}
** specify timezone: {{kbd | key=<nowiki>echo $(TZ=":Asia/Taipei" date -d "yesterday" '+%Y-%m-%d')</nowiki>}} or {{kbd | key=<nowiki>echo $(TZ=":US/Eastern" date -d "yesterday" '+%Y-%m-%d')</nowiki>}}
<pre>
// JavaScript
var date = new Date();
date.setDate(date.getDate() - 1);
date;
// output: Wed Oct 17 2018 16:15:43 GMT+0800 (台北標準時間)
</pre>


the day before yesterday //Returns sample: {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{#time: d |now - 48 hours}}
the day before yesterday //Returns sample: {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{#time: d |now - 48 hours}}
Line 168: Line 206:
* Shell script in {{Linux}} with GUN {{kbd | key=<nowiki>date</nowiki>}} installed. '''Not''' work on {{Mac}}   
* Shell script in {{Linux}} with GUN {{kbd | key=<nowiki>date</nowiki>}} installed. '''Not''' work on {{Mac}}   
** {{kbd | key=<nowiki> date -d '-2 day' '+%Y-%m-%d' </nowiki>}}<ref>[http://stackoverflow.com/questions/22043088/how-to-get-yesterday-and-day-before-yesterday-in-linux shell - How to get yesterday and day before yesterday in linux? - Stack Overflow]</ref>
** {{kbd | key=<nowiki> date -d '-2 day' '+%Y-%m-%d' </nowiki>}}<ref>[http://stackoverflow.com/questions/22043088/how-to-get-yesterday-and-day-before-yesterday-in-linux shell - How to get yesterday and day before yesterday in linux? - Stack Overflow]</ref>
<pre>
// JavaScript
var date = new Date();
date.setDate(date.getDate() - 2);
date;
// output: Wed Oct 16 2018 16:15:43 GMT+0800 (台北標準時間)
</pre>


==== Day of week ====
==== Day of week ====
Line 237: Line 285:


== Text / String ==
== Text / String ==
=== length of characters ===
=== Number of characters ===
* PHP: [http://php.net/mb_strlen PHP mb_strlen function]
 
<pre>
[[Number of characters]]
echo mb_strlen("《王大文 Dawen》", 'UTF-8'); // return 11
echo strlen("《王大文 Dawen》"); // return 21
</pre>
* MySQL: [http://www.w3resource.com/mysql/string-functions/mysql-char_length-function.php MySQL CHAR_LENGTH() function]
<PRE>
SELECT CHAR_LENGTH("《王大文 Dawen》"); /* return 11 */
SELECT LENGTH("《王大文 Dawen》"); /* return 21 */
</PRE>
* Excel: [https://support.office.com/en-us/article/LEN-LENB-functions-29236f94-cedc-429d-affd-b5e33d2c67cb?ui=en-US&rs=en-US&ad=US LEN function] / [https://support.office.com/zh-tw/article/LEN%E3%80%81LENB-%E5%87%BD%E6%95%B8-29236f94-cedc-429d-affd-b5e33d2c67cb LEN 函數]
<pre>
=LEN("《王大文 Dawen》") // return 11
</pre>
* [http://string-functions.com/length.aspx Calculate String Length Online]


=== text match ===
=== text match ===
Line 278: Line 313:
* {{Gd}} [http://php.net/manual/en/pdo.prepare.php PHP: PDO::prepare] + [http://php.net/manual/en/pdostatement.bindparam.php PDOStatement::bindParam]
* {{Gd}} [http://php.net/manual/en/pdo.prepare.php PHP: PDO::prepare] + [http://php.net/manual/en/pdostatement.bindparam.php PDOStatement::bindParam]
* [http://www.php.net/manual/en/mysqli.real-escape-string.php PHP: mysqli::real_escape_string]
* [http://www.php.net/manual/en/mysqli.real-escape-string.php PHP: mysqli::real_escape_string]
== List of binary safe functions ==
[[List of PHP binary safe functions]]


== trigger to make backup of deleted data before deleting them ==
== trigger to make backup of deleted data before deleting them ==
Line 348: Line 386:
[[Category:Web_Dev]]
[[Category:Web_Dev]]
[[Category:MySQL]]
[[Category:MySQL]]
[[Category:Bash]]
[[Category:Data_hygiene]]
[[Category:Data_hygiene]]
[[Category:Data Science]]
[[Category:Data Science]]
[[Category:Time river]]
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/19407...20822"

Navigation menu