Use dropdown select in php mysql search autocomplete

Question

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

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 categories dropdown before the text input 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> 

Answer 1

Apparently the chat didn’t work very well here, so I’ll answer with what I think might solve your problem.

The first thing we need to do is popular dropdown select with the existing categories in the database. For this, we searched the categories with PHP and iterated on the results, defining a option for each.

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

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

The SQL directive distinct serves to search uniquely all existing categories. That is, if there are two records in the category technology, we will have * technology* only once in the result. Now we populate the select with the 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 send the selected category as a search filter to the PHP file post_search.php. We can do this by retrieving the selected value through jQuery and passing it through 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’ve defined a id to the country select, recovered its value for the variable categoria and passed its value by query string, when set the path of the search file. Thus 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 directive where of SQL.

In the archive post_search.php:

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

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

This way, the search will be executed only in posts of the selected category.

I tried to describe the idea. I’m not sure if the form presented works 100% and I did not attempt to do all the validations and follow-up tests. That I leave to you.