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 reply to Re^5: database and deployment questions by Bod
in thread Newbie question by SpaceCowboy

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.