SQL is in array query

Question

i have a column in my database with string value like "1,2,3,4,5,". I want to compare and find an integer value I defined before in that string. But I want this string to have the purpose of an array. There is no problem up to here. I am finding the integer in array like that:

$sql='SELECT * FROM '. TOPIC_TABLE .' WHERE multi_dept LIKE \'%'.$prjId.',%\' ORDER BY topic';

but, when there is a topic have id 11 in the string, there will be an error.

{1,2,11,}-> when i search ID=1 in the string, it will find also 1 and 11. Is there anyway to avoid and solve this problem with SQL query?

Yeah, you can normalize your data.

Corbin
– Corbin

2012-05-15 07:44:37 +00:00
Commented May 15, 2012 at 7:44 — Corbin
– Corbin, Commented May 15, 2012 at 7:44

nyson · Accepted Answer · 2012-05-15 07:48:01Z

3

As you have a , after every integer, isn't this as easy as using the WHERE clause below?

WHERE multi_dept LIKE '$prjId,%' XOR multi_dept LIKE '%,$prjId,%'

answered May 15, 2012 at 7:48

nyson

1,0306 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

scragar · Accepted Answer · 2012-05-15 07:52:12Z

0

FIND_IN_SET to the rescue.

 $sql='SELECT * FROM '
     . TOPIC_TABLE
     . ' WHERE FIND_IN_SET( '
     . $prjId
     . ', multi_dept ) ORDER BY topic'

answered May 15, 2012 at 7:52

scragar

6,84530 silver badges37 bronze badges

Collectives™ on Stack Overflow

SQL is in array query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related