How to insert new values of multiple select

0
votes

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
2
php
What do you want to achieve, your question is not clear??? – Nagesh Katke
You have a table name tag, So what the issue insert data into tag table? – rawathemant
As @rawathemant said, use same query just change table name – Nagesh Katke
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
You have to put $id_post somewhere in the form, for example as a hidden input field. Also read SQL Injection – Adder

2 Answers

0
votes

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.

0
votes

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++;
    }
}
?>