Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Search mysql row result for certain values

Posted on 2011-04-18
7
Medium Priority
?
269 Views
Last Modified: 2013-12-12
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!
0
Comment
Question by:t3chguy
7 Comments
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35419464
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
 
LVL 49

Expert Comment

by:Roonaan
ID: 35420151
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
 
LVL 1

Author Comment

by:t3chguy
ID: 35443492
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35466978
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
 
LVL 27

Accepted Solution

by:
skullnobrains earned 1336 total points
ID: 35497221
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
 
LVL 1

Assisted Solution

by:armina_99
armina_99 earned 664 total points
ID: 35694760
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
 
LVL 27

Assisted Solution

by:skullnobrains
skullnobrains earned 1336 total points
ID: 35860901
<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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question