Limit per hour without repeat

[SkyRanger]

I was wondering if there way way to limit the amount of emails per hour and not repeat the same emails twice, with the code I have?

 

$query = "SELECT email FROM users";
$result = mysql_query($query) or DIE (mysql_error());

while ($r = mysql_fetch_array($result)) { $emailAry[] = $r['email']; }

if (count($emailAry)) {
       $list = implode(", ", $emailAry);
       $to = "email@email.com"; 
       $subject = "Monthly Newsletter";
       $body= $postnewsletter;
       $headers .= "Bcc: $list\r\n";
       mail($to, $subject, $body, $headers);
} else {
       echo "no users";
}

[soycharliente]

If it were me, I would probably being trying to log the last time the user was emailed as a field in the database and only pull emails that haven't been emailed in the last 60 minutes.

[SkyRanger]

Yeah, but I have almost 200 email addresses in my database. Is there a way to throttle it so there is say only 50 sent every 2 min?

[soycharliente]

You could combine the previous comment with a LIMIT on the query as well.

 

Maybe run something like this every two minutes:

 

SELECT email FROM users WHERE lastEmailed < (NOW() - INTERVAL 1 HOUR) LIMIT 0,50

[SkyRanger]

oh perfect, never thought of that. Thanks charlieholder

[soycharliente]

You're welcome.

 

Be sure to mark solved if you're finished and post the final code used so future users can benefit as well.

[SkyRanger]

ok was just testing to code, and I screwed something up. This isn't right because it does not update database. How could I do this to update the database once email has been sent?

 

//send Newsletter to subscribers based on type (text or html)
$query = "SELECT email FROM newsletter WHERE nltype='1' and lastEmailed < (NOW() - INTERVAL 1 HOUR) LIMIT 0,50 ";
$postnewsletter = "This is a newsletter test"; //will be pulled from database
$result = mysql_query($query) or DIE (mysql_error());

while ($r = mysql_fetch_array($result)) { $emailAry[] = $r['email']; }

if (count($emailAry)) {
               $list = implode(", ", $emailAry);
               $to = "newsletter@insuranceesafe.com"; 
               $subject = "Insurance eSafe Newsletter";
               $body= $postnewsletter;
               $headers .= "Bcc: $list\r\n";
               mail($to, $subject, $body, $headers);
$query1 = ("update newsletter set lastEmailed = date('Y-m-d H:i:s') where email='$list'") or mysql_error();                
} else {
               echo "no users";
}

[soycharliente]

First, your $list variable is just that, a list. You won't have any matches for "WHERE email=$list" in your database. So you'll either want to move the query into the while loop or put the query in a for loop.

 

Second, just in case the code you posted is the code you're working with, you're not actually running the query to update the lastEmailed. You're only creating a string and storing it to your $query1 variable.

 

Lastly, since you're writing new code, I strongly suggest you start using mysqli_* as mysql_* is deprecated. See the large red warning on every mysql_* manual page? Example.

[SkyRanger]

Ok, thanks charlie, For the first, yeah I will have to move that so It will update on each email submission and update. Second, thanks for catching that., and lastly, just checked out the mysqli_ and mysql_ and noticed, got some reading to do before I continue with this script. Thank you. Will post when completed new code.

[soycharliente]

Cool cool. Happy to help. I'll look out for more code.

 

Yeah, I made the switch from mysql_* to mysqli_* myself fairly recently. It's actually not too bad.

 

Here's a small bit example code that might help you get started with how to make the switch:

 

<?php
function db_connect()
{
$hostname = 'localhost';
$username = 'root';
$password = 'root';
$database = 'my_db_table';

$link = mysqli_connect($hostname, $username, $password, $database)
	or die('Connect Error ('.mysqli_connect_errno().')');

return $link;
}

$link = db_connect();
$query = "SELECT * FROM `table` ORDER BY `title` ASC";
if ( $result = mysqli_query($link, $query, MYSQLI_STORE_RESULT) )
{
echo '<table>';
echo '<tbody>';
while ( $r = mysqli_fetch_array($result, MYSQLI_ASSOC) )
{
	$var = $r['field'];
	echo '<tr>';
	echo '<td>'.$var.'</td>';
	echo '</tr>';
}
echo '</tbody>';
echo '</table>';
}
else
{
mysqli_close($link);
die('<h1>Unable to process query: make_table</h1>');
}
mysqli_close($link);
?>

[SkyRanger]

Awesome thank you. Just finally start to catch on to one way, now they change it and have to start all over again...lol, doesn't look to bad. Again, thank you for your help.