Jump to content

Help Selecting multi tables from mysql


gucci_406

Recommended Posts

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()); 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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?

 

 

Link to comment
Share on other sites

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,

 

Link to comment
Share on other sites

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',

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.