in reply to Re^4: database and deployment questions
in thread Newbie question

can you please elaborate

Sure...
Note that I am very much self-taught so may use incorrect terms and make assumptions - others will hopefully correct any errors I make.

A Temporary Table is much like a regular table in the RDBMS except that it created on the fly by your code. It is automatically dropped by the RDBMS when the database session ends. So it only exists within the current running instance of your code.

So a common use for a Temporary Table is to gather together data from various different sources (usually other tables) so that SQL operations can be performed on all the data at once. For example, in my CRM I have a reminder view. This takes birthdays from the 'Person' table, reminders from the 'Note' table, anniversaries from the 'Anniversary' table, etc. It loads all this into a Temporary Table before sorting in into date order and showing just the first 20 events.

$dbh->do("CREATE TEMPORARY TABLE Temp_Remind (idRemind INT AUTO_INCREM
+ENT PRIMARY KEY, date DATE, year DATE, user INT, name VARCHAR(100), t
+ype CHAR(1), idNote INT, text VARCHAR(80))");

# Load dob data into temp table
$query=$dbh->prepare("SELECT idPerson,hidden,fname,sname,DAY(dob),MONT
+H(dob),YEAR(dob),Friendship_idFriendship FROM Person WHERE MONTH(dob)
+>0");
$query->execute();
while (($id,$hidden,$fname,$sname,$day,$month,$year,$friendship)=$quer
+y->fetchrow_array()) {
    next unless $friendship and !$hidden;
    $name=$fname;
    $name.=' ' if $fname and $sname;
    $name.=$sname;
    $dbh->do("INSERT INTO Temp_Remind SET date='0000-$month-$day',year
+='$year-00-00',user=?,name=?,type='D',text='Birthday'", undef, $id, $
+name);
}

# Load anniversary data into temp table
$query=$dbh->prepare("SELECT idPerson,hidden,fname,sname,DAY(date),MON
+TH(date),YEAR(date),name FROM Anniversary,Person WHERE Person_idPerso
+n=idPerson AND MONTH(date)>0");
$query->execute();
while (($id,$hidden,$fname,$sname,$day,$month,$year,$text)=$query->fet
+chrow_array()) {
    next if $hidden;
    $name=$fname;
    $name.=' ' if $fname and $sname;
    $name.=$sname;
    $dbh->do("INSERT INTO Temp_Remind SET date='0000-$month-$day',year
+='$year-00-00',user=?,name=?,type='A',text=?", undef, $id, $name, $te
+xt);
}

# Load future note reminder data into temp table
$query=$dbh->prepare("SELECT idPerson,idNotes,fname,sname,DAY(remindDa
+te),MONTH(remindDate),YEAR(remindDate),note FROM Note,Person WHERE Pe
+rson_idPerson=idPerson AND reminder=1 AND remindDate>=DATE_ADD(NOW(),
+INTERVAL -1 DAY) AND remindDate<DATE_ADD(NOW(),INTERVAL 1 YEAR)");
$query->execute();
while (($id,$noteid,$fname,$sname,$day,$month,$year,$text)=$query->fet
+chrow_array()) {
    $name=$fname;
    $name.=' ' if $fname and $sname;
    $name.=$sname;
    if (length($text) >= 80) {
        $text=substr($text,0,76);
        $text.='...';
    }
    $dbh->do("INSERT INTO Temp_Remind SET date='0000-$month-$day',year
+='$year-00-00',user=?,name=?,type='N',idNote=?,text=?", undef, $id, $
+name, $noteid, $text);
}
$dbh->do("COMMIT");


# Display future reminders
$query=$dbh->prepare("SELECT YEAR(NOW()),DATE_FORMAT(date,'%m-%d'),DAT
+E_FORMAT(date,'%D %b'),YEAR(NOW())-YEAR(year),user,name,type,idNote,t
+ext FROM Temp_Remind WHERE date > CONCAT('0000-',MONTH(NOW()),'-',DAY
+(NOW())) ORDER BY date LIMIT 20");
$query->execute();

while (($thisyear,$rawdate,$date,$year,$user,$name,$type,$noteid,$text
+)=$query->fetchrow_array()) {

    # Deal with printing out the reminders...
    
}

[download]

This code snippet is ancient legacy code and I wouldn't write it quite like this now but it should give you the idea of pulling data from different places and then working on the combined result.

In this example, all the data sources are within the same database schema. But they could be in different schemas within the same RDBMS or different RDBMS's. They could be on different machines in different locations potentially accessed over ODBC. They don't even have to be data sources from databases - they could come from anywhere.

In Section Seekers of Perl Wisdom