在EXTRACT（）查询中使用PDO参数

Question

I'm creating a function to get stats on different players from a database and I need to be able to specify the timescale the stats are for as one of the function's variables - it'll be something like 'month' or 'year'.

I've had some success in creating SQL queries using EXTRACT() to compare the record timestamp with the current time but I can't seem to use a PDO variable to change this with each function call.

Any ideas what I'm doing wrong? Or is this just a limitation of bindParam()?

function get_player_stats($player_id, $timescale) {
    global $pdo;

    $query = $pdo->prepare('
        SELECT count(results.winner)
        FROM results
        WHERE results.winner = :player_id
        AND EXTRACT(:timescale FROM results.date) = EXTRACT(:timescale FROM NOW())
        LIMIT 1
    ');

    $query->bindParam(':player_id', $player_id);

    if ($timescale = 'this_month') {
        $query->bindParam(':timescale','YEAR_MONTH');
    }

    else if ($timescale = 'this_year') {
        $query->bindParam(':timescale','YEAR');
    }

    $query->execute();

    return $query->fetchAll(PDO::FETCH_OBJ);
}

P.S. It's a MySQL database

Answer 1

I see two problems here:

1. The EXTRACT() function doesn't expect a string but an identifier. You cannot use bind parameters to provide identifiers (such as table names).

2. Repeating place-holders is not supported by all PDO drivers and modes thus it's discouraged.

You'll have to inject the value into the SQL code with your favourite string manipulation technique.