PHP and MySQL syntax

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 04:09: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
  • 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 */
• EXCEL: =TODAY() Returns the current date. ex: 2024/04/29

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
• 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')
• 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 04:09)
  • =(A2-DATE(1970,1,1))*86400

convert month name to numeric date ex: May 1, 2015 -> 2015-05-01

• PHP:
• MySQL: STR_TO_DATE(str,format) ex: SELECT STR_TO_DATE('May 1, 2015','%M %d, %Y');
• 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 */

Query by month

• mysql ex: find the records in January, 2014 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

• PHP: round - Manual
• JavaScript floor() Method
• ROUND 函數 - Excel - Office.com

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
• (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?

if($var =="AAA" OR $var =="BBB" OR $var =="CCC"){
//do something
}

text input filter

• PHP: PDO::prepare + PDOStatement::bindParam
• PHP: mysqli::real_escape_string

export/import sql file

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

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

SQL syntax debug