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

Hi hope some one can help me with this query:

$query = DB::table('user as u')
                ->selectRaw('u.id, u.username, u.status,
					(SELECT COUNT(*) FROM user_posts up WHERE up.userId = u.id) AS posts,
					(SELECT COUNT(id) FROM user_story us WHERE us.userId = u.id) AS stories,
		    		(SELECT COUNT(id) FROM user_requests uf2 WHERE uf2.fUserId = u.id AND uf2.fStatus = :status) AS followers,
			    	(SELECT COUNT(id) FROM user_requests uf WHERE uf.userId = u.id AND uf.fStatus = :status) AS following,
					(SELECT COUNT(id) FROM user_requests uf3 WHERE (uf3.fUserId = u.id OR uf3.userId = u.id) AND uf3.type = :type AND uf3.pStatus = :status) AS partners', ['status'=> 1, 'type'=> 'Partner'])
->where('status', 1);

And I get this error message:

 "message": "SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters (SQL: select u.id, u.username, u.status,\n\t\t\t\t\t(SELECT COUNT(*) FROM user_posts up WHERE up.userId = u.id) AS posts,\n\t\t\t\t\t(SELECT COUNT(id) FROM user_story us WHERE us.userId = u.id) AS stories,\n\t\t    \t\t(SELECT COUNT(id) FROM user_requests uf2 WHERE uf2.fUserId = u.id AND uf2.fStatus = :status) AS followers,\n\t\t\t    \t(SELECT COUNT(id) FROM user_requests uf WHERE uf.userId = u.id AND uf.fStatus = :status) AS following,\n\t\t\t\t\t(SELECT COUNT(id) FROM user_requests uf3 WHERE (uf3.fUserId = u.id OR uf3.userId = u.id) AND uf3.type = :type AND uf3.pStatus = :status) AS partners from `user` as `u` where `status` = 1)",
    "exception": "Illuminate\Database\QueryException",

tykus

3 years ago

Level 104

Pretty sure you can set a PDO option in the database config connection to enable this behaviour.

Edit found it https://www.php.net/manual/en/pdo.prepare.php - PDO::ATTR_EMULATE_PREPARES ; so your database config should be something like:

// ...
    'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            // etc.
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::ATTR_EMULATE_PREPARES => true // add this...
            ]) : [],
]

jonathannet

3 years ago

Level 1

@tykus Tried that, but did not work for me, but the solution Tray2 posted did work :)

Tray2

3 years ago

Best Answer

Level 73

Try changing the bind variables to a ? and then pass the values as [1, 1, 'Partner', 1].

1 like

Please or to participate in this conversation.