使用MYSQL阻止已选择的选项Jquery自动完成

Question

I am using Jquery Autocomplete to fetch a list of tags from a mysql table. When the user picks one tag from the list, it gets saved on the page. I am trying to prevent the already saved tag from being displayed again.

Here is my code:

HTML

<input type="text" id="tag">
<input type="text" id="tags" style="display:none;">

Jquery

$('#tag').autocomplete({
            source : function(request, response) {
                $.ajax({
                    url : 'tags.php',
                    dataType : "json",
                    method : 'post',
                    data : {
                        searchQuery : request.term, 
                        selectedTags: $('#tags').val() //sends already selected terms
                    },
                    success : function(data) {
                        response($.map(data, function(item) {
                            var code = item.split("|");
                            return {
                                label : code[0],
                                value : code[0],
                                data : item
                            }
                        }));
                    },
                    error: function(jqxhr, status, error)
                    {
                        alert(error);
                    }
                });
            },
            autoFocus : true,
            minLength : 1,
            select : function(event, ui) {
                var names = ui.item.data.split("|");

                tag_ids = [];
                tag_names = [];
                tags = $('#tags').val();
                if(tags != '')tag_names = tags.split(',');
                tag_ids.push(names[1]);
                tag_names.push("'" + names[0] + "'");
                $('#tags').show();
                $('#tags').val( tag_names.join( "," ) );
                $('#tag').val('');
            }

PHP

$searchQuery = $_POST['searchQuery'];
$selectedTags = $_POST['selectedTags'];

if(!empty($selectedTags))
{ $query = $db->prepare("SELECT * FROM tags WHERE name LIKE ? AND name NOT IN ?");
$query->execute(array($searchQuery . "%", $selectedTags));
}
else
{
$query = $db->prepare("SELECT * FROM tags WHERE name LIKE ?");
$query->execute(array($searchQuery . "%"));
}

When I select the first suggestion, it gets saved in #tags but then no other suggestion is displayed. If there is any other suggestion to achieving this, that'd be great.

Answer 1

I figured it out. I was trying to pass an array to the prepared statement. PDO doesn't work that way. To solve this, I declared the number of parameters first and then put them in the prepared statement while using foreach to bindvalue to each parameter.

Here is the final solution:

//exploding the string to an array first
$selectedTags = explode(',', $selectedTags); 

//creating another array with parameters equal to the size of the selectedTags
$bindValues = implode(',', array_fill(0, count($selectedTags), '?')); 

//putting the parametes
$query = $db->prepare("SELECT * FROM tags WHERE name LIKE ? AND name NOT IN (" . $bindValues .")");


//binding values
$query->bindValue(1, $searchQuery . '%');

//Now, using foreach to bind selected tags values
foreach($selectedTags as $k => $selectedTag)
{
 //using k+2 as index because index starts at 0 and first parameter is the search query
 $query->bindValue($k+2, $selectedTag);
}
$query->execute();

And this solved the problem. I hope it helps others too.