Auto-fill calendar stops upon the insertion of another db entry

[aarontbarksdale]

Okay, here's HOW the code is supposed to work: Customer enters $startdate for a client in one form (info stored in one db table). Then customer enters several payments since $startdate. Then a report is automatically generated where the list on the left side of the report it automatically starts a date count from $startdate to $today. What I need to do is have the while loop pause, insert a payment entry based on date, then continue from there. There's also other things the report will do, but first thing's first...here's the working code that adds 1 month to the $startdate then stops when the payment date is entered into the while loop.

...require ('connect.php');$myusername = $_SESSION["myusername"];$data1 = mysql_query("SELECT * FROM clients WHERE client_id = ". $_SESSION['clientid'] ."") or die(mysql_error());$data = mysql_query("SELECT * FROM payments WHERE client_id = ". $_SESSION['clientid'] ." ORDER BY YEAR(pmt_date) ASC") or die(mysql_error());$row = mysql_fetch_assoc($data1);$row2 = mysql_fetch_assoc($data);$startdate = strtotime($row['datestart']);$today = strtotime('now');$pmt_date = strtotime($row2['pmt_date']);$amtdue = $row['supportamount'];...<p>Report for Client: <? echo $row['cp_firstname'] . " " . $row['cp_lastname'] ;?> <br />		  <?phpecho '<table  width="900" cellpadding="2" cellspacing="2">';echo '<tr><td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-left-style:dashed; border-right-style:dashed; border-width:thin;"><center>Date</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-left-style:dashed; border-right-style:dashed; border-width:thin;"><center>Amount Due</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-left-style:dashed; border-right-style:dashed; border-width:thin;"><center>Amount Paid</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed;border-left-style:dashed; border-right-style:dashed; border-width:thin;"><center>Interest Accumulated</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed;border-left-style:dashed; border-right-style:dashed; border-width:thin;"><center>Applied to Interest</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed;border-left-style:dashed; border-right-style:dashed; border-width:thin;"><center>Applied to Principle</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed;border-left-style:dashed; border-right-style:dashed; border-width:thin;"><center>Balance</center></td></tr>';while($startdate < $today){  echo '<tr><td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. date("m.d.Y", $startdate) . '</center></td>';  echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($amtdue, 2, '.',',') .'</center></td>';  echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($row2["pmt_amt"], 2, '.',',') . '</center></td>';  echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($intacc, 2, '.',',') . '</center></td>';  echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appint, 2, '.',',') . '</center></td>';  echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appprinc, 2, '.',',') . '</center></td>';  echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($bal, 2, '.',',') . '</center></td></td>';  $startdate = strtotime('+1 month', $startdate);}echo '</table>';

If there are any suggestions that you might be able to give me to rectify this particular issue, that would be FANTASTIC!!!

[justsomeguy]

I'm not clear about what you're asking for. You don't "pause" and restart a loop, but you can add additional code inside the loop. I'm not sure what you're trying to accomplish though.

[aarontbarksdale]

For instance, if a payment is made on Jan 20, 2013 but payments were supposed to start on June 1, 2012, the output would start atJune.1.2012July.1.2012Aug.1.2012Sept.1.2012Oct.1.2012Nov.1.2012Dec.1.2012Jan.1.2013 <<Above is automatically done with the $startdate = strtotime('+1 month', $startdate);Jan.20.2013 <<Another line entered this paymentFeb.1.2013 <<Auto-increment code continues.That's what I'm going for...do you have any suggestions. I am still learning so maybe using "pause" isn't the right thing.I've tried:

if ($pmt_date < $startdate) {   echo $pmt_date;}

However, it STOPS there...it won't go further...so...I welcome suggestions

[justsomeguy]

Inside the loop you can get the start and end of the month, and check for payments within that range. $monthStart = $startdate;$monthEnd = strtotime('+1 month', $monthStart) - 1; Now you can check for payments that are between $monthStart and $monthEnd.

[aarontbarksdale]

would I need to use foreach? What would the code, something like

if ($pmt_date > $monthEnd && $pmt_date < $monthStart) {	 echo "$pmt_date;}

[justsomeguy]

It's the other way, it needs to be greater than or equal to $monthStart, and less than or equal to $monthEnd. If you have a list of payments then you can loop through them to check each one.

[aarontbarksdale]

Here's my new while loop:

while($monthStart < $today){if ($pmt_date >= $monthStart && $pmt_date <= $monthEnd) {echo '<tr><td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. date("m.d.Y", $pmt_date) . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>$0.00</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($row2["pmt_amt"], 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($intacc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appint, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appprinc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($bal, 2, '.',',') . '</center></td></td>';} else {echo '<tr><td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. date("m.d.Y", $monthStart) . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($amtdue, 2, '.',',') .'</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>$0.00</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($intacc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appint, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appprinc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($bal, 2, '.',',') . '</center></td></td>';}$monthEnd = strtotime('+1 month', $monthStart);}

It's still not quite working. The output is nothing but the FIRST starting date (set in DB as 05.01.2010). My first payment is set in DB as 07.05.2010....all it does it continues to repeat the starting date on EACH LINE and continues running.

Edited May 10, 2013 by aarontbarksdale

[justsomeguy]

It's not going to end because you're not updating $monthStart, that value never changes. After each time through the loop you need to update both $monthStart and $monthEnd.

[aarontbarksdale]

Yeah...I caught that...like an idiot. So now why isn't it working???

while($monthStart <= $today){if ($pmt_date <= $monthStart && $pmt_date >= $monthEnd) {echo '<tr><td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. date("m.d.Y", $pmt_date) . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>$0.00</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($row2["pmt_amt"], 2, '.',',') . '</center></td>'; echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($intacc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appint, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appprinc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($bal, 2, '.',',') . '</center></td></td>';$monthEnd = strtotime('+1 month', $monthStart);$monthStart = strtotime('+1 month', $monthStart);} else {echo '<tr><td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. date("m.d.Y", $monthStart) . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($amtdue, 2, '.',',') .'</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>$0.00</center></td>'; echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($intacc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appint, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appprinc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($bal, 2, '.',',') . '</center></td></td>';}$monthEnd = strtotime('+1 month', $monthStart);$monthStart = strtotime('+1 month', $monthStart);}

It doesn't inject the payment....should there be another SQL call like

$row3 = mysql_query("SELECT * FROM payments WHERE pmt_date > '$monthStart'"); 

or something like that...that way I'm always pulling the information correctly???

[justsomeguy]

You switched the condition in the if statement again, you're checking if the payment date is both before the start and after the end, obviously that can't be true. Also, remove updating the start and end from inside the if statement, that's going to cause it to skip a month because it would update it twice in one loop. It's always going to update at the end, you don't also need to update in the if statement. As far as getting the data from the database, it would be most efficient if you had a single database query outside of the loop to get all payments. Inside the loop you can loop through the database results to see if any of the dates match for that month. That would be more efficient than running a query each time through the loop, doing that will cause it to run slower. Queries inside loops should be avoided if they can, even if it means you need to loop through all of the payments each time through.

[justsomeguy]

You can do something like this to get all of the payments in an array that you can loop through to check the dates:

$data = mysql_query("SELECT * FROM payments WHERE client_id = ". $_SESSION['clientid'] ." ORDER BY YEAR(pmt_date) ASC") or die(mysql_error());$payments = array();while ($row = mysql_fetch_assoc($data)){  $payments[] = $row;}

Now $payments will be an array of all of the records from the database which you can loop through to check the dates on inside the calendar loop.

[aarontbarksdale]

Okay...I don't know what I did wrong...I've even tried tweaking things and I am still coming up short. Did something and had it progressing starting at $startdate to infinity...realized THAT issue...now I have it back to starting at $startdate (changed to $monthStart) and stopping correctly, I corrected the if () statement and moved the progression outside of the if statement...it's back to where it was...no payments entered...I even tried using $payments['pmt_date'] thinking that might work...didn't...didn't do anything more than $row2['pmt_date'] which still doesn't insert the payment line.

//Data from Clients table and variables associate$data = mysql_query("SELECT * FROM clients WHERE client_id = ". $_SESSION['clientid'] ."") or die(mysql_error());$row = mysql_fetch_assoc($data);$monthStart = strtotime($row['datestart']);$today = strtotime('now');$amtdue = $row['supportamount'];//Data from Payments table and variables associated$data2 = mysql_query("SELECT * FROM payments WHERE client_id = ". $_SESSION['clientid'] ." ORDER BY YEAR(pmt_date) ASC") or die(mysql_error());$payments = array();while ($row2 = mysql_fetch_assoc($data2)){  $payments[] = $row2;}...//While loopwhile($monthStart <= $today){if ($row2['pmt_date'] >= $monthStart && $row2['pmt_date'] <= $monthEnd) {echo '<tr><td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. date("m.d.Y", $row2['pmt_date']) . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>$0.00</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($row2['pmt_amt'], 2, '.',',') . '</center></td>'; echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($intacc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appint, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appprinc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($bal, 2, '.',',') . '</center></td></td>';}else {echo '<tr><td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. date("m.d.Y", $monthStart) . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($amtdue, 2, '.',',') .'</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>$0.00</center></td>'; echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($intacc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appint, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appprinc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($bal, 2, '.',',') . '</center></td></td>';}$monthEnd = strtotime('+1 month', $monthStart);$monthStart = strtotime('+1 month', $monthStart);}

[justsomeguy]

You're only checking a single payment date, not all of them. You need to loop through the payments and check each one. If the value in the database for the date is a date string instead of a Unix timestamp then you also need to use strtotime to convert it before checking.

while($monthStart <= $today){  $payment_found = false;  foreach ($payments as $payment)  {    if (strtotime($payment['pmt_date']) >= $monthStart && strtotime($payment['pmt_date']) < $monthEnd) {	  $payment_found = true;	  // show information from $payment	}  }   if (!$payment_found)  {	// show default information for no payment  }   $monthStart = strtotime('+1 month', $monthStart);  $monthEnd = strtotime('+1 month', $monthStart);}

[aarontbarksdale]

So, it works...except...it doesn't display the correct date...but rather the default of 12.31.1969. I'm not sure exactly...also, the way you provided the code it dropped the 1st of the month of the same month that the payment is in. For example, if the first payment is made on 07.05.2010, it dropped 07.01.2010. So I added another if statement that you'll see in the following code. If you can find where it is that the code for the date that's not working correct lies, please let me know.

while($monthStart <= $today){$payment_found = false;  foreach ($payments as $payment)  {	if (strtotime($payment['pmt_date']) >= $monthStart && strtotime($payment['pmt_date']) < $monthEnd) {		  $payment_found = true;          if (strtotime($payment['pmt_date']) > $monthStart) {                 echo '<tr><td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. date("m.d.Y", $monthStart) . '</center></td>';                 echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($amtdue, 2, '.',',') .'</center></td>';                 echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>$0.00</center></td>';                 echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($intacc, 2, '.',',') . '</center></td>';                 echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appint, 2, '.',',') . '</center></td>';                 echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appprinc, 2, '.',',') . '</center></td>';                 echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($bal, 2, '.',',') . '</center></td></td>';           }      echo '<tr><td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. date("m.d.Y", $payment['pmt_date']) .'</center></td>';      echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>$0.00</center></td>';      echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($payment['pmt_amt'], 2, '.',',') . '</center></td>';      echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($intacc, 2, '.',',') . '</center></td>';      echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appint, 2, '.',',') . '</center></td>';      echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appprinc, 2, '.',',') . '</center></td>';      echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($bal, 2, '.',',') . '</center></td></td>';   }  }  if (!$payment_found)  {echo '<tr><td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. date("m.d.Y", $monthStart) . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($amtdue, 2, '.',',') .'</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>$0.00</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($intacc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appint, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($appprinc, 2, '.',',') . '</center></td>';echo '<td style="border-bottom-color:#3105b0; border-bottom-style:dashed; border-bottom-width:thin;"><center>'. "$". $english_format_number = number_format($bal, 2, '.',',') . '</center></td></td>';}$monthStart = strtotime('+1 month', $monthStart);$monthEnd = strtotime('+1 month', $monthStart);}

Edited May 11, 2013 by aarontbarksdale

[aarontbarksdale]

Fixed it!

date("m.d.Y", strtotime($payment['pay_date']))

[aarontbarksdale]

Okay...so can I put my calculations for the other entries above the first IF statement? That way it will calculate all the math as it passes through the loop?

[justsomeguy]

Sure, you can arrange it however you want to. If you're getting an error creating the DateTime object, check this page for examples on what to pass to it: http://www.php.net/manual/en/datetime.construct.php If you're passing a Unix timestamp it looks like you need to prefix it with an @ sign.