请求协助：修复MySQL按日期区间导出Excel的PHP代码问题

Let's break down why your code isn't triggering the Excel download and fix it step by step.

Key Issues in the Original Code #

• SQL Injection Risk + Invalid Query Execution: You're directly plugging $_POST values into your SQL query, which is a critical security flaw. Worse, if the date format doesn't match MySQL's expected YYYY-MM-DD standard, the query will either return no results or throw an error that breaks the download flow entirely.
• Header Output Conflict: If there's any accidental output (like whitespace before <?php, error messages from your database connection, or content from db_con.php), PHP will fail to send the download headers. That's why you're seeing raw text instead of a file prompt.
• Lack of Error Handling: There's no check for missing date parameters, failed query preparation, or execution errors. If any of these steps fail, your code just keeps running and outputs unformatted text.
• Incorrect Formatting: Using application/octet-stream is less reliable for Excel files, and using \n instead of \r\n for line breaks can cause formatting glitches in some Excel versions.

Fixed Code #

Here's the revised code with all issues addressed:

<?php
// Enable debug mode temporarily (disable in production)
ini_set('display_errors', 1);
ini_set('log_errors', 1);
error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Prevent accidental output from breaking headers
ob_start();

include('db_con.php');

// Validate required POST parameters exist
if (!isset($_POST['search_text'], $_POST['search_text2'])) {
    die("Please provide both start and end dates.");
}

$startDate = $_POST['search_text'];
$endDate = $_POST['search_text2'];

// Validate date format (adjust regex if your frontend uses DD/MM/YYYY instead)
$datePattern = '/^\d{4}-\d{2}-\d{2}$/';
if (!preg_match($datePattern, $startDate) || !preg_match($datePattern, $endDate)) {
    die("Invalid date format. Please use YYYY-MM-DD.");
}

// Use prepared statements to avoid SQL injection and ensure valid query execution
$query = "SELECT * FROM installment WHERE inst1date BETWEEN ? AND ?";
$stmt = $db_con->prepare($query);
// Bind date parameters safely (s = string type, matches date format)
$stmt->bind_param('ss', $startDate, $endDate);
$stmt->execute();
$result = $stmt->get_result();

// Define column headers with tab separators
$columnHeader = "Sr NO\tRoll\tName\tParent Name\tCourse Name\tCourse Fees\tBatch\tDue Amount\tAdmission Date\tAdmission Amount\tAdmission Status\tInstallment 1 Date\tInstallment 1 Amount\tInstallment 1 Status\tInstallment 2 Date\tInstallment 2 Amount\tInstallment 2 Status\tInstallment 3 Date\tInstallment 3 Amount\tInstallment 3 Status\tInstallment 4 Date\tInstallment 4 Amount\tInstallment 4 Status\tInstallment 5 Date\tInstallment 5 Amount\tInstallment 5 Status\tInstallment 6 Date\tInstallment 6 Amount\tInstallment 6 Status\tInstallment 7 Date\tInstallment 7 Amount\tInstallment 7 Status\tInstallment 8 Date\tInstallment 8 Amount\tInstallment 8 Status";

$setData = '';

// Format rows for Excel
while ($rec = $result->fetch_assoc()) {
    $rowData = '';
    foreach ($rec as $value) {
        // Escape double quotes and wrap values to handle commas/spaces
        $value = '"' . str_replace('"', '""', $value) . '"' . "\t";
        $rowData .= $value;
    }
    // Trim trailing tab and add proper line break
    $setData .= trim($rowData) . "\r\n";
}

// Clear buffered output and send download headers
ob_end_clean();

// Use standard Excel MIME type for proper browser recognition
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=duelist.xls");
header("Pragma: no-cache");
header("Expires: 0");

// Output the final Excel content
echo ucwords($columnHeader) . "\r\n" . $setData;

// Clean up database resources
$stmt->close();
$db_con->close();
?>

What Changed & Why #

1. Output Buffering: ob_start() and ob_end_clean() ensure no accidental output (like whitespace or errors) blocks the download headers.
2. Input Validation: We check that both dates exist and match the correct format, so users get clear feedback instead of broken results.
3. Secure Query: Prepared statements with bind_param() eliminate SQL injection risks and ensure the date range query executes correctly.
4. Proper Excel Formatting: Switched to application/vnd.ms-excel (the standard MIME type for Excel) and used \r\n for line breaks to ensure cross-system compatibility.
5. Error Handling: Added checks for missing parameters and invalid dates to prevent silent failures.
6. Resource Cleanup: Explicitly closed database connections and statements to avoid resource leaks.

Additional Tips #

• If your frontend sends dates in a different format (e.g., MM/DD/YYYY), convert them to MySQL's YYYY-MM-DD standard with:

  $startDate = DateTime::createFromFormat('m/d/Y', $_POST['search_text'])->format('Y-m-d');
  $endDate = DateTime::createFromFormat('m/d/Y', $_POST['search_text2'])->format('Y-m-d');
  

• Disable display_errors in production to avoid exposing sensitive database details.
• Test with valid date ranges—if no rows match, the Excel file will still download but only include headers.

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