Introduction

Filtering data by year and month is a common requirement in web applications. This article demonstrates how to achieve this in MySQL using PHP.

✅ Database Structure

sales Table

✅ PHP Code to Fetch Data by Year and Month

Step 1: Connect to the Database

$mysqli = new mysqli("localhost", "username", "password", "database");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

Step 2: Extract Year and Month from $_POST['period']

if (isset($_POST['period'])) {
    $period = $_POST['period']; // e.g., "2012-02"
    list($year, $month) = explode('-', $period);

Step 3: Execute and Fetch Results

    $stmt->execute();
    $result = $stmt->get_result();

    while ($row = $result->fetch_assoc()) {
        print_r($row);
    }

    $stmt->close();
}

$mysqli->close();

✅ Complete Code

$mysqli = new mysqli("localhost", "username", "password", "database");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

if (isset($_POST['period'])) {
    $period = $_POST['period']; // e.g., "2012-02"
    list($year, $month) = explode('-', $period);

    $stmt = $mysqli->prepare("SELECT * FROM sales WHERE YEAR(created_at) = ? AND MONTH(created_at) = ?");
    $stmt->bind_param("ii", $year, $month);

    $stmt->execute();
    $result = $stmt->get_result();

    while ($row = $result->fetch_assoc()) {
        print_r($row);
    }

    $stmt->close();
}

$mysqli->close();

Tips

• Ensure the created_at column is indexed for better performance.

• Validate and sanitize the $_POST['period'] input to prevent SQL injection.

Common Pitfalls

• Incorrect handling of user input can lead to SQL injection vulnerabilities.

• Time zone differences can affect date-based filtering.

✅ Conclusion

Using PHP and MySQL functions like YEAR() and MONTH(), you can efficiently filter data by year and month. This approach is effective for time-based data analysis and reporting.