使用新值更新联结表 [英] Update a junction table with new values

问题描述

我有一个一对多表(实际上应用程序实际上将使用一对多，但这与我的问题无关).假设两列具有以下值:

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:

c1 c2
 2  3
 4  1
 4  3
 4  4
 6  4

对于给定的c1值4，我想用数组[1,2,4,6]中的c2值更新表.因此，我的表将如下所示(请注意，添加了记录4,2和4,6，不再存在记录4,3，并且记录4,1和4,4保持不变):

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):

c1 c2
 2  3
 4  1
 4  2
 4  4
 4  6
 6  4

完成此任务的最佳方法是什么?我可以"首先查询数据库以选择c1 = 4的现有值，然后使用array_intersect()标识添加和删除的记录，并根据需要插入/删除，但是，这似乎过多.

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.

推荐答案

我知道这很老，但是花了我一段时间才找到答案，这个问题在google上排名很高.

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;

源代码的来源: http://borzacchiello.it/how- to-update-a-junction-table/

我修改为与PHP变量&一起使用的代码PDO循环:

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();      

                    } 

使用原始问题的示例，您将必须为要更新的每个c1 id号运行此代码(在我的示例中，c1等同于$a_id变量).

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).

这篇关于使用新值更新联结表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！