Best practice for updating the many-to-many table?

Question

This is a question about how best to assign or update multiple records in the Many-to-Many table. Simply put, the ability to change the category or tags for an article or blog.

In this application that I am creating, I have a table (category) of categories and a table of articles (called reflections).

Since each article can belong to many categories (and each category can be assigned to many articles), I have a join table for this relationship (category_musings). I set limits - to delete an article, it deletes all related entries in the Join table. But deleting a category is prohibited if it is currently assigned to an article.

WHAT I WANT TO ACCESS:

I want the end user to be able to update / modify / delete several categories for this article (reflection).

MY WORK SO FAR:

I figured out a way to display the current category values ​​in a drop-down list of several styles using the following functions

First I get a list of all categories

//Return list of categories
public function categories($column)
{
$sql = "SELECT * FROM category";
$query = $this->connection->prepare($sql);
$query->execute();

return $query->fetchAll(\PDO::FETCH_COLUMN, $column);;
}

Then the categories currently assigned to a particular music

//Return categories selection for each musing
public function getMusingCategoryList($id)
{
$sql = "SELECT category AS categories
              FROM musings 
              JOIN category_musings ON musings.musing_id=category_musings.musing_id
              JOIN category ON category_musings.category_id=category.category_id
              WHERE category_musings.musing_id=:musing_id";
$query = $this->connection->prepare($sql);

$values = [
':musing_id' => $id,
];
$query->execute($values);

return $query->fetchAll(\PDO::FETCH_COLUMN,0);
}

Both return arrays, which are then passed to another function to create my drop-down list. This is the PHP code in the interface

$data = new \norm_musing\MusingData();
$Musing = $data->getMusing($_GET['id']);

$name = 'multi_dropdown';
$options = $data->categories("1");
$selected = $data->getMusingCategoryList($_GET['id']);

$result = $data->multi_dropdown( $name, $options, $selected );

And this is a drop-down list creation function, which I later turn to the form along with all the other relevant data for the thought field

//Create dropdown for categories
public function multi_dropdown( $name, array $options, array $selected=null, $size=4 )
{
    /*** begin the select ***/
    $dropdown = '<select class="form-control" name="'.$name.'[]" id="'.$name.'" size="'.$size.'" multiple>'."\n";

    /*** loop over the options ***/
    foreach( $options as $key=>$option )
    {
            /*** assign a selected value ***/
            $select = in_array( $option, $selected ) ? ' selected' : null;

            /*** add each option to the dropdown ***/
            $dropdown .= '<option value="'.$key.'"'.$select.'>'.$option.'</option>'."\n";
    }

    /*** close the select ***/
    $dropdown .= '</select>'."\n";

    /*** and return the completed dropdown ***/
    return $dropdown;}

HTML (verbose). CTRL , .

Join, ( , ).

$selection = $_POST['multi_dropdown']. 

. 6 - 1 4 - [0, 3]

, , , PHP-

if (isset($_POST['id']) && !empty($_POST['id'])) 
{
$aCategories = $_POST['multi_dropdown'];
if(!isset($aCategories)) 
{
echo("<p>You didn't select any categories!</p>\n");
}
else
{
$nCategories = count($aCategories);
$categoryID = $data->categories("0");

echo("<p>You selected $nCategories categories: ");
for($i=0; $i < $nCategories; $i++)
{
  echo($aCategories[$i] . " ");
  echo($options[$aCategories[$i]] . " ");
  echo("(id: " . $categoryID[$aCategories[$i]] . ") | ");
}
echo("</p>");
$nCount = $_POST['count'];
$newSelected = $data->getMusingCategoryList($_POST['id']);
echo("<p>Originally you had $nCount categories: ");
for($i=0; $i < $nCount; $i++)
{
  echo($newSelected[$i] . " ");
}
echo("</p>");

exit;}}

, (id )

    You selected 2 categories: 0 Aging (id: 3) | 2 Health (id: 4) |

    Originally you had 1 categories: Happiness 

:

, Musing ID, INSERT ? , , , , - .

?

(, )?

Answer 1

, MySQL "INSERT IGNORE INTO" , , " " ( , 2 ).

. http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html .

Answer 2

- , REPLACE INTO INSERT IGNORE INTO .

//Delete all categories for a given musing ID
public function deleteMusingCats( $id ) {

   $sql="DELETE FROM category_musings WHERE musing_id = :musing_id";
   $query = $this->connection->prepare($sql);
   $values = ['musing_id' => $id];
   $query->execute($values);

   }


//Insert new category for musing
public function updateMusingCats( $a, array $b, $n ) {  

$stmt = $this->connection->prepare("
                 INSERT INTO category_musings (category_id, musing_id) 
                 VALUES (:catID, :musID);
                                  ");  

for ($i=0; $i < $n; $i++) 
{
  $stmt->bindValue(':catID', $b[$i], \PDO::PARAM_INT);
  $stmt->bindValue(':musID', $a, \PDO::PARAM_INT);
  $stmt->execute();
}

return $i;
}   

, , , .

if (isset($_POST['id']) && !empty($_POST['id'])) 
{
    // Simple number array of the list items selected
    $aCategories = $_POST['multi_dropdown']; 

    if(!isset($aCategories))   {
        // No categories selected. Abort!
        header("Location: /norms_musings");
    } else {
        $nCategories = count($aCategories); // Number of categories selected
        $categoryID = $data->categories("0");  // Create complete category ID array

        for($i=0; $i < $nCategories; $i++) 
        /* Create array of selected categories the number value from the
        dropdown list corresponding to the place in the full category list array */
            $a[]=$options[$aCategories[$i]]; 
            $b[]=$categoryID[$aCategories[$i]]; // Create array of selected category IDs
        }

        $nCount = $_POST['count'];
        $newSelected = $data->getMusingCategoryList($_POST['id']);

        $equal = ($a==$newSelected);

        if ($equal) {
            // No Changes Made!
            header("Location: /norms_musings");
            exit;
        } else {
            $data->deleteMusingCats($_POST['id']);
            $arrayDiff = (array_diff($a,$newSelected));
        }

        if ($data->updateMusingCats($_POST['id'],$b,$nCategories)) {
            header("Location: /norms_musings");
            exit;
        } else {
            echo "An error occurred";
            exit;
        }
    }
}