区分大小写的查询 [英] Cakephp 3 case sensitive query

问题描述

  $ date = date（Y-m-d）; 
 $ prev_date = date（'m-d-Y'，strtotime（$ date。'-1 day'））; 
 $ q =SELECT`id`，`external_id`，`status`，DATE_FORMAT（from_unixtime（`created`），'％m-％d-％Y'）as crt，`extras` 
 FROM`gshuplogs` WHERE DATE_FORMAT（from_unixtime（`created`），'％m-％d-％Y'）='$ prev_date'and 
（BINARY`status` in（'success'，'DEFERRED' ）或`status` IS NULL）; 
 $ conn = \Cake\Datasource\ConnectionManager :: get（'default'）; 
 $ res = $ conn-> execute（$ q） - > fetchAll（'assoc'）; 
 debug（$ res）;退出; 
 

上面的查询我写在mysql现在我只是想在cakephp 3。

我试过了

  $ res = $ this-> Gshuplogs-> ; find（） - > select（[
'id'，'external_id'，'status'，'created'
]） - > where（[
DATE_FORMAT（from_unixtime `created`），'％m-％d-％Y'）=> $ prev_date，
'OR'=> [
 ['LOWER status'=>'success'] ，
 ['status'=>'DEFERRED']，
 ['status IS NULL'] 
] 
]）; 
  

AND

  $ res = $ this-> Gshuplogs-> find（） - > select（[
'id'，'external_id'，'status'，'created'
]） - > where（[
DATE_FORMAT（from_unixtime（`created`），'％m-％d-％Y'）=> $ prev_date，
'OR'=> [
 ['BINARY status'=>'success']，
 ['status'=>'DEFERRED']，
 ['status IS NULL'] 
] $ b]）; 
  

但它是抛出的错误。

错误：SQLSTATE [42000]：语法错误或访问冲突：1064 You
在SQL语法中有错误;检查对应
的手册，你的MySQL服务器版本正确的语法在
'`，'％m-％d-％Y''）= '06 -01- 2016AND（'LOWER` status'success'OR'status'=
'DEFE 'at line 1

  $ date = 
 date（Ymd）; 
 $ prev_date = date（'md-Y'，strtotime（$ date。'-1 day'））; 
 
 $ res = $ this-> ; Gshuplogs-> find（） - > select（[
'id'，'external_id'，'status'，created
]） - FROM_UNIXTIME（`created`，'％m-％d-％Y'）=> $ prev_date，
'OR'=> [
 ['BINARY（status）in'=& ['success'，'DEFERRED']]，
 ['status IS NULL'] 
] 
]）; 
 
 debug（$ res-> toArray （））; exit; 
  

    $date = date("Y-m-d");
    $prev_date = date('m-d-Y', strtotime($date .' -1 day'));
    $q = "SELECT `id`, `external_id`, `status`, DATE_FORMAT(from_unixtime(`created`),'%m-%d-%Y') as crt, `extras` 
            FROM `gshuplogs` WHERE DATE_FORMAT(from_unixtime(`created`),'%m-%d-%Y') = '$prev_date' and
            (BINARY `status` in ('success','DEFERRED') or `status` IS NULL)";
    $conn = \Cake\Datasource\ConnectionManager::get('default');
    $res = $conn->execute($q)->fetchAll('assoc');
    debug($res);exit;

The above query I have written in mysql now I just wanted to write in cakephp 3.

I have tried

$res = $this->Gshuplogs->find()->select([
        'id', 'external_id', 'status', 'created'
    ])->where([
        "DATE_FORMAT(from_unixtime(`created`),'%m-%d-%Y')" => $prev_date,
        'OR' => [
            ['LOWER status' => 'success'],
            ['status' => 'DEFERRED'],
            ['status IS NULL']
        ]
    ]);

AND

$res = $this->Gshuplogs->find()->select([
        'id', 'external_id', 'status', 'created'
    ])->where([
        "DATE_FORMAT(from_unixtime(`created`),'%m-%d-%Y')" => $prev_date,
        'OR' => [
            ['BINARY status' => 'success'],
            ['status' => 'DEFERRED'],
            ['status IS NULL']
        ]
    ]);

But it is throwing error.

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`),'%m-%d-%Y'`) = '06-01-2016' AND (`LOWER` status 'success' OR `status` = 'DEFE' at line 1

解决方案

Thanks for your response. I got the solution.

    $date = date("Y-m-d");
    $prev_date = date('m-d-Y', strtotime($date .' -1 day'));

    $res = $this->Gshuplogs->find()->select([
        'id', 'external_id', 'status', "created" 
    ])->where([
        "FROM_UNIXTIME(`created`,'%m-%d-%Y')" => $prev_date,
        'OR' => [
            ['BINARY(status) in ' => ['success','DEFERRED']],
            ['status IS NULL']
        ]
    ]);

    debug($res->toArray());exit;

这篇关于区分大小写的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！