Return more than one row from a specific table in database

I am currently working on a private messaging system. Right now I'm coding the inbox and have put 2 rows of temporary data in my messages table to test the inbox is working correctly.

I am trying to return all rows where this query is true:

<?php
class Messages_model extends CI_Model {


    public function inbox()
    {
        $user_id = $this->session->userdata('user_id');
        $username = $this->session->userdata('username');

        return $this->db->query("SELECT * FROM messages WHERE to_id = $user_id AND to_user = '$username'"); //to_id = id of currently logged in and to_user = username
    }
}

Here's my controller. I'm currently only having one row returned even though both rows should be returned. I have created both rows in the db with the to_id and to_user equal to my session data. So they should both be returned not just one. When I use row_array the first row is returned as explained in CI user guide. When I use result_array() still only one row is returned.

What I want to do is create a table in html with a few columns and 1 row for title of each column e.g. subject, status, date. Then the row underneath will be the actual subject data and status of the message. Then I want to run a while or foreach loop that will echo that empty row each time a row is returned from the messages table for the inbox.

Here is my controller:

<?php
class Messages extends Public_Controller {
    public function  __construct() {
        parent::__construct();

        $this->load->model('messages_model');

    }

    public function inbox()
    {


        $query = $this->messages_model->inbox(); // return from model which is basically a row from messages table
        if ($query->num_rows() != 0) // if rows returned is equal to 1

        {       
        foreach ($query->result_array() as $row) // give array variable the value row_array

        // grab specific elements from row and assign to variables
        $row['id'];
        $row['to_id'];
        $row['to_user'];
        $row['from_id'];
        $row['from_user'];
        $row['time_sent'];
        $row['subject'];
        $row['message'];
        $row['opened'];
        $row['replied'];

        $this->load->view('messages/inbox', $row);
        }

       else
        {
            echo "You have 0 messages in your inbox";
        }
    }
}

I hope I was clear with explai开发者_StackOverflow社区n what I'm trying to do. Thanks in advance.

You seem to be missing braces in your for loop:

 foreach ($query->result_array() as $row) // give array variable the value row_array
 {
    // grab specific elements from row and assign to variables
    $row['id'];
    $row['to_id'];
    $row['to_user'];
    $row['from_id'];
    $row['from_user'];
    $row['time_sent'];
    $row['subject'];
    $row['message'];
    $row['opened'];
    $row['replied'];
 }

A for loop only applies to the first line unless you surround the body in braces.

First, bear in mind that I am not very familiar with CodeIgniter. Your problem, however, is fairly generic PHP:

foreach ($query->result_array() as $row) // give array variable the value row_array

    // grab specific elements from row and assign to variables
    $row['id'];
    $row['to_id'];
    $row['to_user'];
    $row['from_id'];
    $row['from_user'];
    $row['time_sent'];
    $row['subject'];
    $row['message'];
    $row['opened'];
    $row['replied'];

    $this->load->view('messages/inbox', $row);
}

Every time you encounter a row, you're calling $this->load->view(). I believe you should only be calling the view once, with all the data you want to pass. You probably want to do something like this:

$data = array();
foreach ($query->result_array() as $row) {
    $data[] = array (
        'id' => $row['id'],
        'to_id' => $row['to_id'],
        'to_user' => $row['to_user'],
        'from_id' => $row['from_id'],
        'from_user' => $row['from_user'],
        'time_sent' => $row['time_sent'],
        'subject' => $row['subject'],
        'message' => $row['message'],
        'opened' => $row['opened'],
        'replied' => $row['replied']
    );
}
$this->load->view('messages/inbox', $data);

You would then need to process multiple messages in your view.