Taylor Foster Taylor Foster - 7 months ago 17
PHP Question

SQL DELETE, UPDATE or INSERT based off array comparison

I'm going to do the best I can to try and explain this. I have an application that contain several role types. The only two that matter in this case are 'manager' and 'representative'. When a representative is created, they get assigned to one or multiple managers within that company (the company is assigned on manager creation). Note that all users get stored in the same table called Users.

When that representative to manager(s) relationship is created, the PRIMARY KEY (in my case the column 'userId' in the User Table) for the representative and the primary key for the manager are pushed into a table called ManagerRepRelationship. So for example if I have the following Users table (only going to work with the cols that matter in this case):

userId | username | role | accountId |
______ ________ ______________ _________
1 jsmith manager 14
2 mfoster manager 14
3 lcarter manager 18
4 jdoe representative 14
5 tlarson representative 18
6 rhoward representative 18


Representatives can only be assigned to a manager within their account so if that table above looks that way, the ManagerRepRelationship may look like:

repId | managerId
_____ _________
4 1
5 3
6 3


The trouble I am running into is when it comes to editing these relationships. Currently I have a separate form and page JUST to handle these relationship changes since it will be a very restricted page. Let's say in this case we are going to edit the representative
jdoe
and assign him another manager. Currently he is just assigned to
jsmith
but we want to give him two managers so we will assign him to
mfoster
as well. The url for the page in this case will be something like:

http:localhost:8888/mod-super-admin/edit-relatoinship.php?repId=4&accountId=14


On the top of the page, I grab all the managers for that account (note the Address table in the query below is just where I house personal info for a user, it has no relation to this question. You'll notice I didn't even include addressId in my Users table above. I am not updating anything for the Address table on this page i just want the first and last name of the managers for my options)

// Get the url variables
$rep_id = $_GET["repId"];
$account_id = $_GET["accountId"];

// grab all managers tied to the account (not the rep)
$manager_sql = "SELECT Users.userId, Address.firstName, Address.lastName FROM Users JOIN Address ON Users.addressId=Address.addressId WHERE Users.role='manager' AND Users.accountId=" . $account_id;
$account_managers = mysqli_query($connection,$manager_sql);

// grab all managers assigned to the rep
$relationship_sql = "SELECT managerId FROM ManagerRepRelationship WHERE repId=" . $rep_id;
$rep_rels = mysqli_query($connection, $relationship_sql);

// create and array that houses current assigned managers
$current_managers = array();
while($row = mysqli_fetch_array($rep_rels)){
array_push($current_managers, $row["managerId"]);
}


The form is as follows. It will highlight the manager if already assigned.

<form method="POST" action="./mod-super-admin/edit-relationship.php?repId=<?php echo $rep_id; ?>&accountId=<?php echo $account_id; ?>">
<div class="col-sm-12">
<div class="form-group">
<div class="form-group mbs">
<label for="manager" class="label-medium">Assigned Managers</label><br>
<small>To Select Multiple Managers Hold Down CTRL (CMD for Mac) and click</small>
<select name="manager[]" class="form-control input-medium" multiple="multiple">
<?php
$output;
while($manager = mysqli_fetch_assoc($account_managers)) :
$output .= '<option value="' . $manager["userId"] . '"';
foreach($current_managers as $key=>$value) :
if( $value === $manager["userId"]){
$output .= ' selected';
}
endforeach;
$output .= '>' . $manager["lastName"] . ', ' . $manager["firstName"] . '</option>';
endwhile;
echo $output;
?>
</select>
</div>

<input type="submit" name="editRelationshipSubmit" class="btn btn-large btn-green mtm" value="Update Relationship">
</div>
</div>

</form>


In our case, the above PhP will output a mutliselect with the name of the managers and if the manager is assigned to the rep it will be highlighted already. Here comes the part I am having trouble with. When it comes to editing these realtionships. For example I understand if I were to just add
mfoster
as a manager to
jdoe
I would just run and
INSERT INTO
on the
ManagerRepRelationship
or if I were to delete
jsmith
as a manager I would run a
DELETE FROM ManagerRepRelationship WHERE repId=4 AND managerId=1
.

How do compensate for what happens though? I have no clue if the current user making these changes is going to add manager relationship, delete a manager relationship, maybe they do a little of both.

I made that
$current_managers
array so I could check it against the array
$_POST["manager"];
when the form is submitted. I should note that the only data the arrays house is the
userId
. I am just so stuck on running different SQL commands to update my database based on the comparison of those arrays. I hope I did an ok job of explaining the situation I am in.

Answer

It's a fairly common problem I think. The way I've always solved this sort of thing is possibly a bit simplistic but it works and is easy to implement. Instead of trying to work out what's changed (e.g. deleted 2 managers, added 1, kept 1) and then just insert/delete the correct rows, I've always taken the approach of simply deleting all the existing rows and then inserting a whole set of brand new ones relating to the data the user has input.

So in your case, let's say the user selects rep 4, and then edits the selected managers so that they selected managers 3, 7 and 8. (N.B. What was selected previously is irrelevant). So then you'd run the following against the database:

delete from ManagerRepRelationship where repId = 4;
insert into ManagerRepRelationship (repId, managerId) values (4, 3);
insert into ManagerRepRelationship (repId, managerId) values (4, 7);
insert into ManagerRepRelationship (repId, managerId) values (4, 8);

Obviously you'd want to use a loop to generate the insert statements based on the values selected in your GUI.

This way you don't need to worry about comparing what was there before to what's there now.

P.S. Make sure you use a transaction around all the SQL operations so you don't get stuck with incorrect information if an error occurs during processing.

Comments