How to JOIN 4 tables

[idfrnffnd]

Currently I have 4 tables. activity, category, pic and useraccount.

In this system, there’s 2 user: applicant and person-in-charge. applicant will login and request for activity (which will be stored in table activity).

then depend on the category, person-in-charge will be assigned(will be stored in table pic with their name and activityID)

activity: `activityID`(PK), `username`, `categoryID`
category: `categoryID`(PK), `categoryName`
pic: `activityID`, `personincharge` (name of the pic)  
useraccount: `name`, `username`

note: personincharge in table pic and name in table useraccount has the same value.

note 2: I will join table category and activity using categoryID to display the categoryname.

My system is going to have user log in by using username. I want to display table of activity record of the person in charge, which means it will only shows activity that the user(the one who logged in) is in charge of.

Currently I am only able to join table category and activity and display it. Here’s what I have:

$sql = "
SELECT * 
  FROM activity a
  JOIN category c 
    ON a.categoryID = c.categoryID
 WHERE a.username= '".$_SESSION['username']."'";

Gone through so many trial and error but still cant find the answer. Thanks for the help in advance.

the flow of my system:
situation 1:
user login -> user request activity -> admin will pick the person in charge of the activity (the name of the pic will be insert into pic table along with the activityID)

situation 2:
pic login -> pic see the list of activity that he/she has been assigned to be in charge of and take action on the request.

**the situation 2 is what i’m currently stuck on. to display only activity he/she is assigned to.

[tp45]

JOIN category c ON a.categoryID = c.categoryID

just repeat the join statement for another table with one of the id’s on the tables,
Like
JOIN category c
ON a.categoryID = c.categoryID
JOIN activity a
ON a.categoryID = c.categoryID

something like this until you finish your join statements
i hope it helps

if it does not work try searching on google on stackoverflow

[idfrnffnd]

it doesn’t work. it wont display list of activity he/she is assigned to.

[tp45]

Try
select *
from table1 a
join table2 b on a.x = b.x
join table3 c on b.y = c.y
join table4 d on a.z = b.w

but try testing by joining 2 tables
after you join the tables debug first like die(print_r($result,true));

[phdr]

You need to first store and use the information correctly. The useraccount table needs id (auto-increment integer primary index) and password (hash) columns. You would store the user_id in the other tables (not the username or the name) and store it in the session variable when the user logs in. The hashed password would be used in the login system.

To find and retrieve activities/applicants that the currently logged in user is the person in charge of, you would mainly query the pic table -

SELECT whatever_you_want
FROM pic -- gets the activity ids the current user is in charge of
WHERE user_id = ?

Note: use a prepared query (the ? place-holder in the above) when supplying (external/unknown) data to an sql query statement. In addition to protecting against sql injection, it simplifies the sql query syntax.

If the above query doesn’t match any row(s), the currently logged in user is not in charge of any activities/applicants.

You would JOIN the other tables with this query depending on what it is you want to do. So, the question is what other information do you want? You probably want the categoryName and either the applicant’s name or username? The above query would become -

SELECT c.categoryName, u.username
FROM pic p -- gets the activity ids the current user is in charge of
JOIN activity a ON p.activityID = a.activityID -- gets the category ids and the applicant user ids
JOIN category c ON a.categoryID = c.categoryID -- gets the category names
JOIN useraccount u ON a.user_id = u.id -- gets the applicant names/usernames
WHERE p.user_id = ?

[idfrnffnd]

hye thank you so much for your answer. in my table, i will display the activityID, CategoryID, subcategory, status of activity and the datetime. so basically, all of these things i will display is in the activity table except for that i need to retrieve categoryName in category table by using category ID.

[idfrnffnd]

is it possible if i want to retrieve it by username instead of the useraccount id? since my queries in other php files are using username not useraccount id. i dont really want to change my other files.