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.

All in one piece of sql, or simply have the queries in PHP and run the next if there's no result? — Tony Hopkinson
– Tony Hopkinson, Commented Sep 19, 2013 at 11:42
Whichever would be best really, I was thinking to search the first query, if there is no match it looks at the next query — nsilva
– nsilva, Commented Sep 19, 2013 at 11:46
What the …?! You are using prepared statements but still inserting variables directly in the query. Please use bindValue() or bindParam() to bind the arguments to the statement. Otherwise your code is probably vulnerable. — feeela
– feeela, Commented Sep 19, 2013 at 12:10

feeela · Accepted Answer · 2013-09-19 12:20:11Z

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

answered Sep 19, 2013 at 12:20

feeela

30k7 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - Multiple Queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related