How to Retrieve Info From Two Tables

[eluser]Kentonwebdesign[/eluser]
Hi everyone,

I've been doing little browsing around this forum trying to figure a few things out and I'm hoping that someone more experienced can kindly assist. I'm very very new to all this igniter and overall php besides that's in WordPress

In any case I'm building a small app, it's just a spare of the time type of project. Now im sure it's probably not structured well right now but that's later to clean things up. Currently I'm working on with members area and it's nothing big, just a simple user logins, login gets validated and displays the private page. Now on the private page, I'm trying to achieve of displaying content from database. The content sits in 2 or more tables.

My question (I couldn't so far find examples of this) is how and what do I write (controller? model? or both) ?

Now the first table has; user_id, username, password
The second table has; user_id, title, description

The user_id gets added based on a form submission of the user_id, title and description. However since these two are placed in two different tables, how or what do I write to retrieve username, password, title, description based on the logged in user. I'm guessing that since the user logs in using username, I would reference to that somehow so information is displayed for the right person.

Any assistance would help. I've got the overall code written for model, views and controller but only for the login, form submission of stuff into the database and login part.

Thank you in advance and hope to get few examples so I can hopefully learn this stuff.

[eluser]solid9[/eluser]
I suggest you use IonAuth for login system.

Also If you have two tables you need to join them in your query.

example of join,
http://coder9.com/mysql/join/join.php

[eluser]Kentonwebdesign[/eluser]
[quote author="solid9" date="1330271506"]I suggest you use IonAuth for login system.

Also If you have two tables you need to join them in your query.

example of join,
http://coder9.com/mysql/join/join.php[/quote]

Hi,

Thanks for posting. I will take a look at the authentication library see if I can use it.

As in regards to the join of the tables, I've been reading up on that, however, how do I display the content after they are joined based on the username? So if USER A had posts, when he logs in, it should only show USER A posts. If USER B logs in, only USER B post should be visible to him and no one else. Any code example would help

[eluser]pickupman[/eluser]
You really don't need to join the tables. If you need to it would look something like this:

Code:

function get_user($user, $pass)
{
   //Check user login
   $user = $this->db->from('posts')
            ->join('users', 'users.username=posts.username')
            ->where('users.username', $user)
            ->where('users.password', $pass)
            ->get()
            ->result();
   if($user)
   {
      $this->session->set_userdata('user', $user->username); //Save userid to session      
    
      return $user;

   }
   return FALSE;
}

//In controller call get_user() to get their posts

[eluser]Kentonwebdesign[/eluser]
Hi,

Thank you, however that doesn't explain how I can retrieve information from two tables, for a specific user that is logged in at the time.

[eluser]pickupman[/eluser]
[quote author="Kentonwebdesign" date="1330366056"]Hi,

Thank you, however that doesn't explain how I can retrieve information from two tables, for a specific user that is logged in at the time. [/quote]

Yes it does, as it should return all posts for a user given a username/password passed to the getUser() function/method.

Code:

$user = $this->get_user($username, $pass);
var_dump($user);

[eluser]Kentonwebdesign[/eluser]
Hi,

Thanks, so the following;

Code:

$user = $this->get_user($username, $pass);
var_dump($user);

I place that inside the controller or was this in the view? I do apologize, I'm very fresh at this. Much more confused on the point of models, controllers and views.

[eluser]pickupman[/eluser]
For the way I have posted the code above the function/method would go in your controller. Then from within that controller, you can call it by using the:

Code:

$user = $this->get_user($username, $pass);