Using PDO to select when param columns are unknown/variable

For simplicity's sake, let's say we have this rather contrived table:

[ID]  [Weekday]  [Weather]
 1      Sun        Cloudy
 2      Mon        Sunny
...     ...         ...
 8      Sun        Cloudy
...     ...         ...
15      Sun        Windy

And I'm hitting that table for datasets. Sometimes I want data based on the weekday, sometimes based on the weather. So I create this class:

class weather {

    public static function reportByDay($weekday) {
        return self::weatherServer('Weekday',$weekday);
    }

    public static function reportByWeather($weather) {
        return self::weatherServer('Weather', $weather)
    }

    private static function weatherServer($reportType, $value) {
        $q = "SELECT ID, Weekday, Weather
                FROM table
                WHERE $reportType = $value";
        $r = mysql_query($q);
        etc etc.
        return $results;
    }
}

So I wanted to convert this to PDO, but discovered this morning that the WHERE :field = :thing structure doesn't work... at least I can't make it work.

If I delineate the column, so WHERE Weather = :thing then it works nicely... but I've just lost the convenience of the original class structure because I'd have to type out all of those specialized queries... and there are a lot for my real dataset & table structure.

Is there a PDO way to use params for columns? or can params开发者_如何学JAVA only be used for values?

It looks like you already have half the answer -- don't make PDO bind the column, do that "manually" just as you were doing:

private static function weatherServer($reportType, $value) {
    // you may want to sanitize reportType, but because this is private, you 
    // might not need to
    $q = "SELECT ID, Weekday, Weather
            FROM table
            WHERE $reportType = :value";
    // no idea where $pdo would be declared in your framework. It's up to 
    // what you feel best meets your need.
    $stmt = $pdo->prepare($q);
    $stmt->bindParam(":value",$value);
    etc etc.
    return $results;
}

Keep a safe list and use string concatenation or interpolation to put the column name in there.

$validColumns = array(
   'Weather',
   'Weekday'
);

if ( ! in_array($reportType, $validColumns)) {
   throw new Exception('Not a valid column name.');
}

$q = "SELECT ID, Weekday, Weather
                FROM table
                WHERE `$reportType` = :value";