verify single row works, multiple rows does not

[Walker33]

our db has multiple rows for the same user, as the same customers come and go regularly.  I'm attempting to verify that they have not been active since Jan 1 2008.  What I have works fine if the first entry in db has end_date after Jan 1 2008.  But if first entry is prior to that and second is post, seems to only want to react to first entry.  What I have:

 

// check table to see if reporter has been an active user in last 18 months

$query3 = mysql_query("SELECT * FROM customer_details WHERE FirstName = '$InitFName' AND LastName = '$InitLName' AND Email = '$InitEmail' and status like '%revoked%'");

$num3 = mysql_num_rows($query3);

$result = mysql_fetch_assoc ($query3);

 

//converting date so that year comes first and can use > or < for comparison.

$my_date = $result["End_Date"];

$my_time = strtotime($my_date);

$datenew = date('Y-m-d',$my_time);

 

if($num3>0 && $selected_radio=='reporter' && $datenew > '2008-01-01')

 

Thanks for any help.

[Ken2k7]

Please read Forum DOs #4 - http://www.phpfreaks.com/page/rules-and-terms-of-service#toc_forum_dos

 

Did you know you can compare dates in the SQL?

[Walker33]

not sure exactly what rule I was breaking, unless you believe it was "Do search for your own answers."  I have been doing that, but I must not be looking in the obvious spots, I guess.  My apologies.  Very new to this, and I guess I'm wasting your time.  No, didn't know about comparing dates.

[Walker33]

Ah, I see, I wasn't "Do user proper formatting for posts php or code tags around necessary code."  Sorry 'bout that.  New here, but I'll make sure to do it in the future.  So let's see...

// check table to see if reporter has been an active user in last 18 months
$query3 = mysql_query("SELECT * FROM customer_details WHERE FirstName = '$InitFName' AND LastName = '$InitLName' AND Email = '$InitEmail' and status like '%revoked%'");
$num3 = mysql_num_rows($query3);
$result = mysql_fetch_assoc ($query3);

//converting date so that year comes first and can use > or < for comparison.
$my_date = $result["End_Date"];
$my_time = strtotime($my_date);
$datenew = date('Y-m-d',$my_time);

if($num3>0 && $selected_radio=='reporter' && $datenew > '2008-01-01')

Thanks again for any help.

 

[Ken2k7]

Please read Forum DOs #4 - http://www.phpfreaks.com/page/rules-and-terms-of-service#toc_forum_dos

Please don't think I'm rude. I just want you to get help easier and code tags help. Read my post carefully. I said read "Forum DOs #4". Quite explicit really.

 

In SQL, you should be able to compare a date with another date. May I ask what type field "End_Date" is? Is it a TIMESTAMP?

[Walker33]

I don't think you're being rude.  I should have read your post more closely (got the code stuff in...not sure how to get it to color in like other posters).  End_Date is VARCHAR.

[Ken2k7]

I know End_Date is a column, but what type of data is being inserted into that column or should I say how is the data in that column being treated? You can check via phpMyAdmin. Did you create it? If so, what query did you run or did you do it via phpMyAdmin? I would like it to be TIMESTAMP, but I'm guessing you put varchar?

[Walker33]

Just updated my last reply....sorry, long few days and I'm not reading your replies closely enough.  Yes, VARCHAR.

[Ken2k7]

That's no good. There's a mistake. If you echo $my_date, what does it outputs? Just curious.

[Walker33]

if I echo $my_date , I get 02/05/07  (first end_date for my test case).  But if I echo $datenew I get 2007-02-05 , which is what I need for the > verification.

[Ken2k7]

Right. Okay, so what's after the IF statement? 2007-02-05 *is* before 2008-01-01. So it should hit the else statement right?

[Walker33]

that's the whole problem I'm having.  I want it to loop back to the next db entry for that test person (I have 2nd entry set to 2008-01-12), and fail, going to error message instead of else statement.  That's the issue.  It's only reading the first db entry for submitted values, when there can be several.

[Walker33]

but yes, it is hitting the else statement.

[Ken2k7]

I want it to loop back to the next db entry for that test person (I have 2nd entry set to 2008-01-12), and fail, going to error message instead of else statement.

*Ken2k7 is confused.

[Walker33]

I must not be explaining it clearly, so forgive the longer explanation of the purpose here.  The form is for a free trial of our product.  John Doe has been on our system five different times over the last ten years, using our product when he needs it, cancelling the account when he no longer needs it.  Therefore, John Doe is listed five times in the database with differing start and end dates on each entry.  John Doe decides he needs the product again but doesn't want to pay for it, so he tries to get a free trial.  We don't want him to have a free trial if he's been on the system in the last 18 months.  If it's been longer than that, we do want him to have a free trial because the product has improved since his last use.  So we need to automate the free trial form to check for each entry of his name, email and each separate end date for each entry of John Doe.  If any end date is within the last 18 months, we want him to get the error message "Sorry, no dice".  If none of the end dates are within that time frame, we want him to be delivered his free trial.  So the code needs to check each entry for him, not just the first one in the DB.  My code is currently only checking the very first entry in the DB for John Doe and responding appropriately, but it is not looping back and checking the 2nd, 3rd, 4th and 5th.  Again, I apologize for the long explanation, but maybe that helps clarify what I'm trying to achieve.

[Ken2k7]

This is where TIMESTAMP would have helped out because MySQL would return a better result than having you test each date string as it comes out of the array.

 

// check table to see if reporter has been an active user in last 18 month
$query3 = mysql_query("SELECT * FROM customer_details WHERE FirstName = '$InitFName' AND LastName = '$InitLName' AND Email = '$InitEmail' and status like '%revoked%'");

// the date - 18 months from now
$expire = date('Y-m-d', strtotime('-18 MONTHS'));

// this variable determines if free trial is allowed
$free_trial = false;

while (!$free_trial && $result = mysql_fetch_assoc ($query3)) {
//converting date so that year comes first and can use > or < for comparison.
$my_date = $result["End_Date"];
$my_time = strtotime($my_date);
$datenew = date('Y-m-d',$my_time);

if($num3>0 && $selected_radio=='reporter' && $datenew > $expire) $free_trial = true;
}
echo $free_trial;

 

Something like that?

[Walker33]

VOILA!  Works like a champ under initial testing.  Now I have to spend some time trying to understand why it works.  I thought I tried WHILE in almost every conceivable way, but I had it wrong, obviously.  Beautiful!  Thanks a ton, and thanks for bearing with some of my poorly phrased and formatted questioning.  Greatly appreciated!!!

[Walker33]

Whoops, spoke a little too soon.  Doesn't seem to reading the 18 months quite right.  It's denying free trial for any date.  2005, 2006, etc.

[Ken2k7]

Oops, change the > to < in the if statement. I wasn't thinking about that last part. I assumed you had it right.