Extract values from lookup tables and display

[guoxin]

Hi All,

 

I've encountered a problem which i find for answers but to no avail and i hope you all can help. I've got 4 tables namely, User_Login, Admin_Login, Projects and Mapping.

 

User_Login

-user_id (PK) INTEGER NOT NULL AUTO INCREMENT

-user_username VARCHAR(30) NOT NULL

-user_password VARCHAR(30) NOT NULL

 

Admin_Login

-admin_username VARCHAR(30) NOT NULL

-admin_password VARCHAR(30) NOT NULL

 

Projects

-project_id (PK) INTEGER NOT NULL AUTO INCREMENT

-project_name VARCHAR(30) NOT NULL

 

Mapping

-user_id (FK)

-project (FK)

 

Mapping is used as a lookup table when the administrator want to assign projects to the newly created user so we use the user_id from User_Login and project_id form Projects as a reference.

 

But here comes the problem, i execute the following command, $sql = "SELECT project_name FROM Projects, Mapping, User_Login WHERE Mapping.user_id=User_Login.user_id AND Mapping.project_id=Projects.project_id"; so the scenario is when a particular user is logged in, the projects will be displayed in a dropdown list where user can select which project he/she want to submit. But the dropdown list won't show up anything so i'm really asking you all for help. I'll be really grateful Thanks.

 

 

[aschk]

Assuming the following:

1) Your table names are correct (i.e. Capital starting letter)

2) There is some sample data, and the mapping table is filled.

3) A particular user has logged into your system (and authenticated successfully).

 

I advise that you output the contents of mysql_error() to see what error you are getting (if any).

And then try this query:

SELECT p.project_name
FROM Projects p
JOIN Mapping m ON m.project = p.project_id
JOIN User_Login u ON m.user_id = u.user_id
WHERE u.user_id = '<user id here>';

 

There are some things in above you should take INSTANT note of.

1) The JOIN column for the mapping table is called "project" and NOT "project_id" (according to the information you have supplied).

2) Assuming a particular user has logged in, they will have an id! And thus should probably be included in the WHERE clause.

 

 

note: I would advise that you make a boolean column in the User_Login table called "Admin" instead of a separate table for admins. This will normalise your data structure.

[guoxin]

Hi,

There's a spelling error which i should apologise for as it should be "project_id" in the Mapping table instead of "project". But i have a problem in the WHERE statement cause you put u.user_id='<user_id here>'. I suppose it should be an integer in the <user_id here> and i change the code to u.user_id=1 but it appears as "mysql syntax error". So what should it be in the <user_id here>? Your help is greatly appreciated.