LinuxQuestions.org
Support LQ: Use code LQ3 and save $3 on Domain Registration
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 12-15-2002, 02:43 PM   #1
antken
Member
 
Registered: Nov 2000
Location: england
Distribution: latest Mandrake
Posts: 368

Rep: Reputation: 30
storing multiple values within one field in mysql


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?
 
Old 12-15-2002, 03:20 PM   #2
lhoff
Member
 
Registered: Jun 2001
Location: Chicago
Distribution: Mandrake 10.0 Official
Posts: 181

Rep: Reputation: 30
Re: storing multiple values within one field in mysql

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.
 
Old 12-15-2002, 03:49 PM   #3
leed_25
Member
 
Registered: Jul 2002
Location: san francisco
Distribution: freebsd
Posts: 102

Rep: Reputation: 17
You could also store them as a serialized array
 
Old 12-15-2002, 04:18 PM   #4
antken
Member
 
Registered: Nov 2000
Location: england
Distribution: latest Mandrake
Posts: 368

Original Poster
Rep: Reputation: 30
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
 
Old 12-15-2002, 05:50 PM   #5
lhoff
Member
 
Registered: Jun 2001
Location: Chicago
Distribution: Mandrake 10.0 Official
Posts: 181

Rep: Reputation: 30
...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!

Last edited by lhoff; 12-15-2002 at 05:56 PM.
 
Old 12-15-2002, 05:59 PM   #6
Reasor
LQ Newbie
 
Registered: Aug 2002
Location: Lexington,KY
Distribution: Red Hat 8.0
Posts: 7

Rep: Reputation: 0
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.
 
Old 12-15-2002, 06:05 PM   #7
antken
Member
 
Registered: Nov 2000
Location: england
Distribution: latest Mandrake
Posts: 368

Original Poster
Rep: Reputation: 30
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?
 
Old 12-15-2002, 06:17 PM   #8
Reasor
LQ Newbie
 
Registered: Aug 2002
Location: Lexington,KY
Distribution: Red Hat 8.0
Posts: 7

Rep: Reputation: 0
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

Last edited by Reasor; 12-15-2002 at 06:19 PM.
 
Old 12-15-2002, 10:08 PM   #9
lackluster
Member
 
Registered: Apr 2002
Location: D.C - USA
Distribution: slackware-current
Posts: 488

Rep: Reputation: 30
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
quick hand with awk multiple field separators pld Programming 10 05-28-2010 07:51 AM
Storing and selecting multiple configurations qscomputing Linux - General 2 09-17-2005 03:00 AM
xmodmap for multiple values malo_umoran Slackware 3 03-27-2005 09:39 AM
Multiple Field Seperators in Awk... TheDarktrooper Programming 6 05-06-2004 04:50 AM
Multiple values with SQL (Noob stuff) Travis86 Programming 6 09-03-2003 08:38 PM


All times are GMT -5. The time now is 09:24 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration