Page 1 of 1

Difficult Join - Can't seem to figure it out

Posted: Sun Jul 29, 2007 10:13 pm
by LiveFree
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

Posted: Sun Jul 29, 2007 10:58 pm
by tecktalkcm0391

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.' ');
     //...
}

Posted: Sun Jul 29, 2007 11:57 pm
by LiveFree
Thanks Chris,

But we have one small problem here:

$user['groups'] is an array, aka:

$user['groups'] = array('22', '1'); #Etc

Thanks

Posted: Mon Jul 30, 2007 12:22 am
by John Cartwright
An inner join and using implode() should do the trick. Untested, but should suit your needs.

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
}
@ tecktalkcm0391: You should avoid nesting queries whenever possible. Usually a simple join can resolve this.

Posted: Mon Jul 30, 2007 11:58 am
by LiveFree
Jcart,

My apologies, but I made an error in describing the $user['groups'] array to you:

$user['groups'] = array('Group 1' => 12,
'Group 2' => 1);

Thanks for the help

Posted: Mon Jul 30, 2007 12:02 pm
by John Cartwright
As long as the groupID's are the values in the array, it doesn't matter.

Posted: Mon Jul 30, 2007 12:28 pm
by LiveFree
Woah,

I do not know why I keep screwing up Jcart, but I should have the array the other way around:

$user['groups'] = array(23 => 'Group 1',
1 => 'Group 2');

So the groupID's are the array keys instead.

Thank You for the help

Posted: Mon Jul 30, 2007 12:43 pm
by John Cartwright
Then change

Code: Select all

implode(', ' $user['groups'])
to

Code: Select all

implode(', ' array_keys($user['groups']))

Posted: Mon Jul 30, 2007 1:27 pm
by tecktalkcm0391
Jcart wrote:@ tecktalkcm0391: You should avoid nesting queries whenever possible. Usually a simple join can resolve this.
I didn't know you can do that...i'd gotta look at it.