homeGeek CultureWebstoreeCards!Forums!Joy of Tech!AY2K!webcam

The Geek Culture Forums


Post New Topic  New Poll  Post A Reply
my profile | directory login | | search | faq | forum home
  next oldest topic   next newest topic
» The Geek Culture Forums   » Other Geeky Stuff   » Ask a Geek!   » Muddling my way through MySQL/php

 - UBBFriend: Email this page to someone!    
Author Topic: Muddling my way through MySQL/php
Stibbons
SuperBlabberMouth!
Member # 2515

Member Rated:
5
Icon 1 posted January 18, 2008 07:12      Profile for Stibbons   Author's Homepage     Send New Private Message       Edit/Delete Post   Reply With Quote 
I'm trying to fudge some code I've inherited into working, which has already been fudged in the first place which makes it not work. Unfortunately, I don't do SQL or PHP too well. You follow?

This single script is part of an addon for the CMS we use. It checks if you have permissions to download a file or not by looking at $_SESSION['GROUP_ID'], which should contain a single integer indicating which group you're a member of.

The hitch is that it has been screwed about with to allow users to be members of more than one group for ease of page permissions etc. This is stored in the field 'GROUPS_ID' as a string, eg "1,3,8,12" (or in SQL speak varchar(255)...)

The files to be downloaded are stored in a seperate table, and each file has a integer field 'GROUP_ID'. So when run, the script compared the group id of the file to the user, and lets the user download the file or turns them away.

So my question is this: How do I make SQL look in the user's GROUPS_ID string to see if the file's GROUP_ID is in there? Currently, the line used is

code:
AND in_array($_SESSION['GROUP_ID'],$groups)   

so something simple would be nice. Remember, I'm a geologist not a programmer! [Smile]

TIA

Stibbons

Posts: 1143 | From: UK | Registered: Dec 2003  |  IP: Logged
Ugh, MightyClub
BlabberMouth, the Next Generation
Member # 3112

Member Rated:
5
Icon 1 posted January 18, 2008 10:42      Profile for Ugh, MightyClub     Send New Private Message       Edit/Delete Post   Reply With Quote 
Is the code you posted part of a SQL WHERE clause, or is it PHP logic? I don't know about MySQL specifically, but in general it is very hard to parse delimited fields in SQL.

I'm also a little lost as to which values are session variables and which are in the database. Are you saying the session variable has exactly one group ID, the user table has a "Groups_ID" field containing a comma separated list of groups, and the files table has a "Group" field which contains exactly one group ID?

--------------------
Ugh!

Posts: 1742 | From: Ithaca, NY | Registered: Dec 2004  |  IP: Logged
Mr. Dave
Geek
Member # 1977

Member Rated:
5
Icon 1 posted January 18, 2008 16:43      Profile for Mr. Dave     Send New Private Message       Edit/Delete Post   Reply With Quote 
My first, knee-jerk response is that you need another table for user-group memberships, something like
code:
CREATE TABLE user_groups
(
userid <however you define userid> NOT NULL,
groupid INT NOT NULL,
PRIMARY KEY (userid, groupid)
);

Then you can simply query user_groups to see if this user belongs to this group.

You should also make user_groups.userid a foreign key referencing users.userid, but I'm not sure if MySQL can actually do that yet.

Hope that helps.

--------------------
I'm not normally like this, but then I'm not normally normal.

Posts: 193 | From: Leverkusen Institute of Paleocybernetics | Registered: Jan 2003  |  IP: Logged
AgingAmigaoid
Mini Geek
Member # 764

Member Rated:
5
Icon 1 posted January 19, 2008 08:23      Profile for AgingAmigaoid     Send New Private Message       Edit/Delete Post   Reply With Quote 
quote:
Originally posted by Stibbons:
the line used is

code:
AND in_array($_SESSION['GROUP_ID'],$groups)   

so something simple would be nice. Remember, I'm a geologist not a programmer!
Is that PHP code or SQL code you've posted or is it PHP code that is supposed to produce SQL code?

Could you give us a wider view of the code to see?

PHP has several ways to split a string into array items and join array items into strings.

If it's SQL code then check out the IN() function for checking for a value in a list of values.

Have Fun!

Posts: 83 | Registered: Apr 2001  |  IP: Logged
Stibbons
SuperBlabberMouth!
Member # 2515

Member Rated:
5
Icon 1 posted January 21, 2008 07:28      Profile for Stibbons   Author's Homepage     Send New Private Message       Edit/Delete Post   Reply With Quote 
Ok guys, thanks for the replies so far - it's helping me understand what's going on a bit better, if nothing else!

The (more or less) complete code is:

code:
 $query_files = $database->query("SELECT * FROM ".TABLE_PREFIX."mod_download_gallery_files WHERE file_id = '$file_id'");
$fetch_file = $query_files->fetchRow();

$query_page=$database->query("SELECT * FROM ".TABLE_PREFIX."pages WHERE page_id='".$fetch_file['page_id']."'");
$page_info=$query_page->fetchRow();
$groups=explode(",",$page_info['viewing_groups']);
if(($page_info['visibility'] == 'public' OR $page_info['visibility']=="hidden")
OR ((isset($_SESSION['USER_ID'])
AND $_SESSION['USER_ID'] != ""
AND is_numeric($_SESSION['USER_ID'])
)
AND ($page_info['visibility']=="registered" OR $page_info['visibility']=="private")
AND in_array($_SESSION['GROUP_ID'],$groups)
)){
//$orgfile=WB_PATH.MEDIA_DIRECTORY."/download_gallery/".$fetch_file['filename'];
$orgfile2=$fetch_file['link'];

//$mimetype=mime_content_type($orgfile);
//header('Content-type: $mimetype');
//header('Content-Disposition: attachment; filename="'.$fetch_file['filename'].'"');
//readfile($orgfile);

header('Location: '.$orgfile2);

} else {
exit;
}

To start with, we're digging out the correct file from the mod_download_gallery_files table, which has the fields file_id (int) and page_id (int). We're then looking up that particular page_id in the pages table, which has a viewing_groups (text) field which will look like

code:
"1,4,16,20"

which we explode() into the array $groups.

The table users contains two fields - group_id, an integer left over from the pre-hacked version, and the new groups_id (varchar) which lists all the groups that user belongs to and looks like

code:
2,3,4,6,8,9,10

These become the session variables. So what I think I need is a way to check whether any of the values in $_SESSION['GROUPS_ID'] are in the array $groups. I could explode groups_id into an array, which I could then check against $groups.

Would it be possible to use array_intersect(), such as

code:
AND if array_intersect($_SESSION['GROUPS_ID'], $groups) != 0

or is there an easier way?
Posts: 1143 | From: UK | Registered: Dec 2003  |  IP: Logged


All times are Eastern Time  
Post New Topic  New Poll  Post A Reply Close Topic    Move Topic    Delete Topic next oldest topic   next newest topic
 - Printer-friendly view of this topic
Hop To:

Contact Us | Geek Culture Home Page

2015 Geek Culture

Powered by Infopop Corporation
UBB.classicTM 6.4.0



homeGeek CultureWebstoreeCards!Forums!Joy of Tech!AY2K!webcam