help formating multiple elements for a database field

[latalatina]

Hello,

i am setting multiple values, storing them in database (via checkbox), so when i insert data i check values i need, to store in database.

I had prefilled database in this way, only comma separated, like

In one field: topic1, topic2
In another field: topic1
In another field: topic3

BUT, when i set this values in backend via checkbox are stored in database with extra characters, like:

In one field: ["topic1", "topic2"]
In another field: ["topic1"]
In another field: ["topic3"]

So, fabrik is setting the [] characters and a " in every word, so for working right i supposed to rename to the new format with this characters....so how can i make a mass rename for a field? With [ at beggining ] at the end and " " in every word?


Thanks in advance!

 

[cheesegrits]

Yup, we store checkbox data in JSON format, which is a standard data exchange format.

I'm playing around with a MySQL query to do what you need ...

In the data you inserted, are you always leaving a space after the comma, so it's always ...

topic1, topic2

... and not ...

topci1,topci2

-- hugh

 

[cheesegrits]

I strongly suggest you back up your table before trying this!

Assuming you were consistent with your data, as per my last question, and that you always used a space after the comma, this should work. You'll have to run it as a query by hand, in something like phpMayAdmin:

UPDATE fab_junk SET chbox = CONCAT('["', REPLACE(chbox,', ','","'), '"]') WHERE chbox NOT REGEXP '^\\[';

... replace fab_junk with your table name, and chbox with the element (field) name.

If you were not consistent with whether you used spaces after the commas it would probably be best to run this query first:

UPDATE fab_junk SET chbox = REPLACE(chbox,' ','') WHERE chbox NOT REGEXP '^\\[';

... which should strip out all spaces from the data in that field, and then run a slightly modified version of my first suggestion, which now assumes there are no spaces:

UPDATE fab_junk SET chbox = CONCAT('["', REPLACE(chbox,',','","'), '"]') WHERE chbox NOT REGEXP '^\\[';

-- hugh

 

[latalatina]

Great hugh! That?s amazing, thank you so much! Really i had values without spaces so your last option was the fine for me:

UPDATE fab_junk SET chbox = CONCAT('["', REPLACE(chbox,',','","'), '"]') WHERE chbox NOT REGEXP '^\\[';

Thank you so much again!