How works php date BETWEEN-CodePudding

I have a query and works:

$sql2 = "SELECT id FROM table WHERE '2022-06-06' BETWEEN date(se_from) AND date(se_to)";

But when the date is dynamic the query fails:

$pick_date = '2022-06-06';  
$sql2 = "SELECT id FROM tblseasons WHERE $pick_date BETWEEN date(se_from) AND date(se_to)";

Can't understand, can anyone explain.

Thanks

CodePudding user response：

Your query lacks parentheses in the date (so it fails to do what you want as the query will be invalid)

For security, please use parameterized prepared statement to avoid SQL injection attacks instead

For mysqli, it will be:

$conn = mysqli_connect("localhost", "user", "dbpass", "dbname1");

$pick_date = '2022-06-06';  

$sql2 = "SELECT id FROM tblseasons WHERE ? BETWEEN date(se_from) AND date(se_to)";

$stmt = $conn->prepare($sql2); 
$stmt->bind_param("s", $pick_date);
$stmt->execute();

$result = $stmt->get_result(); // get the mysqli result

while ($row = $result->fetch_assoc()) {
    echo $row['id'] . "<br>"; // if you want to see the result;
}

For PDO, it will be

$dbh = new PDO('mysql:host=localhost;dbname=dbname1', "user", "dbpass");

$pick_date = '2022-06-06';  

$string1 = "SELECT id FROM tblseasons WHERE :pick_date BETWEEN date(se_from) AND date(se_to)";

$stmt = $dbh->prepare($string1, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

$stmt->execute([':pick_date' => $pick_date]); 

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo $row["id"] . "<br>"; // if you want to see the result;
}

CodePudding user response：

It's just a problem with string being broken by your variable $pick_date. You can fix it as follows:

'SELECT id FROM tblseasons WHERE ' . $pick_date . ' BETWEEN date(se_from) AND date(se_to)';