How to Query between two php+mysql Dates?

Asked

Viewed 263 times

1

I can make the query between two dates. However, the query returns dates beyond those that were searched.

Example:

Consultation between 01-02-2000 and 02-02-2000

Veja abaixo que a consulta retorna também 01-05-200 e 01-08-2000

Below is my code:

<?php
$condition = '';

      if(isset($_REQUEST['in_dataInicio']) and $_REQUEST['in_dataInicio']!=""){
           $condition .= ' AND columnDataNasc BETWEEN "'.$_REQUEST['in_dataInicio'].' "';
      }
      if(isset($_REQUEST['in_dataFinal']) and $_REQUEST['in_dataFinal']!=""){
           $condition .= ' AND "'.$_REQUEST['in_dataFinal'].'"';
      }
$userData   = $db->getAllRecords('tb_cruds','*',$condition,'ORDER BY C0_ID DESC');
?>


<?php
class Database{

/**
 * objeto de conexão com o banco de dados
 * @var \PDO
 */
protected $pdo;

/**
 * Conecte-se ao banco de dados
 */
public function __construct(\PDO $pdo)
{
    $this->pdo = $pdo;
    $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
}

/**
 * Retornar a conexão pdo
 */
public function getPdo()
{
    return $this->pdo;
}

public function get($tableName,  $whereAnd = array(), $whereBetween = array(), $whereOr=array(), $whereLike =array())
{
$cond   =   '';
$s=1;
$params =   array();
foreach($whereAnd as $key => $val)
{
    $cond   .=  " And ".$key." = :a".$s;
    $params['a'.$s] = $val;
    $s++;
}
foreach($whereOr as $key => $val)
{
    $cond   .=  " OR ".$key." = :a".$s;
    $params['a'.$s] = $val;
    $s++;
}
foreach($whereLike as $key => $val)
{
    $cond   .=  " OR ".$key." like '% :a".$s."%'";
    $params['a'.$s] = $val;
    $s++;
}
foreach($whereBetween as $key => $val)
{
    $cond   .=  " OR ".$key." between '% :a".$s."%'";
    $params['a'.$s] = $val;
    $s++;
}
$stmt = $this->pdo->prepare("SELECT  $tableName.* FROM $tableName WHERE 1 ".$cond);
    try {
        $stmt->execute($params);
        $res = $stmt->fetchAll();

        if (! $res || count($res) != 1) {
           return $res;
        }
        return $res;
    } catch (\PDOException $e) {
        throw new \RuntimeException("[".$e->getCode()."] : ". $e->getMessage());
    }
}

public function getAllRecords($tableName, $fields='*', $cond='', $orderBy='', $limit='')
{
    //echo "SELECT  $tableName.$fields FROM $tableName WHERE 1 ".$cond." ".$orderBy." ".$limit;
    //print "<br>SELECT $fields FROM $tableName WHERE 1 ".$cond." ".$orderBy." ".$limit;
    $stmt = $this->pdo->prepare("SELECT * FROM $tableName WHERE 1 ".$cond." ".$orderBy." ".$limit);
 //    $stmt = $this->pdo->prepare("SELECT $fields FROM $tableName WHERE 1 ".$cond." ".$orderBy." " );
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $rows;
}

} ?>

php html query

  • the kind of your field C6_DataNasc is date, datetime, varchar or char?

  • The field type is varchar, because it is only for date of birth, I use other columns to record the creation date and modification date.

  • In this case you need to convert the field to date in the SQL statement or change the field type to date, I always use dates as dates, just not to lose features with date.

  • Understood. Thank you

1 answer

1


To use the BETWEEN correctly it is necessary to have dates for comparison, if there is text the comparison will not work even, in this case.

For this we have two options, change the field type to date or convert to the command itself.

SELECT * FROM sua_tabela
WHERE DATE_FORMAT(STR_TO_DATE(C6_DataNasc, '%d/%m/%Y') BETWEEN '01-02-2000' AND '02-02-2000';
  • 1

    Solved. Thanks for the help!

Browser other questions tagged php html query

You are not signed in. Login or sign up in order to post.