update multiple-choice checkbox in a database

Multiselect option

For example, a user with the id of '10' has the option to choose what languages he speaks. He chooses each language using a "multiple select" or "multiple checkboxes", like these:

<input name="lang[]" value="en" type="checkbox" />
<input name="lang[]" value="es" type="checkbox" />
<input name="lang[]" value="jp" type="checkbox" />

What I want to know is, how does the db table that stores these options look like and how would the server side php insert/update them?

My guess so far

What I'm imagining is that the table will look something like this:

CREATE TABLE user_langs (id INT AUTO_INCREMENT PRIMARY KEY, lang VARCHAR, fk_u开发者_高级运维ser INT);

While to INSERT the values into a new user, php does a simple insert loop:

$stmt = $pdo->prepare('INSERT INTO user_langs (lang,fk_user) VALUES(?,?)');
foreach($_POST['lang'] as $lang){
    $stmt->execute(array($lang, $user_id));
}

The problem I'm getting is with UPDATES, the simplest way would be to delete all the existing entries of this user, and insert the new ones.

$stmt1 = $pdo->prepare('DELETE FROM user_langs WHERE fk_user=?');
$stmt1->execute(array($user_id));
$stmt2 = $pdo->prepare('INSERT INTO user_langs (lang,fk_user) VALUES(?,?)');
foreach($_POST['lang'] as $lang){
    $stmt2->execute(array($lang, $user_id));
}

But I think this will over increment the primary id too quickly if it's actively used, even if the upper limits of the id are astronomical I don't like the idea of polluting my database, so I'm guessing I'm doing something wrong, so I would like to know how the pro's handle it.

You can also use some kind of diffs.

Algorithm:

1. User posted the form with selected languages
2. You select current languages
3. Using array_diff() twice (diff between old and new, and opposite one) you get 2 arrays of languages which you need to delete, and which you need to add
4. According to the arrays from 3 you perform one INSERT and one DELETE query

This problem seems to come from the fact that the id in the above example is artificial, the solution is to use multi-column primary keys also called Compound key which I was unaware about when I asked the question.

The solution then becomes to use a table like this:

CREATE TABLE user_langs (
  lang VARCHAR, 
  fk_user INT, 
  PRIMARY KEY(lang,fk_user)
);

This improves data integrity because a single user can no longer have 2 entries of the same language.

To insert a value you do the same:

$stmt = $pdo->prepare('INSERT INTO user_langs (lang,fk_user) VALUES(?,?)');
foreach($_POST['lang'] as $lang){
    $stmt->execute(array($lang, $user_id));
}

And the simplest way to handle an update is to delete all entries bound to this user, and insert the correct ones again in the same manner:

$stmt1 = $pdo->prepare('DELETE FROM user_langs WHERE fk_user=?');
$stmt1->execute(array($user_id));
$stmt2 = $pdo->prepare('INSERT INTO user_langs (lang,fk_user) VALUES(?,?)');
foreach($_POST['lang'] as $lang){
    $stmt2->execute(array($lang, $user_id));
}

You can also do it like zerkms suggests, to SELECT all the languages, then run array_diff to find the old and new values, and delete the old ones and insert the new ones, but this means running 3 queries and comparing results, while in case of languages it is very rare for a user to speak more than 3, which is why just deleting and inserting again seems like the best option.