Use dropdown select in autocomplete of search in php mysql

Question

Use dropdown select in autocomplete of search in php mysql

Navigation

#1 by (0 votes)

0

I have this input=[text] with autocomplete that offers the title with a link (a href =) in the mysql database, however I would like to create 'categories' to select the links that autocomplete offers through a dropdown select. / p>

This is my form:

<form>
    <div class="form-group">
        <div class="input-group">
            <input id="txtSearch" class="form-control input-lg" type="text" placeholder="Selecione Região e busque por Nome..." />
            <div class="input-group-addon">
                <i class="glyphicon glyphicon-search"></i>
            </div>
         </div>
    </div>
</form> 


<script>
$(document).ready(function(){

    $('#txtSearch').autocomplete({
        source: "post_search.php",
        minLength: 2,
        select: function(event, ui) {
            var url = ui.item.id;
            if (url != '#') {
                location.href = url
            }
        },
        open: function(event, ui) {
            $(".ui-autocomplete").css("z-index", 1000)
        }
    })

});
</script>

This is my post_search.php:

<?php

    require_once 'dbconfig.php';

    $keyword = trim($_REQUEST['term']); // this is user input

    $sugg_json = array();    // this is for displaying json data as a autosearch suggestion
    $json_row = array();     // this is for stroring mysql results in json string

    $keyword = preg_replace('/\s+/', ' ', $keyword); // it will replace multiple spaces from the input.

    $query = 'SELECT postID, postTitle, postUrl FROM tbl_posts WHERE postTitle LIKE :term'; // select query

    $stmt = $DBcon->prepare( $query );
    $stmt->execute(array(':term'=>"%$keyword%"));

    if ( $stmt->rowCount()>0 ) {

        while($recResult = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $json_row["id"] = $recResult['postUrl'];
            $json_row["value"] = $recResult['postTitle'];
            $json_row["label"] = $recResult['postTitle'];
            array_push($sugg_json, $json_row);
        }

    } else {
        $json_row["id"] = "#";
        $json_row["value"] = "";
        $json_row["label"] = "Nothing Found!";
        array_push($sugg_json, $json_row);
    }

    $jsonOutput = json_encode($sugg_json, JSON_UNESCAPED_SLASHES); 
    print $jsonOutput;

EDIT: Here's an idea of what it would look like after inserting the dropdown of categories before inputting text with the URL's:

<form>
    <select class="border-white -rounded"> <!-- Esta seriam as categorias dos POSTS -->
        <option>Selecione a Categoria:</option>
        <option>Categoria 1</option>
        <option>Categoria 2</option>
        <option>Categoria 3</option>
    </select>

    <!-- Este seria o input para o título dos POSTS -->
    <input class="border-white -rounded" type="text" placeholder="Busque por Título"> 
    <div class="autocomplete">
        <i class="glyphicon glyphicon-search"></i>
    </div>
    <!-- Aqui apareciam as sugestões conforme digita acima -->
</form>

php mysql busca autocompletar dropdown

asked by anonymous 01.02.2017 / 13:13

1 answer

Non-blocking process in PHP [duplicate] Configure WebService

score 0 · Answer 1

Apparently the chat did not work very well here, so I'll respond with what I think might solve your problem.

The first thing we need to do is dropdown select popular with the existing categories in the database. For this, we search for the categories with PHP and iterate over the results, setting option for each.

$query = "SELECT DISTINCT postCat FROM tbl_posts";
$result = mysql_query($query);

Here I considered that there is a column called postCat in the tbl_posts table that stores the category of each post .

The SQL directive distinct serves to uniquely search for all existing categories. That is, if there are two records in the technology category, we will have * technology * only once in the result. We now populate select with data:

<form>
    <select id="postCat" class="border-white -rounded">
        <option>Selecione a Categoria:</option>

        <?php while($cat = mysql_fetch_assoc()) { ?>
            <option value="<?php echo $cat["postCat"]; ?>"><?php echo $cat["postCat"]; ?></option>
        <?php } ?>
    </select>

    {...}
</form>

Now we need to have the selected category sent as a search filter to the post_search.php PHP file. We can do this by retrieving the value selected through jQuery and passing it to Query String .

<script>
$(document).ready(function(){

    var categoria = $('#postCat').val();

    $('#txtSearch').autocomplete({
        source: "post_search.php?postCat=" + categoria,
        minLength: 2,
        select: function(event, ui) {
            var url = ui.item.id;
            if (url != '#') {
                location.href = url
            }
        },
        open: function(event, ui) {
            $(".ui-autocomplete").css("z-index", 1000)
        }
    })

});
</script>

Notice that I set an id to the select field, retrieved its value for the variable categoria and passed its value to the query string >, when the search file path is set. So the request will be forwarded to post_search.php?postCat=tecnologia , for example.

Now, to filter the data in the search based on the selected category, just use the where SQL directive.

In the file post_search.php :

{...}
$postCat = $_GET["postCat"];

$query = 'SELECT postID, postTitle, postUrl FROM tbl_posts WHERE postTitle LIKE :term AND postCat = "$postCat"';
{...}

In this way, the search will only be performed on the posts of the selected category.

I tried to describe the idea. I'm not sure if the form presented works 100% and I was not careful to do all validations and security tests. This I leave to you.