Mail Script sending multiple times when Table has a lot of data

dsumpter · #1 (**permalink**) 07-08-03, 06:38 PM

I have been trying to use the below script to send emails to clients in my MySQL database announcing special events.

When I run the script, it ends up sending each email multiple times... almost as if the script is timing out, then re-running. The reason I think that is what is happening, is because the last few entries in the table only get the email once.

Does anyone have any ideas how I can remedy this...

Also, the target table as you will see referenced has about 10k entries in it, and on the query that I just ran where I had this experience only about 1200 of the entries met the criteria on in the sql query.

Thanks in advance

---------------------------------

<?

/* MySQL details */

$dbHost = "localhost";

$dbUser = "username";

$dbPass = "password";

$dbName = "name";

$table = "target";

$table2 = "client";

$table3 = "emailSent";

$sendAfterDate = $_GET['sendAfterDate'];

// set birthday date

$year = date("Y");
$month = date("m");
$day = date("d");

$sendDate = date("md", mktime(0,0,0,$month,$day+16, $year) );
$trackDate = date("mdY");

// get the time and date

$date_month = date(m);
$date_year = date(Y);
$date_day = date(d);
$time_hour = date(H);
$time_min = date(i);

// Date

$date = "$date_day/$date_month/$date_year - $time_hour:$time_min";

/* Attempt connection to MySQL server */

$link = @mysql_connect($dbHost, $dbUser, $dbPass) or die("Couldn't Connect to MySQL");

/* Attempt to select our database */

$db = @mysql_select_db($dbName, $link) or die("Couldn't select database.");

// Query

$sql = "SELECT * FROM $table, $table2 WHERE $table.clientID = $table2.clientID AND $table.clientID = \"$clientID\" AND targetEnteredDate > \"$sendAfterDate\"";

$result = mysql_query($sql,$link) or die("Couldn't Execute query.");

while ($row = @mysql_fetch_array($result)) {
$targetID = $row['targetID'];
$targetEmail = $row['targetEmail'];
$targetFirstName = $row['targetFirstName'];
$clientName = $row['clientName'];
$clientEmail = $row['clientEmail'];
$clientSpecialInterest1 = $row['clientSpecialInterest1'];
$clientSpecialInterest1Logo = $row['clientSpecialInterest1Logo'];
$clientSpecialInterest1Border = $row['clientSpecialInterest1Border'];
$clientSpecialInterest1Text = $row['clientSpecialInterest1Text'];

// Subject
$subject = "$clientSpecialInterest1";

// Headers
$headers = "From: $clientEmail\n";
$headers .= "Reply-To: $clientEmail\n";
$headers .= "Organization: $clientName\n";
$headers .= "Content-Type: text/html; carset=iso-8859-1\n";

// Design
$design = "
<html>
<head>
<title>$clientSpecialInterest1</title>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\">
</head>

<body>
Don't miss out on our upcoming event! Please click
on the following link for details.  <a href=\"www.adomain.com\">Details</a> 
If link is not active, please paste into your search window and hit return. 
$clientName
</body>
</html>
";

// Send the Email
mail($targetEmail, $subject, $design, $headers);

// update emailSent
$siSql = "INSERT INTO $table3(emailSentID, targetID, emailSentDatestamp, emailSentType) VALUES ('', '$targetID', '$trackDate', 's')";

mysql_query($siSql) or die("Couldn't update emailSent with FTE data");

}

print ("Special Interest 1 Sent!")

?>

Chris Boulton · #2 (**permalink**) 07-09-03, 03:44 AM

If you are sending mails to a lot of users i would not use php's built in mail functions. This will cause a huge backlog on your server.

You either pipe them directly into the mail program

OR

You process a certain amount of records on each page, then give it a break, then process more on the next page.

dsumpter · #3 (**permalink**) 07-09-03, 12:41 PM

Do you know how I would process a fixed number of records at a time with the above type of query?

Thanks!

ChristGuy · #4 (**permalink**) 07-09-03, 01:15 PM

Greetingz...

To limit the number of records shown...

PHP Code:


			
$NumPerPage = 10;

$Start = $PageNum * $NumPerPage;



$sql  = "SELECT * FROM $table, $table2";

$sql .= " WHERE $table.clientID = $table2.clientID";

$sql .= " AND $table.clientID = \"$clientID\"";

$sql .= " AND targetEnteredDate > \"$sendAfterDate\"";

$sql .= " LIMIT " . $Start . ", " . $NumPerPage . ";";

Hope that helps..

dsumpter · #5 (**permalink**) 07-09-03, 01:20 PM

Awesome man... I will give it a try and let you know...

Thanks!!!

dsumpter · #6 (**permalink**) 07-14-03, 03:41 PM

Clifford,
I tried the above change, but now it is only sending the 10 that are in the first group... How do I make it loop so it is only doing 10 at a time? Please let me know... Thanks for your help! My current code is below...

----------------

<?

/* MySQL details */

$dbHost = "localhost";

$dbUser = "adventure";

$dbPass = "consulting";

$dbName = "aci";

$table = "target";

$table2 = "client";

$table3 = "emailSent";

$sendAfterDate = $_GET['sendAfterDate'];

// set birthday date

$year = date("Y");
$month = date("m");
$day = date("d");

$sendDate = date("md", mktime(0,0,0,$month,$day+16, $year) );
$trackDate = date("mdY");

// get the time and date

$date_month = date(m);
$date_year = date(Y);
$date_day = date(d);
$time_hour = date(H);
$time_min = date(i);

// Date

$date = "$date_day/$date_month/$date_year - $time_hour:$time_min";

/* Attempt connection to MySQL server */

$link = @mysql_connect($dbHost, $dbUser, $dbPass) or die("Couldn't Connect to MySQL");

/* Attempt to select our database */

$db = @mysql_select_db($dbName, $link) or die("Couldn't select database.");

// Query

$NumPerPage = 10;

$Start = $PageNum * $NumPerPage;

$sql = "SELECT * FROM $table, $table2";

$sql .= " WHERE $table.clientID = $table2.clientID";

$sql .= " AND $table.clientID = 56";

$sql .= " AND targetID > 9666";

$sql .= " LIMIT " . $Start . ", " . $NumPerPage . ";";

$result = mysql_query($sql,$link) or die("Couldn't Execute query.");

while ($row = @mysql_fetch_array($result)) {
$targetID = $row['targetID'];
$targetEmail = $row['targetEmail'];
$targetFirstName = $row['targetFirstName'];
$clientName = $row['clientName'];
$clientEmail = $row['clientEmail'];
$clientSpecialInterest1 = $row['clientSpecialInterest1'];
$clientSpecialInterest1Logo = $row['clientSpecialInterest1Logo'];
$clientSpecialInterest1Border = $row['clientSpecialInterest1Border'];
$clientSpecialInterest1Text = $row['clientSpecialInterest1Text'];

// Subject
$subject = "$clientSpecialInterest1";

// Headers
$headers = "From: $clientEmail\n";
$headers .= "Reply-To: $clientEmail\n";
$headers .= "Organization: $clientName\n";
$headers .= "Content-Type: text/html; carset=iso-8859-1\n";

// Design
$design = "
<html>
<head>
<title>$clientSpecialInterest1</title>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\">
</head>

<body>
Don't miss out on our upcoming event! Please click
on the following link for details.  <a href=\"http://www.vtoonstaff.com/aci/specialInterest1.php?targetID=$targetID\">Details</a> 
If link is not active, please paste into your search window and hit return. 
$clientName
</body>
</html>
";

// Send the Email
mail($targetEmail, $subject, $design, $headers);

// update emailSent
$siSql = "INSERT INTO $table3(emailSentID, targetID, emailSentDatestamp, emailSentType) VALUES ('', '$targetID', '$trackDate', 's')";

mysql_query($siSql) or die("Couldn't update emailSent with FTE data");

}

print ("Special Interest 1 Sent!")

?>

ridwank · #7 (**permalink**) 07-14-03, 11:21 PM

For executing each page,
and I think you have to move to another next page, by set variable $PageNum to certain number.
For example:

<?
$NumPerPage = 10;

$Start = $PageNum * $NumPerPage;

$prev=$PageNum - 1;
$next=$PageNum + 1;
echo "<a href='$PHP_SELF?PageNum=$prev'>PREV</a>";
echo " | Page ".($PageNum+1)." : Record from ".$Start." to ".($Start+$NumPerPage)." |";
echo "<a href='$PHP_SELF?PageNum=$next'>NEXT</a>";
?>


Just put those new line above , before your query "$sql = "

dsumpter · #8 (**permalink**) 07-15-03, 03:39 PM

... thanks for your help! That works, but I am trying to get it to automatically cycle through the entire database... Do you know of a way to get it to do that? If not, do you at least now how I can have the NEXT hyperlink not be active if there are no more pages to cycle through?

Thanks,
Dan

playtothebeat · #9 (**permalink**) 07-15-03, 04:34 PM

set http_redirect to the next page for like 10 seconds.. so it sits on each page for 10 seconds.
that way, the first 10 secs will be for the first 10 emails, then it will go to the next and so on.

once it gets to the last, have it stop

ChristGuy · #10 (**permalink**) 07-16-03, 01:11 AM

That will work...

PHP Code:


			
<?php

  $url = ""; // set to the generated url

  $time = 10; // in seconds

  print "<META http-equiv=\"refresh\" content=\"$time; URL=$url\">";

?>