update multiple-choice checkbox in a database

Question

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_user 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.

Answer 1

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.