Insert dropdown multiple checkboxs to mysql db

Question

I have a register.php and I'm trying to insert the data into users table. The problem I have is with "languages" field - I've created a dropdown list with multiple checkboxs. I want the user ofcourse to be able to insert to his profile more than one languages he speaks. Here is a part of the registeration form:

 <script type="text/javascript">

         var expanded = false;

        function showCheckboxes() {
          var checkboxes = document.getElementById("checkboxes");
          if (!expanded) {
            checkboxes.style.display = "block";
            expanded = true;
          } else {
            checkboxes.style.display = "none";
            expanded = false;
          }
        }   
        </script>


        <tr>
      <td>Languages</td>
      <td dir="rtl">
          <div class="multiselect" dir="rtl">
          <div class="selectBox" onclick="showCheckboxes()" dir="rtl">
        <select>
        <option>Select an language</option>
        </select>
        <div class="overSelect" dir="rtl"></div>
        </div>
        <div id="checkboxes" name="languages">
        <label for="one">  
            <input type="checkbox" id="one" />German</label>
            <label for="two">
            <input type="checkbox" id="two" />English</label>
            <label for="three">
            <input type="checkbox" id="three" />French</label>
    </div>
  </div>  
      </td>
      </tr>

Now - this my query for inserting the register form into users table in the database [relevant parts of the code, the other is working fine]:

 $languages= mysqli_real_escape_string($db, $_POST['languages']);

        $sql="INSERT INTO users(
       ....
         languages, 
        ) 

        VALUES(...
        '$languages', 
                )";

        mysqli_query($db,$sql);

What is the right way to insert the data if for example the user will mark check box "english" and "german" then the languages field in the database will be like this: "german,english"? at least for now nothing is getting inserted.

Update : According to the answers here I wrote this:

 <td>Languages</td>
      <td dir="rtl">
          <div class="multiselect" dir="rtl">
          <div class="selectBox" onclick="showCheckboxes()" dir="rtl">
        <select>
        <option>Select an option</option>
        </select>
        <div class="overSelect" dir="rtl"></div>
        </div>
        <div id="checkboxes">
        <label for="one">  
            <input type="checkbox"  id="one" name="languages[]">German</label>
            <label for="two">
            <input type="checkbox" id="two" name="languages[]" >English</label>
            <label for="three">
            <input type="checkbox" id="three" name="languages[]">French</label>
    </div>
  </div>  
      </td>

And:

$sql="INSERT INTO users(
...
 languages,
...
) 

VALUES(
...,
'" . implode(",", $_POST['languages']) . "', 
        )";

But when I checked all the checkboxes I get "on,on,on" Or two checkboxes then "on,on" - What it's mean? Answer : Never mind. forgot putting value field.

input fields without a `name=""` attribute will not get sent to the PHP script! — RiggsFolly, Apr 25 '17 at 16:37
An `input` inside a `label` field? Probably not the best way to write that piece of code. — Script47, Apr 25 '17 at 16:39
I can't see anything wrong with it, if a value like `english,german` is being inserted into the database you can easily convert it into an array, either with PHP - `explode` - or JS - `.split()`. — yaakov, Apr 25 '17 at 16:42
Your code is likely vulnerable to [**SQL injection attacks**](https://en.wikipedia.org/wiki/SQL_injection). You should use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, Apr 25 '17 at 17:02

score 4 · Answer 1 · answered Apr 25 '17 at 16:53

As shown in the following code, fist of all add the same 'name' attribute to all the checkboxes. So that you can grab all the options that the user has selected, to an array in the PHP script.

<input type="checkbox" id="one" name="language[]" />German</label>
<label for="two">
<input type="checkbox" id="two" name="language[]" />English</label>
<label for="three">
<input type="checkbox" id="three" name="language[]" />French</label>

Now in the PHP script you can grab the selected values to a PHP array.

$languages= mysqli_real_escape_string($db, $_POST['language']);

Now to add the selected languages to a database you need to compose one string containing all the values in the $languages array since you have a single column to store all the languages in the database. You can compose the string as shown in the following code using a comma as the delimiter.

$selectedLanguages = "";
foreach($languages as $value){
    $selectedLanguages .= $value.',':
}

Now you run a query to store teh values in the database.

    $sql="INSERT INTO users(
   ....
     languages, 
    ) 

    VALUES(...
    '{$selectedLanguages}', 
            )";

    mysqli_query($db,$sql);

score 0 · Answer 2 · answered Apr 25 '17 at 16:48

two parts:

Part 1: Change your HTML so your script receives an array for $_POST['languages']

HTML should look like:

<input type=checkbox name="languages[]" value="German">German
<input type=checkbox name="languages[]" value="french">french
<input type=checkbox name="languages[]" value="english">english

Part 2: rather than store the values comma delimited in the database I suggest using php function serialize() to convert an array to a string.

example:

$languages = serialize($_POST['languages']; $sql="INSERT INTO users(languages) VALUES('$languages')";

also: The above code is not safe. You should sanitize input from user before storing in the database.

score 0 · Accepted Answer · edited May 23 '17 at 12:10

There are few issues with your code, such as:

There's no point using name="languages" in the div element. You're not using this name attribute anyway, and removing this won't make any difference to your code.
Not sure why you're using both <select ...> ... </select> and <input type="checkbox" ... for the exact same purpose i.e to select multiple languages. Use only one for this purpose, not both.
There are no name and value attributes in your checkbox elements, that's why you're not getting any value with the $_POST array. Add name and value attribute in each of your checkbox input element.

So your code should be like this:

<div id="checkboxes">
    <label for="one">  
    <input type="checkbox" name="languages[]" value="german" id="one" />German</label>
    <label for="two">
    <input type="checkbox" name="languages[]" value="english" id="two" />English</label>
    <label for="three">
    <input type="checkbox" name="languages[]" value="french" id="three" />French</label>
</div>

Subsequently, you need to change your SQL query in the following way,

$sql="INSERT INTO users(..., languages, ...) 
VALUES(..., '" . implode(",", $_POST['languages']) . "', ...)";

Sidenote: Learn about prepared statement because right now your query is susceptible to SQL injection attack. Also see how you can prevent SQL injection in PHP.

Lawrence Job · Answer 4 · 2017-04-25T17:01:45.080

There are a couple of things to consider with your approach:

That for PHP to access your values, each checkbox needs a name and a value.
That when reading the values from $_POST in PHP, you'll need to interpret the array somehow.

Part 1: Making them accessible to PHP

To communicate to the server that you want the checkboxes to be interpreted as a series of options, the standard is to give them a name followed by square brackets: []

What I've done is added name="languages[]" to your inputs to group them and call them languages, and added value="..." to assign them a value.

<label for="one">  
    <input type="checkbox" id="one" name="languages[]" value="german" /> German
</label>
<label for="two">
    <input type="checkbox" id="two" name="languages[]" value="english" /> English
</label>
<label for="three">
    <input type="checkbox" id="three" name="languages[]" value="french" /> French
</label>

Part 2: Reading them in PHP

Once you know you're sending the values to PHP as you intend, you can access the values in the format you hope using:

// combine the languages, separated by commas
$my_string = implode(",", $_POST['languages']);

This code will combine all of the elements of the array $_POST['languages'] into a string, much like the one you're looking for e.g. german,english,french.

Consider adding some logic to check for unexpected or invalid values, including empty arrays.

@Toto88 I'm sure you now know this, but what you missed in your update was the `value` attribute on your checkboxes. Best of luck with your project. — Lawrence Job, Apr 26 '17 at 10:14

Insert dropdown multiple checkboxs to mysql db

4 Answers4

Part 1: Making them accessible to PHP

Part 2: Reading them in PHP