PHP函数内动态SQL查询与日期迭代：优化策略与最佳实践

function my_try($SQL_SELECTION){
    global $conn, $date1, $date2; // 依赖全局变量
    $now  = gmdate('Y-m-t');
    $loop = 0;
    $result = array();

    while ($date2 <= $now) {
        // 尝试将函数作为变量调用，并传入动态参数
        $orders_completed = $conn->query($SQL_SELECTION($date1, $date2)); 
        $result[] = $orders_completed;
        $date2 = gmdate('Y-m-t', strtotime($date2 . '+30 days'));
        $loop++;
    }
    return $result;
}

function sql_selection_1($date_from, $date_till) {
    $sql = "SELECT '$date_till' FROM orders WHERE order_date BETWEEN '$date_from' AND '$date_till'";
    return $sql;
}

// 尝试调用，但sql_selection_1()会立即执行，而不是作为可调用对象传递
// my_try( sql_selection_1() ); 

// 示例：手动定义日期区间，实际应用中可根据业务逻辑动态生成
$dateIntervals = [
    ['date_from' => '2019-10-29', 'date_till' => '2020-10-28'],
    ['date_from' => '2020-10-29', 'date_till' => '2021-10-28'],
    // ... 更多日期区间
];

// 或者，如果需要动态生成，可以这样：
$startDate = '2019-10-29';
$endDate = gmdate('Y-m-t'); // 当前日期
$dynamicIntervals = [];
$currentDate = $startDate;

while ($currentDate <= $endDate) {
    $nextMonthDate = gmdate('Y-m-t', strtotime($currentDate . ' +30 days'));
    if ($nextMonthDate > $endDate) {
        $nextMonthDate = $endDate; // 确保不超过最终日期
    }
    $dynamicIntervals[] = [
        'date_from' => $currentDate,
        'date_till' => $nextMonthDate,
    ];
    $currentDate = gmdate('Y-m-d', strtotime($nextMonthDate . ' +1 day')); // 下一个区间的开始
    if ($currentDate > $endDate) break; // 防止无限循环
}

/**
 * 迭代日期区间并执行SQL查询
 *
 * @param PDO $database PDO数据库连接对象
 * @param array $intervals 包含日期区间的数组，每个元素如 ['date_from' => 'YYYY-MM-DD', 'date_till' => 'YYYY-MM-DD']
 * @return array 所有查询结果的集合
 */
function iterateAndExecuteQuery(PDO $database, array $intervals): array
{
    // 定义基础SQL查询模板，使用命名参数
    $sqlTemplate = "SELECT row.id, row.order_date, row.amount FROM orders row WHERE row.order_date BETWEEN :date1 AND :date2";

    // 准备SQL语句，只准备一次，提高效率
    $stmt = $database->prepare($sqlTemplate);

    $allRowsets = [];

    foreach ($intervals as $interval) {
        // 绑定参数，避免SQL注入
        $result = $stmt->execute([
            ':date1' => $interval['date_from'],
            ':date2' => $interval['date_till'],
        ]);

        if ($result === false) {
            // 处理执行错误，例如记录日志或抛出异常
            error_log("SQL execution failed for interval " . json_encode($interval) . ": " . implode(", ", $stmt->errorInfo()));
            continue; // 跳过当前区间或选择抛出异常
        }

        // 获取查询结果，这里假设每次只获取一行或多行
        // 如果期望获取所有匹配行，使用 fetchAll()
        $rowset = $stmt->fetchAll(PDO::FETCH_ASSOC); 

        if ($rowset === false) {
            // 处理获取结果错误
            error_log("Failed to fetch results for interval " . json_encode($interval));
            continue;
        }

        // 可以在此处对获取到的 $rowset 进行进一步处理或更新
        // 例如：$updatedRowset = some_processing_function($rowset);

        $allRowsets[] = $rowset;
    }

    return $allRowsets;
}

// 假设你已经有了一个PDO数据库连接实例
try {
    $dsn = 'mysql:host=localhost;dbname=your_database;charset=utf8mb4';
    $username = 'your_user';
    $password = 'your_password';
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // 启用异常模式
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // 默认关联数组
    ]);
} catch (PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}

// 准备日期区间数据
$dataIntervals = [
    ['date_from' => '2023-01-01', 'date_till' => '2023-01-31'],
    ['date_from' => '2023-02-01', 'date_till' => '2023-02-28'],
    ['date_from' => '2023-03-01', 'date_till' => '2023-03-31'],
];

// 调用函数执行查询
$results = iterateAndExecuteQuery($pdo, $dataIntervals);

// 打印结果
echo "<pre>";
print_r($results);
echo "</pre>";

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(id) AS total_orders
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY month;