So currently I've created search text field to find keywords for specific columns of my database.
Using this eloquent
$data = DB::table('product')
->select('product.id','product.title','product.description','product.content','product.photo','product.quantity','product.price')
->join('product_per_category','product_per_category.product_id','=','product.id')
->where(['product.deleted' => 0])
->where(['product_per_category.deleted' => 0])
->where(['product_per_category.productcategory_id' => $id])
->where(function($query) use ($keyword){
$query->where('product.content', 'like', '%' . $keyword . '%')
->orWhere('product.title', 'like', '%' . $keyword . '%')
->orWhere('product.quantity', 'like', '%' . $keyword . '%')
->orWhere('product.price', 'like', '%' . $keyword . '%');
})
->groupBy('product.id')
->paginate(10);
If I try to search 234.00 value of Price all possible value that will match with the columns of content, title, quantity, price, quantity will automatically return the data.
How ever if I tried to search for a keyword like this
234.00 Books value of Price and Title. it will not return any data now. Because it will match only this sentence on specific columns.
What I'm trying to do is if I search something like this
= 234.00 Books 20
*Value of Price, Title and Quantity
It should return all the data that will match on every columns. Whether the letters lowercase or uppercase as long as it matches the position of letters
Is that possible?
** UPDATE **
Objective : Just want to match the 234 value on any columns. Be the result. But the problem of my query is, if I add another keyword like 234 Books it doesnt return any data
First: Extract the keywords:
$delimeter = ' '; //or your separator
$keywords = explode($delimeter, $search); // Will return an array containing each keyword
Then: Loop over each keyword and call your search query or expand your query dynamically
Example Something like this should work...
$base_query = DB::table('product')
->select('product.id','product.title','product.description','product.content','product.photo','product.quantity','product.price')
->join('product_per_category','product_per_category.product_id','=','product.id')
->where(['product.deleted' => 0])
->where(['product_per_category.deleted' => 0])
->where(['product_per_category.productcategory_id' => $id]);
foreach($keywords as $keyword){
$base_query->where(function($query) use ($keyword){
$query->where('product.content', 'like', '%' . $keyword . '%')
->orWhere('product.title', 'like', '%' . $keyword . '%')
->orWhere('product.quantity', 'like', '%' . $keyword . '%')
->orWhere('product.price', 'like', '%' . $keyword . '%');
})
}
$result = $base_query->get();
You will need to do a loop because you don't know the amount of keywords in your search result
You can use explode PHP function like this:
$keywords = explode(' ', $keyword); // Will return an array containing each keyword
dd($keywords);
If your keywords can contain spaces, you may should consider using another delimiter or splitting your query parameters.