0

This is an example of a string inserted into a mysql db field after a multiple choice checkbox form field has been submitted. (including the brackets): [1,0,1]

1=box checked

When the first number in the string = 1 value = pie, when the second number in this string =1 value= cookies, when the third number in this string =1 value = fruit. So the output for this string would be cookies, fruit.

I need to assign values to these numbers in an sql query. Something like:

select answer_data from answers where if the first number in the string = 1, the value is pie; if the 2nd number in the string = 1, the value is cookies;if the third number in the string=1, the value is fruit;

phillystyle123
  • 129
  • 10
  • please take a look into https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad if tge number is fixed on entries , you can remove the brackest and use SUBSTING_INDEX to access all numbers, but that i a pain. – nbk Nov 21 '20 at 19:48
  • (1) Treat the string as a JSON array. (2) Create a dependency table (position - value) (3) Link by `"$[I]" = i`, group and select. – Akina Nov 21 '20 at 19:53
  • MySQL doesn't support checkboxes, which are a construct of some kind of application code. – Strawberry Nov 22 '20 at 09:25

1 Answers1

1

Caveat: Storing the data in this implied mapping to an array definition is a very brittle design. A better approach would be to just store the actual text values as JSON.

However, if you must use that approach...

If you defined a stored procedure

You could use the Substring function to tease out the values - and assemble desired output

intltechventures
  • 181
  • 8
  • thanks for the help. I have an issue with changing the data type or entering the data in a different way - the db table in question is related to a third party plugin – phillystyle123 Nov 22 '20 at 00:20
  • 1
    https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html looks promising! will test – phillystyle123 Nov 22 '20 at 00:24