Question: I've 3 tables.

Users (id, mail, username, etc..)

Practices (id, name)

UsersPractices (userId, practiceId)

The last one is the relational table which is n-n.

I'd like to update this one, depending on the user's will.

It means, that he could want to add, or remove some his practices.

What algorithm could i use to do this ?

Should it be better to let this job (if there is a way) to the database engine ?
Or should i write my own algorithm to handle the data, then do my requests to the db ?

**EDIT:**

To be clear :

```
___________________________
| UserId    |  PracticeId |
|-----------|-------------|
|    12     |     21      |
|-----------|-------------|
|    12     |     18      |
|-----------|-------------|

```

Maybe, the user will try to change his practice from 21 to 15 but wants to keep the practice 18.

So, from the request, i'll get practices = array(15,18);

that means the users practice will look like : 

```
___________________________
| UserId    |  PracticeId |
|-----------|-------------|
|    12     |     15      |
|-----------|-------------|
|    12     |     18      |
|-----------|-------------|

```

SO what is the best way to achieve this ?

Should i select & check each practice, and then, delete if needed ?

Delete them all, and add the news.

Answer: 
Assuming you have InnoDB tables, do it as a transaction.

The basic idea here is to wipe the slate clean and then create/re-create the associations. This is easier than determining the delta of each update (i.e., inspecting each row individually and determining if it's an INSERT, UPDATE, or DELETE action).

Here's the raw SQL you would use

```
BEGIN;

DELETE FROM UsersPractices
 WHERE userId = [User ID];

INSERT INTO UsersPractices (
       userId
     , practiceId)
VALUES ([User ID], [Practice ID 1])
     , ([User ID], [Practice ID 2])
     ...
     , ([User ID], [Practice ID N]);

COMMIT;

```
Well - the easiest way I could think of to handle this sort of thing would be to build a `<select>` element something like this:

```
function getUserPractices($userId) {
  $data = array();
  $result = mysql_query("SELECT Practices.* FROM UsersPractices LEFT JOIN Practices on UsersPractices.practiceId = Practices.id WHERE userId = '".mysql_real_escape($userId)."'");
  while ($row = mysql_fetch_array($result)) { 
    $data[$row['id']] = $row['name'];
  }
  return $data;
}

// assuming $user is your user row...
$user_practices = getUserPractices($user['id']);
$result = mysql_query("SELECT * FROM Practices ORDER BY name");
echo "<select multiple='multiple' name='practices[]'>";
while ($row = mysql_fetch_assoc($result)) {
  echo "<option value='".$row['id']."'";
  if (isset($user_practices[$row["id"]])) echo " selected='selected'";
  echo ">".htmlentities($row['name'])."</option>";
}
echo "</select>";

// This should result in an array $_POST['practices'] that contains practiceId's
// will come back to write more about actually inserting the change into the db
// but simplest way is to delete all of them based on userId then insert the new ones

```