CODEWITHSUNDEEP
phpmysqlloopsdatediff
Alucard
Alucard

Reputation: 1902

Do a SELECT on all tables between 2 dates

I have a Mysql Database with a table for each day, it goes this way: my_table_20130310, my_table_20130311 ...

I want to do a select on all tables let's say between '2013/02/02' and '2013/03/28'

There is this stupide solution, which is to transform given dates into this: 

'2013/02/02' => 20130202

'2013/03/28' => 20130328

cast then into (int) and then do a loop:

      $dbStartDate  = str_replace('/', '', substr($startDate, 0, 10));
      $dbEndDate    = str_replace('/', '', substr($endDate, 0, 10));

      for ($tbDate = $dbStartDate; $tbDate <= $dbEndDate ; $tbDate++) { 
        $res = 'SELECT * FROM my_table_' . $tbDate ;
        ...
        ... 
      }

But this is not a solution since it will try to parse all numbers between 20130202 and 20130328 (20130299, 20130245....)

An Idea ?

Thx guys

Upvotes: 1

Views: 135

Answers (2)

HamZa
HamZa

Reputation: 14931

Try this:

$dbStartDate = new DateTime('2013/02/02');
$dbEndDate = new DateTime('2013/03/28');
$diff = $dbEndDate->diff($dbStartDate)->days; // get the difference in N days


// just a loop :p
$currentDate = $dbStartDate;
for($i=0;$i<=$diff;$i++){
    $query = 'SELECT * FROM my_table_' . $currentDate->format('Ymd');
    date_add($currentDate, date_interval_create_from_date_string('1 day'));
    // echo $query."<br/>"; // for testing purposes
}

Upvotes: 1

lc.
lc.

Reputation: 116518

In pseudocode:

DateTime startDate = new DateTime('2013-02-02');
DateTime endDate = new DateTime('2013-03-08');

for(DateTime d = startDate; d <= endDate; d = d.Add(new DateInterval('P1D')))
{
    res = 'SELECT * FROM my_table_' . d.format('YYYYmmdd');
    //...
}

Upvotes: 2

Related Questions