hi,

how would i go about storing multiple values ( numbers and words ) within one field of a mysql database and then extracting them again as and when i need them?

for example:
i want to store the values: 1, 2, foo, tree, and monkey all in the same field in a database

then i want to extract it and put them on seperate lines example:
1
2
foo
tree
monkey

any ideas?

Assuming PHP?

To store:
$a = 1;
$b = 2;
$c = "foo";
$d = "tree";
$e = "monkey";
$delim = "|";
$storstrg = $a.$delim.$b.$delim.$c.$delim.$d.$delim.$e;
//returns a string "1|2|foo|tree|monkey"

Insert this string into your field. To retrieve your values, retrieve the string from the field and consult the PHP manual at http://www.php.net/manual/en/function.split.php
for help on parsing the string.

Note: this is not tested. Perhaps the character chosen for $delim will cause hiccups. If so, choose another one.

You could also store them as a serialized array

thanks for that

i think i will go for the first suggestion it now seems to be the most senseable solution


and yes i am using php, no matter how many questions i ask here i allways seem to miss out some information that could help in the solution!! :-)

thanks

...And I sometimes forget to ask qualifiers, such as:
So, why do you want to do this instead of just adding some columns to your SQL? What if you need to search or sort based on foo? It kind of becomes difficult after foo has been concatenated into the rest of that mess...; it's time for you to lobby vigorously for CREATE, ADD and UPDATE privileges from your dbadmin.

Cheers!

Try storing them in a multiple dimensional array and extracting them that way, you may find that you can store them easier and extract them with a for loop rather then doing it one by one.

the reason why i ask is because we are creating some sort of a shop

in this shop there are various items that have different sizes shape colours etc


because the sizes can range from 2 options to 30 options i dont want to have all those empty fields lurking around in there

so i would like to get it all in one column to make it easer on the programming side ( as far as i can see ) and easyer on the sql server.



Reasor, do you have any examples of these arrays?

the application of these arrays is perfect for an item with mulitple characteristics. e.g. ary[3][4][3][4][5].
THis would be an array with 3 storage areas in the first column 4 in the second, etc, etc. You can set them to whatever you want to set them to, most of the time it is good to make these integer variables a constant. It is pretty much a matrix that has multilple dimensions for storing information.
e.g ary[item][price][location][area][markup].......you can go from there. the more dimensions you put in the array the more integrated your loops have to be and this can be difficult if you let it get that way, so stay organized when you beginn filling it with information.

oh, and when you want to reference the third items price you would type something like ..

cout << ary[3][3] << endl;
you know what to do from here i'm sure

if the options are always the same, then you might consider researching relational database some more. a table called options with ids and descriptions could solve your problem without parsing. we have a saying around here : let the database handle it. actually on second thought, there may be some parsing, but then it would be all ids and could easily be split and joined.