SQL Array Search

Question

I have a group feature on my website that allows 1 user to put the userids of any of his or her friends into an invitedusers column. I need to do an SQL search to determine whether the logged-in user's ID has been stored in any invitedusers columns by his or her friends. How can I do this with an sql search?

EX: My column is invitedusers, and inside that column is stored the following, (234,394,479) When Userid 234 has logged in, I need to search to see whether any columns have userid 234 stored inside the invitedusers column.

SELECT userid, name... FROM mytable WHERE invitedusers = ?

*I looked at the SQL IN operator along with FIND_IN_SET but no help with either.*

I think you're looking for LIKE. As in, SELECT userid, name... FROM mytable WHERE invitedusers LIKE '%234%' — Isaac Fife
– Isaac Fife, Commented Feb 8, 2012 at 20:38
I'm afraid we cannot help you without table schema examples. — Vyktor
– Vyktor, Commented Feb 8, 2012 at 20:39
Why do people constantly struggle to find ways to bypass their bad database design rather than design cleanly normalized tables in the first place? — Mark Baker
– Mark Baker, Commented Feb 8, 2012 at 20:40
Presumably you're storing all of these invites and such in tables, so you'll need to do some kind of join. — Matt Fenwick
– Matt Fenwick, Commented Feb 8, 2012 at 20:40

Vyktor · Accepted Answer · 2012-02-08 20:46:34Z

2

Assuming that you're really using 234,394,479 as value of one column (you at least should use ,234,394,479, to be able to do WHERE invited LIKE '%,234,%' in your query) you should rebuild your user tables, remove field invited_users and create table like this:

CREATE TABLE invited_users (
  id INT AUTO_INCREMENT,
  owner_id INT, -- Who's input it is
  target_id INT, -- What's the target user
  PRIMARY KEY ( id),
  UNIQUE ( owner_id, target_id),
  -- Indexes (FOREIGN KEYs!) to users table
);

And than just select list of users who invited user 234 with query:

SELECT users.id, users.name
FROM invited_users
INNER JOIN users ON invited_users.owner_id = users.id
GROUP BY users.id
WHERE invited_users.target_id = 234

answered Feb 8, 2012 at 20:46

Vyktor

21.1k6 gold badges69 silver badges98 bronze badges

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

1 Comment

user1011713 Over a year ago

Thanks so much for this Vyktor. Definitely implementing it.

Steven Schroeder · Accepted Answer · 2012-02-08 20:56:07Z

1

You may not want to store a comma delimited list in a column, but since you are:

SELECT userid, name FROM mytable
WHERE invitedusers = '(' + '234' + ')' -- One and only one entry
OR invitedusers LIKE '(' + '234' + ',%' -- First entry
OR invitedusers LIKE '%,' + '234' + ',%' -- Middle entry
OR invitedusers LIKE '%,' + '234' + ')%' -- Last entry

I have modified based on EBarr's comment. There are four cases to check for. This assumes you are storing the data with a ( prefix and a ) suffix and separating everything with commas.

Here are some tests to prove that it works:

/* Tests */
INSERT INTO @mytable SELECT 11, 'a', '(234)' -- Should get returned
INSERT INTO @mytable SELECT 12, 'b', '(1234)'
INSERT INTO @mytable SELECT 13, 'c', '(92349)'
INSERT INTO @mytable SELECT 13, 'd', '(2345)'

INSERT INTO @mytable SELECT 21, 'a', '(234,567)' -- Should get returned
INSERT INTO @mytable SELECT 22, 'b', '(1234,567)'
INSERT INTO @mytable SELECT 23, 'c', '(92349,567)'
INSERT INTO @mytable SELECT 23, 'd', '(2345,567)'

INSERT INTO @mytable SELECT 31, 'a', '(567,234)' -- Should get returned
INSERT INTO @mytable SELECT 32, 'b', '(567,1234)'
INSERT INTO @mytable SELECT 33, 'c', '(567,92349)'
INSERT INTO @mytable SELECT 33, 'd', '(567,2345)'

INSERT INTO @mytable SELECT 41, 'a', '(123,234,789)' -- Should get returned
INSERT INTO @mytable SELECT 42, 'b', '(123,1234,789)'
INSERT INTO @mytable SELECT 43, 'c', '(123,92349,789)'
INSERT INTO @mytable SELECT 43, 'd', '(123,2345,789)'

SELECT userid, name FROM @mytable
WHERE invitedusers = '(' + '234' + ')' -- One and only one entry
OR invitedusers LIKE '(' + '234' + ',%' -- First entry
OR invitedusers LIKE '%,' + '234' + ',%' -- Middle entry
OR invitedusers LIKE '%,' + '234' + ')%' -- Last entry

edited Feb 8, 2012 at 20:56

answered Feb 8, 2012 at 20:38

Steven Schroeder

6,2742 gold badges23 silver badges15 bronze badges

4 Comments

EBarr Over a year ago

You need to add the delimiter to your LIKE criteria. As written you'll match entries for 234, 1234, 92349, and 2345

user1011713 Over a year ago

Hi Steven. What would be a better solution to a comma delimite list?

Steven Schroeder Over a year ago

Create a child table that has a one to many relationship with your parent. Then your query would become something like: SELECT a.userid, a.name FROM mytable a LEFT OUTER JOIN mychildtable b ON a.userid = b.userid WHERE b.invitedusers = 234

user1011713 Over a year ago

Wow, thanks! This did it but it seems I have to re-design my structure.

animuson · Accepted Answer · 2012-02-08 23:17:46Z

1

This should do the job:

SELECT userid, name... FROM mytable WHERE FIND_IN_SET('234', invitedusers) !=0;

edited Feb 8, 2012 at 23:17

animuson♦

54.9k28 gold badges142 silver badges150 bronze badges

answered Feb 8, 2012 at 20:46

frizzby

4192 silver badges10 bronze badges

Comments

user800014 · Accepted Answer · 2012-02-08 20:42:39Z

0

I'm not sure if this is the best way but:

where concat(',',invitedusers,',') like '%,234,%'

So in your example ,234, is inside ,234,394,479,

answered Feb 8, 2012 at 20:42

user800014

Collectives™ on Stack Overflow

SQL Array Search

4 Answers 4

1 Comment

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related