PHP and MySQL syntax

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

timestamp

Convert time stamp <--> the human readable time format

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: which mapping to DATETIME type
  • 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')
• 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 函数

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

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

• in_array

$array = array("AAA", "BBB", "CCC");

if(in_array($var, $array){
//do something
}

• preg_match...

• verbose approach:

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

text input filter

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

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.