1
Table name: `team`  
Column name: `team_members`  
Column value1: 1,72,34 //these are userids [Row1]  
Column value2: 72,38 [Row2]  
Column value2: 10,3  [Row3]

Now I want to match if login user's id exists in team_members or not.

For this, I fire a query as below :

<?php
$loginuserid = 72;
$query = 'SELECT * FROM `team` WHERE `team_members` IN ("'.$loginuserid.'")';
?>

Expected Result:

It must return 2 rows: 1 and 2

But it is not working like this. It returns only row1.

Kindly, suggest the solution.

Liz.
  • 795
  • 2
  • 13
  • 31
  • 2
    http://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string – Aakash Jain Nov 24 '15 at 05:45
  • From this code it looks like it won't even return row1. You will have to use wild card % before and after as you don't want exact match and there are other values too – Techie Nov 24 '15 at 05:46
  • @Nimesh I used % using LIKE clause but it would also return a row when the string includes a number like 723. That is why I tried using IN clause – Liz. Nov 24 '15 at 06:02
  • 2
    Don't store values in comma separated strings if you need to access the individual values. That's why you are using a database - to enable easy access to your data; so store it in a way that facilitates it. That is to say, make another table and put each value in it's own row. Also, don't use `SELECT *` in your program code. Finally, use parameterized statements: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – Turophile Nov 24 '15 at 06:14
  • Please tag dbms used. – jarlh Nov 24 '15 at 07:32

1 Answers1

1

I found the solution by using FIND_IN_SET() as recommended. Thank you! Following is the query which resolved my issue:

SELECT * FROM `team` WHERE FIND_IN_SET('72',`team_members`)

Result: This returns : row1 and row2

Liz.
  • 795
  • 2
  • 13
  • 31
  • You can accept your answer so that the question will be marked as resolved. Also since this is mysql I'd suggest to tag is appropriately. – Techie Nov 24 '15 at 07:18
  • You're using product specific functionality here, and no dbms is specified in the question. At least inform which dbms product this is for. – jarlh Nov 24 '15 at 07:35
  • @Nimesh I already tried accepting it before but it gave me that I can accept my answer only after 2 days :( – Liz. Nov 24 '15 at 08:24