Jump to content


Photo

relational table


  • Please log in to reply
3 replies to this topic

#1 Pout

Pout
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 31 July 2003 - 08:47 AM

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?

#2 michael yare

michael yare
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationLondon, UK

Posted 31 July 2003 - 08:56 AM

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, 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.

#3 Pout

Pout
  • Members
  • Pip
  • Newbie
  • 8 posts

Posted 31 July 2003 - 09:15 AM

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

#4 michael yare

michael yare
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationLondon, UK

Posted 31 July 2003 - 12:07 PM

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]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users