Help Selecting multi tables from mysql

[gucci_406]

Hi Guys,

 

I need a little help. This one is giving me a real head ache :-(

 

I have created a archive database in mysql. Within the database there are tables named  YYYYMMDD_var. The tables layout is as follows:

####################################

node + T00 + T03 + T06 + T09 + T12 + T15 + T18 + T21

####################################

 

I have created some drop down menus in php to call for the dates Dateto and Datefr. I then calculate the diff between the dates and return the output (20101101_var, 20101102_var etc,,,)

 

But when I call for the mysql Select I cant get all tables :-(

 

This is the following code I have been using, starting from getting requesting the dates from the drop down menus.

 

The problem lies in the UNION, where because it's caught in the loop, it returns it at the end of the line. Which creates the error.

 

If anybody has any other ideas, please help :-)

 

//dates from

$dayfr = $_REQUEST['dayfr'];

$monthfr = $_REQUEST['monthfr'];

$yearfr = $_REQUEST['yearfr'];

//dates to

$dayto = $_REQUEST['dayto'];

$monthto = $_REQUEST['monthto'];

$yearto = $_REQUEST['yearto'];

$var = $_REQUEST['var'];

 

//cacluate the diff

function days($date1, $date2){

$month1 = date("m", strtotime($date1));

$day1 = date("d", strtotime($date1));

$year1 = date("Y", strtotime($date1));

$month2 = date("m", strtotime($date2));

$day2 = date("d", strtotime($date2));

$year2 = date("Y", strtotime($date2));

$first = gregoriantojd($month1, $day1, $year1);

$second = gregoriantojd($month2, $day2, $year2);

$diff = abs($first-$second);

return $diff;

}

 

$date1 = "$yearfr-$monthfr-$dayfr";

$date2 = "$yearto-$monthto-$dayto";

 

$days = days($date1, $date2);

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

$newdate = date("Ymd", strtotime("$date1 +$i days"));

 

$datevar="$newdate$var ,";

$getdata="SELECT * FROM $newdate$var WHERE node='1' UNION ";

echo "$getdata";

}

//NOW SELECT OUR TABLES FROM THE ARCHIVE DATABASE

$data=mysql_query("$getdata") or die(mysql_error()); 

[Psycho]

Why are you making this har on yourself? Don't create new tables, simply have a column in a single table for the date. Then just query the single table using a between operator for the date.

 

If you really feel you need multiple tables, then you will need to programatically determine all the tables you need to pull records from and use MERGE in your query to append the results from multiple tables.

 

http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html

 

[gucci_406]

Hello thanks for the reply,

 

The reason I wanted to create new tables, is that I have 30,000 rows in each table each pointing to a lat, lon (node). These are created everynight and uploaded, while the T00 + T03 points to a times step. I thought this way would seem the easiest.

 

I have been looking at the Left Join. Could this be applied to call multiable tables?

 

 

[gucci_406]

Hi again mjdamato,

 

I have nearly sorted this, however I am stuck on one last bit.

 

The code SELECT * FROM $newdate$var WHERE node='1' UNION will work, when I copy the echo output in the mysql_query

 

I.E SELECT * FROM 20100103_tmp WHERE node=1 UNION SELECT * FROM 20101102_tmp WHERE node=1

 

But I am stuck on the loop and where to put it:

 

//dates from
$dayfr = $_REQUEST['dayfr'];
$monthfr = $_REQUEST['monthfr'];
$yearfr = $_REQUEST['yearfr'];
//dates to
$dayto = $_REQUEST['dayto'];
$monthto = $_REQUEST['monthto'];
$yearto = $_REQUEST['yearto'];
$var = $_REQUEST['var'];

//cacluate the diff
function days($date1, $date2){
$month1 = date("m", strtotime($date1));
$day1 = date("d", strtotime($date1));
$year1 = date("Y", strtotime($date1));
$month2 = date("m", strtotime($date2));
$day2 = date("d", strtotime($date2));
$year2 = date("Y", strtotime($date2));
$first = gregoriantojd($month1, $day1, $year1);
$second = gregoriantojd($month2, $day2, $year2);
$diff = abs($first-$second);
return $diff;
}

$date1 = "$yearfr-$monthfr-$dayfr";
$date2 = "$yearto-$monthto-$dayto";

$days = days($date1, $date2);
for($i=0; $i<$days; $i++){
$newdate = date("Ymd", strtotime("$date1 +$i days"));

$datevar="$newdate$var ,";
$getdata="SELECT * FROM $newdate$var WHERE node=1 UNION ";
echo "$getdata";
}
//NOW SELECT OUR TABLES FROM THE ARCHIVE DATABASE
$data=mysql_query("$getdata SELECT * FROM $yearfr$monthfr$dayfr$var WHERE node=1") or die(mysql_error()); 

 

What I need is for $getdata to loop, and display the returned loop in the mysql_query and for the last part of the mysql_query to remain how it is (SELECT * FROM $yearfr$monthfr$dayfr$var WHERE node=1)

 

Any ideas of how to do this?

 

Cheers,

 

[Psycho]

Haven't tested, but I think you could create your query something like this:

$selects = array();
for($i=0; $i<$days; $i++)
{
    $newdate = date("Ymd", strtotime("$date1 +$i days"));
    $selects[] = "(SELECT * FROM $newdate$var WHERE node=1)";
}
$query = implode("\nUNION\n", $selects) . 'ORDER BY a LIMIT 10',