Codeigniter 1-many db query on two tables which call for nested loops

Question

I've read through a couple posts which are really close to the same question I am about to ask, but I'm still not grasping the MVC way. One example was close and titled "Codeigniter db query on two tables", but I'm getting the "Undefined property" errors which reinforces that fact I'm not calling objects properly. I would prefer not to use DataMapper simply because if I need to go out and get additional classes, then I might just go back to the procedural coding route. I was just a little excited on understanding how this framework implementation works.

I have two tables, lets call one "apps" and the other would be "mappings". "Apps" are applications which use apache as a reverse proxy configuration like this: ProxyPass /mapping/ https:/// ProxyPassReverse /mapping/ https:///

Some of the apps could request multiple reverse proxy mappings, so its a 1-many design.

My objective is to use a select statement for the apps table then print out every column I desire from that table: for example, I currently have two application defined in my "apps" table, in which one of the applications has two reverse proxy mappings,and the other has one. The prxypass lines below the data from the apps table would be coming from the apps_mapping table.

App: App ABC
Owner: ABC Team
Date Requested: 2013-10-27
ProxyPass /ABC-Resource/ https://<to-some-internal-URL>/
ProxyPassReverse /ABC-resource/ https://<to-some-internal-URL>/

App: App XYZ
Owner: XYZ Team
Date Requested: 2013-10-27
ProxyPass /XYZ-mapping/ https://<to-some-internal-URL>/
ProxyPassReverse /XYZ-mapping/ https://<to-some-internal-URL2>/
ProxyPass /anothermapp/ https://<to-some-internal-URL>/
ProxyPassReverse /anothermapping/ https://<to-some-internal-URL2>/

I've tried to model my first attempt of using CodeIgniter by way of modifying their tutorial provided by the CodeIgniter example in their user_guide.

Now what I haveis. Model Class:

<?php
class Prp_model extends CI_Model {


        public function __construct()
        {
                $this->load->database();
        }

//GET APP THE ROWs FROM THE apps TABLE
        public function get_appsUsingPrp()
        {
                $query = $this->db->get('apps');
                return $query->result_array();

        }

//GET THE MAPPINGS FROM THE apps_mappings TABLE
//FOR EACH APP BY WAY OF UNIQUE id IN THE appsTABLE
        public function get_prpMappings($id = FALSE)
        {
                $query = $this->db->get_where('apps_mappings', array('resourceID' => $id));
                return $query->result_array();
        }
?>

for the above class, instead of using a return function on get_prpMappings() should I be putting it in a variable? Even then, how would one supply a value for the $id in the code called int he views page?

Controller Class

<?php
class Prp extends CI_Controller {

        public function __construct()
        {
                parent::__construct();
                $this->load->model('prp_model');
        }

        public function index()
        {
                $data['apps'] = $this->prp_model->get_prp();
                $data['mappings'] = $this->prp_model->get_prpMappings($id);
                $data['title'] = 'PRP archive';
                $this->load->view('templates/header', $data);
                $this->load->view('prp/index', $data);
                $this->load->view('templates/footer');

        }

        public function view($id)
        {
                $data['apps'] = $this->prp_model->get_prp();
                $data['mappings'] = $this->prp_model->get_prpMappings($id);
        }


}

View

<?php
    print_r($apps);
    print_r($mappings);

    foreach ($apps as $details): ?>

        <div id="main">
            <ul>
             <?php echo "<li>App Name: ".$details['app_name'] ?>
             <?php echo "<li>EPRID: ".$details['EPRID'] ?>
             <?php echo "<li>Requestor: ".$details['requestor'] ?>
             <?php echo "<li>Team PDL: ".$details['PDL'] ?>

                    <?php foreach ($mappings as $map): ?>
                    <?php echo "<li>ProxyPass /".$map['resource']."/ ".$map['destURL'] ?>
            <?php echo "<li>ProxyPassReverse /".$map['resource']."/ ".$map['destURL'] ?>
                    <?php endforeach ?>

            </ul>

        </div>
        <hr />




<?php endforeach ?>

?>

Getting down to my confusion.... Should I be doing all the foreach looping in the model and/or the controller classes if I'm not going to setup the DataMapper Class?

Is there a way to call the get_prpMappings($id) function defined in the model class directly in the view? The only way for that function to work for me, is when the id of the app row is provided when iterating through the rows.

I tried putting $this->prp_model->get_prpMappings($id); in the view, but the view page doesn't seem to identify the class.

Lastly.. By the looks of it, it appears I would be doing more coding with a framework (such as CodeIgniter or Zend), then it would be to just write everything from scratch. But I'm under the impression the framework is to provide convenience down the road of additional coding. I'm just not seeing it. Please help.

UPDATED! I hope I understand this.

I have modified my logic to express the output desired. Would you be able to tell me if it is an inefficient way? Here is the logic: MODEL CLASS:

<?php
class Prp_model extends CI_Model {


        public function __construct()
        {
                $this->load->database();
        }

//GET APP THE ROWs FROM THE sharedITG TABLE
        public function get_appsUsingPrp()
        {
                $query = $this->db->get('sharedITG');
                return $query->result_array();

        }

//GET THE MAPPINGS FROM THE sharedITG_mappings TABLE
//FOR EACH APP BY WAY OF UNIQUE id IN THE shareITG TABLE
        public function get_prpMappings($id = FALSE)
        {
                $this->db->select('resourceID, resource, destURL');
                $query = $this->db->get_where('sharedITG_mappings', array('resourceID' => $id));
                return $query->result_array();
        }

        public function get_prp()
        {

                //loop through all apps
                foreach ($this->get_appsUsingPrp() as $app)
                {
                        //print_r($app);
                        foreach ($this->get_prpMappings($app['id']) as $mappings)
                        {
                                 $map[] = $mappings;
                        }

                }
                return $map;
        }
}

Controller Class

<?php
class Prp extends CI_Controller {

        public function __construct()
        {
                parent::__construct();
                $this->load->model('prp_model');
        }

        public function index()
        {
                $data['mappings'] = $this->prp_model->get_prp();
                $data['apps'] = $this->prp_model->get_appsUsingPrp();
                $data['title'] = 'PRP archive';
                $this->load->view('templates/header', $data);
                $this->load->view('prp/index', $data);
                $this->load->view('templates/footer');

        }

        public function view($id)
        {
                $data['apps'] = $this->prp_model->get_appsUsingPrp();
                $data['mappings'] = $this->prp_model->get_prp();

        }

}

VIEW code:

<?php
//print_r($apps);
//print_r($mappings);

foreach ($apps as $details): ?>

    <div id="main">
        <ul>
         <?php echo "<li>App Name: ".$details['app_name'] ?>
         <?php echo "<li>EPRID: ".$details['EPRID'] ?>
         <?php echo "<li>Requestor: ".$details['requestor'] ?>
         <?php echo "<li>Team PDL: ".$details['PDL'] ?>

        <?php
                foreach ($mappings as $map)
                {
                        if ($details['id'] == $map['resourceID'])
                        {
                                echo "<li>ProxyPass /".$map['resource']."/ ".$map['destURL'];
                                echo "<li>ProxyPassReverse /".$map['resource']."/ ".$map['destURL'];
                        }
                }

        ?>
        </ul>

    </div>
<hr />

<?php endforeach ?>

H-man · Accepted Answer · 2014-01-08 04:30:37Z

1

So after further research, I found a forum that gave me a pretty decent example. I'm not sure if its the most optimal way to do this, but it worked for me and still (kind of) maintained the MVC model for Code igniter. The foreach in the function below is what I added. It appears it loops through the current result and obtains all rows for the second table that has a relationship between Table2ID and IDFromTable. Then adds the results by way of $result{$key}.

public function get_resuts()
    {

            $query = $this->db->get('table1');

            $result = $query->result_array();

            foreach ( $result as $key => $row )
            {

                    $query = $this->db->get_where('Table2', array('Table2ID' => $row['IDFromTable']));
                    $row['DesiredFieldFromTable@'] = $query->result_array();
                    $result[$key] = $row;
            }

            return $result;
    }

Then its available in the view by looping through the second array with the content from the foreach results in the get_resuts() function. I'm interested in learning of other ways to do this too.

edited Jan 8, 2014 at 4:30

answered Dec 21, 2013 at 13:47

H-man

1731 gold badge2 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

H-man Over a year ago

removed the reference to the other forum, and tried to summaries my findings.

Collectives™ on Stack Overflow

Codeigniter 1-many db query on two tables which call for nested loops

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related