Writing a for loop in Active Record (4 lines)

Writing a for loop in Active Record (4 lines) - 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: Writing a for loop in Active Record (4 lines) (/showthread.php?tid=58000)

Writing a for loop in Active Record (4 lines) - El Forum - 05-04-2013

[eluser]alvaroeesti[/eluser]
Hello,

The problem that I am having is in translating the Loop, so that it renders this syntax

Code:
AND (`x`.`id` =  '37' OR `x`.`id` =  '31' OR `x`.`id` =  '36')

being x the city table.

And here is the active record.

Code:
$this->db->select('asset_id, region_name, etc etc);

  $this->db->from('asset a');

  $this->db->join('region y', 'y.id = a.region_fk');

  $this->db->join('city x', 'x.id = a.city_fk');

  $this->db->join('seller s', 's.seller_id = a.seller_fk');

  $this->db->join('object_type ot', 'ot.type_id = a.object_type_fk');

  $this->db->join('duration du', 'du.duration_id = a.duration_fk');

  $this->db->join('transaction_type tt', 'tt.transtype_id = a.transaction_type_fk');

  $this->db->where('y.id', $regions);

                $i = 0;

                $nc = sizeof($cities); 

                if ($nc > 1)

                {

                   $this->db->where('x.id', $cities[$i]);  

                   for($i = 1; $i <sizeof($cities); $i++)

                    {

                      $this->db->or_where('x.id', $cities[$i]);

                    }    

                }

                else {

                    $this->db->where('x.id', $cities[$i]);

                     }

                ...irrelevant code

I need to equal it like I said, to this sql rendering

Code:
AND (`x`.`id` =  '37' OR `x`.`id` =  '31' OR `x`.`id` =  '36')

and what it renders instead is

Code:
AND `x`.`id` =  '31'

OR `x`.`id` =  '36'

OR `x`.`id` =  '37'

which is not quite the same, because of the parentheses etc

Writing a for loop in Active Record (4 lines) - El Forum - 05-04-2013

[eluser]TheFuzzy0ne[/eluser]
I think WHERE IN would be more suitable:

Code:
$this->db->from('asset a')

         ->join('region y', 'y.id = a.region_fk')

         ->join('city x', 'x.id = a.city_fk')

         ->join('seller s', 's.seller_id = a.seller_fk')

         ->join('object_type ot', 'ot.type_id = a.object_type_fk')

         ->join('duration du', 'du.duration_id = a.duration_fk')

         ->join('transaction_type tt', 'tt.transtype_id = a.transaction_type_fk')

         ->where('y.id', $regions)

         ->where_in('x.id', $cities);

Writing a for loop in Active Record (4 lines) - El Forum - 05-04-2013

[eluser]alvaroeesti[/eluser]

Look at that!! awesome man!

I have been a full day wrestling because I did not know that such where_in existed in active record, (giving me lots of dead times not making progresses)

that was such a straightforward solution! bless you man!

I m trying to finish the web and when I do, and get it working I ll definitively need someone who takes it from there and adds more stuff. I check your web now,

kind regards and thank you very much

Álvaro