three table join in codeigniter

[eluser]freshigniter[/eluser]
1 > table:registration
-----------------------------------
regid | indtype | name |password
----------------------------------
1 |1,2 | obama | 12rt56
----------------------------------
2 |2,3 | pinto | 14sd35
----------------------------------

2 > table:users
-------------------------------------
id | registeredid | name |password
-------------------------------------
1 | 1 | obama | 12rt56
-------------------------------------
2 | 2 | pinto | 14sd35
-------------------------------------

3 > table:industrytype
---------------------------
iid | name |
---------------------------
1 |computers |
---------------------------
2 |electronics|
---------------------------
3 |footwears|
---------------------------
*******************************************************
flow of events->registration->login.

1)
a new user register himself, his name,password and type of industry he selects from
drop down[example: name=computers,value=1(only value of industry type)] will be stored in the table registration.

2)
the drop-down options to select in the registration form[view] are coming through the table 3 [industry type].

3)
the table 2[users] used to login.
during registration, the same values(as in registration table) will be passed to table 2 [table:users].
are except industry type(indtype)

WHAT I WANT

1) WHEN A USER logins [say obama]
in his landing page[named as "normaluser" in views] he should be shown as "your industries are computers,electronic"


2)WHEN A USER logins [say pinto]
in his landing page he should be shown as "your industries are electronics,footwears".
thats it. :cheese:

WHAT I HAVE

1)a controller named "users"
2)a model named "users_model"
3)a view named "normal user"

help me please.

[eluser]psychoder[/eluser]
where's the code of your model?

[eluser]astroanu[/eluser]
Are you trying to save what the user selected from the list box(which is driven from the table) on to a database?

[eluser]boltsabre[/eluser]
It's not a good idea to "copy" data from one table to the next (ie, your registration and user table), just have the one table... otherwise you're just opening yourself up to a world of hurt in regards to data corruption.

Just have a user table, and if you need to have a "approved" column, and once the user is approved set their status to 1 (or some such mechanism).

It's also a bad idea to have "indtype" containing a string like that... what if later you want to search for all users who have indtype 2... you'll have to perform very time/processing power intensive sql searches.

So... just one table for your user, and one table for your indtype (like you have, 2 columns, id and name).
And a third table (indtype_user)with three columns (id, indtype_id, user_id).
When a user creates an account and selects and indtype, insert it into this table. Id will auto increment, intype is the id from table industrytype, and user_id is.. yep, the user id.

That way when a user logs in you can either do a join, or 2 db queries. 1st query to get the user id where name and password match, and the second query to select indtype from indtype_user table where user_id is equal to the id from your first query.

That make sense???

[eluser]freshigniter[/eluser]
[quote author="psychoder" date="1334921296"]where's the code of your model?[/quote]



function Getcategory()
{
$this->db->select('it.name,r.indtype,u.reg_id');
$this->db->from('users u');
$this->db->join('users u','registration r','u.reg_id=r.regid');
$this->db->join('registration r','ind_type it','r.ind_type=it.id');
$query = $this->db->get();
return $query->result();
}

[eluser]freshigniter[/eluser]
[quote author="boltsabre" date="1334921657"]

So... just one table for your user, and one table for your indtype (like you have, 2 columns, id and name).
And a third table (indtype_user)with three columns (id, indtype_id, user_id).
When a user creates an account and selects and indtype, insert it into this table. Id will auto increment, intype is the id from table industrytype, and user_id is.. yep, the user id.

That way when a user logs in you can either do a join, or 2 db queries. 1st query to get the user id where name and password match, and the second query to select indtype from indtype_user table where user_id is equal to the id from your first query.

That make sense???[/quote]

did you meant user_id = registered id ?

[eluser]boltsabre[/eluser]

Just have a user table, and if you need to have a “approved” column, and once the user is approved set their status to 1 (or some such mechanism).

No need to have a "register id", just a registered "status" (0 for unregistered/incomplete registration, and 1 for fully registered). Sorry, I called it "approved" above, should have been "registered"

So your query would be something like:
SELECT id FROM users WHERE name = $name AND password = $password AND registered = 1;

This gets your user id, now you can select all "intype" from the "indtype_user" table where the user id's match.

[eluser]boltsabre[/eluser]
Or maybe I'm missing something here... what is the purpose of have a user table and a registered table in your first post???

[eluser]freshigniter[/eluser]
[quote author="astroanu" date="1334921444"]Are you trying to save what the user selected from the list box(which is driven from the table) on to a database?[/quote]

yes...and display the name associated with that number when the registered user logs in.

[eluser]freshigniter[/eluser]
[quote author="boltsabre" date="1334926497"]Or maybe I'm missing something here... what is the purpose of have a user table and a registered table in your first post???[/quote]

am glad you noticed. the reason to have a separate user table
a registered user can create a sub user, without going to registration form.

when a user registers, he need to be approved by a super-admin, which will be done in "user"
table through a column "status". i haven't showed that in my first post.