MySQL - Multiple Queries

Question

I have the following SQL query:-

    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;
        }

    }

Basically what I need to do is split this into 5 different queries, so first it will search:

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')";

If a match is found, it returns a result else it will try the next query:

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')";

again, if no result is found it will try the next query:-

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')";

and so on...

I'm just not sure how to go about doing this so any help would be much appreciated.

Answer 1

You can prepare the statement once and reuse it with different values (that's one main concept behind prepared statement):

$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;
    }
}