Register filter by date

Asked

Viewed 40 times

1

My code is not working which may be wrong?
In the database the date is with Date/Time as datetime, follows the code:

<?php       
    $query = $mysql->query("SELECT * FROM c_clientes WHERE cliente_data_cadastro 
               BETWEEN '2018-04-07 12:53:14' AND '2018-05-07 12:53:14'");

    echo "<center>". mysqli_num_rows($query). " Registros</center>";

    while($row = $query->fetch_array())
    {       
        echo $row['cliente_data_cadastro']." - "
             .$row['cliente_nome']." "
             .$row['cliente_sobrenome']."<br>"; 

    }       
?>
php mysql

  • But what is not working? And what is the purpose of the code?

  • Filter entries within 1 month! Load nothing

  • My question is obvious, but have you checked if there are actually existing records within this date range?

  • Yes I have about 300 records per day! Plus I did tests with DB only with test dates!

  • Try SELECT * FROM c_clientes WHERE ( cliente_data_cadastro &#xA; BETWEEN '2018-04-07 12:53:14' AND '2018-05-07 12:53:14')

  • If the column type is DATETIME, which stores date and time values in the format YYYY-MM-DD hh:mm:ss, why did the hours working? I ran your code through a table in my bank and ran right. The real solution does not seem to me to go through the withdrawal of hours! Some misconception on your part in the database regarding the date column. See this https://i.stack.Imgur.com/zTUfU.png result

Show 1 more comment

1 answer

2


If you don’t need to compare the hours, use the function TRUNC:

<?php       
    $query = $mysql->query("SELECT * FROM c_clientes WHERE trunc(cliente_data_cadastro) 
               BETWEEN '2018-04-07' AND '2018-05-07'");

    echo "<center>". mysqli_num_rows($query). " Registros</center>";

    while($row = $query->fetch_array())
    {       
        echo $row['cliente_data_cadastro']." - "
             .$row['cliente_nome']." "
             .$row['cliente_sobrenome']."<br>"; 

    }       
?>

  • It didn’t work, nothing keeps popping up!

  • how is the date format in the bank? YEAR-MONTH-DAY ?

  • Database: cliente_data_cadastro datetime. http://prntscr.com/jeulpj

  • I changed the answer without the time. Try now.

  • It worked! But I took the trunc!

  • Okay! If your intention is not to know the exact time, just pass the date. But if you receive a variable for this query leaves the trunc, it is more guaranteed that you will pass the date without hours.

  • See the edition I made, I passed the trunc to the field of Where.

  • If column type is DATETIME, which stores date and time values in format YYYY-MM-DD hh:mm:ss, why take out the hours?

  • Check this result https://i.stack.Imgur.com/zTUfU.png

Show 4 more comments

Browser other questions tagged php mysql

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