1

I've got checkbox group field with 4 days of the week - Thursday, Friday, Saturday & Sunday. I populate this field based on whether 4 separate text fields for each corresponding day of the week are populated or not.

At the moment, I'm doing this by running 15 separate queries! This is every combination of Thursday, Friday, Saturday & Sunday I can think of, which then updates the value for this checkbox group field.

The queries are listed below for info. My question is, can anyone think of a way to update this checkbox group field with a single query rather than 15 separate queries? Thanks for any help. Regards, Stephen.

UPDATE exp_channel_data SET field_id_285 = "Thursday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 = "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Friday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Saturday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Sunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 = "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday\nSaturday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday\nSaturday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Friday\nSaturday\nSunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nSaturday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nSaturday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Friday\nSaturday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Friday\nSunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Saturday\nSunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 <> "");
1
  • 4
    Wow, what an awful design!
    – Philᵀᴹ
    Commented Sep 9, 2016 at 12:32

1 Answer 1

4

Yes, you can update the group in one go using an UPDATE statement like this:

UPDATE
  exp_channel_data
SET
  field_id_285 =
    CONCAT_WS(
      '\n',
      CASE WHEN field_id_151 <> '' THEN 'Thursday' END,
      CASE WHEN field_id_152 <> '' THEN 'Friday'   END,
      CASE WHEN field_id_153 <> '' THEN 'Saturday' END,
      CASE WHEN field_id_154 <> '' THEN 'Sunday'   END
    )
WHERE
  channel_id = 14
  AND (field_id_151 <> ''
    OR field_id_152 <> ''
    OR field_id_153 <> ''
    OR field_id_154 <> ''
  )
;

Each of the CASE expressions produces either a week day name or null. The CONCAT_WS function concatenates the names using \n as the delimiter, omitting the nulls, if any.

However, even though you can do this with a single statement, the fact remains that this is a terrible design. Without more information it is hard to suggest a proper alternative but it seems likely that a junction table could be used here instead.

1
  • Thanks Andriy, that works great. This is just a hack/workaround to populate a checkbox group field within a CMS so that it's in a format needed to work with a search plugin. It's a long story!
    – Stephen
    Commented Sep 9, 2016 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.