Combining AND & OR in MySQL Queries

Combining AND & OR in MySQL Queries - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Combining AND & OR in MySQL Queries (/showthread.php?tid=12044)

Combining AND & OR in MySQL Queries - El Forum - 10-03-2008

[eluser]sideshowbob[/eluser]
Hi Everyone,

I have what I thought was a simple question but I'm struggling to find the answer...

Within CI how do I create a query that produces the following :

Code:
SELECT * FROM Persons WHERE

LastName='Svendson'

AND (FirstName='Tove' OR FirstName='Ola')

There can be many LastNames and Firstnames.

I can create the query easily without the brackets but then it is not the same query.

Thanks!
Bob

Combining AND & OR in MySQL Queries - El Forum - 10-03-2008

[eluser]jcopling[/eluser]
I am yet to find a way to accomplish this using strictly the CI framework so I would accomplish this by doing the following:

Code:
$this->db->from("Persons");

$this->db->where("LastName","Svendson");

$this->db->where("(FirstName='Tove' OR FirstName='Ola')");

Combining AND & OR in MySQL Queries - El Forum - 10-03-2008

[eluser]gRoberts[/eluser]
You could use the "in" method.

i.e.

Code:
SELECT * FROM Persons WHERE

LastName='Svendson'

AND FirstName in ('Tove','Ola')

Combining AND & OR in MySQL Queries - El Forum - 10-03-2008

[eluser]sideshowbob[/eluser]
Thanks a lot, the SQL "in" statement was the key.

Here's my solution:

Code:
$arrFirstnames = array('Tove','Ola');

$this->db->from('Persons');

$this->db->where('LastName','Svendson');

$this->db->or_where_in('FirstName',$arrFirstnames);

Produces :

Code:
SELECT * FROM Persons

WHERE 'FirstName' IN ('Tove', 'Ola') AND 'LastName' = 'Svendson'

Bob