Search by date in Mysql does not work

Question

I have a query in my PHP code that was working normal, but suddenly stopped working without me having made any kind of changes.

SELECT * FROM comportamento_loja 
where ativado = 1 
and datahoje BETWEEN DATE_FORMAT(STR_TO_DATE(data_inicial, '%Y-%m-%d'), '%d/%m/%Y') AND DATE_FORMAT(STR_TO_DATE(data_final, '%Y-%m-%d'), '%d/%m/%Y')

I tried it directly in phpadmin that before was working and also does not work anymore. I made a test with this other query direct in phpadmin:

SELECT * FROM comportamento_loja 
where ativado = 1 
and 2018-07-05 BETWEEN (data_inicial) AND (data_final)

And it doesn’t work, but if I put quotes in the date it works:

SELECT * FROM comportamento_loja 
where ativado = 1 
and '2018-07-05' BETWEEN (data_inicial) AND (data_final)

So I tried using this query that works in PHP but also doesn’t work, maybe because the parameter is without quotes, I don’t know. I’ve been doing this for two days and nothing, I’ve tried many ways and nothing, can anyone find a solution to this problem?

I am using: Win7, wamp server, PHP version 7, 5.7.14 - Mysql and also PDO, but as I said all queries I also test in phpadmin and nothing.

This is my code:

$data_atual = date("d/m/Y");  

$sql = "SELECT * FROM comportamento_loja where ativado = 1 and ? BETWEEN DATE_FORMAT(STR_TO_DATE(data_inicial, '%Y-%m-%d'), '%d/%m/%Y') AND DATE_FORMAT(STR_TO_DATE(data_final, '%Y-%m-%d'), '%d/%m/%Y')

try {
    $stmt = $con->prepare($sql);            
    $stmt->execute($data_atual); 
    $comportamento = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
catch(PDOException $error) {
    echo '<span class="box-error"><h5>Erro ao carregar comportamento_loja:' . '<span class="description-error">' .$error->getMessage(). '</span>' .'</span>';                   
}                           
$stmt = null; 

Answer 1

I noticed by looking at your code that the quotes of $sql is not closed

And I saw that in execute has a parameter, usually has not.

Follow my suggestion:

A file called class.dao.php

class Dao
  {
  	public function getDatas($data_inicial, $data_final){
        $this->connection = null;
        $this->connection = new ConnectionFactory();
        $row = array();
        try {
            $sql = "SELECT * 
            		  FROM comportamento_loja 
            		 WHERE ativado = 1 
            		   AND colunaData BETWEEN :data_inicial AND :data_final";

            $stmt = $this->connection->prepare($sql);
            $stmt->bindValue(":data_inicial", $data_inicial, PDO::PARAM_STR);
            $stmt->bindValue(":data_final", $data_final, PDO::PARAM_STR);
            $stmt->execute();
            $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $this->connection = null;
        } catch (PDOException $ex) {
            echo "Erro: ".$ex->getMessage();
        }
        return $row;
    }

And in a previous file you file it

<?php 

ini_set('display_errors',1);
ini_set('display_startup_erros',1);
error_reporting(E_ALL);
 
  include 'class.dao.php';

  $dao = new Dao();
  $data1 = '2015-10-30';
  $data2 = '2018-07-30';
  
  $retorno = $dao->getDatas($data1, $data2 );
  $return = array_values($retorno);
  echo json_encode($return);
  echo "<pre>";
  print_r($retorno);
  echo "</pre>";

 ?>

Answer 2

Thank you adventistaam became perfect and that was it. The final code was as follows:

$data_atual = date("Y/m/d");  
date_default_timezone_set('America/Sao_Paulo');

$hora_atual = date('H:i:s');                


$sql = "SELECT * 
              FROM comportamento_loja 
             WHERE ativado = 1 

             AND :data_atual BETWEEN data_inicial AND data_final
             AND :hora_atual BETWEEN hora_inicial AND hora_final";
try {
 $stmt = $con->prepare($sql);           
 $stmt->bindValue(":data_atual", $data_atual, PDO::PARAM_STR);            
 $stmt->bindValue(":hora_atual", $hora_atual, PDO::PARAM_STR);          
 $stmt->execute(); 
 $comportamento = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
catch(PDOException $error) {
echo '<span class="box-error"><h5>Erro ao carregar comportamento_loja:' . '<span class="description-error">' .$error->getMessage(). '</span>' .'</span>';                   
}                           
$stmt = null; 

These 03 lines were the solution to the problem:

$stmt->bindValue(":data_atual", $data_atual, PDO::PARAM_STR);            
$stmt->bindValue(":hora_atual", $hora_atual, PDO::PARAM_STR);          
$stmt->execute(); 

Thanks!!