php query array in array

Question

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

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

[Query Results]

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

[Controller]

$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');
}

user_18791044 · Answer

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

php query array in array

1 Answers