Pdo: Invalid Parameter Number: Mixed Named and Positional Parameters

PDO: Invalid parameter number: mixed named and positional parameters

Change

LIMIT :offset, :limit

LIMIT ?, ?

and

$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);

to:

$stmt->bindValue($index+1, $offset, PDO::PARAM_INT);
$stmt->bindValue($index+2, $limit, PDO::PARAM_INT);

PHP PDO Invalid parameter number: mixed named and positional parameters

Your problem is exactly what the error message says, you are using positional parameters (?) and named parameters (:year) in the same query, which is not allowed. You need to consistently use one or the other e.g.

$name = $_GET['name'];
$stmt = $pdo->prepare("SELECT `st_id`, `st_name` FROM `students` WHERE st_name LIKE ? or st_phone LIKE ? AND atd_year = ?");
$param = array("%$name%", "%$name%", $_GET['yid']);
$stmt-> execute($param);

or using named parameters:

$name = $_GET['name'];
$stmt = $pdo->prepare("SELECT `st_id`, `st_name` FROM `students` WHERE st_name LIKE :name or st_phone LIKE :phone AND atd_year = :year");
$param = array(':name' => "%$name%", ':phone' => "%$name%", ':year' => $_GET['yid']);
$stmt-> execute($param);

PHP PDO: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

Use a properly parametrized query. And the assignments in an UPDATE statement must be separated by ,, not AND.

$stmt = $this->db->prepare("UPDATE matches SET `winner` = :setone 
                            , `looser` = :settwo 
                            , `winner_score` = :getChallengerScore 
                            , `looser_score` = :getOpponentScore 
                            , `opponent_blob` = :fileOpponentData
                            , `challenger_blob` = :fileChallengerData
                            WHERE `id` = :matchID");
$stmt->execute(array(
    ':setone' => $setone,
    ':settwo' => $settwo,
    ':getChallengerScore' => $getChallengerScore,
    ':getOpponentScore' => $getOpponentScore,
    ':fileOpponentData' => $fileOpponentData,
    ':fileChallengerData' => $fileChallengerData,
    ':matchID' => $matchID
));

Invalid parameter number: mixed named and positional parameters

This

$insertPage = $db->prepare("
INSERT INTO about (name, position, detail, imageType, imageData)
VALUES (:name, :position, :detail, {$imageProperties['mime']}, {$imageData})
");
$insertPage->execute([
'name'      => $name,
'position'  => $position,
'detail'    => $detail,
'imageType' => $imageProperties['mime'],
'imageData' => $imageData
]);

should be

$insertPage = $db->prepare("
INSERT INTO about (name, position, detail, imageType, imageData)
VALUES (:name, :position, :detail, :imageType, :imageData)
");
$insertPage->execute([
'name'      => $name,
'position'  => $position,
'detail'    => $detail,
'imageType' => $imageProperties['mime'],
'imageData' => $imageData
]);

note the change in the VALUES section of the INSERT query. This will fix the error you get. I'm not digging in what you are trying to achieve with this query and with the point that from my perspective it is better to store images in the filesystem and urls in the DB

SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

I believe the problem is with the Doctrine ORM support for NativeSQL.

I don't find any examples in the documentation of the setParameters method being used for named parameters. All of the examples I see of that method being used are for positional, rather than named.

All of the examples for named parameters use the setParameter (with no "s") method. And they only show a single occurrence of a matching placeholder in the SQL.

As a test (and as a possible workaround), try making each placeholder in the SQL text unique, and then set each one separately.

It looks like "named parameter" support may be somewhat incomplete (compared to what we are used to with Oracle and other ORM frameworks). It looks like Doctrine may have better support for the positional notation. (Which is great for simple statements, but it can be a real bear when you have lots of parameters, and you need to make changes to a SQL statement. That's where the benefit of named parameters really begins to shine... if they are supported right.)

Here's a link to the Doctrine ORM documentation http://doctrine-orm.readthedocs.org/en/latest/reference/query-builder.html?highlight=setParameters (Search for setParameters on that page for examples.)

(See my comments on your question.)

That may not really answer your question, but it may get you moving in the right direction. Be careful out there.