homepage Welcome to WebmasterWorld Guest from 54.197.215.146
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
How to define an array in sql?
jonasisme




msg:4332476
 11:19 am on Jun 29, 2011 (gmt 0)

Hi people!


I need to store an array of numbers (22,22,33,44,etc) in my database.. but what data type and (special?) attributes i have to set for this?


I googled but didn't find an answer..


Please help me, thanks!

 

rocknbil




msg:4332583
 4:02 pm on Jun 29, 2011 (gmt 0)

You **could** use varchar. But something tells me you are likely to use programming operations on these - splitting up the array **after** reading in. A more efficient way would be to use a second table and joins:

main_table
id|title
1|widgets
2|thingamajigs

array_table
id|record_id|array_member
1|1|22
2|2|14
3|2|17
4|2|22
5|1|22
6|2|19
7|1|33
8|1|44

select main_table.title as title,array_table.array_member as member where main_table.id=array_table.record_id order by main_table.title asc, array_table.array_member.asc

Then in the while loop, print out title|member

Note a group by is not needed with the ordering. This should give you

title|member
---------------
thingamajigs|14
thingamajigs|17
thingamajigs|19
thingamajigs|22
widget|22
widget|22
widget|33
widget|44

If you use this approach, you can store your numbers as int fields, which would be much faster - it would also allow you to leverage the power of mysql searches. With the varchar method you'd have to resort to (unnecessarily) complicated regexps. Instead you can do

select main_table.title as title,array_table.array_member where main_table.id=array_table.record_id and array_table.array_member = 22

title|member
---------------
widget|22
widget|22

You can also explore left, right, and inner joins if there are null values (no records in the joined table), etc - all of which you can't do storing a comma-separated list of values. Faster results, more possibilities.

brotherhood of LAN




msg:4332590
 4:07 pm on Jun 29, 2011 (gmt 0)

Going with rocknbil's suggestion of another table, you could use the GROUP_CONCAT [dev.mysql.com] function to group all the int values alongside the 'main table' record.

However, if you are only using a small set of numbers (64 or less), the SET Type [dev.mysql.com] may be a good option to you.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved