Clever Geek Handbook

Using a where clause with a sequence of IDs

In my table, I have a column that has a set of user IDs. his name users_id

. each id is separated by comma ( ,

).

ex: users_id

= '1,2,3,4,5';

if i passed to $id=1

my function how can i use the operator where

?

function($id){
$sql = "select * from content_noti
        where  ??????"
}
+3


source to share


4 answers


A leading and an ending can be added to the value field in the DB ,

.

eg.

Edit

1,2,3,4,5

to

,1,2,3,4,5,


So that each Id has a leading and trailing comma ,

Now update the body of the function:

function($id){
  $sql = "select * from content_noti 
        where  users_id LIKE '%,$id,%'"
}

In this case, you don't have any error, as if you were looking for a user id 1

, then you will only get 1

, not 11

either 111

or 1343

.

Working demo

+1


source


if you have multiple ids than it would be better to use IN

with the request

$sql = "select * from content_noti where id IN  (?)"


if you are looking for the opposite than using find_in_set 

 $sql = "select * from content_noti where FIND_IN_SET(?, id)";
+1


source


You can use regular expressions in mysql:

function($id){
  $sql = "SELECT * FROM content_noti WHERE users_id REGEXP '(^|,)" . $i . "($|,)'";
}
+1


source 


SELECT * from content_noti where users_id = "1" OR users_id = LIKE '%1,%' OR users_id = LIKE '%,1%'

Storing multiple chunks of data as a separate comma separated column is really bad design for databases, and if at all possible, you should look into normalizing it by creating a join table and joining it.

The performance of the above query will be terrible and difficult to maintain.

-1


source






More articles:


All Articles