Multiple data in one mysql field

[Andy92]

Hi there,

Is there any way of storing multiple data in 1 mysql field and seperate it with commas like...

word 1, this, another thing, 4th thing, hello

etc, and that would all be in 1 mysql field, and you could be able to pull out each word seperatly?

[guelphdad]

Avoid it. it violates first normal form of database normalization and make searching, matching and other tasks much more difficult than they need be.

this article (http://www.guelphdad.wefixtech.co.uk/sqlhelp/lists.shtml) should give you insight as to why it is a bad idea.

[sftl99]

you could always explode the data. let's say the field name is "data". when you display $data you get "word 1, this, another thing, 4th thing, hello" (to use your example). the following should work...
//just for visually seeing it...
echo($data);
//this would display word1, this, ...
explode(",",$data);
//then recall the results
echo($data[0]);
//displays "word 1"
echo($data[1]);
//displays "this", etc, etc

[guelphdad]

yes you can explode data.

let me give you a list of ten items
air, bees, crowns, duncecaps, earwigs, fries, gravel, horses, icecream, jam

each user in the table holds 5 items or more

There are 25,000 users.

please tell me how many of those users have at least three items in common?

I will tell you using explode and having to compare against strings will be significantly slower than using a normalized table.

here (http://www.guelphdad.wefixtech.co.uk/sqlhelp/lists.shtml) is an article for further clarification.

[sftl99]

I totally agree, however he may have needed a workaround regardless of whether or not it's a good idea.

[guelphdad]

agreed, sometimes you have to work with the slop left over by other programmers. never any harm in finding out how to fit a square peg in a round hole. my reading of the question though was it wasn't a done deal so want to explain why to avoid doing it rather than just suggest not doing it.

i find it easier normalizing the data and then using explode after gathering variables in php and then matching that data against the normalized data.

i just sometimes see people use too many recursive loops, unnormalized data etc. etc. so may be over cautious about explaining it.

depends on whether or not the data needs searched and how it would be queried really.