Active record help

[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

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

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

[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

Other solution

Code:

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

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

Perfect, thanks. I think I will go for the 2nd option. Cheers!