mysql join and my dumbness

@jogolSep 04.2008

hi,

i studied the JOIN manual over and over and it looks like i´m just too stupid to manage the following:

table_permissions holds id, username, projectname and level.

table_projects holds id, projectname and description.

to get a list of projects to which the user has access to i´d select projectname from table_permissions where username = $username and level > 0

then i´d need to do some sort of join to get the corresponding id from table_projects for each projectname.

the output should be a list of links that look like this:

[CODE]<a href=”projects.php?id=$id_from_table_projects”>$projectname</a>[/CODE]

thanks in advance for your help.

jogol

to post a comment

PHP

5 Comments(s) _↴

@Phill_PaffordSep 04.2008 — #Well the JOIN would be based on the relationship of the 2 tables.

Table 1 - table_permissions: id, username, projectname and level

Table 2 - table_projects: id, projectname and description

Now the question is, Does the table_projects table hold all the project info and you need to check the table_permissions table to see if the user has access? and/or how do the two tables relate? are the common fields id (so the id in table_permissions is related to the id in table_projects) or is it the projectname field in both tables? or is it both id and projectname?

You have this

[CODE]
 select projectname from table_permissions where username = $username and level > 0
 [/CODE]

My guess at what you want is to find all the projects the selected user has permission to?

try this

[CODE]
 SELECT t1.projectname
 FROM table_permissions AS t1 
 LEFT JOIN table_projects AS t2
 ON (t2.projectname = t1.projectname)
 WHERE t1.username = $username 
 AND t1.level > 0
 [/CODE]

or this

[CODE]
 SELECT t1.projectname
 FROM table_permissions AS t1 
 LEFT JOIN table_projects AS t2
 ON (t2.id = t1.id)
 WHERE t1.username = $username 
 AND t1.level > 0
 [/CODE]

or even this

[CODE]
 SELECT t1.projectname
 FROM table_permissions AS t1 
 LEFT JOIN table_projects AS t2
 ON (t2.id = t1.id AND t2.projectname = t1.projectname)
 WHERE t1.username = $username 
 AND t1.level > 0
 [/CODE]

You can also try other JOINS instead of just a LEFT JOIN try a RIGHT JOIN or INNER JOIN or maybe just a JOIN.

@SyCoSep 04.2008 — #Wikipedia has a nice page on joins.

http://en.wikipedia.org/wiki/Join_(SQL)

MySQL handles numerical data types faster then text. So joining on projectname (type varchar) is not going to be as fast as joining on projectid (type int). About 50,000 records in you'll see significant slowdown. Joining on indexed int() columns and you can be in the millions before you see any slowdown.

Calling both the id columns 'id' will get confusing. If they are different ids and you join, you return 2 columns, both called id. If you return that result to PHP with mysql_fetch_assoc() then only one id column will return. The reason is the column name becomes the key in the array so because they're the same, the second id value overwrites the first.

I'm not sure of the info you are trying to store here but it looks to me like you would need 3 tables. If you user (given in username) manages more than one project ever.

So table 1 (users) stores the user info
[CODE]userid username firstname lastname userlevel[/CODE]

Table 2 (projects) stores the project details
[CODE]projectid projectname project_description projectlevel active[/CODE]

Table 3 (log) logs the relationship of users to projects.
[CODE]logid projectid userid[/CODE]

The autoincrement id column logid is optional here. The advantage is it keeps the record unique which is useful during development. When assigning a user to a project you can compare userlevel and projectlevel to see if the user has the required level to work on the project. Or return a list of all users and the projects they currently have assigned (inner join), all users with project details even if they don't have a project (left or right join) or all projects and users regardless of whether projects have users assigned or users have no projects (outer join).

Now you can find all users currently working on an active project like this

[CODE]SELECT * FROM log
 INNER JOIN users ON log.userid=users.userid
 INNER JOIN projects ON log.projectid=projects.projectid
 WHERE projects.active=1
 [/CODE]

The biggest obvious advantage is now you can assign 2 users to the project while keeping the data normalized. You have a list of everyone (users), everything (projects) and who is doing what. From that data you can determine who isn't doing anything, what anyone can do (user/project level) and what needs to be done.

You can build easily on this structure by adding more tables. Say for example you added a project_progress table. You could join it on project id and have the project user(s) submit comments or create a deadline schedule etc. How about adding a user rating system. Another table could store other input from other people about how the projects assigned users performed. The thing is to keep the data normalized. The data shouldn't be repeated in different tables with the one exception being the identifying ids that tie it all together.

You need to understand indexing the id columns too to maximize efficiency. There's lot of info already on the net so I'll not repeat it here. A Google for 'mysql create index' should get you started.

@callumdSep 04.2008 — #I wrote a tutorial on left joins:

http://easytutes.blogspot.com/2008/01/left-joins-in-sql-why-they-are.html

@jogolauthorSep 05.2008 — #hi and thank you all for your great feedback!

let me try to explain the whole thing a bit more.

there is a website that has a public and a private area.

there are files (e.g. images, pdf, etc) that belong to different projects.

so there might be 5 files belonging to project1 that are viewable by the public. these 5 files have a level of 0.

then there might be 2 files with access level 1, 3 files with level 2 and 1 file with level 3. all belonging to project1. level 1 - 3 are not viewable by the public and you need to have a user account to see them. the site admin can give you either a level 1, 2 or 3, depending on your role in the project.

table_permissions looks like this:

-id-----username-----projectname-----level

-1------user1---------project1----------1

-4------user1---------project2----------0

-7------user1---------project3----------2

-8------user1---------project4----------3

so when a user logs in i have his username. with this username i select all projects (projectname) from table_permissions that are not public (the user doesnt need to see them in the restricted area) and list them as html links.

in this example the list would be:

project1

project3

project4

now the user can click on project3 to get to a page where he can see the files belonging to project3.

since i dont want to pass along the projectname to the next page via URL (filelist.php?projectname=project3) i need to get the id from table_projects where the projectname is the same as in table_permissions.

table_projects looks like this:

-id-----projectname-----description

-1------project1---------some text

-3------project2---------some text

-4------project3---------some text

-7------project4---------some text

in our case (project3) the id would be 4, so the link would be filelist.php?projectid=4

the whole procedure is simply a switch from working with the projectname to working with the id.

in filelist.php i´d then GET projectid, select the projectname from table_projects again ($projectname) and go and select the level from table_permissions where username = $username and projectname = $projectname.

now that i know the user´s level ($level) for project3 i can select all files from table_files where projectname = $projectname and level = $level

table_files looks like this:

-id-----filename-----projectname-----level

-1------file1---------project1---------1

-1------file2---------project2---------3

-2------file3---------project3---------1

-3------file4---------project3---------2

-4------file5---------project3---------3

since user1 is level 2 on project3 he´d see file3 and file4 (seeing a lower level is ok), but not file5.

@SyCoSep 09.2008 — #So even after all I said you still want to join on projectname? Why build a system doomed to slow down soon?

Also in #PHP _↴

How can I calculate info from form field?URGENT : PHP DateFormat whereis php

Success!

Help @jogol spread the word by sharing this article on Twitter...

Tweet This

mysql join and my dumbness

5 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

mysql join and my dumbness

5 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

5 Comments(s) _↴

Also in #PHP _↴