Search mysql row result for certain values

Hello,

i have a field in mysql for each person that contains a string of permissions that correlates with different programs they can view.

The field is varchar(300).

After I extract the row out in my php program, I want to run a function that searches for any numbers that I may specify in any order.

For somebody like me with all the permissions, the value would look like 1,2,3,4,5,6,7,8,9.

However, for some of the less powerful people, it may look like, 2,5,7  

How can I have it search for anybody who has permission 2 or 7 and allow access or 1, 3, and 5?

Thank you!
LVL 1
t3chguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Lukasz ChmielewskiCommented:
Seems like a similiar problem:
http://stackoverflow.com/questions/4768008/mysql-query-if-value-in-string
You should use mysql query directly for those.
0
 
RoonaanCommented:
Hi,

You can use mysql's find_in_set function: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

SELECT * FROM mytable WHERE find_in_set('2', permissions) or find_in_set('7', permissions)

As far as I know there is no function to do a complete cross section easily.

-r-
0
 
t3chguyAuthor Commented:
Am I able to use a variable with the IN operator?  

I have this but its not working.

$getprimary = "SELECT id, grpid, link, text, definition, parentid, hidden FROM aa_primary_pages WHERE grpid IN($permgrps)";


aa_primary_table structure and data

aa_user_groups structure and data
<?php

require_once("includes/sqlconnect.php");
require_once("includes/displayerror.php");

//This gets basic information about employee : name & permission groups
$respid = "5";

$getuserperm = "SELECT pr_resp.respid, aa_user_groups.grpid, pr_resp.ckname FROM pr_resp LEFT JOIN aa_user_groups ON pr_resp.respid = aa_user_groups.respid WHERE aa_user_groups.respid = '$respid' ";
$userpermres = mysql_query($getuserperm) or die(mysql_error());
$foundresult = mysql_num_rows($userpermres);

if($foundresult == 0)
	{
	die;
	}

$fetchperms = mysql_fetch_row($userpermres);
$respid = $fetchperms[0];
$permgrps = $fetchperms[1];
$ckname = $fetchperms[2];

echo "Employee Resp: " . $respid . "<br />";
echo "Employee Name: " . $ckname . "<br />";
echo "Employee Perms: " . $permgrps . "<br />"; 
echo "---------------------------<br /><br />";

//This will find the primary links that we have access to

$getprimary = "SELECT id, grpid, link, text, definition, parentid, hidden FROM aa_primary_pages WHERE grpid IN($permgrps)";

$primaryres = mysql_query($getprimary) or die(mysql_error());

$foundprimaryresult = mysql_num_rows($primaryres);

echo $getprimary . "<br /><br />"; 

echo "<br />" . $foundprimaryresult . "<br />";

while ($row = mysql_fetch_assoc($primaryres))
	{
	$primaryid = $row['id'];
	$groupids = $row['grpid'];
	$link = $row['link'];
	$text = $row['text'];
	$definition = $row['definition'];

	echo $link . "<br /> " . $text . "<br /> " . $definition . "<br /><br />";
	}

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Ray PaseurCommented:
It's my sense that you should redesign the application.  Each level of permission should be individually associated with each user in a separate column and the columns should be indexed binary values.  As it stands today, if your application has many users it will get progressively slower every time you search for permissions.
0
 
skullnobrainsCommented:
i understand the question is a php question and not mysql
plenty of ways around including parsing the values in an array

i'll go for this short one

function hasright($right,$rights){
return false!==strpos(','.$right.',' , ','.$rights.',');
}

// How can I have it search for anybody who has permission 2 or 7 and allow access or 1, 3, and 5?

( hasright(2,$rights)
or hasright(7,$rights)
or hasrights(1,$rights)
and hasrights(3,$rights)
and hasrights(5,$rights)
) and $granted=true
or $granted=false;

using mysql like clauses will let you perform the same thing

my advice overall would be to use an integer column and bitmasks
this is much faster and easier to maintain, but a little harder to code the first time

converting data could be performed easily in either php or mysql, even with no mathematic knowlege
$bitmask='0000000000';
foreach($right in explode(',',$rights))$bitmask[$right]=1;
$bitmask=base_convert($bitmask,2,10);
// do not try such an algorythm in any other language than php ;)

then you check rights using bitwise operators

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
armina_99Commented:
I agree with Skullnobrains, though I would simply convert the integer to a binary value using a php function. That would be much quicker:

$binair = sprintf('%010b', 10);   // I'm not using decbin here because you now get a 10char lengthy string. change %010b to %020b if you need a 20char lenghty string (aka 20 flags)

and

$integer = bindec($binair);

Once you have the binary number, you can see if a flag is true by doing the following:
if( $binair[5] = '0' )
{
	//flag is false
}
else
{
	//flag is true
}

Open in new window


Note that if you want to be able to expand the rights at a later time, you should calculate the rights from right to left, because binary works from right to left. 0001 = 1, 0010 =2, etc... meaning that a new number will be added on the left side.

If this becomes a problem for you, note that you can also request the flag by using the following method:

$flag = substr($binair, -4,1); // will give you the 4th character from the right.
0
 
skullnobrainsCommented:
<quote>
I agree with Skullnobrains, though I would simply convert the integer to a binary value using a php function. That would be much quicker:
</quote>

agreed as well, i was too lazy to check the right syntax
... but the input should not be the number corresponding to the access right but 2^that_number

something like

<code>
bitmask=0;
foreach(explode(',',$rights) as $right)bitmas+=pow(2,$right);
bitmask=bindec(sprintf('%0100b', 10));
</code>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.