Run SQL Query multiple times between two dates (MYSQL)

Asked

Viewed 89 times

0

I wonder how I do to run an SQL query between two dates where for example:

the user will choose two dates:

starting date = 20/04/2020 final date = 25/04/2020

so I wanted to know how do I do to do this (for example):

select * from teste where data = 20/04/2020 ... ( data inicial)
select * from teste where data = 21/04/2020 ... 
select * from teste where data = 22/04/2020 ...
select * from teste where data = 23/04/2020 ...  
select * from teste where data = 24/04/2020 ...  
select * from teste where data = 25/04/2020 ... ( data final )`

I tried it in php but not with this SQL, I put an example :

 $data_ini = "24/04/2020";
 $data_fim = "05/05/2020";

 $dt1 = DateTime::createFromFormat('d/m/Y', $data_ini);
 $dt2 = DateTime::createFromFormat('d/m/Y', $data_fim);

 $data_inicio = new DateTime(($dt1->format('Y-m-d')));
 $data_fim = new DateTime(($dt2->format('Y-m-d')));

 // Resgata diferença entre as datas
 $dateInterval = $data_inicio->diff($data_fim);

 $i = 0;

 for($i = 0; $i <= $dateInterval->days; $i++) {

 $dataSql = date('Y-m-d', strtotime('+'.$i.'days', strtotime(($dt1->format('Y-m-d')))));
         //echo $dataSql;
         echo "<br/>";

         $sql = "select * from teste where data = $dataSql";

         echo $i.' '.$sql;
         echo '<br>';
         echo '<br>';
  }

Result shown in echo:

0 select * from teste where data = 2020-04-24


1 select * from teste where data = 2020-04-25


2 select * from teste where data = 2020-04-26


3 select * from teste where data = 2020-04-27


4 select * from teste where data = 2020-04-28


5 select * from teste where data = 2020-04-29


6 select * from teste where data = 2020-04-30


7 select * from teste where data = 2020-05-01

End

Sorry if this is not possible, I am a student and this is my first question, this solution can be either in mysql or in php, if someone could help me... Thank you very much

php mysql query for while

  • You need to effectively make different Selects or would just be a SELECT checking if the date is within range? ... WHERE data BETWEEN '2020-04-20' AND '2020-04-25' ....

  • Hello I would like to make different selects repeatedly between the dates the user chose that is possible? And the date of each query is the initial date +1 day and so on until you reach the final date

  • If you want something procedural then create a procedural routine. SQL is a declarative language. Search by previous in the manual.

  • In your code, it prints on the screen, the X selects what you want to run in the database? function mysqli_query() for example?

1 answer

1

Got two outs to make:

Between dates with BETWEEN

select 
  * 
from teste 
where data between '20/04/2020' and '24/04/2020'

This will return the day (20, 21, 22, 23 and 24) if they exist

Either date

select 
  * 
from teste 
where data = '20/04/2020' 
or data = '24/04/2020'

This will return only on the 20th and 24th if they exist

All the results will be returned in one table.

Browser other questions tagged php mysql query for while

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