Welcome Guest, Not a member yet? Register   Sign In
[ SOLVED ] SQL Like Handling (Active Record class)
#1

[eluser]tkaw220[/eluser]
Hi,

I built a search function with below code in my model:

Code:
$data= array(
  'title' => $keyword,
  'brand' => $keyword,
  'property' => $keyword,
  'group' => $keyword,
  'format' => $keyword,
  'isbn' => $keyword
);

$query = $this->db
->or_like($condition)
->where('publish !=', 'no')
->order_by('brand, format, title', 'asc')
->get('products');

Dump:
Code:
'SELECT *
FROM (`products`)
WHERE `publish` != 'no'
AND `title`  LIKE '%keyword%'
OR  `brand`  LIKE '%keyword%'
OR  `property`  LIKE '%keyword%'
OR  `group`  LIKE '%keyword%'
OR  `format`  LIKE '%keyword%'
OR  `isbn`  LIKE '%keyword%'
ORDER BY `brand`, `format`, `title` asc' (length=294)

The above return results of the search keyword, but ommit the
Code:
where('publish !=', 'no')
(returns products with publish equal to 'no').

What is wrong with my code?

Many thanks.
#2

[eluser]plain jane[/eluser]
Check the priority operator for sql query

Code:
'SELECT *
FROM (`products`)
WHERE (`publish` != 'no')
AND (`title`  LIKE '%keyword%'
OR  `brand`  LIKE '%keyword%'
OR  `property`  LIKE '%keyword%'
OR  `group`  LIKE '%keyword%'
OR  `format`  LIKE '%keyword%'
OR  `isbn`  LIKE '%keyword%')
ORDER BY `brand`, `format`, `title` asc'
#3

[eluser]tkaw220[/eluser]
MySQL operator precedence (http://dev.mysql.com/doc/refman/5.0/en/o...dence.html) shown != and comparison operator has higher priority over the AND and OR.

Thus, in my code above, the first clause (WHERE `publish` != 'no') should had taken into account first. Please correct me if I get it wrongly.
#4

[eluser]plain jane[/eluser]
Code:
WHERE (`publish` != ‘no’)AND (`title`  LIKE ‘%keyword%’ OR `brand`  LIKE ‘%keyword%’ OR `property`  LIKE '%keyword%’ OR `group`  LIKE ‘%keyword%’ OR `format`  LIKE ‘%keyword%’ OR `isbn`  LIKE ‘%keyword%’)

This is what we want
Code:
publish !='no' AND (title, brand, property, group, format, isbn LIKE keyword)

Correct me if i am wrong.
#5

[eluser]tkaw220[/eluser]
Hi ci user,

Your code does not work. However, I tried to use $this->db->query method instead of Active Record class below and it works:

Code:
$query = $this->db->query("SELECT * FROM `products` WHERE (`title` LIKE '%$keyword%' AND `publish` != 'no') OR (`property` LIKE '%$keyword%' AND `publish` != 'no')");

But I wish to know the right way to implement this with Active Record class instead.

Thanks.
#6

[eluser]jmadsen[/eluser]
[quote author="ci user" date="1307374340"]WHERE (`publish` != ‘no’)AND (`title` LIKE ‘%keyword%’ OR `brand` LIKE ‘%keyword%’ OR `property` LIKE '%keyword%’ OR `group` LIKE ‘%keyword%’ OR `format` LIKE ‘%keyword%’ OR `isbn` LIKE ‘%keyword%’)

This is what we want publish !='no' AND (title, brand, property, group, format, isbn LIKE keyword)

Correct me if i am wrong.[/quote]

correct.

twak220, you are getting the equivalent of:

Code:
WHERE (`publish` != 'no' AND `title`  LIKE '%keyword%') OR  `brand`  LIKE '%keyword%'

without the parenthesis, it just works straight down the line...
#7

[eluser]plain jane[/eluser]
I have used a similar query,

Code:
$query = $this->db->query("SELECT * FROM `products` WHERE (`publish` != 'no') AND (`title` LIKE '%$keyword%' OR `property` LIKE '%$keyword%')");

I guess this should work.

Sorry if you dont find this helpful.

Thanks,
ci user
#8

[eluser]tkaw220[/eluser]
Hi jMadsen, ci user,

Your codes are working. Sorry for that. But I wish to have solution in Active Record class instead. I used WHERE instead of LIKE and it works, but I do not sure if this is the best way to do this.

Code:
$query = $this->db
        ->where('title LIKE', "%$keyword%")
        ->where('publish !=', 'no')
        ->or_where('property LIKE', "%$keyword%")
        ->where('publish !=', 'no')
        ->get('products');
#9

[eluser]plain jane[/eluser]
Instead of writing the active record methods for many LIKE, why not use the $this->db->query method so that if the LIKE parameters increase in number we don't have to add lengthy code.

If you are getting the result this way, why not use it.

Thanks, ci user.
#10

[eluser]plain jane[/eluser]
I guess i could not work it by active record method.


Please try this..

$query = $this->db->where('(publish !=\'no\')')->or_like($data)->order_by('brand, format, title', 'asc')->get('products');

Thanks,
ci user.




Theme © iAndrew 2016 - Forum software by © MyBB