基于SQL Server与PHP的定时邮件技术问询：条件触发及每日执行方案

Hey there! Let's break down your questions step by step since they're closely related—here's a practical, actionable solution tailored to your SQL Server + PHP setup:

核心逻辑（对应你的第一个问题） #

No matter the tech stack, the core workflow stays the same to send emails only when target data exists:

1. Trigger the script on schedule: Automatically run your PHP script at the specified time
2. Check database for target data: Query SQL Server to verify if there are pending approval requests for each approver
3. Send emails conditionally: If the query returns data, fire off the reminder emails; if not, just exit the script quietly

针对SQL Server + PHP的具体技术方案（对应你的第二个问题） #

Below are two reliable approaches, pick the one that matches your server environment:

方式1：Windows任务计划（适合Windows服务器） #

This is the most straightforward option if your PHP runs on Windows:

• Step 1: Write the PHP script
  The script needs three key functions: connect to SQL Server, fetch pending data, and send emails. Here's a simplified example:

  <?php
  // 1. Connect to SQL Server (ensure sqlsrv extension is installed/enabled)
  $serverName = "your-sql-server-address";
  $connectionOptions = [
      "Database" => "your-db-name",
      "Uid" => "db-username",
      "PWD" => "db-password"
  ];
  $conn = sqlsrv_connect($serverName, $connectionOptions);
  if (!$conn) die(print_r(sqlsrv_errors(), true));
  
  // 2. Fetch pending requests grouped by approver email
  $sql = "SELECT ApproverEmail, COUNT(RequestId) AS PendingCount 
          FROM ApprovalRequests 
          WHERE Status = 'Pending' 
          GROUP BY ApproverEmail";
  $stmt = sqlsrv_query($conn, $sql);
  $pendingApprovalData = [];
  if ($stmt) {
      while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
          $pendingApprovalData[] = $row;
      }
  }
  sqlsrv_free_stmt($stmt);
  sqlsrv_close($conn);
  
  // 3. Send emails only if there's pending data
  if (!empty($pendingApprovalData)) {
      foreach ($pendingApprovalData as $item) {
          $to = $item['ApproverEmail'];
          $subject = "Daily Approval Reminder: You have {$item['PendingCount']} pending requests";
          $message = "Hi there,\n\nYou currently have {$item['PendingCount']} approval requests waiting for your action. Please log into the system to process them at your earliest convenience.\n\nThanks,\nThe Admin Team";
          $headers = "From: Admin <your-admin@example.com>" . "\r\n" .
                     "Reply-To: your-admin@example.com" . "\r\n" .
                     "X-Mailer: PHP/" . phpversion();
  
          // Use PHPMailer instead of mail() for better reliability with SMTP
          mail($to, $subject, $message, $headers);
      }
  }
  ?>
  

  Pro tip: Replace the built-in mail() function with PHPMailer—it supports SMTP and avoids common email delivery issues.

• Step 2: Set up Windows Task Scheduler

  1. Open "Task Scheduler" → Create Basic Task
  2. Name it "Daily Approval Email Reminder", set the trigger to "Daily" at 12:00 AM
  3. For the action, select "Start a program"—browse to your php.exe path (e.g., C:\php\php.exe) and add your script path as an argument (e.g., C:\www\approval_reminder.php)
  4. Test the task manually first to ensure it works before relying on the schedule

方式2：Linux Cron Job（适合Linux服务器） #

For Linux environments, Cron is the standard tool for scheduled tasks:

• Step 1: Write the PHP script
  Use the same logic as the Windows version—just make sure your SQL Server connection (via sqlsrv or PDO_SQLSRV) is configured correctly for Linux.

• Step 2: Configure the Cron job

  1. Log into your Linux server and run crontab -e to edit the cron table
  2. Add this line to trigger the script every midnight:

     0 0 * * * /usr/bin/php /var/www/html/approval_reminder.php
     

     Breakdown: 0 0 * * * means run at 00:00 every day; replace the paths with your actual PHP and script locations
  3. Save and exit—Cron will automatically apply the schedule. Use crontab -l to verify the task is listed

额外实用建议 #

• Log everything: Add logging to your script (e.g., write execution status to a log file) to debug issues easily
• Handle exceptions: Wrap database queries and email sending in try-catch blocks to prevent the script from crashing unexpectedly
• Test thoroughly: Run the script manually first to confirm it fetches data and sends emails correctly, then enable the schedule
• Check permissions: Ensure the user running the script has access to SQL Server and the necessary filesystem permissions

内容的提问来源于stack exchange，提问作者King Jherold