track selecting and unselecting an option When updating a table

Question

I have three tables (I simplified it in this question) :

Table 1

id | Name
-----------
1  | John
2  | Smith


Table 2

id | Title
-----------
1  | Developer
2  | Web Developer
3  | A New Title

Table 3 (links between 1 and 2)

idName | idTitle
-----------
1      | 1
1      | 2
2      | 1

My problem is with the update page. My HTML is <select multiple> ... options ... </select> and I am using chosen to select and deselect options. I am trying to do this with PHP only. Lets say that we have the following scenario: The administrator wanted to remove the 'Developer' Title for 'John' and add 'New Title' for 'John'. He/She will deselect Title id 1 and select id 3. When He/She submits the form I will get two select values: id 1 (the one that he selected) and id 2 (the one that was already there). But I will not get id 3 because he deselected it.

What I am struggling with is : when the user posted the new selected values the ones that were deselected were not submitted with the form. There is no way for me to track the ones that were deselected so I can delete them from my table. How do you update your table with the new changes then? Do you delete what is already existed in that table and add the ids again? Is there a better option?

UPDATE : It seems @wander answer is less destructive than the other ones. However, @wonder did not explain how to compute step 4 in his answer. to distinguesh between new selected options, already existing selected options, and deselected options.

What I've done in one of my pages is put all the original selections into a hidden input. Then the PHP script compares the new values to the original values, and removes the ones that no longer apply. — Barmar, Jan 09 '15 at 16:41
Another way to do it is with `DELETE FROM Table3 WHERE idName = :name AND idTitle NOT IN (list of current selections)` — Barmar, Jan 09 '15 at 16:42

score 4 · Answer 1 · answered Jan 16 '15 at 04:33

There's no need to submit the deselected one. e.g.

John has two titles(Developer and Web Developer) saved in database.
The administrator opens the page, selects 'New Title', deselects 'Developer' and clicks submit.
Now on server side, we can get
- the title list of John stored in database: Developer and Web Developer
- title list submitted from the users: Web Developer and New Title
We compare the two title lists and figure out: we shall delete Developer title and add New Title on John.

score 3 · Answer 2 · edited May 23 '17 at 12:20

You could add hidden fields before the select menu which will cause 0/falsey values to also be sent to PHP.

<input type="hidden" name="stuff[]" value="0" />
<input type="hidden" name="stuff[]" value="0" />
<input type="hidden" name="stuff[]" value="0" />
<select multiple name="stuff[]">
    <option value="1">1</option>
    <option value="2">2</option>
    <option value="3">3</option>
</select>

This is similar to how you would send unchecked check boxes to the server:

https://stackoverflow.com/a/1992745/268074 (note the answer without JS)

score 3 · Answer 3 · answered Jan 16 '15 at 08:54

You can dump your old values in a hidden select, so you'll get them when handling your form.

<!-- Invisible select field -->
<select multiple name="oldData[]" style="display:none;" aria-hidden="true">
    <option value="1">1</option>
    <option value="2" selected>2</option>
    <option value="3" selected>3</option>
</select>

<!-- Real select field -->
<select multiple name="data[]">
    <option value="1">1</option>
    <option value="2">2</option>
    <option value="3">3</option>
</select>

Note: I was inspired by @Petah's answer, don't forget to upvote him :-).

I am gonna upvote you for one reason, You acknowledged @Petah well and Rightly... — Zaffar Saffee, Jan 22 '15 at 00:35

Vick · Answer 4 · 2015-01-22T06:48:02.857

Let's say we have $old_titles list with titles from db, and $new_titles titles from submit.

$add = array_diff($new_titles, $old_titles);
$delete = array_diff($old_titles, $new_titles);

$add - list of titles to add, $delete - list of titles to delete.

Add action:

$dbh->prepare('INSERT INTO table3(idName, idTitle) VALUES (:idName, :idTitle)');
foreach($add as $titleId) {
    $dbh->execute(['idName' => $userId, 'idTitle' => $titleId]);
}

Delete action:

$dbh->prepare('DELETE FROM table3 WHERE idName = :idName AND idTitle = :idTitle');
foreach($delete as $titleId) {
    $dbh->execute(['idName' => $userId, 'idTitle' => $titleId]);
}

I think you can improve your good answer by putting some extra code for the delete and add action. — Henrik, Jan 21 '15 at 13:32

score 1 · Answer 5 · answered Jan 17 '15 at 07:33

1

The only better way is the simplest one, remember KIS

on update you just need to do following

DELETE FROM table3 WHERE idName=??

and then insert all selected idTitles again in table3

answered Jan 17 '15 at 07:33

Nasir Iqbal

909
7
24

would you like to share me, what is wrong with this solution, I will be happy to updated my concept, Thanks – Nasir Iqbal Jan 23 '15 at 05:13
Nothing is wrong. However you are doing unnecessary deletes when you are doing this. you should only delete what should be deleted.. not delete the whole table then reinsert the new values. – syrkull Jan 25 '15 at 08:12

score 1 · Answer 6 · answered Jan 20 '15 at 10:55

This can be done in two SQL statement without knowing which value has been de-selected. Following these 2 steps

1) In your PHP, create a comma separated list of all selected option. For example: 2, 3.

2) Then execute the following statements:

 DELETE FROM tblLink WHERE idName = $id AND idTitle NOT IN ($list);
 INSERT IGNORE tblLink (idName, idTitle)
 SELECT $id, id FROM tblTitle WHERE id IN ($list);

Note: for shake of simplification, I use $id and $list in my example. When you intent to use it, you should properly prepare and bind parameter properly. Make sure that idName and idTitle are primary key to make it work.

Hugo Delsing · Accepted Answer · 2015-01-21T16:46:42.923

Not sure why everybody keeps using hidden input fields to track the existing data. When you POST your form, you can just retrieve the original values from the database. That's a lot safer then depending on user input, because even hidden fields can be altered. I know it won't matter too much in this particular situation, but I think it's best to always use the best practise.

Now in PHP you have two options. The first would be to delete all relations and then add the new relations. The second would be to delete all that wasn't posted and add/update the relations that where.

Although the second options might seem to be the "least destructive" as you call it, it is the easiest and least error prone way. The only reason I would abandon this tactic is when I would store extra data in the relations table. EG: date added (when did somebody get into a function) or added by user.

How would you go about it (method 2)?

Keep in mind that I use a method that tries to continue whenever possible. Another approach would be to halt when false data is posted.

<?php
if (array_key_exists("relations",$_POST) && is_array($_POST["relations"])) {
  //no duplicates, you dont want to store the same function twice
  //only integers
  $list = array_unique(array_filter($_POST["relations"], "is_int"));
  $csv = implode(',', $list);

  //get the userid hoewever you normally would
  $userId = 1

  //Delete existing
  $query = "DELETE FROM Table3 WHERE idname=".$userId
  //insert all posted functions
  //I select the id's from the actual table so it will never inserted a none existing ID.
  //this approach never has duplicates, so the duplicate check in the beginning is redundant now, but I leave it incase I change this.
  $query = "INSERT INTO Table3 (idName, idTitle) SELECT ".$userId.", Id FROM Table2 WHERE id IN (".$csv.")".

  //execute queries
} else {
  //nothing posted, delete all relations
}
?>

Now if you only want to insert new data and remove none-existing

<?php
if (array_key_exists("relations",$_POST) && is_array($_POST["relations"])) {
  //no duplicates, you dont want to store the same function twice
  //only integers
  $list = array_unique(array_filter($_POST["relations"], "is_int"));
  $csv = implode(',', $list);

  //get the userid hoewever you normally would
  $userId = 1

  //Delete existing
  $query = "DELETE FROM Table3 WHERE idname=".$userId." AND idTitle NOT IN (".$csv.")"
  //only insert new functions
  //I select the id's from the actual table so it will never inserted a none existing ID.
  //this approach never has duplicates, so the duplicate check in the beginning is redundant now, but I leave it incase I change this.
  $query = "INSERT INTO Table3 (idName, idTitle) SELECT ".$userId.", Id FROM Table2 WHERE id IN (".$csv.") AND id NOT IN (SELECT idTitle FROM table3 WHERE idName=".$userId.")".

  //execute queries
} else {
  //nothing posted, delete all relations
}
?>

score 1 · Answer 8 · answered Jan 22 '15 at 11:50

To my opinion you have to check what actions should be performed first. This is a small example that you can run here http://sandbox.onlinephpfunctions.com/code/0fa11b3c9f846e344c912f5a3e994eea5e9cac34. First run a select on Table 3 for idName=1 the output would be an array of idTitle's (1,2). In the following example the $array1 is the output of such a select statement and $array2 is the array of new idTitle's for the same idName that will have to be updated or deleted or inserted. If for example the new idTitles for a user are more or less than the previous ones then there you should have some sort of control over it in order to decide what queries you will run to the database.

$array1 = array(1,7,3,5);// initial idTitles try it on the above link (php sandbox) with more or less values 
$array2 = array(1,4,3,6);//new idTitles try it on the above link (php sandbox) with more or less values
$count1=count($array1);
$count2=count($array2);

$diff=$count1-$count2;
if ($diff==0) {
  $result1=array_values(array_diff_assoc($array2,$array1));
  $result2=array_values(array_diff_assoc($array1,$array2));
  $countr=count($result1);// or $result2 it is the same since they have the same length
  $cr=0;
  while($cr < $countr) {
  print_r("update tblname set val=$result1[$cr] where id=theid and val=$result2[$cr]\n");
  $cr++;
  }
}
if ($diff!=0) {
  $result1=array_diff($array2,$array1);
  $result2=array_diff($array1,$array2);
    if(count($result2)>0) {
      foreach($result2 as $r2) {
      print_r("delete from tblname where id=theid and val=$r2\n");
      }
    }
  foreach($result1 as $r1) {
  print_r("insert into tblname where id=theid and val=$r1\n");
  }
}

score 0 · Answer 9 · answered Jan 22 '15 at 15:50

0

How I would do it would extend the 1st table

id | Name | Title IDs

1 | John | 1,2

2 | Smith | 1,3

Each time the row is updated I would simply just replace the title_ids fields and be done with it.

answered Jan 22 '15 at 15:50

Demodave

6,242
6
43
58

track selecting and unselecting an option When updating a table

9 Answers9

id | Name | Title IDs