Convert database column value to array during query buildler laravel and use wherein

✔ Recommended Answer

If $request->input('customer_id') is an integer, and column customer_id is string, add to query:

->where('customer_id', 'LIKE', '%'.$request->input('customer_id').'%')

instead of


Edit: this solution would get all records with id 12, 20, 200 ...(every number that cointains 2).

To prevent this, you could add , (comma) to beginning and end of customer_id before storing it to database (your first row's customer_id would be ",1,2,"). Then you could add to query:

->where('customer_id', 'LIKE', '%,'.$request->input('customer_id').',%')`//added comma next to % sign

Best practice solution would probably be to use many to many relationship, more info here


Answered By: franB

Method #2

To convert a database column value to an array during query building in Laravel and use whereIn, you can make use of the DB::raw() method.

Here's an example:

$myArray = ['foo', 'bar', 'baz']; $results = DB::table('my_table') ->whereIn('my_column', DB::raw('("' . implode('","', $myArray) . '")')) ->get();

In this example, we have an array $myArray which we want to use to filter rows in a database table my_table based on the values in a column my_column. We first use the implode() method to convert the array to a comma-separated string of values, wrapped in quotes. We then pass this string to the DB::raw() method, which generates a raw SQL expression that can be used in the query.

Finally, we use the whereIn() method to filter the rows based on the values in the my_column column. The whereIn() method takes two arguments: the name of the column to filter on, and an array of values to match. In this case, we pass the name of the column (my_column) and the raw SQL expression generated by DB::raw().

Note that using DB::raw() can be a potential security risk, as it allows raw SQL code to be injected into the query. Be sure to validate and sanitize any user input before using it in a raw SQL expression.


Most Popular

Remove Unicode Zero Width Space PHP

PhpStorm, return value is expected to be 'A', 'object' returned

Laravel file upload returns forbidden 403, file permission is 700 not 755