How to insert new values of multiple select

Question

I have 3 tables: tags, posts, tags_post.

I'm using the Select2 plugin to create a nice select option with dynamic tagging

The user can pick a tag from the list (TAGS table) or write their own tag. I don't know how to code to insert new tags to the TAGS table. I only code the php to add the existing tags to the relation table.

FORM:

 <select class="js-basic-multiple form-control" name="tags[]" multiple="multiple">
      <?php
         $query = mysqli_query($mysqli, "SELECT id_tag, name FROM tags ORDER BY name ASC;")
    or die('error '.mysqli_error($mysqli));
         while ($data = mysqli_fetch_assoc($query)) {
           echo"<option value=\"$data[id_tag]\">$data[name] </option>";
          }
       ?>
 </select>

PHP (adds the relation in tags_post) This code works fine:

$id_post = $_POST['id_post'];
$tags     = $_POST['tags'];
$i = 0;
$nTags = count($_POST['tags']);

while ($i <= $nTags){

    foreach ($tags as $value) {
          $query = mysqli_query($mysqli, "INSERT INTO tags_post(id_post, id_tag) VALUES('$id_post', '$value')")
                               or die('error '.mysqli_error($mysqli));             
    }
 $i++;
}

How can I add the new tags in the table tags? (The user can add their own tags) Maybe using lists? ($tags= Tags::lists('name','id'))

I would like to do something like that in php:

Pseudocode:

1. Tag exists in table “tags”?
  1.1  No > INSERT INTO tags
2. Insert all tags in the relation table.
  2.1  INSERT INTO tags_post

You have a table name tag, So what the issue insert data into tag table? — rawathemant, Jun 25 '18 at 13:11
You mean that the front end already works? Can you then specify what kind of input you get posted so we can help you with the PHP code to save that to the database? Now you're expecting us to know how this JS plugin works, while that doesn't seem relevant for your question. — GolezTrol, Jun 25 '18 at 13:12
You have to put `$id_post` somewhere in the form, for example as a hidden input field. Also read [SQL Injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Adder, Jun 25 '18 at 13:14
Possible duplicate of [PHP Select List - Insert multiple values in database](https://stackoverflow.com/questions/15770011/php-select-list-insert-multiple-values-in-database) — Ravi Kumar Yadav, Jun 25 '18 at 13:27

score 0 · Answer 1 · answered Jun 25 '18 at 13:34

Regarding the insertion I can offer the following php code. It protects against SQL injection. I am taking a guess where id_post is coming from.

$tags = $_POST['tags'];
$id_post = $_POST['id_post'];
foreach ($tags as $value) {
    $stmt =  mysqli_prepare($mysqli, "INSERT INTO tags_post(id_post, id_tag) VALUES(?, ?)") or die('error '.mysqli_error($mysqli));             
    $stmt->bind_param('ii', $id_post, $value);
    $stmt->execute() or die('error '.mysqli_error($mysqli));     
}

If you show more of the form, I might be able to help with that.

score 0 · Accepted Answer · answered Jul 07 '18 at 23:42

I've figured it out by myself (this code works for me):

<?php
$query_id = mysqli_query($mysqli, "SELECT id_tag FROM tags
                            ORDER BY id_tag DESC LIMIT 1")
                            or die('error '.mysqli_error($mysqli));
$count = mysqli_num_rows($query_id);
   if ($count <> 0) {
     $data_id = mysqli_fetch_assoc($query_id);
     $id_tag_new  = $data_id['id_tag']+1;
   } else {
     $id_tag_new = 1;
   }

$id_post = $_POST['id_post'];
$tags = $_POST['tags'];

foreach ($tags as $value) {
    if (is_numeric(substr($value, 0, 1))){
        $query = mysqli_query($mysqli, "INSERT INTO tags_post(id_post, id_tag) VALUES('$id_post', '$value')")
                               or die('error '.mysqli_error($mysqli)); 
    }
    //new tag
    else {
        $query_new = mysqli_query($mysqli, "INSERT INTO tags(id_tag, name) VALUES('$id_tag_new', '$value')")
            or die('error '.mysqli_error($mysqli));
        $query_relation = mysqli_query($mysqli, "INSERT INTO tags_post(id_post, id_tag) VALUES('$id_post', '$id_tag_new')")
                               or die('error '.mysqli_error($mysqli));     
        $id_tag_new++;
    }
}
?>

How to insert new values of multiple select

2 Answers2