Hello:

Here is an interesting question.

I have a client payment recording form which retrieves data from a table that has clients balances and other.

My task with this form is to take payments (which can be payments in full or partial payments). I need to be able to update the originating table with new payments as well as have the ability to record multiple payment to this account.

Not sure if this is doable with the existing table or will I need a separate table in conjunction with the initial table.

Currently I'm able to update the originating table in this way:

<?php
error_reporting(0);
error_reporting(E_ERROR | E_WARNING | E_PARSE);
//error_reporting(E_ALL);

$host = "localhost";
$login_name = "23232";
$password = "23232242";

$link=mysql_connect("$host","$login_name","$password");		
mysql_select_db("shop", $link); 
//or die("Could not find database");
//echo mysql_errno() . ": " . mysql_error(). "\n";

// define each variable
if(isset($_POST['submit']))//this defines any variable that are not define below
  {
$edit_sel_id = mysql_real_escape_string($_POST['edit_sel_id']);
$paymentamount = $_POST['paymentamount'];
$paymenttype = mysql_real_escape_string($_POST['paymenttype']);
$paymentref = mysql_real_escape_string($_POST['paymentref']);
$paymentdate = mysql_real_escape_string($_POST['paymentdate']);
$memo = mysql_real_escape_string($_POST['memo']);
$newbalance=$_POST['newbalance'];
$newbalance= "$".''.$newbalance;
$paymentamount= "$".''.$paymentamount;

if ($edit_sel_id == "") echo "! No identifier retrieved";
else

$mysql_query=("UPDATE billofservice SET newpayment_date=NOW(), newpayment_amount='$paymentamount', newpayment_type='$paymenttype', 
paymentref='$paymentref', newpmtmemo='$memo', Balancedue='$newbalance' WHERE id=$edit_sel_id");
$result=mysql_query($mysql_query);
if(!$result){die("Error: ".mysql_error());
}
mysql_close($link);
}

?>

I need to figure out a plan when partial payments needs to be entered --as this current mechanism would replace exiting fields with new ones.
Any thoughts!
Mossa

Recommended Answers

All 8 Replies

Do you want to capture the new payment amount? and replace the existing payment data of that user?

or add to the payment amount to the existing data.

Hello:

Here is an interesting question.

I have a client payment recording form which retrieves data from a table that has clients balances and other.

My task with this form is to take payments (which can be payments in full or partial payments). I need to be able to update the originating table with new payments as well as have the ability to record multiple payment to this account.

Not sure if this is doable with the existing table or will I need a separate table in conjunction with the initial table.

Currently I'm able to update the originating table in this way:

<?php
error_reporting(0);
error_reporting(E_ERROR | E_WARNING | E_PARSE);
//error_reporting(E_ALL);

$host = "localhost";
$login_name = "23232";
$password = "23232242";

$link=mysql_connect("$host","$login_name","$password");		
mysql_select_db("shop", $link); 
//or die("Could not find database");
//echo mysql_errno() . ": " . mysql_error(). "\n";

// define each variable
if(isset($_POST['submit']))//this defines any variable that are not define below
  {
$edit_sel_id = mysql_real_escape_string($_POST['edit_sel_id']);
$paymentamount = $_POST['paymentamount'];
$paymenttype = mysql_real_escape_string($_POST['paymenttype']);
$paymentref = mysql_real_escape_string($_POST['paymentref']);
$paymentdate = mysql_real_escape_string($_POST['paymentdate']);
$memo = mysql_real_escape_string($_POST['memo']);
$newbalance=$_POST['newbalance'];
$newbalance= "$".''.$newbalance;
$paymentamount= "$".''.$paymentamount;

if ($edit_sel_id == "") echo "! No identifier retrieved";
else

$mysql_query=("UPDATE billofservice SET newpayment_date=NOW(), newpayment_amount='$paymentamount', newpayment_type='$paymenttype', 
paymentref='$paymentref', newpmtmemo='$memo', Balancedue='$newbalance' WHERE id=$edit_sel_id");
$result=mysql_query($mysql_query);
if(!$result){die("Error: ".mysql_error());
}
mysql_close($link);
}

?>

I need to figure out a plan when partial payments needs to be entered --as this current mechanism would replace exiting fields with new ones.
Any thoughts!
Mossa

Do you want to capture the new payment amount? and replace the existing payment data of that user?

or add to the payment amount to the existing data.

Thanks for the reply! Essentially, I want to create a track record of any payment. To that effect, I want the new payment(s) to be substrated from whatever is the balance and reflect such balance as new as well as create a record of the payment(s) -- its date and time, reference number and payment type. My payment recorder is setup to capture this information. As matter of fact here is the form: (hope this helps in clarifying my intention)

<form name='PaymentForm' onsubmit='return ValidatePaymentForm();' action='updatebalancedue.php' method='post'>
<input type=\"hidden\" name=\"edit_sel_id\" value=\"$sel_id\">

<div id=\'pmt_header\'><center><table class=\'tablestyle2\' width=90% cellpadding=2 cellspacing=0>
<tr valign='top'>
	<td style=\'border:1px solid #cccccc;\' width=\"28%\" align=\"justify\">
<table class=\'tablestyle_inner\' width=\"170\">
<tr>
</td>
</tr>
<tr><td bgcolor='#000000'><font color='#FFFFFF' class='label'>Payment Received Date:</td>
</tr>
</table>
<input type='text' value='$date' readonly name='theDate' size='11'><input type='button' class='NonPrintable' value='Calendar' onclick='displayCalendar(document.forms[0].theDate,'yyyy/mm/dd',this)'>
</td>
<td style='border:1px solid #cccccc;' width=\"35%\">
<table class='tablestyle_inner' width='607'>
<tr><td valign='top'class='label' width='110'>Date of Invoice</td>
	<td width='196'><span id='_PayType_sel'><input name='$date_on_invoice' value='$date_on_invoice' disabled='true' style='float: left' valign='top'></td>
	<td valign='top' width='106'>Client ID:	</td><td valign='top'>
<input name='$clientID' value='$clientID' disabled='true' style='float: left'></span></td>
<tr><td class='label' width='110'>First Name:</td><td width='196'><input  type='text'  size='22' maxlength='50' value='$firstname' style='float: left'></td><td valign='top'>Last Name:</td><td valign='top'><input  type='text'  size='22' maxlength='50' value='$lastname' name='T1' style='float: left'></td>
</tr>
</table>
</td>
<td style='border:1px solid #cccccc;' width='35%' align='right'>
<table class='tablestyle_inner'>
<tr><td bgcolor='#000000'><font color='#FFFFFF' class='label' align='left'>New Bal.:</td></tr><tr><td><input name='dollarsymbol3' size='1' readonly style='border:0px;' value='$'><input name='newbalance' readonly style='color: #EE0000' size='8' maxlength='16' value='0.00'> <!--<input type='text' name='newbalance' disabled='true' size='16' maxlength=\"18\" value='$0.00'--></td>
</tr>
</table>

<td style='border:1px solid #cccccc;' width='35%' align='right'>
<table class='tablestyle_inner'>
<tr><td bgcolor='#000000'><font color='#FFFFFF' class='label' align=\"left\">Payment Reference:</td></tr><tr><td><input type='text' name='paymentref'  size='16' maxlength='18' value='$RNO'</td>
</tr>
</table>

</table></center>
<br></div>
<div id='items_table'><center>
	<table class='tablestyle' colspan=7 width='89%' cellpadding=2 cellspacing=0>
<tr>
<td style='border:1px solid #cccccc;' width=' 15%' class='tableheader' bgcolor='#000000'><font color='#FFFFFF'> Invoice Number</td>
<td align='center' style='border: 1px solid #cccccc;' width=' 15%' class='tableheader' bgcolor='#000000'><font color='#FFFFFF'>Service Description</td>
<td style='border: 1px solid #cccccc;' width=' 15%' class='tableheader' bgcolor='#000000'><font color='#FFFFFF'>Balance Due</td>
<td style='border: 1px solid #cccccc;' width=' 15%' class='tableheader' bgcolor='#000000'><font color='#FFFFFF'>Payment</td>
<td style='border: 1px solid #cccccc;' width=' 15%' style='border:1px solid #cccccc;' width=' 15%'class='tableheader' bgcolor='#000000'><font color='#FFFFFF'>Payment Type</td>
</tr>
<tr>
<td valign='top' style='border-left: 0px solid #cccccc;' width=' 15%'><input  type='text' name='$invoicenum' disabled='true' size='12' maxlength='10' value='$invoicenum' class='combo3' rel='code_id' title=''>
</td><td valign='top' style='border-right: 0px solid #cccccc;' width=' 15%' ><span id='_code_id_sel'>
<!-- the line below serves to auto adjust the textarea box in accordance with the box's content. The class allows the box to expend to 1000 this works with two files jquery.textarea-expander.js and jquery-1.3.2.min.js -->
<textarea name='mytextarea' id='mytextarea' rows='3' cols='60' disabled='true'class='expand10-100'>$servicedesc</textarea>
<!--<textarea name='servicedesc' cols='30' rows='2' disabled='true'  >$servicedesc</textarea></span>-->
</td>
<input type=\"hidden\" name=\"dimension2_id\" value=\"0\">
<td valign='top' style='border-left: 0px solid #cccccc;' width=' 15%' align='left'> <input name='dollarsymbol2' size='1' readonly style='border:0px;' value='$'><input name='balance' size='12' disabled='true' value='$new_bal' onChange='updatesum()'> <!--<input class='amount' type=\"text\" name='balance' disabled='true' size=\"15\" maxlength=\"15\" dec=\"2\" value='$Balancedue'>--></td>
<td valign='top' style='border-left: 0px solid #cccccc;' width=' 15%' align='left'> <input name='dollarsymbol1' size='1' readonly style='border:0px;' value='$'><input name='paymentamount' size='12' style='color: #71C671' value='0.00' onChange='updatesum()'></td>
<td valign='top' style='border-left: 0px solid #cccccc;' width=' 15%' ><span<select autocomplete='on'  name='paymenttype' ><OPTION>Select One
						<OPTION>Cash
						<OPTION>Check
						<OPTION>Visa
						<OPTION>Master Card
						<OPTION>AMEX
						<OPTION>Discover
						<OPTION>Other
</select></span></td>
</tr>
</table></center>
</div>
<br><table align='center'><tr><td >Memo</td><td><textarea name='memo' cols='50' rows='3'></textarea><textarea STYLE='display:none' name='pmtthistory'  cols='50' rows='3'></textarea></td>
</tr>
</table>
  <br><br><center><input type='button'  class='NonPrintable' value='Select Another Record' ONCLICK='history.go(-1)'> &nbsp;&nbsp;&nbsp;&nbsp;<input type='submit' name='submit' class='NonPrintable' value='Record Payment'>&nbsp;&nbsp;&nbsp;&nbsp;<input type='button' class='NonPrintable' onClick='window.print()' value='Print' />
</center>
</p>
</form>";
							
}
?>

maintain a flag as payment_status in your table

Actual Payment amt : 2000

Suppose user paid 500 then in your database the record will goes

$needs_to_pay = 2000-500;
Record 1 :
actual_amount : 2000
paid_amount : 500
remaining_amount : $needs_to_pay
datetiem : XXX
payment_type : CC
payment_status : pending

Suppose if user paid 800 in second payment, $paidNow = 800
condition1 : check whether user's remaining amount is 0 or not
step1 : check whether this user has paid any amount, if yes bring that data to $paidAmount = 500;

$totalPaidAmount = $paidAmount + $paidNow;
$needs_to_pay = 2000-$totalPaidAmount;

actual_amount : 2000
paid_amount : 800
remaining_amount : $needs_to_pay
datetime : XXX
payment_type : CC
payment_status : pending

Update the Record 1 : status to finished

:)

Thanks for the reply! Essentially, I want to create a track record of any payment. To that effect, I want the new payment(s) to be substrated from whatever is the balance and reflect such balance as new as well as create a record of the payment(s) -- its date and time, reference number and payment type. My payment recorder is setup to capture this information. As matter of fact here is the form: (hope this helps in clarifying my intention)

<form name='PaymentForm' onsubmit='return ValidatePaymentForm();' action='updatebalancedue.php' method='post'>
<input type=\"hidden\" name=\"edit_sel_id\" value=\"$sel_id\">

<div id=\'pmt_header\'><center><table class=\'tablestyle2\' width=90% cellpadding=2 cellspacing=0>
<tr valign='top'>
	<td style=\'border:1px solid #cccccc;\' width=\"28%\" align=\"justify\">
<table class=\'tablestyle_inner\' width=\"170\">
<tr>
</td>
</tr>
<tr><td bgcolor='#000000'><font color='#FFFFFF' class='label'>Payment Received Date:</td>
</tr>
</table>
<input type='text' value='$date' readonly name='theDate' size='11'><input type='button' class='NonPrintable' value='Calendar' onclick='displayCalendar(document.forms[0].theDate,'yyyy/mm/dd',this)'>
</td>
<td style='border:1px solid #cccccc;' width=\"35%\">
<table class='tablestyle_inner' width='607'>
<tr><td valign='top'class='label' width='110'>Date of Invoice</td>
	<td width='196'><span id='_PayType_sel'><input name='$date_on_invoice' value='$date_on_invoice' disabled='true' style='float: left' valign='top'></td>
	<td valign='top' width='106'>Client ID:	</td><td valign='top'>
<input name='$clientID' value='$clientID' disabled='true' style='float: left'></span></td>
<tr><td class='label' width='110'>First Name:</td><td width='196'><input  type='text'  size='22' maxlength='50' value='$firstname' style='float: left'></td><td valign='top'>Last Name:</td><td valign='top'><input  type='text'  size='22' maxlength='50' value='$lastname' name='T1' style='float: left'></td>
</tr>
</table>
</td>
<td style='border:1px solid #cccccc;' width='35%' align='right'>
<table class='tablestyle_inner'>
<tr><td bgcolor='#000000'><font color='#FFFFFF' class='label' align='left'>New Bal.:</td></tr><tr><td><input name='dollarsymbol3' size='1' readonly style='border:0px;' value='$'><input name='newbalance' readonly style='color: #EE0000' size='8' maxlength='16' value='0.00'> <!--<input type='text' name='newbalance' disabled='true' size='16' maxlength=\"18\" value='$0.00'--></td>
</tr>
</table>

<td style='border:1px solid #cccccc;' width='35%' align='right'>
<table class='tablestyle_inner'>
<tr><td bgcolor='#000000'><font color='#FFFFFF' class='label' align=\"left\">Payment Reference:</td></tr><tr><td><input type='text' name='paymentref'  size='16' maxlength='18' value='$RNO'</td>
</tr>
</table>

</table></center>
<br></div>
<div id='items_table'><center>
	<table class='tablestyle' colspan=7 width='89%' cellpadding=2 cellspacing=0>
<tr>
<td style='border:1px solid #cccccc;' width=' 15%' class='tableheader' bgcolor='#000000'><font color='#FFFFFF'> Invoice Number</td>
<td align='center' style='border: 1px solid #cccccc;' width=' 15%' class='tableheader' bgcolor='#000000'><font color='#FFFFFF'>Service Description</td>
<td style='border: 1px solid #cccccc;' width=' 15%' class='tableheader' bgcolor='#000000'><font color='#FFFFFF'>Balance Due</td>
<td style='border: 1px solid #cccccc;' width=' 15%' class='tableheader' bgcolor='#000000'><font color='#FFFFFF'>Payment</td>
<td style='border: 1px solid #cccccc;' width=' 15%' style='border:1px solid #cccccc;' width=' 15%'class='tableheader' bgcolor='#000000'><font color='#FFFFFF'>Payment Type</td>
</tr>
<tr>
<td valign='top' style='border-left: 0px solid #cccccc;' width=' 15%'><input  type='text' name='$invoicenum' disabled='true' size='12' maxlength='10' value='$invoicenum' class='combo3' rel='code_id' title=''>
</td><td valign='top' style='border-right: 0px solid #cccccc;' width=' 15%' ><span id='_code_id_sel'>
<!-- the line below serves to auto adjust the textarea box in accordance with the box's content. The class allows the box to expend to 1000 this works with two files jquery.textarea-expander.js and jquery-1.3.2.min.js -->
<textarea name='mytextarea' id='mytextarea' rows='3' cols='60' disabled='true'class='expand10-100'>$servicedesc</textarea>
<!--<textarea name='servicedesc' cols='30' rows='2' disabled='true'  >$servicedesc</textarea></span>-->
</td>
<input type=\"hidden\" name=\"dimension2_id\" value=\"0\">
<td valign='top' style='border-left: 0px solid #cccccc;' width=' 15%' align='left'> <input name='dollarsymbol2' size='1' readonly style='border:0px;' value='$'><input name='balance' size='12' disabled='true' value='$new_bal' onChange='updatesum()'> <!--<input class='amount' type=\"text\" name='balance' disabled='true' size=\"15\" maxlength=\"15\" dec=\"2\" value='$Balancedue'>--></td>
<td valign='top' style='border-left: 0px solid #cccccc;' width=' 15%' align='left'> <input name='dollarsymbol1' size='1' readonly style='border:0px;' value='$'><input name='paymentamount' size='12' style='color: #71C671' value='0.00' onChange='updatesum()'></td>
<td valign='top' style='border-left: 0px solid #cccccc;' width=' 15%' ><span<select autocomplete='on'  name='paymenttype' ><OPTION>Select One
						<OPTION>Cash
						<OPTION>Check
						<OPTION>Visa
						<OPTION>Master Card
						<OPTION>AMEX
						<OPTION>Discover
						<OPTION>Other
</select></span></td>
</tr>
</table></center>
</div>
<br><table align='center'><tr><td >Memo</td><td><textarea name='memo' cols='50' rows='3'></textarea><textarea STYLE='display:none' name='pmtthistory'  cols='50' rows='3'></textarea></td>
</tr>
</table>
  <br><br><center><input type='button'  class='NonPrintable' value='Select Another Record' ONCLICK='history.go(-1)'> &nbsp;&nbsp;&nbsp;&nbsp;<input type='submit' name='submit' class='NonPrintable' value='Record Payment'>&nbsp;&nbsp;&nbsp;&nbsp;<input type='button' class='NonPrintable' onClick='window.print()' value='Print' />
</center>
</p>
</form>";
							
}
?>

Thanks again for the follow up.
Could I ask of you to give a little more explanation on implementing your suggestion as relates to what have thus for --the form code and the php code posted in earlier above.

I want to create a track record of any payment. To that effect, I want the new payment(s) to be substrated from whatever is the balance and reflect such balance as new as well as create a record of the payment(s) -- its date and time, reference number and payment type. My payment recorder is setup to capture this information. As matter of fact here is the form: (hope this helps in clarifying my intention)

Sorry to say that I didn't see your code, I have given you the implementation flow for your question...

Thanks again for the follow up.
Could I ask of you to give a little more explanation on implementing your suggestion as relates to what have thus for --the form code and the php code posted in earlier above.

I do appreciate the implementation flow as per my question. It would be helpful if such flow would augment what I have thus far. Should I post the code again?

I saw your code, you are updating the entire record as

$mysql_query=("UPDATE billofservice SET newpayment_date=NOW(), newpayment_amount='$paymentamount', newpayment_type='$paymenttype',
paymentref='$paymentref', newpmtmemo='$memo', Balancedue='$newbalance' WHERE id=$edit_sel_id");

Instead follow the steps what I have given to you, every time when user pays, insert a new record and update the existing record payment_status to finished.

finally if you want to check user's pending amt, you can fire a query like
select remaining_amount from table where payment_status = 'pending' and userid = $userid;
:cool:

I do appreciate the implementation flow as per my question. It would be helpful if such flow would augment what I have thus far. Should I post the code again?

Ok, sounds good. I'll attempt your advice.
Thank you very much!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.