mysql - php query array in array

View Original

I am using php phalcon5 framework. I have 2 columns on a product table. Product title and keyword. The product title contains 1 complete sentence and the keyword contains multiple comma separated words. I have done different queries but am getting unexpected results. How do I get the right results?

[Table]

table -> products
column -> product_title, keywords

[Query Results]

{-code-2}

[Controller]

{-code-3}

Answer

------- product_title | keywords

Answer

------- three piece | women,cloth,three,piece shari | women,cloth Kurti | cloth,women

Answer

-------|||Query1: women cloth Result: three piece, shari Expected Result: three piece, shari, Kurti Query2: cloth women Result: Kurti Expected Result: three piece, shari, Kurti Query3: women/cloth Result: Three piece, shari, Kurti Expected Result: three piece, shari, Kurti Query4: cloths Result: No Data Found Expected Result: three piece, shari, Kurti|||$data = trim($this->request->getPost('q')); $query = Products::find(["keywords LIKE :key: OR product_title LIKE :data:", "bind" => ["data" => '%'. $data .'%',"key" => '%'.$data.'%'], ]); if($query->count() > 0) { $this->view->setVar('pro',$query); $this->view->pick('index/query'); }

Answer

Solution:

One thing to take in account is that you can use only one bind param, ex. data, and reemploy it in either keywords and product_title comparison:

$query = Products::find(["keywords LIKE :data: OR product_title LIKE :data:",
 "bind" => ["data" => '%'. $data .'%'],
]);

In the other hand, you should to explode your query string to prevent it from affecting the words order, and repeat your keywords/product_title comparison with each of them:

$words = array_map('trim', preg_split('|\s+|', $this->request->getPost('q')));

$params = [];
foreach ($words as $key => $word) {
    $params['conditions'] .= isset($params['conditions']) ? ' AND ' : '';
    $params['conditions'] .= "(keywords LIKE :word".$key.": OR product_title LIKE :word".$key.":)";
    $params['bind']['word'.$key] = '%'. $word .'%';
}

$query = Products::find($params);

Source