Selecting A Row Based On One Value In A Multi-Value Array Using IN Clause

Question

I have a column in a database with values stored as a comma separated array; let's say a sample entry in the database would be: 1, 5, 8, 15

I am trying to use this data now in a SELECT statement to allow a user to select an entry in the database where that array contains the value they select (the values are tied to a look-up table I join in the actual application, the user does not see the integers).

My simplified SELECT statement is essentially:

$arrayvalue = "1";

   $query = "SELECT * FROM table WHERE tablearray IN ($arrayvalue)";

For this example I put in 1 as the array value one would be searching for; in actuality that variable is populated by POST from form input the user has control of.

'tablearray' is the name of the column containing the comma separated array that we said would contain 1, 5, 8, 15 as an example (though it varies in reality).

This statement with the IN clause works fine for me right now for data in the table column that has only one value, or with multiple values in the array when I select for the very first value in the array, but does not return for the second value if I select for that i.e. if I were to set $arrayvalue to 1 it works, but to 5 it does not return that row in my results.

I do not need to select for multiple values from the array at this time, I just want it to work to select for one value from the table column regardless of where that integer falls in the array. I suspect I am missing something simple here, thanks if you can help.

Why are you storing data in this denormalized fashion? Normal form would be to have each of those values as individual rows in a seperate table. — Declan_K, May 13 '13 at 16:54

Declan_K · Accepted Answer · 2013-05-13T18:49:12.123

1

SELECT * FROM table WHERE tablearray LIKE CONCAT('%',$arrayvalue,'%');

To avoid the '5' finding '15',etc... issue identified in the comment below, you could use the following pattern, which assumes that values are seperated by a comma with no spaces before or after the comma.

WHERE   tablearray LIKE CONCAT('%,',$arrayvalue,',%')
OR      tablearray LIKE CONCAT($arrayvalue,',%');
OR      tablearray LIKE CONCAT('%,',$arrayvalue)

BTW, this really is all a hack around the fact that these attributes should be normalized into rows.

edited May 13 '13 at 18:49

answered May 13 '13 at 16:57

Declan_K

6,726
2
19
30

I had tried using LIKE earlier but the issue I was running into was that it would then return BOTH 5 and 15 if I had $arrayvalue = "5" for example, as both contain the integer "5"; in this example I need to only return rows with the value 5 and not also if they contain 15 or 55 or 25, etc... – Dev_At_InboundHorizonsInc May 13 '13 at 18:03
Thank you, that did help solve the issue. Just for my own education here, what is the issue with storing an array within a table column? The integers correspond to another table in the dbase that it joins to in order to label the integers for the user in the output once I explode the array; I was not aware it wasn't normal to stora an array in the database and explode it in the output, let me know if there is a better way, thank you very much for your help! – Dev_At_InboundHorizonsInc May 13 '13 at 19:09
Detailed explaination below. – Declan_K May 13 '13 at 19:24

score 1 · Answer 2 · answered May 13 '13 at 19:24

In response to the question posed in the comments above re: Normal Form

I don't know what data you are storing in the array of integers. As an example I will use the a sample relation "Person Knows Pogramming Language" where (using your method) you would store the language ID's directly in the person table. The sample data might look like this;

Language Table
ID  | Name
------------
1   | C#
2   | SQL
3   | Java

Person Table
ID  | Name  | Languages
-------------------------
1   | Tom   | 1,2
2   | Dick  | 2,3
3   | Harry | 1,2,3

Normal Form would move the Person to Language relationship into a seperate table (do a google search for "xref tables"). Lets call out table PersonLanguage. When we add this, we remove the 'Languages' column from the person table.

PersonLanguage
PersonID    | LanguageID
--------------------------
1           | 1
1           | 2
2           | 2
2           | 3
3           | 1
3           | 2
3           | 3

Now if you just want people who know Java, your query is as follows

SELECT  P.NAME
FROM    PERSON P
INNER JOIN
        PERSONLANGUAGE PL
ON      P.ID = PL.PERSONID
INNER JOIN
        LANGUAGE L
ON      PL.LANGUAGEID = L.ID
WHERE   L.NAME = 'JAVA'

Declan_k, thank you, yes the above example is similar to why I am storing an array in my table such as the example used for Languages in the Person Table, then joining it with a separate table similar to your Language Table example; I probably did not communicate that well enough in the initial question, glad to know I am on the right track, thanks again for your help! — Dev_At_InboundHorizonsInc, May 14 '13 at 04:07

Selecting A Row Based On One Value In A Multi-Value Array Using IN Clause

2 Answers2