Go to solution Solved by Barand,

## Recommended Posts

Hi all.

In my database, i have a column recurring which is derived from a multiple option

form field with values: Weekly, Bi-Monthly, Monthly, Quarterly, Half Yearly and

Yearly. I want to have as next due the current date plus the recurring value. eg

current date = 2014-11-24
recurring = monthly
next due = current date + recurring (in the next due will be 2014-12-24)

so i did:

```\$stmt = \$pdo->query("SELECT recurring, due_date FROM \$table");
while(\$row = \$stmt->fetch(PDO::FETCH_ASSOC)) {
\$recur = \$row['recurring'];
\$date_due = \$row['due_date'];
}
\$weekly = "Weekly";
\$bi_monthly = "Bi-Monthly";
\$monthly = "Monthly";
\$quarterly = "Quarterly";
\$half_yearly = "Half Yearly";
\$yearly = "Yearly";

if(strcmp(\$recur, \$weekly) == 0){
\$recurs = "7 DAY";
}elseif(strcmp(\$recur, \$bi_monthly) == 0){
\$recurs = "14 DAY";
}elseif(strcmp(\$recur, \$monthly) == 0){
\$recurs = "1 MONTH";
}elseif(strcmp(\$recur, \$quarterly) == 0){
\$recurs = "3 MONTH";
}elseif(strcmp(\$recur, \$half_yearly) == 0){
\$recurs = "6 MONTH";
}elseif(strcmp(\$recur, \$yearly) == 0){
\$recurs = "1 YEAR";
}

\$stmt = \$pdo->query("SELECT ADDDATE('\$date_due', INTERVAL \$recurs) as nex_due FROM

\$table");
\$row = \$stmt->fetch(PDO::FETCH_ASSOC);
\$nex_due = \$row['nex_due'];

\$stmt = \$pdo->prepare("SELECT * FROM \$table ORDER BY trans_id DESC");
\$stmt->execute();
\$num_rows = \$stmt->rowCount();

while(\$row = \$stmt->fetch(PDO::FETCH_ASSOC)) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo \$row['trans_ref'];
echo "</td><td>";
echo \$row['acct_num'];
echo "</td><td>";
echo ucwords(\$row['payee']);
echo "</td><td>";
echo ucwords(\$row['company']);
echo "</td><td>";
echo \$row['acct_no'];
echo "</td><td>";
echo number_format(\$row['amt'],2);
echo "</td><td>";
echo \$row['purpose'];
echo "</td><td>";
echo \$row['recurring'];
echo "</td><td>";
echo \$row['due_date'];
echo "</td><td>";
echo \$nex_due;
echo "</td><td>";
echo "<strong>".\$row['status']."</strong>";
echo "</td><td>";
echo "<strong>".\$row['pay_status']."</strong>";
echo "</td><td>";
}```

The problem is that it's giving me as next due the value of the first row even

when the recurring is different!
##### Share on other sites

You could do this in the query with a CASE statement.

```SELECT recurring, due_date,
CASE recurring
WHEN 'weekly' THEN due_date + INTERVAL 7 DAY
WHEN 'bi-weekly' THEN due_date + INTERVAL 14 DAY
WHEN 'monthly' THEN due_date + INTERVAL 1 MONTH
WHEN 'quarterly' THEN due_date + INTERVAL 3 MONTH
WHEN 'half-yearly' THEN due_date + INTERVAL 6 MONTH
WHEN 'yearly' THEN due_date + INTERVAL 1 YEAR
END as next_due
FROM table
```
Note that you'll have to determine in what manner you want to handle dates that may not be valid for all intervals. For example if the due day were Feb. 29th and the interval was yearly, or Jan 31st and the interval was monthly. You can check mysql's default behavior in those instances and see if that is acceptable or if you want to do something different.
##### Share on other sites

You could do this in the query with a CASE statement.

```SELECT recurring, due_date,
CASE recurring
WHEN 'weekly' THEN due_date + INTERVAL 7 DAY
WHEN 'bi-weekly' THEN due_date + INTERVAL 14 DAY
WHEN 'monthly' THEN due_date + INTERVAL 1 MONTH
WHEN 'quarterly' THEN due_date + INTERVAL 3 MONTH
WHEN 'half-yearly' THEN due_date + INTERVAL 6 MONTH
WHEN 'yearly' THEN due_date + INTERVAL 1 YEAR
END as next_due
FROM table
```
Note that you'll have to determine in what manner you want to handle dates that may not be valid for all intervals. For example if the due day were Feb. 29th and the interval was yearly, or Jan 31st and the interval was monthly. You can check mysql's default behavior in those instances and see if that is acceptable or if you want to do something different.

thanks but what you did is a better and simpler way of doing what i did but it still did not solve my problem. The problem is that it calculates the next due for a single row and gives the result to all the rows in the table even when they are different dates.

I need something that will give me the values of different rows/dates eg

2014-12-25 ------ 2015-01-25 (Monthly)

2014-12-10 ------ 2014-12-24 (Bi-Weekly)

thanks

##### Share on other sites

The problem is that it calculates the next due for a single row and gives the result to all the rows in the table even when they are different dates.

Then you are processing the results incorrectly

```mysql> SELECT recurring, due_date,
->     CASE recurring
->        WHEN 'weekly' THEN due_date + INTERVAL 7 DAY
->        WHEN 'bi-weekly' THEN due_date + INTERVAL 14 DAY
->        WHEN 'monthly' THEN due_date + INTERVAL 1 MONTH
->        WHEN 'quarterly' THEN due_date + INTERVAL 3 MONTH
->        WHEN 'half-yearly' THEN due_date + INTERVAL 6 MONTH
->        WHEN 'yearly' THEN due_date + INTERVAL 1 YEAR
->    END as next_due
-> FROM tblchidi;
+-----------+------------+------------+
| recurring | due_date   | next_due   |
+-----------+------------+------------+
| monthly   | 2014-12-25 | 2015-01-25 |
| Bi-Weekly | 2014-12-10 | 2014-12-24 |
| yearly    | 2014-12-20 | 2015-12-20 |
| quarterly | 2014-12-24 | 2015-03-24 |
+-----------+------------+------------+
```
##### Share on other sites

I noticed that the problem is with my code but i can't figure out where the problem is from or if something is wrong with my code.
Maybe there's a better way of re-writting my code.

Thanks, here's my code:

```\$table_name = "Chidi";
\$stmt2 = \$pdo->query("SELECT recurring, due_date, CASE recurring WHEN 'weekly' THEN due_date + INTERVAL 7 DAY WHEN 'bi-weekly' THEN due_date + INTERVAL 14 DAY WHEN 'monthly' THEN due_date + INTERVAL 1 MONTH WHEN 'quarterly' THEN due_date + INTERVAL 3 MONTH WHEN 'half-yearly' THEN due_date + INTERVAL 6 MONTH WHEN 'yearly' THEN due_date + INTERVAL 1 YEAR END as next_due FROM \$table_name");
while(\$row2 = \$stmt2->fetch(PDO::FETCH_ASSOC)){
\$next_due = \$row2['next_due'];
}

\$stmt = \$pdo->prepare("SELECT * FROM \$tbl_name ORDER BY trans_id DESC LIMIT \$start, \$limit");
\$stmt->execute();

echo "<table width='100%' border='1' bordercolor='#0cc' cellspacing='0' cellpadding='2'>";
echo "<tr>
<th bgcolor='#444444' align='center'><font color='#fff'>Trans Ref</th>
<th bgcolor='#444444' align='center'><font color='#fff'>Subscriber's A/C No</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Subscriber's Name</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Services Provider</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Provider's A/C No</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Amount</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Service</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Recurring</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Due Date</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Next Due Date</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Status</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Payment Status</font></th>

</tr>";
// keeps getting the next row until there are no more to get
while(\$row = \$stmt->fetch(PDO::FETCH_ASSOC)) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo \$row['trans_ref'];
echo "</td><td>";
echo \$row['acct_num'];
echo "</td><td>";
echo ucwords(\$row['payee']);
echo "</td><td>";
echo ucwords(\$row['company']);
echo "</td><td>";
echo \$row['acct_no'];
echo "</td><td>";
echo number_format(\$row['amt'],2);
echo "</td><td>";
echo \$row['purpose'];
echo "</td><td>";
echo \$row['recurring'];
echo "</td><td>";
echo \$row['due_date'];
echo "</td><td>";
echo \$next_due;
echo "</td><td>";
echo "<strong>".\$row['status']."</strong>";
echo "</td><td>";
echo "<strong>".\$row['pay_status']."</strong>";
}
echo "</table>";```
##### Share on other sites

I really don't know whether to laugh or cry when I read your code.

You are supposed to incorporate the CASE statement that Kicken gave you into your query so you calculate the next_due for every row.

##### Share on other sites

I really don't know whether to laugh or cry when I read your code.

You are supposed to incorporate the CASE statement that Kicken gave you into your query so you calculate the next_due for every row.

Oops! I'd rather you laugh than cry. lolz. I did that on the top of my query if you checked. Maybe I'd do it along with other query? was thinking it wouldnt work. Will give it a shot. But a point to the right direction wouldnt do any harm!

Thanks

##### Share on other sites

• Solution

Does this help?

```SELECT trans_ref
, acct_num
, payee
, company
, acct_no
, amt
, purpose
, recurring
, due_date
, CASE recurring
WHEN 'weekly' THEN due_date + INTERVAL 7 DAY
WHEN 'bi-weekly' THEN due_date + INTERVAL 14 DAY
WHEN 'monthly' THEN due_date + INTERVAL 1 MONTH
WHEN 'quarterly' THEN due_date + INTERVAL 3 MONTH
WHEN 'half-yearly' THEN due_date + INTERVAL 6 MONTH
WHEN 'yearly' THEN due_date + INTERVAL 1 YEAR
END as next_due
, status
, pay_status
FROM \$tbl_name
ORDER BY trans_id DESC
LIMIT \$start, \$limit

```

And throw away that useless first query.

Edited by Barand
##### Share on other sites

Thanks kicken and berand you both got my best answer both unfortunately only one has to be chosen. thanks a thrillion dozen time. some day i know i'll give back to this wonderful forum. cheers

##### Share on other sites

You could do this in the query with a CASE statement.

```SELECT recurring, due_date,
CASE recurring
WHEN 'weekly' THEN due_date + INTERVAL 7 DAY
WHEN 'bi-weekly' THEN due_date + INTERVAL 14 DAY
WHEN 'monthly' THEN due_date + INTERVAL 1 MONTH
WHEN 'quarterly' THEN due_date + INTERVAL 3 MONTH
WHEN 'half-yearly' THEN due_date + INTERVAL 6 MONTH
WHEN 'yearly' THEN due_date + INTERVAL 1 YEAR
END as next_due
FROM table
```
Note that you'll have to determine in what manner you want to handle dates that may not be valid for all intervals. For example if the due day were Feb. 29th and the interval was yearly, or Jan 31st and the interval was monthly. You can check mysql's default behavior in those instances and see if that is acceptable or if you want to do something different.

But how can the invalid dates for intervals be handled, like the case you sighted with feb 29th?

##### Share on other sites

But how can the invalid dates for intervals be handled, like the case you sighted with feb 29th?

That's up to you. First you have to decide what you want to happen. After you decide that, you write the appropriate logic to make that happen.

For example Feb 29th + 1 Year. Should it roll back a day to Feb 28th, or forward a day to Mar 1st. Mysql's default behavior if you overflow a month is to roll it back to the last day of the month. So Feb 29th + 1 year would be Feb 28th. Jan 31st + 1 Month would be Feb 28th.

If that policy is fine for you, then you don't need to do anything special for the calculation.

The other thing to consider is if these are intended to recur several times, how are you going to handle that? For example, if you just replace the original due date with the new calculated date, you'll no longer be accurate for these end dates. Jan 31st + 1 month would end up making the new date Feb 28th. After that all your subsequent calculations would be based on the 28th.

Again, if that policy is fine for you, there's nothing special that needs done. Otherwise you'll need to come up with a strategy to handle those cased and implement it.

##### Share on other sites

Or you could sidestep the problems and not allow any dates after the 28th of any month

edit : If the recurring value is monthly or higher

Edited by Barand

Hmmmm. Thanks

##### Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.