passing sql query results from controller into view with code igniter

Question

So I'm having this problem, it should be pretty simple, but I don't know why I can't figure it out. I"m new to the whole idea of MVC, and I'm trying to pass a database query from my controller into a view and display the results in the view. The way I'm doing it now says "undefined variable, sql" when I load the view. This is what I have:

CONTROLLER

function make_login()
{
    //Select list of departments for dropdown
    $this->load->database();
    $sql = $this->db->query('SELECT departmentName FROM department ORDER BY departmentName ASC');



    $this->load->view('siteheader.php');
    $this->load->view('makelogin.php', $sql->result_array());
    $this->load->view('sitefooter.php');
}

VIEW

<?php
 foreach($sql->result_array() as $row)
    {
        echo $row['departmentName'];
    }
    ?>

(If I just echo it out in the controller, it displays the results)

Any help would be awesome... THANKS!

Answer 1

few tips ~

your make_login should be in a model. your controller will look something like this:

function make_login
{
    $this->load->model('login_model'); // whatever you call it

    $data['departments'] =  $this->login_model->get_departments();

    /* note - you don't need to have the extension when it's a php file */
    $this->load->view('siteheader');
    $this->load->view('makelogin', $data);
    $this->load->view('sitefooter');
}

now in your model, have something like:

function get_departments()
{
    $sql = $this->db->query('SELECT departmentName FROM department ORDER BY departmentName ASC');
    return $sql->result();
    /* you simply return the results as an object
     * also note you can use the ActiveRecord class for this...might make it easier
     */
}

and finally, your view:

<?php
    foreach($departments as $store)
    {
        echo  $store->name . '<br />'; // your fields/whatever you want to output.
    }
?>

Answer 2

The SQL query should be done in the model.

Cheap mnemonic device: the D in model = database.

In the Controller, you assign part of the $data array to the results of the query:

$this->load->model('blog_model');
$data['posts'] = $this->blog_model->getPosts();

// Load the view with the data
$this->load->view('blog', $data);

In the Model, you do the actual query:

public function getPosts()
{
    // Method chaining supported in PHP 5
    $this->db->select('*')->from('posts');

    // Assign the query object to a variable
    $query = $this->db->get();

    // We don't want to return an empty result, so let's handle that error somehow
    if (!$query->num_rows() > 0) {
        die("There are no posts in the database.");
    }

    // Make a new array for the posts
    $posts = array();

    // For the purposes of this example, we'll only return the title
    foreach ($query->result() as $row) {
        $posts[$row->id] = $row->title;
    }

    // Pass the result back to the controller
    return $posts;
}

Now, in the view, each element of $data will be its own variable:

    <div class="post">
    <?php foreach ($posts as $id => $title) : ?>
        <h1 class="post-title"><?php echo $title; ?> (ID: <?php echo $id; ?>)</h1>
        <p class="post-content">
        .......
        </p>
        <?php endforeach; ?>
    </div>

That's it!