How fix this error : "SQLSTATE[HY093]: Invalid parameter number"-CodePudding

I try many way but not fixed this error what can I do ?

There is 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 dd $ticketId it works and show id's what I want but in join it is not working.

CodePudding user response：

$ticketId return collection if you are trying to get all the users that have ticket then in that case you can do something like that

$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();

CodePudding user response：

The SQLSTATE[HY093]: Invalid parameter number generally indicates that you have provided an incorrect number of placeholders in your query, and there is a mismatch between the number of placeholders and the number of values you are attempting to bind to those placeholders.

You could try with:

$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 issue could 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();

CodePudding user response：

Try this...

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

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