0
id | name | permission
1  | John | 1,4,3
2  | mike | 7,4,3
3  | sky  | 3,2,1

this is my database

now i have fetch select query with where condition

e.g. select * from friend where permission='4'

but i m not able fetch any data so what to do ?

Please help

SPS
  • 116
  • 1
  • 11

3 Answers3

2

Select * from friend where FIND_IN_SET('4',permission);

Mihir Bhatt
  • 3,019
  • 2
  • 37
  • 41
2

It looks like you have multiple permissions stored together in a string.

To you, the query looks like:

select * from friend where permission='4'

That means anything containing 4. To mysql, it sees:

select * from friend where permission= ONLY '4'
// Meaning the permissions column can ONLY CONTAIN 4.
// Also, ONLY is meant as a visual, don't use it in queries.

Try:

find_in_set('4',permission) <> 0
// This means It needs to find number 4 and can't return 0 results.

Check out these for more info:

MySQL query finding values in a comma separated string

http://www.w3resource.com/mysql/string-functions/mysql-find_in_set-function.php

Community
  • 1
  • 1
Mitch Mullvain
  • 185
  • 10
0

It can be done by the following query :

select * from friend where permission like '%4%'
Nivs
  • 376
  • 2
  • 15
  • 1
    This is a bad example as it will allow anything with 4. So if for some reason there was a permission 4 and 14, it would allow both, even if they only wanted just 4. This is because it just finds something with 4 in it from the way you wrote it. – Mitch Mullvain Feb 21 '15 at 04:30