How to make a custom filter using the Bootstrap Selectpicker?

Asked

Viewed 696 times

4

Good morning guys. I have a problem using Selectpicker to make a Combobox with the products I bring from BD with PHP. My problem is this: I need to make a "custom" filter because when I put inside select all products that come from BD, they give more than 800, and the user has the option to add more "Products" in the order, which makes the Site and select’s very heavy. In short, I need a way to bring all these products from the Database, but limit a maximum amount that will appear in Select ( I know I can limit the query )but I need that when the user type in the search field a name that is not appearing there in Select, the filter searches in all the database results, even the hidden " of Select. How could I do this ?

I tried to do something like that but it didn’t work, I tried it this way:

main.php:

<?php  // Parte que pesquiso os Produtos em um BD Interno.
include 'verifica_login.php';
include 'conexao.php';
include 'oracle.php';

if (!isset($_SESSION)) {
    session_start();
}

error_reporting(0);
header('Content-Type: text/html; charset=utf-8');


# Fornecedores
$query_fornecedores = "SELECT nome FROM fornecedores ORDER BY nome ASC LIMIT 40";
$result1 = mysqli_fetch_all(mysqli_query($connect, $query_fornecedores), MYSQLI_ASSOC);
mysqli_close($connect);
# Produtos

$stid = oci_parse($ora_conexao,"SELECT a.desccompleta
from map_produto a, map_prodcodigo b, mrl_produtoempresa d, mrl_departamento f, map_famdivcateg ff, map_categoria cc
where a.seqproduto = b.seqproduto
and   a.seqproduto = d.seqproduto
and   a.seqfamilia = ff.seqfamilia
and   ff.seqcategoria = cc.seqcategoria
and   ff.nrodivisao   = cc.nrodivisao
and   d.nroempresa = f.nroempresa(+)
and   cc.tipcategoria = 'M'
and   ff.status = 'A'
and   cc.nivelhierarquia = 2
and   d.nrodepartamento = f.nrodepartamento(+)
and   a.seqfamilia = ff.seqfamilia
and   b.indutilvenda = 'S'
and   ff.nrodivisao = 1
and   b.tipcodigo in ('E','B')
and   d.nrodepartamento = 4
and   d.nroempresa = 1
and   ROWNUM <= 100
and   f.descricao = 'HORTIFRUTI'
ORDER BY a.desccompleta ASC");
oci_execute($stid);

$result2 = oci_fetch_all($stid, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW);

oci_free_statement($stid);
oci_close($ora_conexao);
?>

// HTML da Div Produtos 

div class="container" id="produtos">
                        <div class="separator"></div>

                        <div class="title-padrao">
                            <h1 class="text-center">
                                Produtos
                            </h1>
                        </div>

                        <div id="allProducts">
                            <section id="all-section">

                                <div class="inform-produtos">

                                    <div class="primeira">
                                        <b>Item:</b>
                                    </div>

                                    <div class="segunda">
                                        <b>Cod.:</b>
                                    </div>

                                    <div class="terceira">
                                        <b>Selecione um produto:</b>
                                    </div>

                                    <div class="quarta">
                                        <b>Embalagem:</b>
                                    </div>

                                    <div class="quinta">
                                        <b>Preço:</b>
                                    </div>

                                    <div class="sexta">
                                        <b>Quantidade:</b>
                                    </div>

                                    <div class="setima">
                                        <b>Preço do Produto:</b>
                                    </div>
                                </div>

                                <div class="clone-prod" name="clone-prod[]">

                                    <div class="wrap-prod" name="wrap-prod[]">

                                        <div class="produtos-wrap" name="produtos-wrap[]">
                                            <div class="text-center select_height produto-padrao" id="primeiro-produto">
                                                <input type="text" class="index font-pop input-div" id="index_produto"
                                                    name="index_produto[]" value="1" readonly="true">
                                            </div>

                                            <div class="text-center select_height produto-padrao" id="segundo-produto">
                                                <input class="font-pop number_id_produto input-div" value=""
                                                    readonly="true" name="id_produto[]" required>
                                            </div>

                                            <div class="text-center select_height produto-padrao terceiro-produto"
                                                id="terceiro-produto">
                                                <select class="selectpicker form-control" data-show-subtext="false"
                                                    data-live-search="true" name="select_produtos[]"
                                                    id="select_produtos" onchange="initProdutos(this)" required>
                                                    <?php
                                                    echo '<option disabled selected hidden
                                                    value="Selecione um produto..."
                                                    data-subtext="Selecione um produto...">Selecione um produto...
                                                    </option>';
                                                    foreach ($res as $item_produtos) {
                                                        echo '<option data-subtext="' . $item_produtos['DESCCOMPLETA'] . '" value="'
                                                    . $item_produtos['DESCCOMPLETA'] . '">' . $item_produtos['DESCCOMPLETA'] . '</option>';
                                                    }
                                                    ?>
                                                </select>
                                            </div>

                                            <div class="text-center select_height produto-padrao" id="quarto-produto">
                                                <input type="text" maxlength="2" class="edit-input font-pop"
                                                    name="embalagem[]" value="" required>
                                            </div>

                                            <div class="text-center select_height produto-padrao" id="quinto-produto">
                                                <input type="number" id="preco-input" name="preco[]" step="0.01" min="0"
                                                    class="edit-input font-pop" required>
                                            </div>

                                            <div class="text-center select_height produto-padrao" id="sexto-produto">
                                                <input type="number" id="qtd-input" step="0.01" min="0"
                                                    class="edit-input font-pop" value="" name="quantidade-produto[]"
                                                    required>
                                            </div>

                                            <div class="text-center select_height produto-padrao" id="setimo-produto">
                                                <input class="font-pop preco-produto input-div" readonly="true"
                                                    name="preco-produto[]" required>
                                            </div>
                                        </div>

                                        <div class="text-center select_height produto-padrao oitavo-produto"
                                            id="div-remove">
                                            <button type="button"
                                                class="remover glyphicon glyphicon-remove button-produto"></button>
                                        </div>

                                    </div>

                                </div>

                            </section>
                            <div id="wrap-addbutton">
                                <button type="button" id="add-button"
                                    class="glyphicon glyphicon-plus-sign button-produto"></button>
                                <b>Adicione um produto...</b>
                            </div>
                        </div>

                    </div>

                    <div class="container" id="produto-total">
                        <div class="col-lg-12">
                            <div class="assinatura col-lg-9">
                                <div id="wrap-assinatura" class="text-center">
                                    <div id="assinatura"></div>
                                    <b>Assinatura</b>
                                </div>
                            </div>

                            <div class="preco-final col-lg-12 text-right">
                                <b>Preço Total:</b>
                                <br>
                                <input id="total" readonly="true" name="total_pedido" class="text-right input-div"
                                    value="R$ 0.00">
                            </div>
                        </div>
                    </div>

API.php:

switch ($_GET['mode']) {
    case 'fornecedores':
        $letras = $_GET['letras_fornecedores'];
        $query = "SELECT * FROM fornecedores WHERE nome LIKE '{$letras}%' ORDER BY nome ASC LIMIT 20";
        $result = mysqli_fetch_all(mysqli_query($connect, $query), MYSQLI_ASSOC);
        break;

    case 'ultimoPedido':
        $query = "SELECT id_pedido as ultimoPedido FROM pedido ORDER BY id_pedido DESC LIMIT 1";
        $result = mysqli_fetch_all(mysqli_query($connect, $query), MYSQLI_ASSOC);
        break;

    default:
        $letras = $_GET['letras_produtos'];
        $stid = oci_parse($ora_conexao,"SELECT b.codacesso, c.embalagem, c.qtdembalagem FROM map_produto a, map_prodcodigo b, Map_Famembalagem c, MAP_Familia d  
        WHERE a.desccompleta LIKE '{$letras}%'
        AND a.seqproduto = b.seqproduto
        AND b.tipcodigo in ('B', 'E')
        AND a.seqfamilia = d.seqfamilia
        AND d.seqfamilia = c.seqfamilia
        ORDER BY c.embalagem, a.desccompleta, c.qtdembalagem");
        oci_execute($stid);
        $res = oci_fetch_all($stid, $result, null, null, OCI_FETCHSTATEMENT_BY_ROW);
        oci_free_statement($stid);
        oci_close($ora_conexao);
        //$query = "SELECT * FROM produto WHERE desc_produto LIKE '{$letras}%' ORDER BY desc_produto ASC LIMIT 20";
        break;
}

mysqli_close($connect);

if($result == NULL)
    die('Nenhum resultado encontrado');

header('Content-Type: application/json');
echo json_encode($result, JSON_PRETTY_PRINT);

Script.js :

function filterProdutos(letras_produtos) {
    console.log(letras_produtos);
    $.ajax({
        type: "GET",
        url: "API.php",
        data: {
            "mode": "produtos",
            "letras_produtos": letras_produtos,
        },
        dataType: "JSON",
        //CASO DÊ TUDO CERTO
        success: function (data) {
            console.log(data);
            console.log(letras_produtos);
            var options = '';
            options = '<option disabled selected hidden value="Selecione um produto..." data-subtext="Selecione um produto...">Selecione um produto...</option>';
            for (var i in data) {
                options = options + '<option value="' + data[i]['desc_produto'] 
                    + '" data-subtext="' + data[i]['desc_produto'] + '">' 
                    + data[i]['desc_produto'] + '</option>';
            }
            $("[name='select_produtos[]']").html(options);
            $("[name='select_produtos[]']").selectpicker('refresh');
        },
        error: function (request, error) {
            console.log("Request: " + JSON.stringify(request));;
        }
    });
}


function filtragem_valores(div, valor) {
    if (div == 'div_fornecedores') {
        filterFornecedores(valor);
    } else {
        filterProdutos(valor);
    }
}

What is happening is that the value I type in the selectpicker, is not getting in this my filter. Someone can give me a light ?

php javascript jquery twitter-bootstrap combobox

  • The log console indicates something when you trigger this action?

  • And what event are you running this filter with? From what I understand you are using Event onChange, have tried with onKeyUp

  • to call the function initProducts and these pass the element reference and not its value too

  • @Scrapbench as I said in the question, nothing appears on the console, even though I’m putting the console.log there. It is not entering the filter function, and yes, I have tried with onKeyUp and onKeyDown. In question of Initproducts, it is working normally, its function is to complement the fields according to what is selected in SELECT, my problem is even in the filter of this.

  • So from what I’ve seen you’re calling function function filtragem_valores(div, valor) to call the filterProdutos() right?

  • I tried to look for other mentions of filtragem_valores() in your code but I didn’t see :/

  • How do you think you could get her to do that call in my HTML ?

  • maybe make a call with the onkeyup event make a get request like $.get("url_do_api.php?letrasProdutos="+valor_passado_pela_funcao,function(data){})

  • and use the get parameter to make a select * from a_sua_tabela like "%$_GET['letrasProdutos']%" and return the array of results only so you do not need to return the total of the products but only those that have the searched letters, do not forget to escape the parameter before performing the query

  • 1

    I could post an example by putting the way you thought this in my code ?

  • I have the same question, with the only difference that use Arable and there it is: 'select * from a_sua_tabela like "%$_GET['letrasProducts']%"' did not work

Show 6 more comments

3 answers

1


I was able to solve the problem by reformulating my personal filter functions, thanks to everyone who tried to help.

0

In my intention on this issue you should use Ajax to request the database, so you could query with LIKE and select a certain amount of standard items with LIMIT to update the client screen.

Take a look: Ajax Introduction/Guide

This way, you can avoid loading all the data with select, doing the query only when the user type in the search field, which activates the event in js by making the request by ajax, which makes the connection to the database via php, returning the data.

Got it?

0

Leo, as you requested, you have a solution here that might work in your case.

JS:

    document.getElementById("Id_do_campo_de_pesquisa").addEventListener("keyup",function() {
                            getInfo(this.value);
});



function getInfo(letras){

  $.get("url_do_api.php?letrasProdutos="+letras,function(data){
    var select = document.getElementById('Id_do_Seu_select');
    var info = JSON.parse(data);
    var len = info.length;
    var options = "";

    for(var i = 0; i<len ; i++){
      options += "<option value='"+info[i]+"'>"+info[i]+"</option>";
      //aqui podes simplesmente recuperar os teus produtos e colocar dentro do teu select 
    }

    select.innerHTML = "";
    select.innerHTML = options;
  })

}

As for PHP just build your query with a Like and send the answer with the json_encode(); , however it would be good to have your indexed table so that it is faster in the search

Edit: if you think your filtering function already does that call it instead of calling getInfo(); which was just an example of me same I hope I helped

Edit 2 I’m not seeing any mention of your file either

  • Didn’t work @Scrapbench

  • I also didn’t see any mention of your JS file in <script> tags, you put them ?

  • 1

    Yes, they are all set in tags scripts in my HTML, I did not put because the focus of the question was directly directed to the products section.

  • OK you could try doing a console.log inside get to see if it works, if it doesn’t, go see your PHP log file to see if there are any errors there

Browser other questions tagged php javascript jquery twitter-bootstrap combobox

You are not signed in. Login or sign up in order to post.