CODEWITHSUNDEEP
phpmysql
GreyRoofPigeon
GreyRoofPigeon

Reputation: 18113

MySQL updating "link" table

I've got a table users and a table salesmen.
I want to assign salesmen to users. One salesmen can be linked to multiple users.

So I made a users_salesmen "linking" table with the colums user_id and salesmen_id.

I have a page where I can link them

       | Salesemen 1|  Salesmen 2 | Salesmen 3 |
-------|------------|-------------|------------|
User 1 | checkbox   |  checkbox   | checkbox   |
-------|------------|-------------|------------|
User 2 | checkbox   |  checkbox   | checkbox   |
-------|------------|-------------|------------|
User 3 | checkbox   |  checkbox   | checkbox   |
-------|------------|-------------|------------|

The checkbox is in HTML:

<input type="checkbox" name="user_id[6][]" value="3" />

Where the value is the salesmen_id

An example of how the could be linked:

       | Salesemen 1|  Salesmen 2 | Salesmen 3 |
-------|------------|-------------|------------|
User 1 |     x      |             |     x      |
-------|------------|-------------|------------|
User 2 |            |     x       |            |
-------|------------|-------------|------------|
User 3 |            |     x       |     x      |
-------|------------|-------------|------------|

Which would result in a the users_salesmen

salesmen_id  |   user_id
------------ |------------
    1        |     1
    2        |     2
    2        |     3
    3        |     1
    3        |     3

So my question is, how do I update the table after I made some changes.
I've always did:

Is this the right way, or is there a better way of updating the data?

Upvotes: 0

Views: 59

Answers (4)

Avishek
Avishek

Reputation: 824

I don't think your procedure is a feasible one. Because, if you are allowed to update only row (in your HTML) in UI , that means you are allowed to update data only for a single user at a time. So, on that time you should only delete and reinsert data for that user only, other should remain same. But if you are allowed to modify more than user's record at a single call, then you have to delete all the records of that link(map) table and reinsert them.

Upvotes: 0

Lelio Faieta
Lelio Faieta

Reputation: 6663

I agree with @MarcB that your is the best approach. Anyway, just for discussion purpose I can suggest you another way that is with array_diff to get only what to add and what to remove:

function array_diff_ORG_NEW(&$org, &$new, $type){
    switch($type){
        case 'VALUES':
            $int = array_values(array_intersect($org, $new)); //C = A ^ B
            $org = array_values(array_diff($org, $int)); //A' = A - C
            $new= array_values(array_diff($new, $int)); //B' = B - C
            break;
        case 'KEYS':
            $int = array_values(array_intersect_key($org, $new)); //C = A ^ B
            $org = array_values(array_diff_key($org, $int)); //A' = A - C
            $new= array_values(array_diff_key($new, $int)); //B' = B - C
            break;
    }
}

This will look at both the keys or the values and will provide you all the items that are to be added or removed.

Upvotes: 1

Marc B
Marc B

Reputation: 360592

Updating checkbox lists is a pain, but the easiest/cleanest method is basically what you're doing:

start transaction
delete old stored checkbox data
insert all checkboxes from form
commit transaction

Otherwise, you're stuck with getting the old list, comparing it to the new list, and building up separate insert/delete queries to delete any "unchecked" boxes and insert any "checked" ones.

It's easier to just nuke everything old and insert everything new. The transaction guarantees you end up with a consistent result at the end, and hides the deletions/insertions from other parallel code up until you actually commit.

Upvotes: 0

Tim Sparks
Tim Sparks

Reputation: 71

You say that "One salesmen can be linked to multiple users.". If this is true and the opposite is NOT true ("one user can be linked to multiple salesmen") then the following is a good solution.

Have a 'salesman_id' column in your users table, and simply add the salesman id to the relevant users row. Your salesmen table will then simply have the salesman details and their id - the users table will contain the id of the salesman assigned to them. Each salesman could have several users assigned.

Upvotes: 1

Related Questions