Need Help With Loop And Date Incrementing Logic

[rbemiller]

What I need to do is query a table based on a date. Then, if the query returns a "W" or an "H", I need to add one day to the date, then run the query again, until it doesn't return either a "W" or an "H".

 

My code works fine when the initial query doesn't return a W or H, but when it does, it times out, seemingly in and endless loop. So clearly, something I'm doing with the loop isn't correct.

 

Can someone help with my logic? Thanks very much from this first time visitor to PHP Freaks!

 

Here's the code:

<?
$testdate = date("Y-m-d", strtotime("2012/09/30"));

include("includes/databasedateretrieval.inc");
$dateverify = mysql_result($date_result,0,"1_day");
$dfresult = $dateverify;

// Check for dating errors
while ($dfresult == "W" || $dfresult == "H"){
 //convert the date to time
 $todaydate = strtotime($testdate);
 //add 1 day to the date
 $todaydate = $todaydate + (24*60*60);
 //convert the date back to a format compatible with the table data
 $testdate = date("Y-m-d", $todaydate);
 //here I'm querying the table again to see if the new date returns a W or H
 $dateverify = mysql_result($date_result,0,"1_day");
 $dfresult = $dateverify;
}
?>
<? echo $testdate; ?>

Edited October 2, 2012 by rbemiller

[KevinM1]

For every loop, you're running the same query and obtaining the same row. You either need to create a new result by modifying $date_result, or change the row number you're accessing in the mysql_result() function.

[Maq]

I don't understand why you're not just doing an UPDATE query and using something like ADDDATE() - http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_adddate

[rbemiller]

For every loop, you're running the same query and obtaining the same row. You either need to create a new result by modifying $date_result, or change the row number you're accessing in the mysql_result() function.

 

What I failed to mention is that the included mysql includes a query with a WHERE clause that references the $testdate variable, which, I think, does get updated each time it loops. I do want to obtain the same row each time i run the query, but check it against an incremented $testdate.

[rbemiller]

I don't understand why you're not just doing an UPDATE query and using something like ADDDATE() - http://dev.mysql.com...unction_adddate

 

Well, I'm not trying to update the database at all. Just query it, and make sure today's date doesn't fall on a designated W or H day...and if it does fall on one of those days, increment 1 more day, query again, and repeat until it doesn't.

 

Make sense?

[Mahngiel]

Well, I'm not trying to update the database at all. Just query it, and make sure today's date doesn't fall on a designated W or H day...and if it does fall on one of those days, increment 1 more day, query again, and repeat until it doesn't.

 

Make sense?

 

No. If you don't update the database, you'll query the same row and (as far as mysql gives a hoot) do nothing over and over and over and over again. It will never meet your criteria of "until it doesn't".

[rbemiller]

No. If you don't update the database, you'll query the same row and (as far as mysql gives a hoot) do nothing over and over and over and over again. It will never meet your criteria of "until it doesn't".

 

But my query contains a variable ($testdate), which is what I'm trying to increment during each loop. Wouldn't that mean the query is different every time? The query is: WHERE bdod = '$testdate'" So, if test date is different, it's going to look in a different row, b/c bdod contains a different day of the year in each row.

[Mahngiel]

What is your expected end result with all these Ws and Hs? Any reason you're not grabbing the entire table at once and iterating through each row, building arrays of Ws and Hs?

 

It's difficult to give advice when you don't give all the information.

[rbemiller]

What is your expected end result with all these Ws and Hs? Any reason you're not grabbing the entire table at once and iterating through each row, building arrays of Ws and Hs?

 

It's difficult to give advice when you don't give all the information.

 

I'll try to be more clear hear. The end result is to prepopulate a form field with today's date. However if "today" is a holiday (H) or a weekend (W), I need to make the date the next available non holiday or weekend...it must be a week day. We already have this database table set up for another function with all of the days of the year (in the bdod column) and a W or H for those that are a weekend or holiday (in the 1_day column). I simply want to do the following. When the page loads, create $testdate as today's date. Then, check the database to make sure $testdate doesn't fall on one of the weekend or holiday days. If it does, increment by one day, then check again if the new $testdate falls on a weekend or holiday. Once it no longer falls on a weekend or holiday, populate the form field with that value, as that will be the next available week day. I hope this clarifies a bit more. I'm not sure this is the best way, but I'm trying to leverage an existing database table which I cannot change.

[Christian F.]

I don't see the database being queried at all in the loop. No updates nor any selects, just some variable manipulation. The only thing that's related to the database there, is that you're fetching a value from a previous query over and over again.

 

In fact, I don't see a single query at all in the code you've posted. Only the fetching of the same result which is stored in memory.

 

You really should sit down and translate what the code does, line for line, into plain English. Make sure you check with the PHP manual, and don't rely upon your previous assumptions. Once you've done that the problem should be very apparent, as well as what you need to do to solve it.

[Barand]

Seems like you need to

 

Query table for date >= today, ordered by date
read first row
While H or W
   read next row's date

[rbemiller]

Thanks to everyone for your help.

 

I was able to get it working properly. All i had to do was reference the mysql include again, inside the while loop. I was assuming that including it once would be sufficient.

 

This is clearly a greet and highly active forum, and as I do more with php, I'm sure I'll be contributing here more often and I look forward to it.

 

Here's the final working code:

//get and format today's date
$testdate = date('Y-m-d');

include("includes/databasedateretrieval.inc");
$dateverify = mysql_result($date_result,0,"1_day");
$dfresult = $dateverify;
// Check for dating errors increment date by 1 day if W or H
while ($dfresult == "W" || $dfresult == "H"){
   $todaydate = strtotime($testdate);
   $todaydate = $todaydate + (24*60*60);
   $testdate = date("Y-m-d", $todaydate);
   include("includes/databasedateretrieval.inc");
   $dateverify = mysql_result($date_result,0,"1_day");
   $dfresult = $dateverify;
   }
?>
<input type="text" name="businessdateofdeposit" value="<? echo date('m/d/Y', strtotime($testdate)); ?>" >

[Christian F.]

You really should have followed my previous advice:

You really should sit down and translate what the code does, line for line, into plain English. Make sure you check with the PHP manual, and don't rely upon your previous assumptions. Once you've done that the problem should be very apparent, as well as what you need to do to solve it.

 

While what you've done above may seem to do the right thing, it is at the very least extremely wasteful. Not to mention the high risk of bugs in that code, which is the direct result of you not understanding what your own code does.

Trust me when I say that following my recommendation is the best course of action you can take, for your own sake.

[mikosiko]

^+1 .... you should check Barand first line of pseudo code too, that and the fact that you can also use NOT IN and LIMIT in your query most likely should help you to solve it easily

[salathe]

Why not just change the SQL query to fetch only the information that you are wanting, rather than what you're not wanting? Of course, we can't help with your specific query until you decide to show it to us.

[Barand]

In fact it may be that all you require is

 

SELECT MIN(date) as dateRequired

FROM mytable

WHERE date >= CURDATE()

AND flagfield NOT IN ('H', 'W')