Member Avatar for theflashbr

The system basically work in this series: a new registration is made in the database, the system sends me an e-mail notifcando a new record.

  • 5 Contributors
  • 4 Replies
  • 508 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by dean8710

Dani AI

Generated

Brief practical pattern that covers the options already suggested by , and , and avoids common pitfalls.

Write the new record and a "mail queue" row in the same transaction, then let a separate background worker (cron or a daemon) pull pending queue rows and actually send the message. This keeps the user request fast, avoids sending before the DB commit, and gives retries/logging when mail fails. Avoid trying to call external scripts directly from a MySQL trigger — that is fragile and usually unsupported.

Example queue table:

CREATE TABLE email_queue (
  id INT AUTO_INCREMENT PRIMARY KEY,
  target_email VARCHAR(255) NOT NULL,
  template VARCHAR(50) NOT NULL,
  payload TEXT,
  status ENUM('pending','processing','sent','failed') NOT NULL DEFAULT 'pending',
  attempts INT NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_attempt DATETIME NULL
);

Insert + queue (transactional, simplified PDO example):

$db->beginTransaction();
$ins = $db->prepare("INSERT INTO users (name,email) VALUES (?,?)");
$ins->execute([$name,$email]);
$uid = $db->lastInsertId();
$q = $db->prepare("INSERT INTO email_queue (target_email,template,payload) VALUES (?,?,?)");
$q->execute([$email,'new-registration',json_encode(['user_id'=>$uid])]);
$db->commit();

Worker loop (outline):

# atomically claim one pending row, then send
UPDATE email_queue SET status='processing' WHERE id =
 (SELECT id FROM email_queue WHERE status='pending' ORDER BY id LIMIT 1);
SELECT * FROM email_queue WHERE status='processing' LIMIT 1;
# send via a mail library (PHPMailer or similar), then UPDATE status to 'sent' or 'failed' and increment attempts

Practical notes: use a well-maintained mail library (as suggested) and SMTP/TLS for reliability; sanitize inputs and never build headers from raw user input; record attempts and backoff retries; log failures and set an alert threshold; use prepared statements to prevent SQL injection. If you cannot run a daemon, a cron that runs every minute to process the queue is a perfectly acceptable alternative.

Recommended Answers

All 4 Replies

Member Avatar for kindo

Do you have any code that ypu've written. So the community can know what you need.
Php code and mysql schema.

Member Avatar for James_43

Look up PHPMailer, in my experience it really is the best option. You can of course use the php mail() function, however, it does depend on a local mail server, wheras PHPMailer can connect through SMTP. https://github.com/PHPMailer/PHPMailer

Once configured correctly, you can easily make calls to PHPMailer to construct a message. Simply place that code once your database function returns true.

Member Avatar for AleMonteiro

If you doubt is who to know when to send an email, i see two options for you:

  1. Use cron jobs that will run at specific intervals, identify the new records and send the e-mail. This way you can do it easily with PHP but you'll need to have some flag to know if that record was already mailed or not.

  2. Use a trigger on your SQL Server that will run and external command (probably call your PHP mailing page) each time a record is created.

Member Avatar for dean8710 
<?php
//query insert new record

//if(//inserted successfully){
    //send email
    if(//email_was_sent){
        //inform_user
    }
}
else{

}

?>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.