Mysql PHP query with filtering by various criteria

Question

Mysql PHP query with filtering by various criteria

Asked 4 years ago

Viewed 73 times

-2

Good morning, fellas. I have a church membership database. I made a table to view these records, but as my BD has 39 columns, I took only the most important, and at the end of the line I put a link to the complete record.

On this home page where all the records appear, I put 7 selects (combobox) to do a filtering combining situations.

The selects are:

Selects will not be filled dynamically. Values are fixed.

I want that, when entering the page, the user receives the list of all the records. And by clicking, for example, in situation (member), sex (M) and status(absent), and receiving data from all male members who are absent.

And other combinations too.

I already managed to return the value with all the records when entering the page, and this part is working. . The results appear correctly in the table.

So I added the selects, already populated, and the button.

And now I need help: How do I mount the query so that, when clicking the button, it meets the selected criteria? Since, if any select is empty, it must return all records, but if it is marked, return only those corresponding. If anyone can help...

Below follows my complete code:


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

  if (!isset($_SESSION['UsuarioID'])) {

      session_destroy();
      header("Location: login.html"); exit;
  }


include("classe/conecta.php");

$consulta = "SELECT Cod,Nome,Tel,Cel,Email,DtNasc,Status FROM CadPessoas order by Nome";
$con = $mysqli->query($consulta) or die($mysqli->error);

?>

<html xmlns="http://www.w3.org/1999/xhtml" lang="pt-br" xml:lang="pt-br">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    </head>
    <body><center>
       <br><br>
       
       
            <p>
            <table  bgcolor="azure" border=0 cellpadding="10">
                <tr>
                    <td width="130px">Situação<br>
                    <select style=" width:130px" name="situacao">
                    <option value=""></option>
                    <option value="Membros">Membros</option>
                    <option value="nao_membros">Solteiros</option>
                </select></td>
                    <td width="130px">Estado Civil<br>
                    <select style=" width:130px" name="estcivil">
                    <option value=""></option>
                    <option value="solteiros">Solteiros</option>
                    <option value="casados">Casados</option>
                    <option value="separados">Separados</option>
                    <option value="divorciados">Divorciados</option>
                    <option value="viuvos">Viúvos</option>
                </select></td>
                    <td width="130px">Sexo<br>
                    <select style=" width:130px" name="sexo">
                    <option value=""></option>
                    <option value="m">Masculino</option>
                    <option value="f">Feminino</option>
                </select></td>
                    <td width="130px">Status<br>
                    <select style=" width:130px" name="status">
                    <option value=""></option>
                    <option value="ativos">Ativos</option>
                    <option value="inativos">Inativos</option>
                    <option value="impossibilitados">Impossibilitados</option>
                    <option value="ausentes">Ausentes</option>
                    <option value="sob_disciplina">Sob Disciplina</option>
                </select></td>
                    <td width="130px">Faixa Etária<br>
                    <select style=" width:130px" name="faixaetaria">
                    <option value=""></option>
                    <option value="criancas">Até 08 Anos</option>
                    <option value="juniores">09 a 11 Anos</option>
                    <option value="adolescentes">12 a 17 Anos</option>
                    <option value="Jovens1">18 a 25 Anos</option>
                    <option value="Jovens2">26 a 35 Anos</option>
                    <option value="Adultos1">36 a 49 Anos</option>
                    <option value="Adultos2">50 a 59 Anos</option>
                    <option value="Idosos1">60 a 64 Anos</option>
                    <option value="Idosos2">65 a 79 Anos</option>
                    <option value="Idosos3">80 acima</option>
                </select>
                    <td width="130px">Forma de Admissão<br>
                    <select style=" width:130px" name="admissao">
                    <option value=""></option>
                    <option value="batismo">Batismo</option>
                    <option value="transferencia">Transferência</option>
                    <option value="reconciliacao">Reconciliação</option>
                    <option value="aclamacao">Aclamação</option>
                </select></td>
                    <td width="130px">Forma de Saída<br>
                    <select style=" width:130px" name="saida">
                    <option value=""></option>
                    <option value="transferencia">Transferência</option>
                    <option value="falecimento">Falecimento</option>
                    <option value="desligamento">Desligamento</option>
                </select></td>
                <td width="130px"><br><input type="submit" name="botaobuscar" value="PESQUISAR" style=" width:130px"  /></td>
                </tr></table>

            </p>
        </form>
</form>



       <br><br>
       
       <table border=0>
           <tr bgcolor="blue" align="center" <div style="font-size: 18px; font-weight: bold; color: white;">
               <td>Código:</td>
               <td>Nome:</td>
               <td>Telefone:</td>
               <td>Celular:</td>
               <td>E-mail:</td>
               <td>Data de Nascimento:</td>
               <td>Status:</td>
                <td>Ação</td>
           </tr>
           <?php while($dado = $con->fetch_array()){ ?>
           <tr style="cursor:default" onMouseOver="javascript:this.style.backgroundColor='#B0E0E6'" onMouseOut="javascript:this.style.backgroundColor=''">
               <td><?php echo $dado["Cod"]; ?></td>
               <td><?php echo $dado["Nome"]; ?></td>
               <td><?php echo $dado["Tel"]; ?></td>
               <td><?php echo $dado["Cel"]; ?></td>
               <td><?php echo $dado["Email"]; ?></td>
               <td><?php echo date("d/m/Y", strtotime($dado["DtNasc"])); ?></td>
               <td><?php echo $dado["Status"]; ?></td>
               <td><a href="dadoscompletos.php?codigo=<?php echo $dado["Cod"]; ?>">Dados Completos</a>
               </td>
           </tr>
           <?php } ?>
           
       </table>
        </center>  
    </body>
    
</html> ```

1 answer

Browser other questions tagged php mysql

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

by Luis Miguel • 59 points · Answer 1 · 2020-12-16T15:57:01+00:00

There is no answer just for that, what I would do is this:

In each select put an option called "Ignore" as default

Do a query with the beginning of the query like this

$query = "SELECT Cod,Nome,Tel,Cel,Email,DtNasc,Status FROM CadPessoas where ";

then you test to see if it came as ignore or not and will complete the query, thus:

if ($sexo != "Ignorar"){

$query .= " sexo = $sexo and"; 

}

Repeat this with each criterion and at the end put

$query.= " order by nome";

Then note that the query will end and order by nome then you have to use replace to subtitle and order by order only.

Do not worry about the negativity of the question, the question is ok, what is not ok is the ego of these guys who think the question is not up to its inflamed ego.

Skewer give you direction.