How do I write this SQL statement ?

How do I write this SQL statement ? - 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: How do I write this SQL statement ? (/showthread.php?tid=15856)

How do I write this SQL statement ? - El Forum - 02-16-2009

[eluser]Unknown[/eluser]
Hi everyone,

I have this SQL statement :

Select * from `abc_table` abc
where abc.field1 = 1 and
(
abc.field2 = 2 and
(abc.field = 3 or abc.field4 = 4)
);

If don't use $this->db->query() statement, how do I write this statement (by $this->db->where...)

Thanks for helping.

How do I write this SQL statement ? - El Forum - 02-17-2009

[eluser]sybrex[/eluser]
Have a look at this post

How do I write this SQL statement ? - El Forum - 02-17-2009

[eluser]whobutsb[/eluser]
[quote author="CuongVM" date="1234867285"]Hi everyone,

I have this SQL statement :

Select * from `abc_table` abc
where abc.field1 = 1 and
(
abc.field2 = 2 and
(abc.field = 3 or abc.field4 = 4)
);

If don't use $this->db->query() statement, how do I write this statement (by $this->db->where...)

Thanks for helping.[/quote]

Code:
$this->db->select('*');

$this->db->where('abc.field1', 1);

$this->db->where('abc.field2', 2);

$this->db->where('(abc.field3 = 3 OR abc.field4 = 4)');

$query = $this->db->get('abc_table')

return $query->result();

You can either write your where statement in key/value pairs like I did with fields 1 and 2 or you can write your entire where statement right in to the $this->db->where() method. Either way works just fine.

Steve

How do I write this SQL statement ? - El Forum - 02-17-2009

[eluser]bEz[/eluser]
[quote author="whobutsb" date="1234907468"]

Code:
$this->db->select('*');

$this->db->where('abc.field1', 1);

$this->db->where('abc.field2', 2);

$this->db->where('(abc.field3 = 3 OR abc.field4 = 4)');

$query = $this->db->get('abc_table')

return $query->result();

Code:
Select * 

from `abc_table` abc 

where abc.field1 = 1 

    and (

        abc.field2 = 2 

        and (

            abc.field = 3 or abc.field4 = 4

        )

    );

How do I write this SQL statement ? - El Forum - 02-17-2009

[eluser]whobutsb[/eluser]
For Nested Clauses like that I would just write it as a string in to the $this->db->where() method. I don't know how to write it any other way.

How do I write this SQL statement ? - El Forum - 02-17-2009

[eluser]bEz[/eluser]
[quote author="whobutsb" date="1234929305"]For Nested Clauses like that I would just write it as a string in to the $this->db->where() method. I don't know how to write it any other way.[/quote]
Understood, a custom string would be the natural choice for such a short (overall two clause) string.

The method you provided would have required the second and third where portions to be combined with an "AND".

Code:
$this->db->where('abc.field2 = 2 AND (abc.field3 = 3 OR abc.field4 = 4)');

Code:
$this->db->where('(abc.field2 = 2 AND (abc.field3 = 3 OR abc.field4 = 4))');

notice the extra set of parenthesis

How do I write this SQL statement ? - El Forum - 02-18-2009

[eluser]Unknown[/eluser]
Thank you very much.