Jump to content

Archived

This topic is now archived and is closed to further replies.

Pout

relational table

Recommended Posts

well, i really can\'t figure this out, maybe because of the early hour here...

 

ok 2 tables, one \'users\' the other \'projects\'

in a user-config page the user can set his \'favorite projects\', these are shown the first in a drop down list when they give in work they did for a certain project.

 

I think i have to make a new relational table (user-projects) who combines all the project-id and all the user-id and shows wich users has wich projects as his favourites...

 

But how the heck do i do this?

At first sight a make all the column names the project names or id\'s (when a new project is created a new column is autommatically created) and all the rows as the usernames (or id\'s) (also when a new user is created, a new row is created)

Now when a user selects is favourites on the user-config page i set a 1 in the correct user vs project field in that table.

Is this the way to do it?

I don\'t think so since, if i wanna list the user favourites i have to make a strange query, something like:

 

select * from userprojects where user=\"$userid\"

this gives me all 0 or 1 results, and then i have to make some (php) filter wich just lists the project who have a 1?

 

I dunno if this has to be done like this... Can someone help me out?

Share this post


Link to post
Share on other sites

So you have tblUser(userID), tblProject(projectID) and tblUserProject (userProjectID, userID, projectID)?

 

To get a user\'s projects you use this query:

 

"SELECT * FROM tblUser, tblProject, tblUserProject

WHERE tblUser.userID = tblUserProject.userID

AND tblProject.projectID = tblUserProject.projectID

AND user = $user";

 

And that\'s not the only way - you baically need to JOIN the tables because of the many-to-many relationships. There are simple guides to SQL that will teach you these answers.

Share this post


Link to post
Share on other sites

well the way you saying it is completely different but would indeed give me the correct results i think.

 

my tblUserProject was designed different with the userid as rows and projectids as columns.

 

Your\'s is different, it makes 5 rows if one user has 5 favorite projects, and another 4 rows if another user has 4 favorite projects.

 

My idea was to make this smaller and create something like this:

 

---------------project1-----project2-----project3

user1-----------0--------------1------------1------

user2-----------1--------------0------------1------

 

but your solution is the way to do it i suppose

Thx

Share this post


Link to post
Share on other sites

That\'s essentially how the tblUserProject works.

 

Consider this

 

SELECT * FROM tbluser, tblProject, tblUserProject GROUP BY  projectID

 

You\'d get:

 

Project 1

_______

User1

User2

User3

 

Project 2

_______

User1

 

Project 3

_______

User2

User3

 

etc.

 

Your table of ticks and crosses is not normalised - it is coding for nulls.

 

You could output the GROUP BY query into an HTML table:

 

[Project 1][Project 2]

User 1 User1

User2 User 3

 

How about that?[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.