Search by date in MySQL does not work

Question

Search by date in MySQL does not work

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I have a query in my PHP code that was working normal, but it suddenly stopped working without me making any kind of change.

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 tested it directly on phpadmin that was working before and also does not work anymore. I ran a test with this other query directly in phpadmin:

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

And it does not work, but if I put quotation marks on the date it works:

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

So I tried to use this query that works in PHP but it also does not work, maybe because the parameter is without quotes, I do not know. I've been doing this for two days and nothing, I've tried in many ways and nothing, can anyone get 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 testo 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;

php mysql data busca

asked by anonymous 06.07.2018 / 03:35

2 answers

1

Thank you adventistaam it was perfect and that was it. The final code looks like this:

$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!

06.07.2018 / 12:23

Get last grouped MySql record per year How to align a photo in the center of the page?

score 1 · Accepted Answer

I noticed looking at your code that the $ sql quotation marks is not closed

And I saw that it does not have a parameter, it usually does not.

Here's my suggestion:

A file named 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 instantiate 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>";

 ?>