How to fix this error: "SQLSTATE: invalid parameter number"

Question

I tried many methods but nothing fixed this error, what should I do?

has code

Controller

$ticketId = Tickets::get('id');

$assig_user_name = DB::table('tickets')
        ->join('users', 'tickets.assigned_id', '=', 'users.id')
        ->select('users.id','users.name')
        ->where('tickets.id', '=', $ticketId)
        ->get();

When I add $ticketId it works and shows the id is what I want but when joining it doesn't work.

Answer 1

$ticketId returns the collection, if you are trying to get all users who have tickets, then in this case you can do something like this

$ticketId = Tickets::select('id')->get();

$assig_user_name = DB::table('tickets')
        ->join('users', 'tickets.assigned_id', '=', 'users.id')
        ->select('users.id','users.name')
        ->wherein('tickets.id',[$ticketId])
        ->get();

Answer 2

SQLSTATE[HY093]: Invalid parameter number usually means that you provided the wrong number of placeholders in the query, and there is a gap between the number of placeholders and the number of values ​​you are trying to bind to those placeholders Mismatch.

You can try:

$assig_user_name = DB::table('tickets')
        ->join('users', 'tickets.assigned_id', '=', 'users.id')
        ->select('users.id','users.name')
        ->where('tickets.id', '=', ':ticketId')
        ->setBindings([':ticketId' => $ticketId])
        ->get();

Another problem might be that $ticketId is an array, so you need:

$assig_user_name = DB::table('tickets')
        ->join('users', 'tickets.assigned_id', '=', 'users.id')
        ->select('users.id','users.name', 'tickets.*')
        ->whereIn('tickets.id', $ticketId->pluck('id')->toArray())
        ->get();