php - MySQL - 多个查询

Question

我有以下 SQL 查询:-

    try {

        $query =   "SELECT VehicleSystemId, Fare FROM tblfixedfares
                    WHERE ShortPostCodeA IN ('$post_code_a','$post_code_a_two','$post_code_a_three','$post_code_a_four','$post_code_a_five')
                    AND ShortPostCodeB IN ('$post_code_b', '$post_code_b_two', '$post_code_b_three','$post_code_b_four','$post_code_b_five')
                    AND DayHalf = :day_half
                    AND VehicleSystemId IN ('Car', 'Est', 'Exec', 'ExecEst', '6B', '7B', '8B', 'Bus', '7W')";

        $stmt = $dbh->prepare($query);

        $stmt->bindParam(':day_half', self::$day_half, PDO::PARAM_STR);

        $stmt->execute();

        $result = $stmt->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP);
        //$result = $stmt->fetch(PDO::FETCH_COLUMN);
        $car = $result['Car'][0]['Fare'];
        $est = $result['Est'][0]['Fare'];
        $exec = $result['Exec'][0]['Fare'];
        $exec_est = $result['ExecEst'][0]['Fare'];
        $six_seater = $result['6B'][0]['Fare'];
        $seven_seater = $result['7B'][0]['Fare'];
        $eight_seater = $result['8B'][0]['Fare'];
        $bus = $result['Bus'][0]['Fare'];
        $wheelchair = $result['7W'][0]['Fare'];

        $stmt->closeCursor();

        $dbh = null;

        // Set fare to specific vehicle

        if ($_REQUEST['v_sys'] == NULL || $_REQUEST['v_sys'] == 'NULL' || $_REQUEST['v_sys'] == ''){
            $result = $car;
            return $result;
        }

        if ($_REQUEST['v_sys'] == 'Car') {
            $result = $car;
            return $result;
        }
        if ($_REQUEST['v_sys'] == 'Est') {
            $result = $est;
            return $result;
        }
        if ($_REQUEST['v_sys'] == 'Exec') {
            $result = $exec;
            return $result;
        }
        if ($_REQUEST['v_sys'] == 'ExecEst') {
            $result = $exec_est;
            return $result;
        }
        if ($_REQUEST['v_sys'] == '6B') {
            $result = $six_seater;
            return $result;
        }
        if ($_REQUEST['v_sys'] == '7B') {
            $result = $seven_seater;
            return $result;
        }
        if ($_REQUEST['v_sys'] == '8B') {
            $result = $eight_seater;
            return $result;
        }
        if ($_REQUEST['v_sys'] == 'Bus') {
            $result = $bus;
            return $result;
        }
        if ($_REQUEST['v_sys'] == '7W') {
            $result = $wheelchair;
            return $result;
        }

    }

基本上我需要做的就是将其分成 5 个不同的查询，所以首先它将搜索:

query =   "SELECT VehicleSystemId, Fare FROM tblfixedfares
                WHERE ShortPostCodeA = '$post_code_a_five
                AND ShortPostCodeB = '$post_code_b_five
                AND DayHalf = :day_half
                AND VehicleSystemId IN ('Car', 'Est', 'Exec', 'ExecEst', '6B', '7B', '8B', 'Bus', '7W')";

如果找到匹配项，它将返回结果，否则它将尝试下一个查询:

query =   "SELECT VehicleSystemId, Fare FROM tblfixedfares
                WHERE ShortPostCodeA = '$post_code_a_four
                AND ShortPostCodeB = '$post_code_b_four
                AND DayHalf = :day_half
                AND VehicleSystemId IN ('Car', 'Est', 'Exec', 'ExecEst', '6B', '7B', '8B', 'Bus', '7W')";

同样，如果没有找到结果，它将尝试下一个查询:-

query =   "SELECT VehicleSystemId, Fare FROM tblfixedfares
                WHERE ShortPostCodeA = '$post_code_a_three
                AND ShortPostCodeB = '$post_code_b_three
                AND DayHalf = :day_half
                AND VehicleSystemId IN ('Car', 'Est', 'Exec', 'ExecEst', '6B', '7B', '8B', 'Bus', '7W')";

等等...

我只是不确定如何执行此操作，因此我们将不胜感激。

Answer 1

您可以准备一次语句并以不同的值重用它(这是准备好的语句背后的一个主要概念):

$query = <<<EOSQL
    SELECT VehicleSystemId, Fare FROM tblfixedfares
    WHERE ShortPostCodeA = :post_code_a
    AND ShortPostCodeB = :post_code_b
    AND DayHalf = :day_half
    AND VehicleSystemId IN ('Car', 'Est', 'Exec', 'ExecEst', '6B', '7B', '8B', 'Bus', '7W');
EOSQL;

$stmt = $dbh->prepare( $query );

foreach( $postCodes as $postCode )
{
    $stmt->bindValue( ':post_code_a', $postCode['A'] );
    $stmt->bindValue( ':post_code_b', $postCode['B'] );
    $stmt->bindValue( ':day_half', $day_half );

    if( $stmt->execute() === true && $stmt->rowCount() > 0 )
    {
        /* fetch data here */

        /* then leave the loop */
        break;
    }
    else
    {
        continue;
    }
}