Multiple Table Select Question

[tomtimms]

I need to run a query for a report, however it needs to be taken from 2 tables on the same database.  I have reports201005 and reports201006 as my tables (year and month).  Say I need to run a report that gets data from may 15th (reports201005) to june 15th (reports201006).  Each table has a date field (obviously), a total column, and a location column.  So my query would need to select date, total, location FROM reports201005 and reports201006 from the date range BETWEEN 05-15-2010 AND 06-15-2010.    Also keep in mind I have a table for each month so the user can also select a different date range.  I have been making this really complicated in my head and just need a solid starting point on where to begin.  Any logic would be great!

[fenway]

Sounds like you could just UNION the appropriate tables.

[tomtimms]

I agree, however what if my date range is 01/01/2009 to 06/01/2010?  How could I obtain all the months in that date range then append it to my query to select each table for each month.  So I would be selecting from a total of 18 tables.  Just a starting point would be awesome from anyone.

[PFMaBiSmAd]

As you just discovered, storing same type data across more than one table makes for extremely inefficient and overly complicated coding. Is there some reason you don't have a single table? You could then simply query for the range of dates you want.

 

SELECT your_columns FROM your_table WHERE date BETWEEN some_start_date AND some_end_date

[gizmola]

With PHP you could write a script that parses up the date range and determines how many of the tables you need to UNION together, using your tablename convention of 'reportsYYYYMM'.  Ugly but it could be done.

 

As PFMaBiSmAd stated, this is the reason that putting data in seperate tables like that is a really bad idea. 

[tomtimms]

I wish I could be putting it into 1 table however there is going to be too much data.  Every month new tables will be created then eventually archived over time.  Any clue on where to begin?  I assume I would need to grab all the months/year and see how many there are between that range and get its value...

[gizmola]

So the decision to structure this using multiple tables is yours?  What exactly is "too much data"? 

[PFMaBiSmAd]

I'm going to guess you are NOT going to fix your data structure.

 

The code necessary to dynamically build the query is a straightforward programming exercise (tested to the point of producing the query string) -

 

<?php
// function to 'increment' the month w/year rollover
function next_table($current_table){
$yr = substr($current_table, 0, 4);
$mo = (int)substr($current_table, 4,2); // month w/o leading zero
$mo++;
if($mo > 12){
	$mo = 1;
	$yr++;
}
return $yr . str_pad($mo, 2, '0', STR_PAD_LEFT);
}

// The following example is inclusive of the start and end dates (due to the BETWEEN ... AND ... syntax). If you want to exclude either or both the start/end date, the code must be modified.
$start = '2009-01-01';
$end = '2010-06-01';
if($start >= $end){
echo 'Date range error, the starting date is not less-than the ending date.';
} else {
list($start_year,$start_month,$start_day) = explode('-',$start);
list($end_year,$end_month,$end_day) = explode('-',$end);
$current_table = $start_year . $start_month; // initialize before loop (yyyymm)
$end_table = $end_year . $end_month; // end condition
$query = "SELECT * FROM report{$current_table} WHERE date BETWEEN {$start} AND {$end}"; // base query
$current_table = next_table($current_table); // get the next table
while($current_table <= $end_table){
	$query .= "\n UNION SELECT * FROM report{$current_table} WHERE date BETWEEN {$start} AND {$end}"; // append union query
	$current_table = next_table($current_table); // get the next table
}
echo "<pre>$query</pre>";
}

// version that excludes the end date
// If the end date is the first day of a month, this query will include that year/month table but won't match any data in that table.
// This allows that table to be included in the query in case the date is not the first day of the month w/o needing any extra logic.
$start = '2010-01-01';
$end = '2011-01-01';
if($start >= $end){
echo 'Date range error, the starting date is not less-than the ending date.';
} else {
list($start_year,$start_month,$start_day) = explode('-',$start);
list($end_year,$end_month,$end_day) = explode('-',$end);
$current_table = $start_year . $start_month; // initialize before loop (yyyymm)
$end_table = $end_year . $end_month; // end condition
$query = "SELECT * FROM report{$current_table} WHERE date >= {$start} AND date < {$end}";
$current_table = next_table($current_table); // get the next table
while($current_table <= $end_table){
	$query .= "\n UNION SELECT * FROM report{$current_table} WHERE date >= {$start} AND date < {$end}";
	$current_table = next_table($current_table); // get the next table		
}
echo "<pre>$query</pre>";
}
?>

[fenway]

Or you could just derive a view on-the-fly -- or similar solution.

[tomtimms]

Thanks for the help, I was able to use the script posted and it works great, however what if the table doesn't exists?  I have a date range picker and I don't want to adjust it to only be able to select a specific date range.  How can I have it so that if a user selects current year and I only have 3 of the current 12 tables created that it won't show a sql error that the tables don't exists?

 

I tried using

 

while($current_table <= $end_table){

$Table = mysql_query("show tables like '" . $current_table . "'");

if(mysql_fetch_row($Table) === true) {

$query .= "\n UNION SELECT *  FROM  reports_$current_table WHERE date BETWEEN '" .date("Y-m-d", strtotime($start)) . " 00:00:00' AND '" .date("Y-m-d", strtotime($end)) . " 23:59:59' AND source='" . $source . "' GROUP BY day(date)"; // append union query

$current_table = next_table($current_table); // get the next table

}
}

 

however it doesn't work.

[tomtimms]

is there an "IF EXISTS" clause for a SELECT statement?

[fenway]

is there an "IF EXISTS" clause for a SELECT statement?

No -- you should know what table you have.  If not (!), use SHOW TABLES and keep track.