Error in SELECT with BETWEEN between a range of dates

Question

Error in SELECT with BETWEEN between a range of dates

Navigation

#1 by (2 votes)

1

My problem is this, I am trying to show a table with the people who must cancel in the month of January or any month, I do it in the following way:

First step in a link the initial data via GET

<a href="calendario.php?dato=<?php echo date('01-m-Y');; ?>">

That information I receive here and execute my query:

<?php

 if(isset($_GET['dato'])){
   $dato2 = date('t-m-Y');
   $fecha = $_GET['dato'];

   $query = "SELECT * FROM students INNER JOIN inscritos ON 
   inscritos.id_student = students.id_students 
   INNER JOIN pagos_estudiantes ON pagos_estudiantes.id_inscripcion = 
   inscritos.id WHERE pagos_estudiantes.fecha_a_pagar BETWEEN '$fecha' AND 
   '$dato2'";

   $sql = $mysqli->query($query);

   while($row = $sql->fetch_assoc()){ ?>
    <tbody>
      <tr>
       <td><?php echo $row['names'] ?></td>
       <td><b><?php echo $row['monto'] ?></td>
       <td><?php echo $row['fecha_a_pagar'] ?></td>
      </tr>
    </tbody>
     <?php } ?>
    <?php } ?>

The result I get from that is not the desired one, it shows me all the records that I have, that is, all the people that are registered in the system. And I just want to show what the date to pay this for example between 01-01-2018 and 01-01-2018. Thanks in advance for your help.

mysql php

asked by Alejo Mendoza 08.01.2018 в 02:45

source

1 answer

Calculate difference between two PHP dates [duplicated] Difference between assigning variables by value or reference

score 2 · Accepted Answer

Since you are using BETWEEN with an inappropriate field (which is a shame and you can pass a very high bill), the MySQL documentation says the following:

If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to DATE.

That is, you will have to convert your chain to date to make the comparison. If the column were of type DATETIME this conversion would not be necessary.

Try the query like this:

SELECT * FROM students 
INNER JOIN inscritos ON inscritos.id_student = students.id_students 
INNER JOIN pagos_estudiantes ON pagos_estudiantes.id_inscripcion =  inscritos.id 
WHERE CAST(pagos_estudiantes.fecha_a_pagar AS DATETIME) BETWEEN '$fecha' AND '$dato2';

If in your column fecha_a_pagar you have the dates stored in the format DD-MM-YYYY we are before a total nonsense, because MySQL stores the dates in YYYY-MM-DD format. Then you will have to reformat everything to make a correct comparison:

SELECT * FROM students 
INNER JOIN inscritos ON inscritos.id_student = students.id_students 
INNER JOIN pagos_estudiantes ON pagos_estudiantes.id_inscripcion =  inscritos.id 
WHERE STR_TO_DATE(pagos_estudiantes.fecha_a_pagar, '%d-%m-%Y')
BETWEEN STR_TO_DATE('$fecha', '%d-%m-%Y') AND STR_TO_DATE('$dato2', '%d-%m-%Y');

Note: As noted by @ AlvaroMontoro in a comment, your queries are vulnerable to SQL injection.