We help IT Professionals succeed at work.

Search mysql row result for certain values

t3chguy
t3chguy asked
on
Medium Priority
287 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!
Comment
Watch Question

Seems like a similiar problem:
http://stackoverflow.com/questions/4768008/mysql-query-if-value-in-string
You should use mysql query directly for those.
CERTIFIED EXPERT
Top Expert 2006

Commented:
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-

Author

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

Most Valuable Expert 2011
Author of the Year 2014

Commented:
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.
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.