CODEWITHSUNDEEP
phplaravellaravel-4eloquent
baao
baao

Reputation: 73241

Laravel select query with array

I get a string like this

$str = 'What is a typical day like';

Now I want to search the DB for every word this string contains.

What
is
a
...

I'm trying the following:

$var = Input::get('search');
// first try was $data = explode(' ',$var);
$data = array(str_replace(' ',',',$var));

$result = Faq::whereIn('heading', $data)->orWhereIn('wording',$data)->get();

return Response::json(array('name' => $result));

Both gives me no results, but each of those words is at least 4 times in the database. Where is my error?

Upvotes: 0

Views: 6516

Answers (2)

Jacek
Jacek

Reputation: 1

 public static function getProductSearch($words){

        $word = explode(' ', $words);

        $value = Product::where('active', 'Y');
        foreach ($word as $key) {
            $value->where('title', 'like', "%$key%");
        }
            $data = $value->get();
        return $data;
    }

Upvotes: 0

Tim Lewis
Tim Lewis

Reputation: 29278

explode() is the right function to use here, as it will split all your words into an array properly. This will only not work if you're looking for (as an example) a name like "Mary Ann" or something with a space in it. As for the query, if you're trying to search every column for every word, that might be a little tricky... BUT you could do something like this:

$query = Faq->select(); // Initializes the query
foreach($data AS $value){
  $query->where(DB::raw("CONCAT_WS(' ', column_1, column_2, ...)"), 'LIKE', '%'.$value.'%');
}
$faqs = $query->get(); // Runs the query

The logic behind that is CONCAT_WS on all the columns to create a giant string of all the values of all the colmuns, then querying that string to match each word in your $data array.

This kind of query (ie searching/filtering results based on input) is much easier when you know what column you're looking for. An associative array of column_name => value_to_find makes this much easier as you can do:

foreach($data AS $key => $value){
  $query->where($key, 'LIKE', '%'.$value.'%');
}
$faqs = $query->get();

Edited Example

+----------+----------+----------+
| address  | city     | province |
+----------+----------+----------+
| 123 Main | Toronto  | Ontario  |
| 123 2nd  | Montreal | Quebec   |
+----------+----------+----------+

Given this test data, calling CONCAT_WS(" ", address, city, province)) in your SQL would result in a string of 123 Main Toronto Ontario and 123 2nd Montreal Quebec. If your search array was something like [0] => 123, [1] => Toronto, [2] => Ontario I think you'd (maybe?) get 1 result as $query->where(...) followed by another $query->where(...) produces a WHERE ... AND WHERE ... structure. As I mentioned in my edit, add a counter to the foreach:

$counter = 0;
$query = Faq->select(); // Initializes the query
foreach($data AS $value){
  if($counter == 0){
    $query->where(DB::raw("CONCAT_WS(' ', column_1, column_2, ...)"), 'LIKE', '%'.$value.'%'); 
  } else {
    $query->orWhere(DB::raw("CONCAT_WS(' ', column_1, column_2, ...)"), 'LIKE', '%'.$value.'%'); 
  }
  $counter++;
}

Upvotes: 2

Related Questions