Checking Whether Expiration_Date has Passed

[WendyLady]

Hello --

First let me say that I've gotten a ton of help on here, & I really appreciate it! Especially how fast everyone is to respond. In response, I've been trying to help others as much as possible with my meager PHP skills, and plan to make a donation this week to help with server costs.

Right now I'm working on a housekeeping function, to go at the beginning of my index pages, which would look through the user database, find all occurrences where the user's account_expiration_date has passed, and if it has, would change a boolean flag in another field to the value for account_is_expired. This prevents people from logging in whose accounts have expired, and flags them for the administrator to deal with.

First of all, I'm not sure if I'm approaching this correctly, to put this in as a "housekeeping" function at the top of the user & admin index pages.

Second, the code I've been using is simply changing ALL accounts to account_is_expired instead of only the expired ones. My first attempt was to select ALL users, then check all the account_expire fields, then try to return the expired ones (changed all users to expired there, too). It has been changing all morning, but my most current attempt is:

[code]

$today = time();

$query = "SELECT account_expire FROM user WHERE account_expire < $today";
$result = mysql_query($query);

while (list($account_expire) = mysql_fetch_row($result))
    {

    $query = "UPDATE user SET confirmed = '2' WHERE ($account_expire < $today)";
        $result = mysql_query($query);
    }

[/code]

All of my stored times are php timestamps (which has been working best for me so far). Thanks so much for any help or advice!

Wendy

[joquius]

could you do this however?

$query = "UPDATE `user` SET `confirmed` = '2' WHERE `account_expire` < '$today'";
$result = mysql_query($query) or die (mysql_error ());

I didn't understand the reason for the first query

[Chips]

Not really used dates much myself, so not exaclty sure how they are evaluated.

However:
[code]
$today = time();

$query = "SELECT account_expire FROM user WHERE account_expire < $today";
$result = mysql_query($query);

while (list($account_expire) = mysql_fetch_row($result))
    {

    $query = "UPDATE user SET confirmed = '2' WHERE ($account_expire < $today)";
        $result = mysql_query($query);
    }
[/code]


I don't understand why you don't just role it all into one query?
[code]
$sql = mysql_query("Update tablename SET confirmed = '2' where account_expire < '$today';");
[/code]

Course, i may be totally wrong with that just there, but looks okay to me!

[WendyLady]

Um, [sheepishly] that worked great!

For some reason I have recently had a mental lapse that causes me to think all the time I need a double-query when I [b]don't[/b]. The other day I did this & after I took a break & came back, I thought, "what am I doing?". I'm awfully tired of this project, but it has been invaluable for learning.

Thank you!
Wendy


[!--quoteo(post=383838:date=Jun 14 2006, 08:57 AM:name=joquius)--][div class=\'quotetop\']QUOTE(joquius @ Jun 14 2006, 08:57 AM) [snapback]383838[/snapback][/div][div class=\'quotemain\'][!--quotec--]
could you do this however?

$query = "UPDATE `user` SET `confirmed` = '2' WHERE `account_expire` < '$today'";
$result = mysql_query($query) or die (mysql_error ());

I didn't understand the reason for the first query
[/quote]

[Monkeymatt]

Your problem with the second query was that when the where was correct once, it did not only change that row, but it changed every row if the where was right at least once.

Monkeymatt