Difficult Join - Can't seem to figure it out

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Difficult Join - Can't seem to figure it out

Post 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
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post 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.' ');
     //...
}
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Post by LiveFree »

Thanks Chris,

But we have one small problem here:

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

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

Thanks
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

As long as the groupID's are the values in the array, it doesn't matter.
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Then change

Code: Select all

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

Code: Select all

implode(', ' array_keys($user['groups']))
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post 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.
Post Reply