如果给定日期在两个日期之间，如何选择行？

I'm trying to select rows if the given date only falls between two dates in the table In the curriculum table I have startdate and enddate.

如果给定日期仅在表格中的两个日期之间,我正在尝试选择行。在课程表中,我有startdate和enddate。

If it is possible I need also to do condition inside the query

如果可能的话,我还需要在查询中做条件

$coursneededdate >= startdate AND $coursneededdate <= enddate

Here is my code, any help would be highly appreciated.

这是我的代码,任何帮助将非常感谢。

$coursneededdate = '2020-08-27';
$sql = "SELECT * FROM curriculum where ".$coursneededdate." between  'startdate' and 'enddate'";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {


    while($row = $result->fetch_assoc()) {

echo $row["curriculum_id"];


    }
}

4 个解决方案

#1

The issue is probably that you aren't quoting the variable which is a string when it goes to your DB. Also don't quote the column names.

问题可能是你没有引用变量,当它转到你的数据库时是一个字符串。也不要引用列名。

$coursneededdate = '2020-08-27';
$sql = "SELECT * FROM curriculum where '".$coursneededdate."' between  startdate and enddate";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
         echo $row["curriculum_id"];
    }
}

The issue also could be that you are checking for a date 5 years in the future. Do you have data that far ahead?

问题也可能是您正在检查未来5年的日期。你有远远领先的数据吗?

#2

Your SQL statement evaluates to SELECT * FROM curriculum where '2020-08-27' between 'startdate' and 'enddate' which contains all string values and no dates.

您的SQL语句求值为SELECT * FROM courses,其中'startdate'和'enddate'之间的'2020-08-27'包含所有字符串值且没有日期。

The supplied parameter starts as a string so you will need to convert this to a date value using STR_TO_DATE. The columns names from the table should not be in quotes. You will sometime see the back quote used to specify column names.

提供的参数以字符串形式开始,因此您需要使用STR_TO_DATE将其转换为日期值。表中的列名不应该用引号括起来。您有时会看到用于指定列名的后引号。

Your query should be something like

你的查询应该是这样的

SELECT * FROM curriculum WHERE STR_TO_DATE('2020-08-27','%Y-%m-%d') BETWEEN `startdate` AND `enddate`;

IMPORTANT NOTE

If the supplied string date values comes from user generated input, creating a SQL query with string concatenation makes the code vulnerable to SQL Injection.

如果提供的字符串日期值来自用户生成的输入,则使用字符串连接创建SQL查询会使代码容易受到SQL注入攻击。

#3

First you must trouble shoot the SQL and ensure what you have "coded" is equal to what is expected in SQL.

首先,您必须解决SQL问题,并确保您“编码”的内容与SQL中的预期相同。

I would suggest to echo $sql; to see if the query is corresponding to mysql and then run it in the DB itself.

我建议回复$ sql;查看查询是否与mysql对应,然后在DB本身中运行它。

If that works then I would suggest to do what chris85 stated is to wrap your values and inside a single quote $sql = "SELECT * FROM curriculum where '".$coursneededdate."' between 'startdate' and 'enddate'";

如果这样有效,那么我建议做chris85所说的是将你的值包装在一个单引号内。$ sql =“SELECT * FROM curriculum where'”。$ coursneededdate。“''startdate'和'enddate'”;

#4

try this

$sql = "SELECT * FROM curriculum where date = '" . $coursneededdate  . "' AND date between  '".$startdate."' and '" . $enddate . "'";

#1