PDO positional and named parameters as part of the same prepared query?

Question

I'm learning the ropes with PDO.

Here is my sql (the number of parameters that can appear in the WHERE is variable).

    SELECT
        ID, title

    FROM
        table

    WHERE
        something = ?

    ORDER BY 
        :sort :dir 

    LIMIT 
        :start, :results

Here is my code:

        $query = $conn->prepare($sql);

        if ($parameters) {

            $i = 0;
            foreach ($parameters AS $parameter) {

                $i++;
                $query->bindParam($i, $parameter);

            }

        }

        $query->bindParam(':start', $pagination['start'], PDO::PARAM_INT);
        $query->bindParam(':results', $pagination['results'], PDO::PARAM_INT);
        $query->bindParam(':sort', $pagination['sort']);
        $query->bindParam(':dir', $pagination['dir']);

        $query->execute();

... and here is the exception that it generates:

 Invalid parameter number: mixed named and positional parameters

Is it impossible to combine positional and named parameters in the same query? Or am I missing something?

Thanks!

Answer 1

Yes, it's impossible.

PDO.prepare

You cannot use both named and question mark parameter markers within the same SQL statement; pick one or the other parameter style.

Answer 2

Use a wrapper function, a naive replacement function will suffice.

if (strpos($sql, ":")) {
    $i = -1;
    while (strpos($sql, "?") && isset($parameters[++$i])) {
        $parameters[":p$i"] = $parameters[$i];
        unset($parameters[$i]);
        $sql = preg_replace("/[?]/", ":p$i", $sql, 1);
    }
}

Mix $sort and $dir directly into the $sql query. These two are SQL identifiers, not data.