Filter by date PHP and MySQL

Question

Filter by date PHP and MySQL

Navigation

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

1

Good morning to everyone.

I am trying to do a Select by filtering only by date in a DB with MySQL, in timestamp column format, where data with this format is inserted ('Y-m-d H: i: s').

The dates I receive from this form:

            Filtrar Registros por dia :<br>
     <form action='<?php $_SERVER['PHP_SELF']?>'  method='POST'>
           <p>23 Noviembre <input type='radio' name='dia' value='2016-11-23'/></p>
           <p>24 Noviembre <input type='radio' name='dia'value='2016-11-24'/></p>
           <p><input type=submit name='filtrar'  VALUE=Filtrar></p>
     </form>
            Filtrar Registros por hora:<br>
     <form action='<?php $_SERVER['PHP_SELF']?>'  method='POST'>
          <p>16:00 H<input type='radio' name='hora' value='16:00:00'/></p>
          <p>17:00 H<input type='radio' name='hora' value='17:00:00'/></p>
          <p>18:00 H<input type='radio' name='hora' value='18:00:00'/></p>
          <p><input type=submit  name='filtrar1' VALUE=Filtrar></p>
     </form>

I pass the data to PHP in this way to do the Select:

          if ( !isset($_POST['filtrar'])  || !isset($_POST['dia']) ){}else{                       
                      $date =$_POST['dia'];
                      $now = new DateTime($date);
                      $hoy=$now->format('Y-m-d'); 
                      echo "<h1>".$hoy."</h1>";
$registros=mysqli_query($conexion,"SELECT * FROM 'nom_tab' WHERE 'Fecha' = '$hoy'")
   or die('Problemas al buscar'. mysqli_error());
   } 
    $row_cnt = $registros->num_rows;

If the query works I show the data in a table.

My question is:

What or Comó, I can do to filter only by ('Y-m-d')?

Since MySQL expects it to happen ('Y-m-d H: i: s').

Thank you in advance for everything. Greetings.

mysql php timestamp date phpmyadmin

asked by Gabriel 26.10.2016 в 10:15

source

2 answers

0

I understand that by simply doing:

$registros = mysqli_query($conexion,"SELECT * FROM nom_tab WHERE Fecha >= '$hoy 00:00:00'AND Fecha <= '$hoy 23:59:59'");

or:

 $registros = mysqli_query($conexion,"SELECT * FROM nom_tab WHERE CAST(
Fecha AS DATE)= '$hoy'");

answered by 26.10.2016 в 10:41

Android drop-down content [closed] IValueConverter refresh

score 0 · Accepted Answer

MySQL is not expected to give you the time, it is more expected a string to order it and compare it, so this is valid:

"SELECT * FROM nom_tab WHERE Fecha > '2016-01-00'"

This way you would remove all the registrations of nom_tab that have the date greater than 2016-01-00: It would start from January 01, 2016 at 00:00:00.

In your case, I would do the SQL statement in the following way:

"SELECT * FROM nom_tab WHERE Fecha BETWEEN '$hoy' AND '$hoy 23:59:59'"

I hope it helps you.

A greeting.

Edit: The response of "Ricardo Fernandez" is good, but a CAST eats MySQL resources unnecessarily (at least this time).