PHP / MySQL - Query loop?

Question

I have the following function:

private static function getFixedFare($FixedFareId) {

    $pdo = new SQL();
    $dbh = $pdo->connect(Database::$serverIP, Database::$serverPort, Database::$dbName, Database::$user, Database::$pass);

    try {

        $query =   "SELECT Fare FROM tblfixedfare
                    WHERE FixedFareId = :fixed_fare_id
                    AND DayHalf = :day_half";

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

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

        $stmt->execute();

        $result = $stmt->fetch(PDO::FETCH_COLUMN);

        $stmt->closeCursor();

        $dbh = null;

        return $result;

    }

    catch (PDOException $pe) {
        die("Error: " .$pe->getMessage(). " Query: ".$stmt->queryString);
    }

}

Basically I want to change the query to do nine different selects, as follows:

        $query =   "SELECT Fare FROM tblfixedfare
                    WHERE FixedFareId = :fixed_fare_id
                    AND DayHalf = :day_half
                    AND CarType = 'Car'";

and

        $query =   "SELECT Fare FROM tblfixedfare
                    WHERE FixedFareId = :fixed_fare_id
                    AND DayHalf = :day_half
                    AND CarType = 'Est'";

and the same for all the other car types, 'Exec', 'ExecEst', '6B', '7B', '7W', '8B' and 'Bus' which will only ever be these car types.

I was hoping I could store each of these query results into different variables without having to do 9 different queries (reducing code). Such as a loop and then storing the results to $resultcar, $resultest and so on...

Not to sure how I would do this so any help would be much appreciated! :-)

Answer 1

You can do an IN

SELECT CarType, Fare FROM tblfixedfare
WHERE FixedFareId = :fixed_fare_id
AND DayHalf = :day_half
AND CarType IN ('Car', 'Est' ...);

Note that you have to include the CarType in the Select so you can filter them after the query.

UPDATED: You can group your results by CarType already like this:

 $results = $stmt->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP);

The code above should give you something like:

array(
    'Car' => array(
        // data
    ),
    'Est' => array(
         // data
    )
)

If you don't mind changing your variables, you can immediately have them in variables without looping through extract(...)

So feeding

$results = $stmt->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP);

which is the array mentioned above to extract:

extract($results, EXTR_PREFIX_ALL, 'result');

will let you have the result sets in variables already:

var_dump($result_Car);
array(2) {
    [0]=> array(1) {
        ["Fare"]=> string(1) "100"
    }
    [1]=>
    array(1) {
        ["Fare"]=> string(1) "200"
    }
}

All in all, in one line:

extract($q->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP), EXTR_PREFIX_ALL, 'result');

Answer 2

For repeating the query on the nine different car types, you can store each of the car names into an array and loop through the array inserting the new name each time:

$cars = array();
array_push($cars, "Car", "Est", "Exec", "ExecEst", "6B", "7B", "8B","Bus");

for($i=0; $i < sizeof($cars); $i++)
{
         try {

            $query =   "SELECT Fare FROM tblfixedfare
                        WHERE FixedFareId = :fixed_fare_id
                        AND DayHalf = :day_half
                        AND CarType = '".$cars[i]."'";

            //.....rest of your code    
         }

         catch (PDOException $pe) {
             die("Error: " .$pe->getMessage(). " Query: ".$stmt->queryString);
         }
}

You can then store the result of each query in a new array using the name of the car ( i.e $cars[i] ) as the key.