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!
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!
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-
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-
ASKER
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)";
I have this but its not working.
$getprimary = "SELECT id, grpid, link, text, definition, parentid, hidden FROM aa_primary_pages WHERE grpid IN($permgrps)";
<?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 />";
}
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://stackoverflow.com/questions/4768008/mysql-query-if-value-in-string
You should use mysql query directly for those.