Is there a way to store multiple selected checkbox values in one database column?

Question

I was working on a personal project where I have an image grid with checkboxes, and wanted to insert in my database the values of the checkboxes, but can't figure out how to. I had success into inserting one of the values in my database, but when it gets to two or more, it only shows the first one.

My checkbox grid:

/* Responsive layout - makes a two column-layout instead of four columns */

@media (max-width: 800px) {
  .row {
    grid-template-columns: 1fr 1fr;
    grid-gap: 1%;
    align-items: start;
  }
}

.gridlabel {
  font-size: 10px;
  margin-top: 70px;
  color: #FFFFFF;
  position: absolute;
  font-weight: 900;
}

.gridlabelg {
  text-align: center;
  margin-left: 12px;
  font-size: 10px;
  margin-top: 60px;
  color: #FFFFFF;
  position: absolute;
}

ul {
  list-style-type: none;
  grid-template-columns: 1fr 1fr 1fr 1fr;
}

li {
  display: inline-grid;
}

input[type="checkbox"][id^="cb"] {
  display: none;
}

.lblimgs {
  border: 1px solid #fff;
  padding: 0px;
  display: grid;
  position: relative;
  margin: 2px;
  cursor: pointer;
}

.lblimgs:before {
  background-color: white;
  color: white;
  content: " ";
  display: block;
  border-radius: 30%;
  border: 1px solid grey;
  position: absolute;
  top: -5px;
  left: -5px;
  width: 25px;
  height: 25px;
  text-align: center;
  line-height: 28px;
  transition-duration: 0.4s;
  transform: scale(0);
}

.lblimgs img {
  height: 100px;
  width: 144px;
  transition-duration: 0.2s;
  transform-origin: 50% 50%;
  border-radius: 10px;
}

 :checked+.lblimgs {
  border-color: #ddd;
}

 :checked+.lblimgs:before {
  content: "✓";
  background-color: rgb(173, 168, 168);
  transform: scale(1);
}

 :checked+.lblimgs img {
  transform: scale(0.9);
  /* box-shadow: 0 0 5px #333; */
  z-index: -1;
}

<div class="images">
  <ul>
    <li><input type="checkbox" id="cb1" name="hotel" value="casamontanha" />
      <label for="cb1" class="lblimgs"><img src="pictures/casamontanha.jpg" /></label>
    </li>
    <li><input type="checkbox" id="cb2" name="hotel" value="fillitheyo" />
      <label for="cb2" class="lblimgs"><img src="pictures/filitheyo.jpg" /></label>
    </li>
    <li><input type="checkbox" id="cb3" name="hotel" value="hardrock" />
      <label for="cb3" class="lblimgs"><img src="pictures/hardrock.jpg" /></label>
    </li>
    <li><input type="checkbox" id="cb4" name="hotel" value="rioquente" />
      <label for="cb4" class="lblimgs"><img src="pictures/rioquente.jpg" /></label>
    </li>
    <li><input type="checkbox" id="cb5" name="hotel" value="sheraton" />
      <label for="cb5" class="lblimgs"><img src="pictures/sheraton.jpg" /></label>
    </li>
    <li><input type="checkbox" id="cb6" name="hotel" value="apart" />
      <label for="cb6" class="lblimgs"><img src="pictures/apart.jpg" /></label>
    </li>
    <li><input type="checkbox" id="cb7" name="hotel" value="lecanton" />
      <label for="cb7" class="lblimgs"><img src="pictures/lecanton.jpg" /></label>
    </li>
    <li><input type="checkbox" id="cb8" name="hotel" value="pestana" />
      <label for="cb8" class="lblimgs"><img src="pictures/pestana.jpg" /></label>
    </li>
    <li><input type="checkbox" id="cb9" name="hotel" value="catello" />
      <label for="cb9" class="lblimgs"><img src="pictures/catello.jpg" /></label>
    </li>
    <li><input type="checkbox" id="cb10" name="hotel" value="wishnatal" />
      <label for="cb10" class="lblimgs"><img src="pictures/wishnatal.jpg" /></label>
    </li>
    <li><input type="checkbox" id="cb11" name="hotel" value="malta" />
      <label for="cb11" class="lblimgs"><img src="pictures/malta.jpg" /></label>
    </li>
    <li><input type="checkbox" id="cb12" name="hotel" value="lhc" />
      <label for="cb12" class="lblimgs"><img src="pictures/lhc.jpg" /></label>
    </li>
  </ul>
</div>

I was trying to do something like:

$cb1=$_POST['hotel'];  
$cbx1="";  
foreach($checkbox1 as $cbx1)  
   {  
      $cb1 .= $cbx1.",";  
   }

New to programming, please understand!

the easiest way is to use a formData, but since you don't use a form, it requires some DIY in JS code — Mister Jojo, Dec 21 '21 at 00:08

Vincent Menzel · Answer 1 · 2021-12-21T00:14:49.273

Yest there is but please don't! Sooner or later this can lead to massive performance issues when you try to query for specific data. Please see this for more information. https://en.wikipedia.org/wiki/Database_normalization. However if you really want to you can just put everything into an array, stringify it with json_encode(...) and json_decode(...) it when you read it. Sometimes it can make sense if you never intend to query it or the values are only intended for long time storage or maybe logs.

I once worked on a project where the loading time of some pages spiked to over 2min because of some similar implementation and we had to invest multiple weeks on trying to work around the issue by caching the pages and doing all sorts of workarounds. Lesson: Stick to best practices and spend the time to create the columns for your data.

// get your data into an array like this
$data = ['cb1' => true, 'cb2' => false]; // ... with the rest of the checkboxes

// transform to json
// this will return a string which you can then save into you database
$str = json_encode($data)

// when you read the data from the database you do the opposite
// then you get back the array with your key-value pairs
$data2 = json_decode($str)

It's a pretty simple application and I really wanted to do it that way. Thanks for the link, gonna study this! — eitibiti, Dec 21 '21 at 00:07
In that case there wont be an issue. I will add an example to my answer. — Vincent Menzel, Dec 21 '21 at 00:09

score 1 · Answer 2 · answered Dec 21 '21 at 00:19

Yes there is, but please don't. Sooner or later you'll want to change something and your data will either become useless or grow out of control, both being unmanageable. (I borrowed this phrasing from Vincent Menzel's answer.)

You can store the true/false values as bits in an integer. You treat the "cb" number as the exponent of 2, so "cb1" becomes 2¹ and "cb3" becomes 2³, and so on. It's not pretty and you start to get large numbers quickly. It's "fine" that you have "only" 12 checkboxes, since a normal integer is going to be 32 or 64 bits depending on your DB, but this comes at a cost. What if you remove "cb8"? You're stuck with that bit. And if you add a new checkbox, you add a new bit, since you don't want to confuse the new checkbox with any old checkboxes. This might seem like a good idea for a small project, but it becomes bad practice when trying it on a larger project with multiple devs and no one knows what the bits mean. And you in 2 years probably won't know what the bits mean, either, even on your own project.

The best practice is to let every data point, in this case Booleans, have their own column. That way you know exactly what those columns mean. Or at least you can document it considerably easier. And if you remove a checkbox and never want to look back at the data, you can just delete the column. A new checkbox gets a new column. Yes, this is still grown and data becomes useless, but it's no longer unmanageable.

Is there a way to store multiple selected checkbox values in one database column?

2 Answers2