i have two tables, costs and users.
Costs table:
id | project | category | type | description | status | value | date_reg | date_edit | edited | id_user | name
Now i need, save id from users on id_user in costs table, how i do it? i save as 1|2|3 or 1,2,3 ? But i need join table users for get some infos, like name, email and others things.
Costs table:
id_user
1|2|3|4 or 1,2,3,4 or anything.
User table:
id | name | password | email | date_reg | level | photo
Because may have many users on the same project(table costs).
I want list users (from user table) infos by id_users on costs table.
I am using codeigniter.
You need a new costs table to each cost:
Projects
-----------------
Project_id
Name
Users
--------------------
User_id
Username
Costs
----------------
Cost_id
Amount
Project_id
User_id
..
To find projects costs per project per user:
Select sum(c.Amount) as total_cost, count(*) as num_costs, p.Project_id,p.Name as project_name, u.user_id, u.Username
From costs c inner join project p on c.Project_id=c.Project_id
Inner join users u
On c.User_id=u.User_id
Group by p.Project_Id, u.user_id
For costs per user
Select sum(c.Amount) as total_cost, count(*) as num_costs, u.user_id, u.Username
From costs c
Inner join users u
On c.User_id=u.User_id
Group by u.user_id
