Jump to content


Photo

store multiple values in cell


  • Please log in to reply
1 reply to this topic

#1 proctk

proctk
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts

Posted 06 August 2006 - 11:40 PM

HI,

is it possible to store multiple values in the same cell in a mysql table.

I'm trying to set up my tables so that many people can view certain rows of information. I added a column called otheruser_id which adds the persons user_id to the table. the problem isvthat there could be more then one person that may need to link to the table row

is there away to store mutliple values in a table cell


------------------

the values will not be entered at the same time. today user_id is add and tomorrow another persons user id will be added.

when the user id is add the person a screen will display showing the information in the row.

can I add values at different times and how do I use a specific value in the table cell

----------------

I'll try and explain better

in column Otheruser_id

the vaule in row 5 is 6

at a latter time I will need to add another value to column Otheruser_id say 10.

so the value in column Otheruser_id would be 5 10 (and possibly more values)

How can I update a column and retain the current value in the cell.
I could user , to seperate my numbers but not shure how to strip the , to extract the value.



any help is great


#2 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 07 August 2006 - 12:25 AM

You can only do this if your column is a string type, and you append the values on the end of the string with some kind of separator character such as a comma or whatever else will work.

But, you really should not ever be storing anything other than atomic values in table cells. It will make it impossible to search on that column. If you find the need to have multiple values in a cell, it means you have a many-to-many relationship and you should resolve this into two relationships, many-to-one and one-to-many, by creating another table (this is one of the database normalisation rules, I can't remember which one).

So you should set up a new table which contains in each row, the other_user_id in one cell with the user_id of the person they relate to in another cell. Each relationship needs to use a separate row.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users