Mysqli PHP从DB中获取的值大于提到的日期时间间隔中的下午6点

My DB

   ID  Date_time 
    1   2016-01-22 01:49:31 pm
    2   2016-01-22 01:49:31 pm
    3   2016-01-21 06:49:31 pm
    4   2016-01-22 01:49:31 pm
    5   2016-01-20 08:49:31 pm

Iam trying fetch value between two dates where the condition is that in Date_time the time should be after 6 pm am not sure where am i going wrong my query is giving all the ides whose time is not more then 6 pm also.

This is my query

$date__G_to ="2016-01-22 06:00:00 pm"; 
    $date__G_frmo ="2016-01-18 06:00:00 pm"; 

    $query_gr6 = "select DISTINCT ID from Mdd_table where  Attendence_taken_date >=  '$date__G_frmo' and Attendence_taken_date <=  '$date__G_to'  ";

    $result_gr6 = mysqli_query($mysqli,$query_gr6)or die(mysqli_error($mysqli));


    $num_row_gr6 = mysqli_num_rows($result_gr6);

    while($row7777w=mysqli_fetch_array($result_gr6))
    {


        echo $row7777w["ID"];

    }

写回答
好问题 0 提建议
追加酬金
关注问题
分享
邀请回答
编辑收藏删除结题
收藏举报

2条回答默认最新

douxia2137 2016-01-22 12:19

关注

Perhaps using between might do it? Edited now to hopefully accomodate varchar rather than datetime column as it should be perhaps!

$query_gr6="select distinct id from `Mdd_table` where
     str_to_date(`Attendence_taken_date`,'%Y-%m-%d %H:%i:%s') between date('{$date__G_frmo}') and date('{$date__G_to}')";

To test this query, which btw, seems to work here:

create table if not exists `mdd_table` (
  `id` int(10) unsigned not null auto_increment,
  `attendence_taken_date` varchar(50) not null default '0',
  primary key (`id`)
) engine=innodb auto_increment=9 default charset=latin1;

/* dummy data */
insert into `mdd_table` (`id`, `attendence_taken_date`) values
    (1, '2016-01-22 06:00:00 pm'),
    (2, '2016-01-21 03:00:00 pm'),
    (3, '2016-01-22 06:20:00 pm'),
    (4, '2016-01-22 06:23:00 pm'),
    (5, '2016-01-24 06:23:00 pm'),
    (6, '2016-01-18 06:00:00 pm'),
    (7, '2016-01-18 06:40:00 pm'),
    (8, '2016-01-19 08:40:00 pm'),
    (9, '2016-01-22 01:49:31 pm'),
    (10, '2016-01-22 01:49:31 pm'),
    (11, '2016-01-21 06:49:31 pm'),
    (12, '2016-01-22 01:49:31 pm'),
    (13, '2016-01-20 08:49:31 pm');

Full example

<html>
    <head>
        <title>date between</title>
    </head>
    <body>
        <?php
            $dbhost =   'localhost';
            $dbuser =   'root'; 
            $dbpwd  =   'xxx'; 
            $dbname =   'xxx';      
            $conn = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );


            $date_from='2016-01-18 06:00:00 pm';
            $date_to='2016-01-22 09:00:00 am';

            $sql="select distinct `id`,`Attendence_taken_date` as 'date' from `mdd_table` where
                    str_to_date( `Attendence_taken_date`, '%Y-%m-%d %H:%i:%s' ) between date('{$date_from}') and date('{$date_to}')
                    and hour(`Attendence_taken_date`) >= 6 and hour(`Attendence_taken_date`) <= 9;";

            $res=$conn->query( $sql );
            if( $res ){
                while( $rs=$res->fetch_object() ){
                    echo 'id:'.$rs->id.' date:'.$rs->date.'<br />'; 
                }
            }

            $conn->close();
        ?>
    </body>
</html>

outputs
-------
id:6 date:2016-01-18 06:00:00 pm
id:7 date:2016-01-18 06:40:00 pm
id:8 date:2016-01-19 08:40:00 pm
id:11 date:2016-01-21 06:49:31 pm
id:13 date:2016-01-20 08:49:31 pm

本回答被题主选为最佳回答 , 对您是否有帮助呢?