dseslyh6662605 2015-03-11 14:49
浏览 27
已采纳

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

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.

  • 写回答

1条回答 默认 最新

  • dongquan6030 2015-03-12 07:37
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 ubuntu系统下挂载磁盘上执行./提示权限不够
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行