CODEWITHSUNDEEP
php
user3766071
user3766071

Reputation: 15

How to select data from multi tables based date range and table name (quarterly)

I have data that are divided quarterly in different tables. For example, first quarter for 2017, the table name is kreports_first2017 (data from jan,feb and mar). So i have kreports_second2017, and so on until kreports_fourth2017 for year 2017. And so on for next years.

My problem is how can i select data from date range click by user. I only managed to select the table from the 'from date' and 'to date'. For example user choose to select data from 2017-04-01 to 2018-03-01. I managed to combined data from kreports_secondly2017 and kreports_first2018 only. How about the tables between them. kreports_third2017 and kreports_fourth2017? Below are my code for this:

$quar = array
  (
  array("first","01","02","03"),
  array("second","04","05","06"),
  array("third","07","08","09"),
  array("fourth","10","11","12")
  );

$month = substr($date1,5,2);
$year = substr($date1,0,4);
$month2 = substr($date2,5,2);
$year2 = substr($date2,0,4);

for ($row = 0; $row < 4; $row++) {
if(in_array($month,$quar[$row]))
{
  $table1 = "kreports_" . $quar[$row][0] . $year . "";  
}
if(in_array($month2,$quar[$row]))
{
  $table2 = "kreports_" . $quar[$row][0] . $year2 . "";
}
}

$quarter = "(select * from $table1 UNION select * from $table2) AS quarter";

$sql = "SELECT DATE(r_time) AS date,SUM(login_total) AS total, SUM(login_unique) AS uniq FROM $quarter WHERE DATE(r_time) BETWEEN '$date1' AND '$date2' GROUP BY DATE(r_time) ORDER BY date ASC";
$data = $db->fetch_array($sql);

Upvotes: 0

Views: 54

Answers (1)

david
david

Reputation: 3218

You don't need to loop for finding the quarter. Just divide the month by 3 to get the quarter. But, first, convert it to int and subtract it by 1.

$quarter1 = floor(((int)$month - 1) / 3);
$quarter2 = floor(((int)$month2 - 1) / 3);

After that, you can use these results along with the years to get the tables.

$quarter = "(";
while ($quarter1 <= $quarter2 || $year < $year2) {
    $quarter .= "select * from kreports_" . $quar[$quarter1][0] . $year . " UNION ";
    $quarter1 = ($quarter1 + 1) % 4;
    $year += ($quarter1 == 0) ? 1 : 0; // move to next year once the quarter is set to first.
}
$quarter = substr($quarter, 0, -7); // remove the last " UNION "
$quarter .= ") AS quarter";

Upvotes: 2

Related Questions