如何用PHP+MySQL实现：Sent列值大于Last Update Date时更新Attempted列

Got it, let's break this down for you. You want to set the Attempted column to 'attempted' whenever the Sent date is later than the Last Update Date using PHP and MySQL. Here are two solid approaches based on what you're trying to achieve:

If you need to update all matching records at once, doing this directly via MySQL is way faster than looping through each row in PHP. Here's how:

First, the core SQL query (replace your_table_name with your actual table name):

UPDATE your_table_name 
SET Attempted = 'attempted' 
WHERE Sent > `Last Update Date`;

To run this query via PHP (using PDO for safety, to avoid SQL injection):

// Establish your PDO connection (adjust credentials to match your DB)
$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'your_username', 'your_password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepare and execute the update
$updateQuery = "UPDATE your_table_name SET Attempted = 'attempted' WHERE Sent > `Last Update Date`";
$stmt = $pdo->prepare($updateQuery);
$stmt->execute();

// Optional: Print how many records were updated
echo "Updated " . $stmt->rowCount() . " records successfully.";

Note: #

If your date columns aren't in MySQL's standard YYYY-MM-DD or YYYY-MM-DD HH:MM:SS format, use STR_TO_DATE to convert them for comparison:

UPDATE your_table_name 
SET Attempted = 'attempted' 
WHERE STR_TO_DATE(Sent, '%d/%m/%Y') > STR_TO_DATE(`Last Update Date`, '%d/%m/%Y');

(Adjust the format string %d/%m/%Y to match your actual date format.)

If you're displaying the table and want to update values on-the-fly (either just for display or to save each row as you loop), modify your existing table code like this:

<table>
  <tr>
    <th class="table-header">Sent</th>
    <th class="table-header">Last Update Date</th>
    <th class="table-header">Attempted</th>
  </tr>
  <?php 
  if(!empty($orderrecords)) { 
    foreach($orderrecords as $k=>$v) {
      // Convert dates to DateTime objects for easy comparison
      $sentDate = new DateTime($v['Sent']);
      $lastUpdateDate = new DateTime($v['Last Update Date']);
      
      // Start with the existing Attempted value
      $attemptedStatus = $v['Attempted'];

      // Check if Sent date is later than Last Update Date
      if($sentDate > $lastUpdateDate) {
        $attemptedStatus = 'attempted';
        
        // Optional: Save this update to the database immediately
        // Make sure to use prepared statements here too!
        $updateStmt = $pdo->prepare("UPDATE your_table_name SET Attempted = 'attempted' WHERE id = ?");
        $updateStmt->execute([$v['id']]); // Replace 'id' with your unique record identifier
      }
  ?>
  <tr>
    <td><?php echo htmlspecialchars($v['Sent']); ?></td>
    <td><?php echo htmlspecialchars($v['Last Update Date']); ?></td>
    <td><?php echo htmlspecialchars($attemptedStatus); ?></td>
  </tr>
  <?php 
    } 
  } 
  ?>
</table>

Key Tips: #

• Always use prepared statements when running SQL queries with user/row data to prevent SQL injection.
• If you're updating multiple records, the batch MySQL update is always better for performance—avoid looping through hundreds/thousands of rows in PHP if you can.
• Double-check your date formats: mismatched formats can lead to incorrect comparisons (e.g., 05/06/2024 vs 2024-06-05).

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