Link to home
Start Free TrialLog in
Avatar of rcowen00
rcowen00Flag for United States of America

asked on

MySQL Temp Table

Hi Experts,

The following code works but is incorrect because multiple users will over write each other.  I have been playing with moving it to a Temp table but that isn't working because the temp table is gone when I move to a new page.  The process I am trying to do is as follows, how should I be doing this? Session variables?  query string? temp table? Other?  I know what I am doing is wrong.  Thanks!

The user selects a group of loans by date/audit type.
A list of the selected loans are listed with a href link that open the profile screen
At the top of the page there is a button to go to the next or previous loan in the subset (that is why I have the table in the first place to store the subset)

<?php
error_reporting(E_ALL);

        $userIDKey=$userListRow['id'];
        include 'include/userConnect.php';

        $beginDate=mysql_real_escape_string($_POST['begin']);
        $endDate=mysql_real_escape_string($_POST['end']);
        $auditTypeKey=mysql_real_escape_string($_POST['type']);
        $branchKey=mysql_real_escape_string($_POST['branch']);

            $d = "DROP Table if exists auditMgmt";
             mysql_query($d) or die(mysql_error());
            $c = "CREATE TABLE auditMgmt SELECT loan.loanKey,
                loan.loanNumber,
                borrower.lastName,
                borrower.firstName,
                audit.auditID,
                loan.branchKey,
                audit.trackingDate
                FROM loan
                JOIN audit USING (loanKey)
                LEFT JOIN borrower USING (loanKey)
                WHERE loan.branchKey='$branchKey'
                AND audit.activeStatus='1'
                AND auditTypeKey='$auditTypeKey'
                AND borrower.type='1'
                AND borrower.active='1'
                AND trackingDate BETWEEN '$beginDate' AND '$endDate'
                ORDER BY audit.auditID";
           
            mysql_query($c) or die(mysql_error());
                                             ?>

Open in new window

Avatar of Beverley Portlock
Beverley Portlock
Flag of United Kingdom of Great Britain and Northern Ireland image

My own preference would be to use a class structure and then serialize this lot and store it in the session. That way the storage is unique to each browser and will be destroyed at the end of the session meaning that there is no clean up to be done. The method I am going to describe seems complicated but is actually quite simple.

First create a class to hold all the data items

class tempLoanData {

     public $loanNumber;
     public $lastName;
     public $firstName;
     public $auditKey;
     public $auditID;
     public $branchKey;
     public $trackingDate;


     function __construct( $ln, $lName, $fName, $ak, $ai, $bk, $td ) {
          $this->loanNumber   = $ln;
          $this->lastName     = $lName;
          $this->firstName    = $fName;
          $this->auditKey     = $ak;
          $this->auditID      = $ai;
          $this->branchKey    = $bk;
          $this->trackingDate = $td;
     }


}

Open in new window


If there is only one of these then we can carry on, but if you need to store several then create an array and simply add as many of these as you need

$arr = array()

$arr [] = new tempLoadData( '1234', 'Public', 'Joe', 'abc123', 1, 1, '2012-04-23' );
$arr [] = new tempLoadData( '5678', 'Public', 'Mary', 'abc123', 1, 1, '2012-04-23' );


To save it in the session I find it causes less problems if you serialize it

$_SESSION [ 'loanStuff' ] = serialize( $singleItem )

or for the array version

$_SESSION [ 'loanStuff' ] = serialize( $arr )

Once you move to the next page simply unserialize and continue like it had always been there

$singleItem = unserialize( $_SESSION['loanStuff'] )

or

$arr = unserialize( $_SESSION['loanStuff'] )


If sometimes you have one item and others you have several then I would always use the array then you only have one style of processing to handle.

I hope that helps you
Avatar of rcowen00

ASKER

The class appears to be working correctly.  The problem I am running into is the page that populates the class should list all the records but I am getting "Table 'xxxxx.tempLoanData' doesn't exist".

should be listing the records stored in tempLoanData
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN http: //www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>

</head>
<body>
 <form enctype="multipart/form-data" method="POST" action="<?php echo $_SERVER['PHP_SELF'].'?'.$_SERVER['QUERY_STRING']; ?>">
    

     <table class="tablesorter" id="borrowers" cellspacing="1" cellpadding="0">
             <thead>
                 <tr>
                    <th>Borrower</th>
                    <th>Loan Number/ID</th>
                    <th>Property Address</th>
                    <th>Client</th>
                    <th>Audit Tracking Date</th>
                    <th>Origination</th>
                    <th>Audit Type</th>
                    <th>Mgr Review Date</th>
                    <th>Initial Report Date</th>
                    <th>Final Report Date</th>
                    <th>Initial Billing Date</th>
                    <th>Final Billing Date</th>
                    <th>Convo Log</th>
                 </tr>
             </thead>
            <?php
    
            $loan = "SELECT t.loanKey, t.loanNumber, t.lastName, t.firstName, t.auditID, t.branchKey, t.trackingDate,a.auditTypeKey,l.originationTypeKey,
                l.loanTypeKey, b.branchName, l.fundDate, at.auditTypeName,o.originationTypeName,l.addr1, a.mgrReviewDt, a.initialRpt, a.finalRpt,a.initialBillingDate,a.finalBillingDate
                FROM tempLoanData as t
                JOIN loan as l ON l.loanKey=t.loanKey
                LEFT JOIN branch AS b ON b.branchKey=t.branchKey
                LEFT JOIN audit AS a ON t.auditID=a.auditID
                LEFT JOIN auditType AS at USING (auditTypeKey)
                LEFT JOIN originationType AS o ON o.originationTypeKey=l.originationTypeKey
                ";

       $loanInfo = mysql_query($loan,$conn) or die(mysql_error());
       while ($row = mysql_fetch_array($loanInfo))

{

             $auditID = $row['auditID'];
             $loanTypeKey = $row['loanTypeKey'];
             $loanNumber=$row['loanNumber'];
             $auditTypeKey = $row['auditTypeKey'];
             $addr1=$row['addr1'];
             $loanKey=$row['loanKey'];
             $firstName = $row['firstName'];
             $lastName = $row['lastName'];
             $originationTypeKey = $row['originationTypeKey'];
             $branchKey = $row['branchKey'];
             $branchName = $row['branchName'];
             $fundDate = $row['fundDate'];
             $auditID=$row['auditID'];
             $auditTypeKey= $row['auditTypeKey'];
             $auditTypeName= $row['auditTypeName'];
             $trackingDate= $row['trackingDate'];
             $loanKey=$row['loanKey'];
             $originationTypeName=$row['originationTypeName'];
             $mgrReviewDt=$row['mgrReviewDt'];
             $initialRpt=$row['initialRpt'];
             $finalRpt=$row['finalRpt'];
             $initialBillingDate=$row['initialBillingDate'];
             $finalBillingDate=$row['finalBillingDate'];
             
             
             
?>

<tbody>
                 <tr>
                 <td><?php echo $lastName;?>, <?php echo $firstName;?></td>
                 <td><a href="auditMgmtLoan.php?auditID=<?php echo $auditID; ?>" target="Blank"><?php echo $loanNumber; ?></a></td>
                <td><?php echo $addr1; ?></td>
                <td><?php echo $branchName; ?></td>
                 <td><?php echo $trackingDate; ?></td>
                <td><?php echo $originationTypeName; ?></td>
                <td><?php echo $auditTypeName; ?></td>
                <td><?php echo $mgrReviewDt; ?></td>
                <td><?php echo $initialRpt; ?></td>
                <td><?php echo $finalRpt; ?></td>
                <td><?php echo $initialBillingDate; ?></td>
                <td><?php echo $finalBillingDate; ?></td>
                <td><a href="convoLog.php?auditID=<?php echo $auditID; ?>" target="Blank">Conversation Log</a></td>
                 </tr>
         </tbody>
        <?php
        }
        ?>
       </table>
</form>

</body>

Open in new window


Code that populates the tempLoanData
<?php
error_reporting(E_ALL);

        $userIDKey=$userListRow['id'];
        include 'include/userConnect.php';
        include 'include/classes/auditMgmtTemp.php';
        
        $beginDate=mysql_real_escape_string($_POST['begin']);
        $endDate=mysql_real_escape_string($_POST['end']);
        $auditTypeKey=mysql_real_escape_string($_POST['type']);
        $branchKey=mysql_real_escape_string($_POST['branch']);

        $createTemp= "SELECT loan.loanKey,
                loan.loanNumber,
                borrower.lastName,
                borrower.firstName,
                audit.auditID,
                loan.branchKey,
                audit.trackingDate
                FROM loan
                JOIN audit USING (loanKey)
                LEFT JOIN borrower USING (loanKey)
                WHERE loan.branchKey='$branchKey'
                AND audit.activeStatus='1'
                AND auditTypeKey='$auditTypeKey'
                AND borrower.type='1'
                AND borrower.active='1'
                AND trackingDate BETWEEN '$beginDate' AND '$endDate'
                ORDER BY audit.auditID";
$arr = array();
$insertTemp = mysql_query($createTemp) or die(mysql_error());
while ($insertTempRow = mysql_fetch_array($insertTemp))
{
$arr [] = new tempLoanData( $insertTempRow['loanKey'], $insertTempRow['loanNumber'], $insertTempRow['lastName'], $insertTempRow['firstName'], $insertTempRow['auditID'], $insertTempRow['branchKey'], $insertTempRow['trackingDate'] );
}
        ?>

Open in new window



tempLoanData class
<?PHP

class tempLoanData {

     public $loanNumber;
     public $lastName;
     public $firstName;
     public $auditKey;
     public $auditID;
     public $branchKey;
     public $trackingDate;


     function __construct( $ln, $lName, $fName, $ak, $ai, $bk, $td ) {
          
          $this->loanNumber   = $ln;
          $this->lastName     = $lName;
          $this->firstName    = $fName;
          $this->auditKey     = $ak;
          $this->auditID      = $ai;
          $this->branchKey    = $bk;
          $this->trackingDate = $td;
     }


}
?>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Beverley Portlock
Beverley Portlock
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
aaaahhh, Thank you.  I will work on that.