Re: [PHP-DB] MySQL circular buffer

 > (i.e. stack_id > 500 && stack_id < 601 vs where stack_id = 500 limit 100)
stack_id between 501 and 600 (stack_id > 500 && stack_id < 601) is much 
better


> What I would like to know is if anybody has experience
> implementing this sort of data structure in MySQL (linked list?) or
> any advice.
>   
tables:
process_table:
    IDProcess PK

mail_table
    IDMail PK
    IDPrrocess FK references process_table.IDProcess ON DELETE SET NULL
    Mail TEXT
    From VCH(255)
    TO VCH(255)
    DateModified DATE ON UPDATE CURRENT_TIMESTAMP
    Mailed TINYINT DEFAULT 0;
--
code:
define('MaxProccessTimeMinutes', 30);
define('BatchCount', 100);

INSERT INTO process_table VALUES ();
$lnIdProcess = GetLastIDProcess();

label send_mail:
$ldDateExpired = time() - MaxProccessTimeMinutes * 60;
UPDATE mail_table
    SET IDProcess = $lnIdProcess
    WHERE
       Mailed = 0 AND
       (
            IDProcess IS NULL OR
           (
                IDProcess IS NOT NULL AND
                DateModified <= $ldDateExpired
           );
       )
    LIMIT BatchCount;
while ($result = SELECT IDMail, MailText, From, To FROM mail_table WHERE 
IDProcess = $lnIDProcess)
{
    if (send_mail())
    {
        UPDATE mail_table SET Mailed = 1 WHERE IDMail = $result['IDMail'];
    }
}
if there are other mails goto send_mail;
else DELETE FROM process_table WHERE IDProcess = $lnIDProcess;

this way more than one process can send mails, also if one process exits 
prematurely the other can send his emails later.
Time to time run cron:
    DELETE FROM mail_table WHERE Mailed = 1;
    rebuild indexes on mail_table;