Comparing Today Date in MySQL

Written on December 07, 2009 by Zaki and 2 peoples have comment

mysql
Conventionally getting today date from database in the past would require the use of date('Y-m-d'); function and add it to MySQL query such as below:

$today_date = date('Y-m-d');
$tmrw_date = date('Y-m-d', strtotime("+1 day"));
$sql = "SELECT * FROM my_table
    WHERE my_datetime BETWEEN '" . $today_date . "' AND '" . $tmrw_date . "'";

However this method might return false positive result if the web server and database server doesn’t have it date and time synchronized. From my point of view, it best to let the database server have the full control to insert, update or compare the dates. So what the alternative? You can check the full specification from MySQL Date and Time Functions but I would opt for DATEDIFF if you specifically want to compare with today date. 

SELECT * FROM my_table
    WHERE DATEDIFF(my_datetime, SYSDATE())=0

UPDATE on SELECT With MySQL

Written on July 14, 2009 by Zaki and No one have comment

mysql
Previously if there a need to something like this, I would have definitely create a temporary tool using PHP to automatically loop a SELECT and UPDATE each field inside the loop, for example:

$sql = "SELECT * FROM item";
$query = $this->db->query($sql);
foreach( $query->result_array() as $row ) :
    $this->db->query("UPDATE relationship
        SET rel_date=?
        WHERE (rel_value=? AND rel_type=1)", array(
        $row['item_datetime'],
        $row['item_id']
    ));
endforeach;

This actually can be simplify using only MySQL query such as:

UPDATE relationship r, item i
    SET r.rel_date=i.item_datetime
    WHERE (r.rel_value=i.item_id AND r.rel_type=1)

MONTHS_BETWEEN Query in Oracle

Written on March 25, 2009 by Zaki and No one have comment

Detect number of months between two date:

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2009-01-01', 'YYYY-MM-DD')) AS MON
FROM DUAL;

MySQL Buddy

Written on May 23, 2008 by Zaki and No one have comment

Tired of looking at plain old PHPMyAdmin or have you ever wish you can provide your non tech-savvy client a better option to browse their database? SQL Buddy – Web based MySQL administration might answer your dreams.