PHP and MySQL syntax: Difference between revisions

Revision as of 13:30, 11 August 2014

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

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

the human readable time format ex: 2026-09-05 22:42: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:
- SELECT NOW(); /*return the human readable current time: 2011-04-01 12:19:43 */ which mapping to DATETIME type<ref>MySQL :: MySQL 5.1 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types</ref>
- 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 */
- SELECT CURRENT_DATE; /* Return 2026-09-05 */<ref>MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions</ref>
EXCEL: =TODAY() Returns the current date. ex: 2026/09/05 <ref>TODAY function - Excel - Office.com</ref>

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 <ref>JavaScript getTime() Method</ref>
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' );

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

convert human-readable time to timestamp

php: using strtotime() function ex: strtotime('2010-12-21 10:05:06') <ref>How to convert date to timestamp in PHP? - Stack Overflow</ref>
mysql: SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57'); /* return timestamp: 1300186437 */

php: using mktime() function
mysql: SELECT TIMEDIFF('2010:01:01 00:00:00', '2010:02:01 00:00:00'); /* return -744:00:00 */

mysql ex: find the records in January, 2014<ref>sql - MySQL Query GROUP BY day / month / year - Stack Overflow</ref>
- SELECT * FROM table_name WHERE YEAR(`time`) = '2014' AND MONTH(`time`) = '1'
- SELECT * FROM table_name WHERE `time` >= '2014-01-01 00:00:00' AND `time` < '2014-02-01 00:00:00'

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

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

SELECT DISTINCT column FROM table<ref>MySQL :: MySQL 5.0 Reference Manual :: 8.3.1.13 DISTINCT Optimization</ref>
(1)SELECT * FROM table; (2)Using PHP: array_unique for data handling two or more columns
SELECT column FROM table GROUP BY column

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

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

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

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.

@@ Line 43: / Line 43: @@
 * 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>
 ** SELECT * FROM table_name WHERE YEAR(`time`) = '2014' AND MONTH(`time`) = '1'
-** SELECT * FROM table_name WHERE `time` >= '2014-01-01 00:00:00' AND `time` < '2014-02-01'
+** SELECT * FROM table_name WHERE `time` >= '2014-01-01 00:00:00' AND `time` < '2014-02-01 00:00:00'
 === further reading ===