Posted 31 July 2003 - 08:47 AM
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?
Posted 31 July 2003 - 08:56 AM
To get a user\'s projects you use this query:
"SELECT * FROM tblUser, tblProject, tblUserProjectWHERE tblUser.userID = tblUserProject.userIDAND tblProject.projectID = tblUserProject.projectIDAND 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.
Posted 31 July 2003 - 09:15 AM
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:
but your solution is the way to do it i suppose
Posted 31 July 2003 - 12:07 PM
SELECT * FROM tbluser, tblProject, tblUserProject GROUP BY projectID
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]
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users