如何在yii2中编写where之间的查询 [英] How to write where Between query in yii2

问题描述

我将 $start_date 和 $end_date 作为参数传递，以将它们与 MySQL 数据库表中的 created 字段进行比较.我正在使用 yii2 框架.

I am passing $start_date and $end_date as parameter to compare them with created field in MySQL database table. I am using yii2 framework.

这是我尝试过的:

$modelStockDetails=StockDetails::find()->where(['BETWEEN', 'created', $start_date, $end_date])->andwhere(['receiving_order_id' =>$modelRecevingOrder->id,'deleted' => 'N'])->all();

当值 $start_date 和 $end_date 与表中的 created 日期不同时，它返回一个空数组.

which returns an empty array when values $start_date and $end_date are different from created date in the table.

但是当我传递 $start_date 时它返回一个包含数据的数组，它与表中的 created 日期完全相同.

But it returns an array containing the data when I pass $start_date which is exactly same as created date in the table.

推荐答案

可能是与输入转换相关的问题 try using a str_to_date and literal Where(根据您的格式使用正确的日期格式转换，我的示例是%d-%m-%Y")

Could be a problem related to the conversion of the input try using a str_to_date and literal Where (use a proper date format conversion based on your format im my sample is "%d-%m-%Y")

$modelStockDetails=StockDetails::find()
  ->where(' date(created) between STR_TO_DATE("'.  $start_date . '", "%d-%m-%Y" ) 
        AND   STR_TO_DATE("' .  $end_date . '", "%d-%m-%Y" )' )
  ->andwhere(['receiving_order_id' =>$modelRecevingOrder->id,'deleted' => 'N'])->all();

或者为了避免在 sql 中使用 var，你可以使用

or for avoid the use of var in sql you could use

$modelStockDetails=StockDetails::find()
->where(' date(created) between STR_TO_DATE(:start_date, "%d-%m-%Y" ) 
      AND   STR_TO_DATE( :end_date, "%d-%m-%Y" )', [':start_date' => $start_date, ':end_date' => $end_date] )
->andwhere(['receiving_order_id' =>$modelRecevingOrder->id,'deleted' => 'N'])->all();

这篇关于如何在yii2中编写where之间的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！