[SOLVED] Can someone tell me how to do this?

[affordit]

I am using the code below to send mail to subscribers b4 there subsciptions run out but the problem is the code runs multiple times a day because it is called from a page on my site so what I need to know is how to insert a value of 1 into the remind field if the mail has already been sent so that my subscribers do not get 400 emails a day?

 

<?php
$ds = date("Y-m-d",strtotime("+8 days")); 
include("sharons_dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database"); 

$query = "select email from test WHERE end_date <= '$ds' AND remind = '0'";
$result=mysql_query($query) or die (mysql_error());
if (($result)||(mysql_errno == 0)) 
{ 

  if (mysql_num_rows($result)>0) 
  { 

          $i = 0; 
          while ($i < mysql_num_fields($result)) 
          { 

       $i++; 
}
    //display the data 
    while ($rows = mysql_fetch_array($result,MYSQL_ASSOC)) 
    { 
      echo "<br>"; 
      foreach ($rows as $data) 
      { 
$to = "$data"; 
$subject = "Your Subscription"; 
$message="Your subscription will expire on $ds.";
$headers = "From: [email protected]"; 
$sent = mail($to, $subject, $message, $headers) ; 
if($sent) 
{print "Your mail was sent successfully"; }
else 
{print "We encountered an error sending your mail"; }
      } 
    } 
  }else{ 
    echo "<tr><td colspan='" . ($i+1) . "'>No Results found!</td></tr>"; 
  } 
  echo "</table>"; 
}else{ 
  echo "Error in running query :". mysql_error(); 
} 
?>

 

Use

 tags - roopurt18[/b]

[roopurt18]

Well, you have the code that prints the success message so you should know where to update the database at.  So what are you having trouble with?  Do you not know enough about MySQL to create the query?

[affordit]

I inserted the line below but it won't work and no I don't know enough yet but im working on it.

 

$to = "$data";

$subject = "Your Subscription";

$message="Your subscription will expire on $ds.";

$headers = "From: [email protected]";

$sent = mail($to, $subject, $message, $headers) ;

if($sent)

 

 

{$query = "INSERT INTO `test` (`remind_date`) VALUES ('1')";

}

 

 

else

{print "We encountered an error sending your mail"; }

      }

[roopurt18]

You set $query but didn't call mysql_query().

[affordit]

I just did that but it created a new record with all the fields empty except the end_date

[roopurt18]

Well, here is your query:

INSERT INTO `test` (`remind_date`) VALUES ('1')

 

You are inserting a new row to the table and only setting one column, which wasn't the one you mentioned as updating.  What is the column type for the `remind_date` column?

 

Second, you probably don't want to use INSERT INTO.  You probably want to perform an UPDATE query with a WHERE clause that causes it to update the current record you just e-mailed for.  In order to do this, you need a way of identifying the current working row.  Do your rows have assigned ids or anything of the sort?

[affordit]

sorry about the wrong field name! My records have auto incremented ids yes but im not sure how to do an update query

[affordit]

OK I tried this line

 

{$query = "UPDATE `test` WHERE `email`=$data (`remind_date`) VALUES ('1')";

$result=mysql_query($query) or die (mysql_error());

}

 

And got this error

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `email`=someone@somewhere (`remind_date`) VALUE

[roopurt18]

I made some quick changes to your code.  This might do what you want.

 

<?php
$ds = date("Y-m-d",strtotime("+8 days")); 
include("sharons_dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database"); 

//
// CHANGE QUERY TO GRAB THE ID
//
$query = "select id, email from test WHERE end_date <= '$ds' AND remind = '0'";
$result=mysql_query($query) or die (mysql_error());
if (($result)||(mysql_errno == 0)) 
{ 

  if (mysql_num_rows($result)>0) 
  { 

          $i = 0; 
          while ($i < mysql_num_fields($result)) 
          { 

       $i++; 
}
    //display the data 
    while ($rows = mysql_fetch_array($result,MYSQL_ASSOC)) 
    { 
      echo "<br>"; 
$to = $rows['email']; 
$subject = "Your Subscription"; 
$message="Your subscription will expire on $ds.";
$headers = "From: [email protected]"; 
$sent = mail($to, $subject, $message, $headers) ; 
if($sent) 
{print "Your mail was sent successfully";
//
// UPDATE THE ROW
//
$update = "UPDATE `test` SET `remind`=1 WHERE `id`=" . $rows['id'];
mysql_query($update);
}
else 
{print "We encountered an error sending your mail"; }
    } 
  }else{ 
    echo "<tr><td colspan='" . ($i+1) . "'>No Results found!</td></tr>"; 
  } 
  echo "</table>"; 
}else{ 
  echo "Error in running query :". mysql_error(); 
} 
?>

[affordit]

Thank you for your help but it did not update or add a new record

[affordit]

Thanks roopurt18 Im just a little slow but I caught on DUH Thanks Again