Best way to handle multiple array data in MySQL?

Question

I'm working on a project where i will be storing arrays/lists in a MySQL table and make a PHP based search on the information in the lists.
How i basically thought of doing this would be to put in the data like this:

column1
,2,6,9,30,
,3,5,8,
,5,6,13,16,17,

column2
,b,g,k,ro,
,c,e,t,
,d,h,i,ar,ez,

then search like this:

SELECT * FROM table_name WHERE column1 LIKE '%,3,%' OR column2 LIKE '%,h,%'

But i might have to search for five items in column1 and three in column2, which would make the query long and possibly slow?..

What i want to know is, is this a smart way to do it? Is there faster and/or better methods?..

Let me know if you have questions or if i'm not clear enough.

roberttdev · Accepted Answer · 2011-03-26 23:03:01Z

4

If I understand correctly, it would make sense to make the DB structure match the arrays better instead of storing string arrays.

For instance, you could use a "LIST" and a "LIST_ITEM" table, with a foreign key from LIST_ITEM.LIST_ID to LIST.LIST_ID. Each entry in List_Item would be an entry in that list. Then queries for those list items could return which lists they were in much faster than having to traverse long strings and compare characters.

answered Mar 26, 2011 at 23:03

roberttdev

47.2k2 gold badges23 silver badges23 bronze badges

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

1 Comment

Kristoffer la Cour Over a year ago

Okay, got confused, thought you talked about a function called "LIST", but yeah, doesn't sound like a bad idea, i think i'll build it up like that, thanks. :)

Collectives™ on Stack Overflow

Best way to handle multiple array data in MySQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related