CODEWITHSUNDEEP
phpmysql
sammry
sammry

Reputation: 412

Updating a field after sending a mail

I have a reminder mail sent to those who do not log on to my site after 30 days. Earlier I got an answer on this forum to create a seperate field and then update it as mentioned here: Need help on unix time stamp.

I have created a new field lastprelogin, now how do I update the respective field when I send the mail for inactive users.

<?php
include("include/data.php");
$query = "SELECT * FROM myusers WHERE DATE_ADD(FROM_UNIXTIME(lastprelogin), INTERVAL 15 DAY) < CURDATE()";
$result = mysql_query($query);
$num = mysql_numrows($result);
$i = 0;
while ($i < $num)
{
    //send mail code
    $sendmail = mail($to,$subject,$msg,$headers);
    $i++;
    if($sendmail) {
        $query1 = "UPDATE myusers SET lastprelogin='".time()."'";
        $result2 = mysql_query($query1);
    }
}
?>

How can I update the respective user's lastprelogin field after sending the mail?

I am lost here beccause am unable to understand the logic in this part.

Upvotes: 0

Views: 189

Answers (3)

John Conde
John Conde

Reputation: 219804

You need to loop through your results by using mysql_fetch_assoc or similar function.

Your update query needs to include the ID of the record you wish to update.

You should not use the mysql_* functions anymore as they are becoming deprecated soon. Use mysqli instead

<?php
    include("include/data.php");
    $query = "SELECT * FROM myusers WHERE DATE_ADD(FROM_UNIXTIME(lastprelogin), INTERVAL 15 DAY) < CURDATE()";
    $result = mysql_query($query);
    
    while ($user = mysql_fetch_assoc($result))
    {
        //send mail code
        $sendmail = mail($user['email_address'],$subject,$msg,$headers);
        $i++;
        if($sendmail){
            $query1 = "update myusers set lastprelogin='".time()."' WHERE id = " . $user['id'];
            $result2 = mysql_query($query1);
        }
    }
?>

Upvotes: 4

shadyyx
shadyyx

Reputation: 16055

The logic of Your script is simple:

  • retrieve all the users that didn't log in for last 15 days
  • send an email to each user
  • and if that succeeds also update the field lastprelogin for that user

You have some important errors within Your script and this should be like this:

include("include/data.php");
$query = "SELECT * FROM myusers WHERE DATE_ADD(FROM_UNIXTIME(lastprelogin), INTERVAL 15 DAY) < CURDATE()";
$result = mysql_query($query);
while($user = mysql_fetch_assoc($result)) {
    // assuming that myusers table has these columns: user_id, user_name, user_email, lastprelogin
    //send mail code
    if(mail($user['user_email'],'Please log in','Please login to my site',$headers)) {
        $query1 = "update myusers set lastprelogin='".time()."' where user_id = {$user['usri_id']}";
        $result2 = mysql_query($query1);
    }
}
// end.

As a $headers variable You can set a From header, etc. Look for PHP mail function here: http://php.net/mail

Also the right query for updating should be this one:

"update myusers set lastprelogin='".time()."' where user_id = {$user['user_id']}"

anyway You will update the lastprelogin of all users everytime...

Upvotes: 1

bartlaarhoven
bartlaarhoven

Reputation: 845

You will need to get some id from your myuser table and run the update query with where id = $id.

Upvotes: 1

Related Questions