Difficult Join - Can't seem to figure it out
Moderator: General Moderators
Difficult Join - Can't seem to figure it out
Hello All,
I have, sitting before me, a rather difficult MySQL Join.
I have some data. In one table I have document's filenames and their titles, and in the next table I have the group_id's that are allowed access to the documents.
However, I have an array which provides a list of all the groups of the user is a member of, and there usually is more than one group in the list.
For the past hour or so, I have been attempting to create a query that will select the document's filename if the user is a member of a group that has access to that particular document.
DB Structure:
docs_access:
`docID`
`groupID`
docs:
`docID`
`docTitle`
`docUploadDate`
`docUploader`
`docFilename`
And the user's group info is contained in (for example, lets say): $user['groups']
I am very sorry to bother you all in this manner, but I simply cannot get my mind around this.
Thank You,
Rick
I have, sitting before me, a rather difficult MySQL Join.
I have some data. In one table I have document's filenames and their titles, and in the next table I have the group_id's that are allowed access to the documents.
However, I have an array which provides a list of all the groups of the user is a member of, and there usually is more than one group in the list.
For the past hour or so, I have been attempting to create a query that will select the document's filename if the user is a member of a group that has access to that particular document.
DB Structure:
docs_access:
`docID`
`groupID`
docs:
`docID`
`docTitle`
`docUploadDate`
`docUploader`
`docFilename`
And the user's group info is contained in (for example, lets say): $user['groups']
I am very sorry to bother you all in this manner, but I simply cannot get my mind around this.
Thank You,
Rick
- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida
Code: Select all
$QuerydocID = mysql_query('SELECT * FROM `doc_access` WHERE `groupID`= '.$user['group'].' '); // you can use that to get all of the docs the user can access....
//------
$QueryAccess = mysql_query('SELECT * FROM `doc_access` WHERE `groupID`= '.$user['group'].' AND `docID` = '.$docID.' ');
if(mysql_num_rows($QueryAccess)==0){
echo 'no access';
} else {
$QueryDoc = mysql_query('SELECT * FROM `docs` WHERE `docID` = '.$docID.' ');
//...
}- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
An inner join and using implode() should do the trick. Untested, but should suit your needs.
@ tecktalkcm0391: You should avoid nesting queries whenever possible. Usually a simple join can resolve this.
Code: Select all
$sql = '
SELECT * FROM `docs`
INNER JOIN `docs_access` USING (`docID`)
WHERE `docs_access`.`groupID` IN ('. implode(', ' $user['grounds']) .')
AND `docs`.`id` = \''. intval($documentID) .'\'';
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result)) {
//user has access
}- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Then change
to
Code: Select all
implode(', ' $user['groups'])Code: Select all
implode(', ' array_keys($user['groups']))- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida