Tuesday, May 21, 2013

PHP/MySQL store time and compare time



1)Using MySQl datetime to create timestamp in year-month-day hour-minute-second
CREATE TABLE  `cnsh_lms`.`cesei_member_extra` (
  `member_id` mediumint(8) unsigned NOT NULL,
  `dept_type` tinyint(4) NOT NULL default '0',
  `edu_type` tinyint(4) NOT NULL default '0',
  `modify_date` datetime default '0000-00-00 00:00:00',
  PRIMARY KEY  (`member_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2)  Using PHP function date('Y-m-d H:i:s') to create time compatible with MYSQL datetime
 $modify_date = date('Y-m-d H:i:s');   
3) Create a form to select month, day and year.
                      <select name="s_m" id="s_m">
                        <?php
                           for($time = 1; $time <= 12; $time++){
                              echo('<option value="'.$time.'"');
                                  if($_GET['s_m'] == $time)
                              {
                                 echo(' selected="selected"');
                              }                  
                            
                              if(date("n") == $time){
                                  if(!isset($_GET['s_d'])) echo(' selected="selected"');
                              }
                              echo('>'.date("F",mktime(0,0,0,$time)).'</option>');
                           }
                        ?>
                     </select> -
                     <select name="s_d" id="s_d">
                        <?php
                           for($time = 1; $time <= 31; $time++){
                              echo('<option value="'.$time.'"');
                              if($_GET['s_d'] == $time)
                              {
                                 echo(' selected="selected"');
                              }
                            
                              if(date("j") == $time){
                                 if(!isset($_GET['s_d'])) echo(' selected="selected"');
                              }
                            
                              echo('>'.sprintf("%02d", $time).'</option>');
                           }
                        ?>
                     </select> -
                     <select name="s_y" id="s_y">
                        <?php
                           for($time = 2005; $time <= 2025; $time++){
                              echo('<option value="'.$time.'"');
                             if($_GET['s_y'] == $time)
                              {
                                 echo(' selected="selected"');
                              }                        
                              if(date("Y") == $time){
                                  if(!isset($_GET['s_y'])) echo(' selected="selected"');
                              }
                              echo('>'.$time.'</option>');
                           }
                        ?>
                     </select>

4)  using PHP function mktime to return unix time
$search_date  = mktime(0,0,0,intval($_POST['s_m']),intval($_POST['s_d']),intval($_POST['s_y']));
5) Using MySQl UNIX_TIMESTAMP to transfer datetime to UNIX time to compare.
$sql="SELECT * FROM cesei_member_extra WHERE UNIX_TIMESTAMP(modify_date)  >= ".$search_edate;

No comments:

Post a Comment