使用PDO选择参数列何时为未知/变量 - Using PDO to select when param columns are unknown/variable

Using PDO to select when param columns are unknown/variable

为简单起见，假设我们有这样一个相当人为的表:

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

我正在点击那个表获取数据集。有时我需要工作日的数据，有时需要天气的数据。所以我创建了这个类:

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

所以我想把它转换成PDO，但今天早上发现WHERE :field = :thing结构不起作用…至少我不能让它工作。

如果我描绘列，所以WHERE Weather = :thing然后它工作得很好…但是我已经失去了原始类结构的便利，因为我必须输入所有那些专门的查询……对于我的真实数据集，有很多表结构。

是否有PDO的方式来使用参数列?或者参数只能用于值?

看起来你已经有了一半的答案——不要让PDO绑定列，像你做的那样"手动":

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

保持一个安全的列表，并使用字符串连接或插值来放置列名。

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