Active record help - Printable Version

Active record 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 help (/showthread.php?tid=32948)

Active record help - El Forum - 08-10-2010

[eluser]bradleyg[/eluser]
Been trying to work this out for a while... just can't get it.

Search param 1 will either be an email or username.
Search param 2 will be an array of regions (integers).

Code:
$param2 = array(2,3);

$this->db->where_in('region', $param2);

$this->db->where('username', $param1);

$this->db->or_where('email', $param1);

I'm not very good with mysql so excuse the incorrect syntax.

Code:
select * from table where username = $param1 OR email = $param1 AND region = 2 OR region = 3;

Any help?

Thanks

Active record help - El Forum - 08-10-2010

[eluser]WanWizard[/eluser]
This AR code produces

Code:
WHERE region IN (2,3) AND username = $param1 OR email = $param2

As there are no brackets in this where clause, it will be true if there is a region and a username match, or an email match.

You probably want

Code:
WHERE region IN (2,3) AND ( username = $param1 OR email = $param2 )

which you can't do with standard AR calls.

A dirty trick is

Code:
$param2 = array(2,3);

$this->db->where_in('region', $param2);

$this->CI->db->where('( 1 =', '1', false); // add a bracket open

$this->db->where('username', $param1);

$this->db->or_where('email', $param1);

$this->CI->db->where('1', '1 )', false); // add a backet close

Other solution

Code:
$this->db->where("(`username` = $param1 OR `email` = $param1)", NULL, FALSE);

but make sure $param1 is properly escaped.

Active record help - El Forum - 08-19-2010

[eluser]far032[/eluser]
[quote author="WanWizard" date="1281487799"]This AR code produces

Code:
WHERE region IN (2,3) AND username = $param1 OR email = $param2

As there are no brackets in this where clause, it will be true if there is a region and a username match, or an email match.

You probably want

Code:
WHERE region IN (2,3) AND ( username = $param1 OR email = $param2 )

which you can't do with standard AR calls.

A dirty trick is

Code:
$param2 = array(2,3);

$this->db->where_in('region', $param2);

$this->CI->db->where('( 1 =', '1', false); // add a bracket open

$this->db->where('username', $param1);

$this->db->or_where('email', $param1);

$this->CI->db->where('1', '1 )', false); // add a backet close

Other solution

Code:
$this->db->where("(`username` = $param1 OR `email` = $param1)", NULL, FALSE);

but make sure $param1 is properly escaped.[/quote]

nice!!!!!!
Thanks a lot.

Active record help - El Forum - 08-19-2010

[eluser]bradleyg[/eluser]
[quote author="WanWizard" date="1281487799"]This AR code produces

Code:
WHERE region IN (2,3) AND username = $param1 OR email = $param2

As there are no brackets in this where clause, it will be true if there is a region and a username match, or an email match.

You probably want

Code:
WHERE region IN (2,3) AND ( username = $param1 OR email = $param2 )

which you can't do with standard AR calls.

A dirty trick is

Code:
$param2 = array(2,3);

$this->db->where_in('region', $param2);

$this->CI->db->where('( 1 =', '1', false); // add a bracket open

$this->db->where('username', $param1);

$this->db->or_where('email', $param1);

$this->CI->db->where('1', '1 )', false); // add a backet close