Update a junction table with new values

Question

I have a one-to-many table (actually application will actually use a many-to-many, but that is not relevant to my question). Assume two columns with the following values:

For a given c1 value of 4, I would like to update the table with the c2 values in array [1,2,4,6]. As such, my table will now be as follows (note that records 4,2 and 4,6 were added, record 4,3 is no longer present, and records 4,1 and 4,4 remain unchanged):

What is the best way to accomplish this? I "could" first query the database to select the existing values for c1=4, and then use array_intersect() to identifiy the added and removed records, and insert/delete as required, however, this seems excessive.

Maybe I missed something but why not just delete rows where c1 = 4 and insert the records based on your array? Rather than trying to figure out which rows exist, missing, or exist with different value. — SQLChao, Jul 22 '14 at 16:33
@JChao. Yes, that is another option. Is it recommended? What is the impact on indexes? — user1032531, Jul 22 '14 at 16:34
True it could cause fragmentation. I guess it depends on how often this type of operation is going to occur, the size of the index. Sorry I was thinking along the lines of one time operation initially. — SQLChao, Jul 22 '14 at 16:51

score 0 · Answer 1 · answered Sep 09 '16 at 03:05

I know this is old, but it took me a while to find the answer and this question ranks high on google.

I found the following code and modified it:

INSERT IGNORE INTO my_table VALUES (1,1), (1,2), (1,3);

DELETE FROM my_table WHERE c1 NOT IN (1,2,3) AND my_table = 1;

Origin of source code: http://borzacchiello.it/how-to-update-a-junction-table/

My code that I modified to use with PHP variables & PDO loops:

    $a_id = $my_main_id //this would be c1 in the above question

    //Set Variable From Post
    $var_1 = isset($_POST['var_1']) ? $_POST['var_1'] : '';
    $var_2 = isset($_POST['var_2']) ? $_POST['var_2'] : '';
    //etc, etc, etc

    //put variables into array
    $data = array('var_1'=>$var_1, 'var_2'=>$var_2, 'var_3'=>$var_3, 'var_4'=>$var_4, 'tarp_5'=>$tarp_5);

                       //get count of variable that contain data and not empty
                       $data_array_count = 0;
                       foreach ($data as $column => $value) {     
                                if($value != '') {
                                    $data_array_count = ++$data_array_count;
                                }
                       }

                       //if contains atleast one variable run update code     
                       if($data_array_count != 0) {

                            //loops through and inserts each varible in array
                            foreach ($data as $column => $value) {

                                //ignores variables without any data
                                if($value != '') {  
                                    $insert = $db->prepare("INSERT IGNORE my_table (a_id, b_id) VALUES (:a_id, :b_id)"); 
                                    $insert->execute(array(':a_id' => $a_id,
                                                           ':b_id' => $value ));        
                                }
                            }


                                //sets up variables in array to remove any records that need to be deleted
                                $columns = "";  
                                $holders = "";  
                                foreach ($data as $column => $value) {     
                                    if($value != '') {      
                                       $columns .= ($columns == "") ? "" : ", ";  
                                       $columns .= $column;  
                                       $holders .= ($holders == "") ? "" : ", ";  
                                       $holders .= ":$column";  
                                    }
                                }


                                $delete = $db->prepare("DELETE FROM my_table WHERE accessory_id NOT IN ($holders) AND (carrier_id = :a_id)"); 

                            //bind value for main id     
                            $delete->bindValue(":a_id", $a_id);

                            //loop to bind value for each variable stored in array with data
                            foreach($data as $placeholder => $value) {
                                if($value != '') {
                               $delete->bindValue(":" . $placeholder, $value);
                                }
                            }

                            $delete->execute();      

                    }

Using the example from the original question you would have to run this code for each c1 id number that you wanted to update (c1 would be the equivalent of the $a_id variable in my example).

Update a junction table with new values

1 Answers1

Linked