0

Can anyone suggest me how can i store Multiple values in a single MySQL cell using Php? I am simply using the insert function but it is not helpfull at all.

Ashish
  • 114
  • 1
  • 14
  • i think you should use 'update' query here after 'insert' query, remember insert query creates a new row because of that use 'update' if you want to insert in created row; – GAURAV MAHALE Jun 30 '13 at 17:02
  • See my answer to [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Jun 30 '13 at 18:11

2 Answers2

8

No, no no, no, NO!

Never store multiple values in a single cell. It will always get back to you. It is against the 3rd rule of DB normalization.

Insert only single values in a cell and add multiple records if you must. Example

photos table:          id  | filename
                       1   | 'myfoto.jpg'

comments table:       photo_id | user_id  | comment
                      1        | 23       | 'great picture'
                      1        | 99       | 'nice'
                      1        | 7        | 'do not like it'
Community
  • 1
  • 1
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • @ uergen d What should i do then – Ashish Jun 30 '13 at 17:02
  • Normally use another table that stores 1 record for every entry. – juergen d Jun 30 '13 at 17:04
  • @ uergen d I am trying to store many comments for a single photo uploaded by the user. – Ashish Jun 30 '13 at 17:07
  • 2
    +1 There's only one problem with this answer: not enough **No**. –  Jun 30 '13 at 17:16
  • +1 but FWIW the rules you linked to, though useful, are not the customary rules of normalization. Some are simply the definition of a relation (a prerequisite to all rules of normalization), plus a couple of extra rules that are good practices but have little to do with normalization. – Bill Karwin Jun 30 '13 at 18:19
1

Although I fully agree with the previous answer that it's really bad practice to store multiple values in a single cell, in some special occasions and with certain limitations you can do this.

So, if you insist, you need to make a string containing all your values. The easiest way is to separate them with comma, line break or some other character which doesn't appear / forbidden in the actual values. Make sure to sanitize them first, though.

If your values are more complex, you can put them into array and use PHP serialization (serialize/unserialize) or json encoding (json_encode/json_decode).

Once you get have your string, simply use UPDATE query to update that DB row.

astax
  • 1,769
  • 1
  • 14
  • 23
  • 1
    +1 There are exceptions to every rule. :-) It would be helpful to describe the special occasions that justify this. For example, when you store a string of values as a indivisible set, and you won't update the string to append or remove elements, you don't need to search for specific elements, count them, sort them, join to them, or do anything else besides fetch the whole string back to your application. – Bill Karwin Jun 30 '13 at 18:14