Page 1 of 1

Using mysql query results for further process

Posted: Mon Oct 01, 2007 10:15 pm
by shwetha004
hi all,

i am developing an online loan application and processing system.

there is a part in my project where i retrieve all loans that have been analyzed and allow user to either approve or reject it.i have no problem in retrieving the data from the database.

here is my code that retrieves the information.

Code: Select all

$query = "SELECT loanID,custNric,loanAmt,score FROM loanDetails WHERE loanDetails.ad=1 "; 
//ad is the variable that shows whether the loan has been analyzed or not

$result = mysql_query($query) or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Loan ID</th> <th>Loan Amount</th><th>Score</th><th>View History</th><th>Action</th></tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	echo "<tr><td>"; 
	echo $row['loanID'];
		echo "</td><td>"; 
	echo $row['loanAmt'];
	echo "</td><td>"; 
	echo $row['score'];
	echo "</td>";
	echo "<td>"; 
	echo '<a href=creditHistory.php>View Credit History</a>';
	echo "</td>";
	echo "<td>"; 
	echo "Approve/Reject";//this is where the user will click
	echo "</td></tr>";

so, what i want to do after this is, i want the user to be able to click on approve/reject, and the system will
automatically save the status into the database. im not sure what code to use, or even where to insert it in the loop..if it is possible,after the user clicks on approve/reject, i want that row of data to be deleted from the table.

any help will be greatly appreciated..thank you so much
:)

Posted: Wed Oct 03, 2007 10:33 am
by JackV
Do you have any UPDATE or DELETE statements , set up that you are not showing?

Re: Using mysql query results for further process

Posted: Wed Oct 03, 2007 4:33 pm
by ReverendDexter
shwetha004 wrote:so, what i want to do after this is, i want the user to be able to click on approve/reject, and the system will automatically save the status into the database. im not sure what code to use, or even where to insert it in the loop..if it is possible,after the user clicks on approve/reject, i want that row of data to be deleted from the table.
Why do you want to delete the data? Why not store the rejection, possibly with a reason in a text field (or 255 length varchar, if that's what you're into). That way if the client comes in at a later time, you can do a lookup on prior attempts, and how many rejected attempts they've had and why.

Posted: Wed Oct 03, 2007 10:40 pm
by shwetha004
hi all,

thanks for your replies.. :)

i dont have any UPDATE or DELETE statements..the code that i posted is the complete one.

actually,by user i mean the loan officer, who analyzes the applications.after clients apply for a loan,the system automatically performs some calculation, with the result being a score(usually in decimal).this score is saved into the database.it acts as one of the indicators to whether the loan should be granted.
so,when the officer retrieves the applications, the loanID,loan amount and score is displayed to him.i made some changes yesterday,and added two buttons:for him to view the credit history of that client,and the other to allow him to approve or reject the loan.

so the reason for rejection is usually a bad credit history and a low score.the officer will be able to view this..while the client will just be informed that their application has been rejected.

so,here is my code for the page that retrieves the applications:

Code: Select all

$query = "SELECT loanID,custNric,loanAmt,score FROM loanDetails WHERE loanDetails.ad=1 "; 
	 
$result = mysql_query($query) or die(mysql_error());
echo "<table border='1'>";
echo "<tr> <th>Loan ID</th> <th>Loan Amount</th><th>Score</th><th>View History</th><th>Action</th></tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	echo "<tr><td>"; 
	echo $row['loanID'];
		echo "</td><td>"; 
	echo $row['loanAmt'];
	echo "</td><td>"; 
	echo $row['score'];
	echo "</td>";
	echo "<td>"; ?>
	
	<form action="../olaps/creditHistory.php" method="post" target="_blank">
	  <p>
	    <input type="hidden" name="loanID"   value="<?php echo $row['loanID']  ?>">
		<input type="hidden" name="type"   value="<?php echo $type  ?>">
<input type="submit" name="Submit" value="View History">	
          </p>
	  </form>
	<?php

	echo "</td>";
	echo "<td>"; ?>
	<form action="../olaps/setLimit.php" method="post" target="_blank">
	  <p>
	    <label>
	    <input type="radio" name="loanAction" value="a">
  Approve</label>
	     
	    <label>
	    <input type="radio" name="loanAction" value="r">
  Reject</label>
	     <input type="submit" name="Submit2" value="Send">
	     <br>
	    </p>
	
	
	</form>
	
	<?php
	
	echo "</td></tr>"; 
}
echo "</table>";

im not sure whether using forms is the most efficient way to do this.any suggestions to improve it is welcomed.

and i still dont know how to make each row dissappear each time the officer has approved/rejected a loan. i've been thinking of changing the ad field in loandetails to 0 after that loan has been processed. that way, a redirect to the page might not show the processed loans.but i am not sure whether this will work.

thanks all :)

Posted: Thu Oct 04, 2007 10:22 am
by ReverendDexter
If I have rows that I know the user will want to "delete", I usually tack on a "deleted" column (tinyint) to just store a flag value (0 or 1). It does add a little to your queries, as now you have to select "WHERE/AND deleted = 0", but it also gives the magic of "undelete" when your loan officer inevitably hits the wrong button :)