active record query help - Printable Version

active record query help - 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: active record query help (/showthread.php?tid=15857)

active record query help - El Forum - 02-16-2009

[eluser]lawrencesilva[/eluser]
hi guys im having some trouble with a MYSQL query when using CI's ActiveRecord Class

Code:
Active Record code piece:

$this->db->select('customer.id,customer.pid,

               customer.name,

           customer.created_by,

           customer.name as customer_name,

           customer.notes,

           customer_parent.name as parent_customer');

$this->db->like('customer_parent.name', $arr['name']);

$this->db->or_like('customer.name', $arr['name']);

$this->db->or_like('customer.notes', $arr['name']);

$this->db->where('customer.is_parent',0);

$this->db->where('customer.active',1);

Code:
actual piece of MYSQL code output from the code above:

WHERE 

`customer_parent`.`name` LIKE '%%' OR 

`customer`.`name` LIKE '%%' OR 

`customer`.`notes` LIKE '%%' AND

`customer`.`active` = 1  AND `customer`.`is_parent` = 0

Code:
preferred piece of MYSQL code output:

WHERE 

(`customer_parent`.`name` LIKE '%%' OR 

`customer`.`name` LIKE '%%' OR 

`customer`.`notes` LIKE '%%') AND

`customer`.`active` = 1  AND 

`customer`.`is_parent` = 0

i cant seem to put a parenthesis on the 'like' part for me to get my desired output...

any ideas guys? i just wanted to put a parenthesis and bind the lines: $this->db->like('customer_parent.name', $arr['name']);
$this->db->or_like('customer.name', $arr['name']);
$this->db->or_like('customer.notes', $arr['name']);

all together.

Thank you Smile

active record query help - El Forum - 02-16-2009

[eluser]umefarooq[/eluser]
hi if you have only one name in your array than don't use array just pass direct name only. like

$this->db->like('customer_parent.name', 'mike');
$this->db->or_like('customer.name', 'mike');
$this->db->or_like('customer.notes','mike');

try it hope it will work

active record query help - El Forum - 02-16-2009

[eluser]lawrencesilva[/eluser]
it seems like i still have the same issue after trying that, actually the array part is dynamic that comes from a form. Im really having trouble in encapsulating a certain part of my Active Record Object with Parenthesis
(customer like 'foo' OR company like 'bar' OR notes like 'heaps') AND status=1

active record query help - El Forum - 02-16-2009

[eluser]umefarooq[/eluser]
i really prefer to create a query not to use Active Records, just create query in controller and pass that query to your model

$this->db->query($query);

if you have query you can know where im doing wrong

active record query help - El Forum - 02-16-2009

[eluser]lawrencesilva[/eluser]
hmmm that will be a way, but im using a base class for my model, is there any other way to make a parenthesis appear on a regular active record method call? thanks...

active record query help - El Forum - 02-18-2009

[eluser]lawrencesilva[/eluser]
just to bump my question up, any ideas on how to do this using the CI's Active Record Class? thanks! Smile

active record query help - El Forum - 02-18-2009

[eluser]Armchair Samurai[/eluser]
I'm not sure why you're dead set on using AR when it doesn't support the feature you're looking for, but if you must do it the hard way:

Code:
$name = $this->db->escape("%$arr[name]%");

$this->db->select('c.id,customer.pid, c.name, c.created_by, c.name AS customer_name, c.notes, cp.name AS parent_customer');

$this->db->where('(cp.name LIKE $name OR c.name LIKE $name OR c.notes LIKE $name)', NULL, FALSE);

$this->db->where('c.is_parent', 0);

$this->db->where('c.active', 1);