[RESOLVED] Mail Queue Database Issue

[frost110]

I created my own Mail Queue database as I did not like PEAR's. It is modeled after PEAR's with a few exceptions to go with my site. Here is the situation:

A user creates an entry. Another user who is subscribed to the current user gets notified via EMail about the new entry being created. When this happens data to be sent is added to a MySQL database. It will be sent in 30 seconds after creation. This works great, the mail sends.

The issue is the following:
To send I made the call for sendQueued() inside a main file of my site that gets hit regularly by users. When 3 or 4 users hit this page at the same time, it sends 4 emails because the data they retrieve from the database has not been updated as this happens in milliseconds.

I am looking for a way around sending 4 emails at a time. Deleting the record does not work. I do not want to make more database calls than I need to but if I have to I will. Here is my code for sending email:

// Sends mail in the queue.
function sendQueued() {
	// See if there is mail to send
	$mailQu = $this->sqldb->query("SELECT * FROM mailq WHERE sent_time IS NULL AND time_to_send < ".time()." ORDER BY create_time LIMIT " . $this->maxMailSend . ";");
	$cnt = mysql_num_rows($mailQu);

	if ($cnt > 0) {
		$cnt = count($this->headers);
		$i=1;
		foreach ($this->headers as $key => $val) {
			if ($i == $cnt) {
				$hdrs .= $key . ": " . $val;
				continue;
			}

			$hdrs .= $key . ": " . $val . "\r\n";
			$i++;
		}

		while ($mailRow = mysql_fetch_assoc($mailQu)) {
			if ($mailRow['mailid'] != $previd && $mailRow['sent_time'] == null) {
				if (!mail($mailRow['recipient'], stripslashes($mailRow['subject']), stripslashes($mailRow['body']), $hdrs, "-f" . $this->headers['From'])) {
					$this->sqldb->query("UPDATE mailq SET mail_error = 1 WHERE mailid = '" . $mailRow['mailid']."';");
				}

				$this->sqldb->query("UPDATE mailq SET sent_time = ".time()." WHERE mailid = '".$mailRow['mailid']."';");
				$previd = $mailRow['mailid'];
			}
		}
	}

	return true;
}

Appreciate any help, if more information is needed let me know:

PHP Version 4.3.x
MySQL Version 4.x
RedHat 7.3 OS

I have full access to server, SSH etc. Whatever needs to be done I can do it.

Thanks again.

--FrosT

[samudasu]

Why not run a cron job to execute sendQueued() every 30 seconds or so. It seems it would be less taxing on your system/database since you'd only be querying the database every 30 second instead of every time someone visits the main page where you presently have sendQueued().

[frost110]

I will give that a try. Thanks for the information.

--FrosT