relational table

[Pout]

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?

[michael yare]

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.

[Pout]

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

[michael yare]

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]