PHP CodeIgniter中的持续时间重叠检查

假设我在数据库中有一条记录有一个开始DateTime:2016-03-10 00:00:00 并结束DateTime 2016-03-10 06:00:00。 现在,每次我在数据库中插入新记录时,我都要检查启动DateTime&amp; 结束日期时间使得持续时间不会与数据库中存在的先前记录重叠。 PHP CodeIgniter中是否有任何内置函数来检查它?</ p>

现在我正在使用以下代码执行此操作,但它不起作用:</ p>
\ n

  public function check_b_adds($ data){
$ sql ='select * from listing_b_screen其中(start_time&gt; =?AND end_time&gt; =?)OR(start_time&lt; =?AND end_time&lt; =?)OR(start_time&lt; =?AND end_time&gt; =?)OR(start_time =?AND end_time =?)OR(start_time&gt; =?AND end_time&lt; =?)';
$ query = $ this - &gt; db-&gt;查询($ sql,array($ data ['mystart_time'],$ data ['myend_time'],$ data ['mystart_time'],$ data ['myend_time'],$ data [' mystart_time'],$ data ['myend_time'],$ data ['mystart_time'],$ data ['myend_time'],$ data ['mystart_time'],$ data ['myend_time']));
$ result = $ query-&gt; result_array();
if($ result == NULL || $ result == 0){
return false;
} else {
return True;
}
} \ n </ code> </ pre>
</ div>

展开原文

原文

Let's suppose I have a record in database which has a start DateTime: 2016-03-10 00:00:00 and end DateTime 2016-03-10 06:00:00. Now each time i insert a new record in the database I want to check that the start DateTime & End DateTime are such that the duration never overlaps the previous records present in the Database. Is there any built-in function present in PHP CodeIgniter to check this?

Right now I am doing it using the following code but it doesn't work:

public function check_b_adds($data){
 $sql='select * from listing_b_screen where (start_time >= ? AND end_time >= ?) OR (start_time <= ? AND end_time <= ?) OR (start_time <= ? AND end_time >= ?) OR (start_time = ? AND end_time = ?) OR (start_time >= ? AND end_time <= ?)';
    $query=$this->db->query($sql, array($data['mystart_time'],$data['myend_time'],$data['mystart_time'],$data['myend_time'],$data['mystart_time'],$data['myend_time'],$data['mystart_time'],$data['myend_time'],$data['mystart_time'],$data['myend_time']));
    $result = $query->result_array();
    if ($result == NULL || $result == 0){
        return false;
    } else {
        return True;
    }
}

dongmeijian1716
dongmeijian1716 你能提供一个代码示例吗?@Jaaaaaaay
4 年多之前 回复
douji6896
douji6896 只是想提醒您PHP将数据时间作为字符串传递给数据库。您的代码将无法正常工作,因为您正在将datatime与字符串进行比较在这种情况下,我建议你摆脱db钩子中的codeigniter构建,但使用纯查询,如$query=$this->db(“insertintovalues”);在插入记录之前,请在查询中执行datetime转换
4 年多之前 回复

2个回答



  $ fromdaysDate =“2016-07-13”; //更改日期
$ todaysDate =“2016-07- 25“; //更改日期
$ this-&gt; db-&gt; select('task_id,task_start_date,task_due_date');

$ this-&gt; db-&gt; where('task_start_date&lt; =“'。date('Ym-d',strtotime($ fromdaysDate))。'”');
$ this-&gt; db- &gt; where('task_due_date&gt; =“'。date('Ym-d',strtotime($ todaysDate))。'''');

$ this-&gt; db-&gt; or_where('task_due_date BETWEEN“'。date('Ym-d',strtotime($ fromdaysDate))。'”和“'。date('Ym-d',strtotime( $ todaysDate)) ''“)。

$ alltask = $ this-&gt; db-&gt; get('wba_task') - &gt; result_array();
echo $ this-&gt; db-&gt; last_query();
</ code> < / pre>
</ div>

展开原文

原文

    $fromdaysDate="2016-07-13";//changed date
    $todaysDate="2016-07-25";//changed date
    $this->db->select('task_id,task_start_date, task_due_date'); 
    $this->db->where('task_start_date <="'.date('Y-m-d', strtotime($fromdaysDate)).'"');
    $this->db->where('task_due_date >="'.date('Y-m-d', strtotime($todaysDate)).'"');    
    $this->db->or_where('task_due_date BETWEEN "'. date('Y-m-d', strtotime($fromdaysDate)). '" and "'. date('Y-m-d', strtotime($todaysDate)).'"');   
    $alltask=$this->db->get('wba_task')->result_array();
    echo $this->db->last_query();



不知道你的db表是什么样的,但下面是一个例子:</ p>

   public function check_b_adds($ data){
//我想你的start_time和end_time作为日期时间类型存储在db中,如2016-03-01 07:30:00
$ myStart_time = $ data ['mystart_time' ]。 //例如,这是一个像2016-03-01 07:30:00这样的字符串,格式为%Y-%m-%d%H:%i:%s
$ myEnd_time = $ data ['myEnd_time “]; //例如,这是一个像2016-03-01 09:30:00的字符串格式为%Y-%m-%d%H:%i:%s

$ query = $ this- &gt; db(“
选择*
来自listing_b_screen
,其中id不在
中(
选择id
来自listing_b_screen
,其中start_time&gt; STR_TO_DATE('$ myEnd_time','%Y-%m- %d%H:%i:%s')
或end_time&lt; STR_TO_DATE('$ myStart_time','%Y-%m-%d%H:%i:%s')

“ );

$ result = $ query-&gt; result_array();
if($ result == NULL || $ result == 0){
return false; //不重叠
} else {
return true;
}
}
</ code> </ pre>

请注意您的逻辑错误。 例如,db中的start_time和end_time是第3和第7,数组中的start_time和end_time是第1和第2。 这不重叠。 但你的逻辑start_time&gt; =? AND end_time&gt; =? 认为它是重叠的。 尝试在查询中使用“not in”</ p>
</ div>

展开原文

原文

Don't know what your db table looks like, but below is an example:

public function check_b_adds($data){
    //I suppose your start_time and end_time are stored as datetime type in db like 2016-03-01 07:30:00
    $myStart_time = $data['mystart_time']; //for example, this is a string like 2016-03-01 07:30:00 in the format of %Y-%m-%d %H:%i:%s
    $myEnd_time   = $data['myEnd_time'];   //for example, this is a string like 2016-03-01 09:30:00 in the format of %Y-%m-%d %H:%i:%s

    $query = $this->db("
                          select * 
                          from listing_b_screen 
                          where id not in
                                      (
                                       select id
                                       from listing_b_screen 
                                       where start_time >  STR_TO_DATE('$myEnd_time', '%Y-%m-%d %H:%i:%s')
                                       or end_time      <  STR_TO_DATE('$myStart_time', '%Y-%m-%d %H:%i:%s')                                          
                                      )
                       ");      

    $result = $query->result_array();
    if ($result == NULL || $result == 0){
        return false; //not overlap
    } else {
        return true;
    }
}

Notice that your logic is wrong. For example, the start_time and end_time in db is 3rd and 7th, the start_time and end_time in your array is 1st and 2nd. This is not overlap. but your logic start_time >= ? AND end_time >= ? consider it is overlap. Try to use "not in" in the query

duanmiyang6201
duanmiyang6201 更新了答案。 如果在比较字符串与日期时间时没有收到错误,则只表示您的字符串与日期时间标准格式之一匹配。
4 年多之前 回复
duanhong4274
duanhong4274 我有困惑! 您之前说过“只是想提醒您,PHP会将数据时间作为字符串传递给数据库。您的代码将无法工作,因为您正在将数据时间与字符串进行比较。” 为什么在dateTime作为字符串传递的情况下我不会得到解析错误? 另外,为什么在尝试将字符串类型与之前建议的DateTime数据类型进行比较时,我不会收到另一个错误。 当我运行自己编写的代码时,它不会给出任何错误,它只会一直返回true。 为什么会这样? @Jaaaaaaay
4 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐