How do I link the auto-incremented id from one table to the id in another table?

Question

I'm currently working on my private messaging system for my website and so far some of what I have to do is making sense.

I've figured I have to use the power of mysql join but have a few questions.. In my model I have started coding for my message inbox and have the following query:

"SELECT * FROM messages WHERE from_id = '$id'";

$id is basically the auto-incremented id from my users table and from_id is the same thing but in my messages table.

What I want to know is how is the message table suppose to get the from_id? I know that the from_id from the messages table and the auto-incremented id from the users table are what I will use to link both tables.. but I'm slightly stuck how to go about this.

I would rather use id's to identify the sender and receiver of a message than their username but I'm quite confused how to go about setting this up. I'm quite certain I can do it once I have an understanding of it.

Answer 1

depending on what auth library you are using, you will need something like this in CI:

view_messages()
{
    //this check could be done in the constructor 
    if($this->auth->logged_in()==TRUE)
    {
        $userID=$this->auth->user_data('userID');
    }
    else
    {
        redirect('not_logged_in');
    }

    $this->load->model('message');
    $messages = $this->message->get_messages($userID);
    // do what you want with messages.
    //print_r($messages);
}

your messages model:

function get_messages($userID=null)
{
    $this->db->where('userID', $userID);
    $data = $this->db->get('tblMessages');
    return $data->result();
}