[ SOLVED ] SQL Like Handling (Active Record class)

[ SOLVED ] SQL Like Handling (Active Record class) - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: [ SOLVED ] SQL Like Handling (Active Record class) (/showthread.php?tid=42382)

Pages: 1 2

[ SOLVED ] SQL Like Handling (Active Record class) - El Forum - 06-06-2011

[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.

[ SOLVED ] SQL Like Handling (Active Record class) - El Forum - 06-06-2011

[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'

[ SOLVED ] SQL Like Handling (Active Record class) - El Forum - 06-06-2011

[eluser]tkaw220[/eluser]
MySQL operator precedence (http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.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.

[ SOLVED ] SQL Like Handling (Active Record class) - El Forum - 06-06-2011

[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.

[ SOLVED ] SQL Like Handling (Active Record class) - El Forum - 06-06-2011

[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.

[ SOLVED ] SQL Like Handling (Active Record class) - El Forum - 06-06-2011

[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...

[ SOLVED ] SQL Like Handling (Active Record class) - El Forum - 06-06-2011

[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

[ SOLVED ] SQL Like Handling (Active Record class) - El Forum - 06-06-2011

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

[ SOLVED ] SQL Like Handling (Active Record class) - El Forum - 06-06-2011

[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.

[ SOLVED ] SQL Like Handling (Active Record class) - El Forum - 06-06-2011

[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.