Search Database By Selecting Select Options

Asked

Viewed 1,645 times

2

good morning. I have a little problem I’m having trouble solving. The situation is as follows: I have 2 selects (State and city), I would like that when selecting a state the system did a search in my BD to find distributors of that state and when selecting the city the system further refines the search for distributors of that particular state + certain city. All this without needing a button and without refreshing the page.

I tried to adapt a code I found on the Internet, but there is no error, nor appears any result.

States and cities are automatically populated:

<select class="select-onde-comprar" onchange="buscarOndeComprar()" id="estado" name="estado"></select>
<select class="select-onde-comprar" onchange="buscarOndeComprar()" id="cidade" name="cidade"></select>

Javascript function that should link to the search. I believe this is where the problem lies.

var req;

// FUNÇÃO PARA BUSCA NOTICIA
function buscarOndeComprar() {

// Verificando Browser
if(window.XMLHttpRequest) {
   req = new XMLHttpRequest();
}
else if(window.ActiveXObject) {
   req = new ActiveXObject("Microsoft.XMLHTTP");
}

// Arquivo PHP juntamente com o valor digitado no campo (método GET)
var estado = document.getElementById('estado').value;
var cidade = document.getElementById('cidade').value;

var url = "busca-onde-comprar.php?estado="+estado+"&cidade="+cidade;

// Chamada do método open para processar a requisição
req.open("Get", url, true);

// Quando o objeto recebe o retorno, chamamos a seguinte função;
req.onreadystatechange = function() {

    // Exibe a mensagem "Buscando Distribuidores e Revendedores..." enquanto carrega
    if(req.readyState == 1) {
        document.getElementById('resultado').innerHTML = 'Buscando Distribuidores e Revendedores...';
    }

    // Verifica se o Ajax realizou todas as operações corretamente
    if(req.readyState == 4 && req.status == 200) {

    // Resposta retornada pelo busca.php
    var resposta = req.responseText;

    // Abaixo colocamos a(s) resposta(s) na div resultado
    document.getElementById('resultado').innerHTML = resposta;
    }
}
req.send(null);
}

SEARCH PAGE:

<?php
// Incluir aquivo de conexão
include("bd.php");

// Recebe o valor enviado
$estado = $_GET['estado'];
$cidade = $_GET['cidade'];

// Procura titulos no banco relacionados ao valor
if(!empty($cidade)){
$sql = mysql_query("SELECT * FROM distribuidor WHERE estado = ".$estado." and cidade = ".$cidade."");
}else {
    $sql = mysql_query("SELECT * FROM distribuidor WHERE estado = ".$estado."");
}
// Exibe todos os valores encontrados
if (!empty($sql)){
while ($ondecomprar = mysql_fetch_object($sql)) {
?>
    <div class="row-fluid">
        <div class="col-lg-4 col-md-4 col-sm-6 col-xs-12 onde-comprar">
         <h4 class=""><?php echo $ondecomprar->nome?></h4>
         <p><?php echo $ondecomprar->endereco?>, <?php echo $ondecomprar->numero?> - <?php echo $ondecomprar->bairro?><p>
         <p><strong>Cidade:</strong> <?php echo $ondecomprar->cidade?> - <?php echo $ondecomprar->estado?></p>
         <p><strong>Telefone:</strong> <?php echo $ondecomprar->telefone?></p>
         <p><strong>Celular:</strong> <?php echo $ondecomprar->celular?> <i class="fa fa-whatsapp" aria-hidden="true"></i></p>
        </div>
    </div>
<?php}
} else{
?>
<div class="row-fluid">
    <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12 titulos text-center">
         <h4>Ainda não possuímos nenhum distribuidor ou revendedor cadastrado para este estado.</h4>

    </div>
</div>
<?php

}
?>

Example of a site where you have this search: https://tulipia.com.br/onde-encontrar

php javascript mysql ajax

  • why it does not do so, as it selects the city it completes the state and suppliers

  • For me to select a city, first I have to choose the state, because to appear the cities I depend on a selected state.

  • from what I saw you do the database search right, da para fazer uma função que a medida que selecionaque algo ele complete os fornecedores

  • You do not prefer to use AJAX with Jquery?

  • Yes, but how to do it? I’m bumping into the execution

  • I will illustrate from your data.

  • @Andrei Coelho awaits your example, to see if it helps me solve the problem. Thank you

  • I’ll do it this afternoon. I’ll use exactly your parameters

Show 3 more comments

3 answers

3

First of all, you lack the "&" that is used when you pass more than one parameter in get.

var url = "search-where-to-buy.php? status="+status+"&city="+city;

  • True. Maybe this will solve.

  • It really had forgotten about &, but it still didn’t work. It doesn’t search, doesn’t generate error or anything.. rsrs

2


What you need to do is change two things...

1) Jquery:

$(document).ready(function() {
    $("#estado").change(function(){
        buscarOndeComprar();
    });

    $("#cidade").change(function(){
        buscarOndeComprar();
    });

// FUNÇÃO PARA BUSCA DISTRIBUIDORES
function buscarOndeComprar() {

// Verificando Browser
if(window.XMLHttpRequest) {
   req = new XMLHttpRequest();
}
else if(window.ActiveXObject) {
   req = new ActiveXObject("Microsoft.XMLHTTP");
}
.... resto do código ...

2) O select

<select class="select-onde-comprar" id="estado" name="estado"></select>
<select class="select-onde-comprar" id="estado" name="estado"></select>

The problem was that you were mixing JQUERY with pure JS. So your select couldn’t call the function buscarOndeComprar() directly.

  • From what I’m seeing the problem is in the JS file call. I changed the JS to just this here: Function searchOndeComprar() { Alert("COULD NOT PERFORM THE REQUEST"); } to see if the alert appeared when choosing an option in select and even that doesn’t happen. It seems that this here "onchange="searchOndeComprar()"" is not calling the function

  • @Fredericomoreira appeared the Alert?

  • You didn’t show up....

  • @Fredericomoreira if it did not appear, try to change the positioning of the script

  • First place HTML after JAVASCRIPT

  • Already this so, first HTML, JS always put at the bottom of the page.

  • @Fredericomoreira Put it like this onchange="buscarOndeComprar();" no select (put a ";")

  • Still not calling. See the link where the code is: http://adeliamendonca.com.br/onde-comprar.php This may make it easier to find the error

  • @Fredericomoreira The problem is that you are mixing the two things... The initial call is within Jquery. I will edit the answer.

  • I have already changed the place of the call several times, to see if somewhere works.. rsrs

  • I think I figured out the problem. I think it’s causing conflict between the JS that generates the names of states and cities with the JS that does the search. I created a select from scratch and called the JS and appeared the alert.

  • @Fredericomoreira I changed the answer... now your functions will work

  • I think it will now. .rsr. At least it has already generated an error in the file that does the search when selecting the city...

  • @Fredericomoreira does not forget to evaluate the answer. Hug!

  • 1

    You can leave that I will evaluate yes. Thank you very much for your help.

  • @Fredericomoreira quiet... If you need to call here!

Show 11 more comments

0

A simple (more or less) example that can help you:

Javascript(jQuery): - Here you will make the ajax request and check which state is selected, with the return of ajax (which will have a json of cities on return), options will be opened in the select of cities.

$(document).ready( function ()
{
    $("#filtro").change(function() {
        var option = $(this).find('option:selected').val();
        if(option == 0)
            $('#cidade').val("0");
        else
            carregaCidades(option, "#cidade");
    });
    // -- Edit --
    $(document).on("change", "#cidade", function() {
        var option = $(this).find('option:selected').val();
        carregaFornecedores(option, "#cidade"); // Só crie a função e o ajax
    });
  });

function carregaCidades(estado, selecionado="#cidade"){
// alert(selecionado);
    $.ajax({
        type:'post',    
        dataType: 'json',
        data: "op=buscaCidade&estado="+estado,
        url: "buscacidades.php",
        success: function(dados){
            // alert(dados);
            $(selecionado).find('option').remove().end();
            $(selecionado).append($('<option>', {
                value: 0,
                text: 'Todas'
            }));
            for(var i=0;dados.length>i;i++){
                $(selecionado).append($('<option>', {
                    value: dados[i].id_cidade,
                    text: dados[i].nome
                }));
            }

        }, error:function(dados){ alert("ERROU"); }
    });
}

HTML or View: - Here is the simple view page with the ids in the selects that will be used in jQuery

<div>
    <label>Estados</label>
    <select class="c-select" style="line-height: 17px;width:100%" name="filtro" id="filtro">
        <option value="0">Todos os Estados</option>
        <option value="AC">Acre</option>
        <option value="AL">Alagoas</option>
        <option value="AP">Amapá</option>
        <option value="AM">Amazonas</option>
        <option value="BA">Bahia</option>
        <option value="CE">Ceará</option>
        <option value="DF">Distrito Federal</option>
        <option value="ES">Espirito Santo</option>
        <option value="GO">Goiás</option>
        <option value="MA">Maranhão</option>
        <option value="MS">Mato Grosso do Sul</option>
        <option value="MT">Mato Grosso</option>
        <option value="MG">Minas Gerais</option>
        <option value="PA">Pará</option>
        <option value="PB">Paraíba</option>
        <option value="PR">Paraná</option>
        <option value="PE">Pernambuco</option>
        <option value="PI">Piauí</option>
        <option value="RJ">Rio de Janeiro</option>
        <option value="RN">Rio Grande do Norte</option>
        <option value="RS">Rio Grande do Sul</option>
        <option value="RO">Rondônia</option>
        <option value="RR">Roraima</option>
        <option value="SC">Santa Catarina</option>
        <option value="SP">São Paulo</option>
        <option value="SE">Sergipe</option>
        <option value="TO">Tocantins</option>
    </select>
    <label>Cidades</label>
    <select type="text" id="cidade" class="form-control " name="cidade">
        <option value="0">Todas</option>
    </select>
</div>

PHP: - Here you only have to search and return the values according to the id selected there in html.

$query = "SELECT * from cidades WHERE estado = ".$_POST["estado"];
$result = mysql_query($query);
echo json_encode(mysql_fetch_row($result));
//Teste esse retorno, não tenho certeza da função correta do mysqli

  • The state and city select I already managed to make work. By selecting the state it already fills the select with the cities of that state. The problem is time to do a search of distributors of that state and city. No query is generated when selecting.

  • Because this city field is a dynamical field your js will have to be a bit dferente, I will edit the section of jQuery

  • I already have the JS making popular the state and city select, these are working perfectly. What I need to do is that when selecting a particular state it calls a js function to do the search in the comic without refreshing the page, then if the person chooses a city there will do another search refining the results just for that city. What is currently happening is q when selecting a state or city does not happen nd, it seems that this calling function JS

Browser other questions tagged php javascript mysql ajax

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