PHP+MySQL如何查询当前日期落在起止日期列之间的记录？

Hey there! Let's get this sorted out. The key to pulling the right records is using the correct MySQL condition to check if today's date sits between your start_date and end_date columns. Here's how to do it properly, with PHP examples to tie it all together.

Core MySQL Query #

First, the SQL part. MySQL has a built-in function CURDATE() that returns the current date (in YYYY-MM-DD format). You can use the BETWEEN operator to check if this date falls within your date range:

SELECT * FROM your_table_name
WHERE CURDATE() BETWEEN start_date AND end_date;

This will return all rows where today's date is greater than or equal to start_date AND less than or equal to end_date.

Edge Case: Handling Ongoing Records (NULL End Date) #

If some records don't have an end date (meaning they're still active), adjust the query to account for NULL values:

SELECT * FROM your_table_name
WHERE CURDATE() >= start_date 
AND (end_date IS NULL OR CURDATE() <= end_date);

PHP Implementation Examples #

Now let's wrap this query in PHP code. I'll show two common approaches: MySQLi procedural and PDO (which is recommended for security and flexibility).

Option 1: MySQLi Procedural Style #

// Database connection details
$servername = "your_server";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Your query
$sql = "SELECT * FROM your_table_name WHERE CURDATE() BETWEEN start_date AND end_date";
$result = mysqli_query($conn, $sql);

// Fetch and display results
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        // Process each row here
        echo "ID: " . $row["id"]. " - Start Date: " . $row["start_date"]. " - End Date: " . $row["end_date"]. "<br>";
    }
} else {
    echo "No records found where current date is in the range.";
}

// Close connection
mysqli_close($conn);

Option 2: PDO (Prepared Statements) #

PDO is better for preventing SQL injection, especially if you ever need to add dynamic values to your query later:

// Database connection details
$servername = "your_server";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Your query
    $sql = "SELECT * FROM your_table_name WHERE CURDATE() BETWEEN start_date AND end_date";
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    // Set the resulting array to associative
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

    // Fetch and process results
    foreach($stmt->fetchAll() as $row) {
        echo "ID: " . $row["id"]. " - Start Date: " . $row["start_date"]. " - End Date: " . $row["end_date"]. "<br>";
    }
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;

Important Notes #

• Date Column Types: Make sure your start_date and end_date columns are stored as DATE, DATETIME, or TIMESTAMP types in MySQL. Storing dates as strings can lead to unexpected results!
• Include Time?: If you need to check the current datetime (not just date), replace CURDATE() with NOW() in your query.
• Time Zones: Ensure your MySQL server's time zone matches the one you're expecting. You can set it in MySQL with SET time_zone = 'your_time_zone'; if needed.

Content of the question originates from Stack Exchange, asked by jasmine shini