[SOLVED] How to access my 'data' variable in the view when it contains a DB result I do NOT want to 'foreach loop' throu

[eluser]boltsabre[/eluser]
Hi guys, I'm kinda stumped on this one, been trying for hours but to no avail.

I have a 2 tables, and am returning a SQL statement from my model which involves an INNER JOIN, no worries there, I'm getting the expected results, but here it is anyway (it's a bit messy, I have not used Alias names for the tables names):

Code:

public function getStarsAndReviewCounts(){
        $SQL = "SELECT  travel_insurance_reviews.insurance_id AS id,
                        COUNT(travel_insurance_reviews.id) AS idCount,
                        ROUND(AVG(travel_insurance_reviews.stars),2) AS avgStars,
                        travel_insurance_details.name AS name,
                        travel_insurance_details.url AS url,
                        travel_insurance_details.imageNameSmall AS imageNameSmall
                FROM travel_insurance_reviews
                INNER JOIN travel_insurance_details
                ON travel_insurance_reviews.insurance_id = travel_insurance_details.id
                GROUP BY travel_insurance_reviews.insurance_id";
        $runSQL = $this->db->query($SQL);
        if ($runSQL){
            return $runSQL;
        }else{
            return false;
        }
    }

The problem is, in my view I don't want to just echo through this SQL result using a foreach loop, I want to be able to independantly access each row of this result in different parts on the html page (due to design issues and what not, I want to display the first row left aligned on the page, then have some html code/paragraph text, then access the second row, and display it right aligned, etc, etc.

And that's where I'm running into major issues in my controller... how do I actually load this SQL result into my $data variable (the one that gets passed to the view so that you can access PHP variables in the view) so that each element of the SQL result is loaded as an indpendant variable?

Note
The SQL statement returns the Insurance Companys Name:

Code:

travel_insurance_details.name AS name,

And I'm happy to use this as a variable prefix, so that my varialbes are like such:

Code:

$essentialTravel_stars = 3.25;
$essentialTravel_url = http://www.essential...;
$globalTraveller_stars = 4.18;
$globalTraveller_url = http://www.global...;
and so on...

In my controller I've tried

Code:

function index(){
        $this->load->model('travel_insurance_model');
        $starsAndReview = $this->travel_insurance_model->getStarsAndReviewCounts();

        if($starsAndReview){
            //get the db result and put it into the $data array to pass to the view.
            foreach ($starsAndReview->result() as $row){
                $id = $row->id;
                $reviewCount = $row->idCount;
                $stars = $row->avgStars;
                $url = $row->url;
                $imageNameS = $row->imageNameSmall;
                $name = $row->name;
                
                // prefix each variable with the insurance name so it can be accessed in the view.
                $data = array(
                    'name' => $name
                    $name.'_id' => $id,
                    $name.'_reviewCount' => $reviewCount,
                    $name.'_stars' => $stars,
                    $name.'_url' => $url,
                    $name.'_imageNameS' => $imageNameS
                );
            }    
            $this->load->view('australian-travel-insurance-view', $data);        
        }else{
            return false;
        }
    }

But ovbioulsy this overrides the $data variable with each iteration of the foreach loop, resulting in only one row of the SQL result being passed to the view. I've also tried a bunch of other stuff and options, but all to no avail (I won't paste it here because: 1. I don't want this post being a mile long full of failures, and 2. I've don't have them anymore anyway as I've deleted them as they've failed and I've tried another option.

I'm sure there is something fundamentally simple I'm missing, this must happen often enough that I'm guessing there is a simple solution... and I'm all ears.

Really hoping someone can point me in the right direction. Thanks a million in advance guys!!!

[eluser]theprodigy[/eluser]
Take a look into the ->row() function. The docs can be found here

Calling it by itself will return 1st row, but it allows you to pass in a number for a specific row to return.

[eluser]boltsabre[/eluser]
I love you, you are a genius!

Now my controller looks like (pretty sweet and simple):

Code:

function index(){
        $this->load->model('travel_insurance_model');
        $data['starsAndReview'] = $this->travel_insurance_model->getStarsAndReviewCounts();

        $this->load->view('australian-travel-insurance-view', $data);        
    }

And in the view
(Load a particular travel insurance company using ->row())

Code:

<?php $globe = $starsAndReview->row(2); ?>

(load its image path, where imagePath() is pre-defined elsewhere in my application)

Code:

<img src="....insert code below...."/>

Code:

<?php echo imagePath().$globe->imageNameSmall ?>

Update: Seems you cannot mix html and php syntax here in the forum, my line above was missing half of the echo statement with I had both languages combined and I had to break it into 2 code snippets.

Thanks a million, so very simple (as I knew it would be)!!!