rcowen00
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)
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());
?>
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
Code that populates the tempLoanData
tempLoanData class
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>
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'] );
}
?>
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;
}
}
?>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
aaaahhh, Thank you. I will work on that.
First create a class to hold all the data items
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