Pout Posted July 31, 2003 Share Posted July 31, 2003 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? Quote Link to comment https://forums.phpfreaks.com/topic/806-relational-table/ Share on other sites More sharing options...
michael yare Posted July 31, 2003 Share Posted July 31, 2003 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. Quote Link to comment https://forums.phpfreaks.com/topic/806-relational-table/#findComment-2655 Share on other sites More sharing options...
Pout Posted July 31, 2003 Author Share Posted July 31, 2003 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 Quote Link to comment https://forums.phpfreaks.com/topic/806-relational-table/#findComment-2656 Share on other sites More sharing options...
michael yare Posted July 31, 2003 Share Posted July 31, 2003 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] Quote Link to comment https://forums.phpfreaks.com/topic/806-relational-table/#findComment-2658 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.