Filter Search

0

I have a problem with creating a search filter with select, I was very confused, I'll post the code and an image of what I need:

the code:

 <?php 
    session_start();
    include_once("../../conexao/conexao.php");
    include_once("../../seguranca.php");
    include_once("../../header.php");
    include_once("../../menu.php");
    seguranca_adm();
    ?>


    <?php
    //Verificar se está sendo passado na URL a página atual, senao é atribuido a pagina 
    $pagina = (isset($_GET['pagina']))? $_GET['pagina'] : 1;
    if(!isset($_GET['pesquisar'])){
        header("Location: adinistrativo.php");
    }else{
        $valor_pesquisar = $_GET['pesquisar'];
    }


    //Selecionar todos os curriculos da tabela, tentei buscar por mais uma tabela tipo o OR tabela empresa mas não rola.
    $result_curriculos = "SELECT * FROM curriculos WHERE nome LIKE '%$valor_pesquisar%' OR empresa LIKE '%$valor_pesquisar'";


    $resultado_curriculos = mysqli_query($conn, $result_curriculos);

    //Contar o total de curriculos 
    //Aqui gera um erro nessa linha - Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\wamp64\www\curriculo\adm\administrativo\pesquisar\pesquisar.php on line 84
    $total_curriculos = mysqli_num_rows($resultado_curriculos);

    //Seta a quantidade de curriculos por pagina
    $quantidade_pg = 6;

    //calcular o número de pagina necessárias para apresentar os curriculos
    $num_pagina = ceil($total_curriculos/$quantidade_pg);

    //Calcular o inicio da visualizacao
    $incio = ($quantidade_pg*$pagina)-$quantidade_pg;

    //Selecionar os curriculos a serem apresentado na página - aqui também da o erro Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\wamp64\www\curriculo\adm\administrativo\pesquisar\pesquisar.php on line 98
    $result_curriculos = "SELECT * FROM curriculos WHERE nome LIKE '%$valor_pesquisar%' OR empresa LIKE '%$valor_pesquisar' limit $incio, $quantidade_pg";
    $resultado_curriculos = mysqli_query($conn, $result_curriculos);
    $total_curriculos = mysqli_num_rows($resultado_curriculos);
    ?>

            <div class="container theme-showcase" role="main">
                <div class="page-header">
                    <div class="row">
                        <div class="col-sm-6 col-md-12">
                        <h1>Curriculos</h1>
                        </div>
                    </div>

                        <!-- INICIO PESQUISA -->
                    <div class="col-sm-12 col-md-12" style="padding:0px;">
                    <form class="form-inline" method="GET" style="padding:20px 0px 20px 0px" action="pesquisar.php">
                        <div class="form-group" style="width:100%;">
                            <div class="form-group" style="width: 205px;">
                                <label class="col-sm-12 control-label">Nome do candidato</label>
                                <div class="col-sm-10">
                                <input type="text" name="pesquisar" class="form-control" style="width:200px;" id="exampleInputName2" placeholder="Nome do candidato...">
                                </div></div>

            <?php if(!empty($row_curriculos['empresa_id'])){
                $empresa_id = $row_curriculos['empresa_id']; 
            }?>
            <div class="form-group" style="width:205px;">
                <label class="col-sm-12 control-label">Empresa</label>
                <div class="col-sm-10">
                    <select class="form-control" name="select_empresa">
                        <option value="">Selecione</option>
                        <?php
                        $result_empresa = "SELECT * FROM empresa";
                        $result_empresa = mysqli_query($conn, $result_empresa);
                        while($row_empresa = mysqli_fetch_assoc($result_empresa)){ ?> 
                            <option value="<?php echo $row_empresa['id']; ?>"<?php
                            if(!empty($_SESSION['value_select_empresa'])){
                                if($_SESSION['value_select_empresa'] == $row_empresa['id']){
                                    echo 'selected';
                                    unset($_SESSION['value_select_empresa']);
                                }
                            }
                            if(!empty($row_curriculos['empresa_id'])){
                                if($empresa_id == $row_empresa['id']){
                                    echo 'selected';
                                }
                            }
                            ?> >                        
                            <?php echo $row_empresa['empresa']; ?></option>
                        <?php } ?>
                    </select>
                </div>
            </div>  
            <?php if(!empty($row_curriculos['area_id'])){
                $area_id = $row_curriculos['area_id']; 
            }?>
            <div class="form-group" style="width:145px;">
                <label class="col-sm-12 control-label">Área de atuação</label>
                <div class="col-sm-10">
                    <select class="form-control" name="select_area">
                        <option value="">Selecione</option>
                        <?php
                        $result_area = "SELECT * FROM area";
                        $result_area = mysqli_query($conn, $result_area);
                        while($row_area = mysqli_fetch_assoc($result_area)){ ?> 
                            <option value="<?php echo $row_area['id']; ?>"<?php
                            if(!empty($_SESSION['value_select_area'])){
                                if($_SESSION['value_select_area'] == $row_area['id']){
                                    echo 'selected';
                                    unset($_SESSION['value_select_area']);
                                }
                            }
                            if(!empty($row_curriculos['area_id'])){
                                if($area_id == $row_area['id']){
                                    echo 'selected';
                                }
                            }
                            ?> >                        
                            <?php echo $row_area['area']; ?></option>
                        <?php } ?>
                    </select>
                </div>
            </div>  
           <?php if(!empty($row_curriculos['cargo_id'])){
                $cargo_id = $row_curriculos['cargo_id']; 
            }?>
            <div class="form-group" style="width:145px;">
                <label class="col-sm-12 control-label">Cargo</label>
                <div class="col-sm-10">
                    <select class="form-control" name="select_cargo">
                        <option value="">Selecione</option>
                        <?php
                        $result_cargo = "SELECT * FROM cargo";
                        $result_cargo = mysqli_query($conn, $result_cargo);
                        while($row_cargo = mysqli_fetch_assoc($result_cargo)){ ?> 
                            <option value="<?php echo $row_cargo['id']; ?>"<?php
                            if(!empty($_SESSION['value_select_cargo'])){
                                if($_SESSION['value_select_cargo'] == $row_cargo['id']){
                                    echo 'selected';
                                    unset($_SESSION['value_select_cargo']);
                                }
                            }
                            if(!empty($row_curriculos['cargo_id'])){
                                if($cargo_id == $row_cargo['id']){
                                    echo 'selected';
                                }
                            }
                            ?> >                        
                            <?php echo $row_cargo['cargo']; ?></option>
                        <?php } ?>
                    </select>
                </div>
            </div>  
            <?php if(!empty($row_curriculos['situacao_id'])){
                $situacao_id = $row_curriculos['situacao_id']; 
            }?>
            <div class="form-group" style="width:145px;">
                <label class="col-sm-12 control-label">Sit. do curriculo</label>
                <div class="col-sm-10">
                    <select class="form-control" name="select_situacao_curriculo">
                        <option value="">Selecione</option>
                        <?php
                        $result_situacao = "SELECT * FROM situacao_curriculo";
                        $result_situacao = mysqli_query($conn, $result_situacao);

                        while($row_situacao_curriculo = mysqli_fetch_assoc($result_situacao)){ ?> 

                            <option value="<?php echo $row_situacao_curriculo['id']; ?>"
                            <?php
                            if(!empty($_SESSION['value_select_situacao_curriculo'])){
                                if($_SESSION['value_select_situacao_curriculo'] == $row_situacao['id']){
                                    echo 'selected';
                                    unset($_SESSION['value_select_situacao_curriculo']);
                                }
                            }
                            if(!empty($row_curriculos['situacao_id'])){
                                if($situacao_id == $row_situacao_curriculo['id']){
                                    echo 'selected';
                                }
                            }
                            ?> >                        
                            <?php echo $row_situacao_curriculo['nome']; ?></option>
                        <?php } ?>
                    </select> 
                </div>
            </div>  


                                    <button type="submit" style=" margin-left: 20px; margin-top: 19px;" class="btn btn-primary">Pesquisar</button>

                                </div>

                            </form>
                        </div>

                        <!-- FIM DA PESQUISA -->
                        <!-- FIM DA PESQUISA -->
                </div>
        <div class="row">
            <div class="col-md-12">
                <table class="table">
                    <thead>
                        <tr>
                            <th class="text-center">ID</th>
                            <th class="text-center">Data de cadastro</th>
                            <th class="text-center">Nome</th>
                            <th class="text-center">E-mail</th>
                            <th class="text-center">Vaga de interesse</th>
                            <th class="text-center">Empresa</th>
                            <th class="text-center">Área de atuação</th>
                            <th class="text-center">Cargo</th>
                            <th class="text-center">Telefone</th>
                            <th class="text-center">Obs. do candidato</th>
                            <th class="text-center">Situação Curriculo</th>
                            <th class="text-center">Observação do R.H.</th>
                        </tr>
                    </thead>
                    <tbody>
<!-- aqui gera outro erro Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\wamp64\www\curriculo\adm\administrativo\pesquisar\pesquisar.php on line 274 -->
                        <?php while($row_curriculos = mysqli_fetch_assoc($resultado_curriculos)){?>
                            <tr>
                                <td class="text-center"><?php echo $row_curriculos["id"]; ?></td>
                                <td class="text-center"><?php echo date('d/m/Y H:i:s',strtotime($row_curriculos["created"])); ?></td>
                                <td class="text-center"><?php echo $row_curriculos["nome"]; ?></td>
                                <td class="text-center"><?php echo $row_curriculos["email"]; ?></td>
                                <td class="text-center"><?php echo $row_curriculos["vaga"]; ?></td>
                                <td class="text-center"><?php 
                $situacao_atual = $row_curriculos['empresa_id'];
                $result_empresa = "SELECT * FROM empresa WHERE id = '$situacao_atual'";
                $result_empresa = mysqli_query($conn, $result_empresa);
                $row_empresa = mysqli_fetch_assoc($result_empresa);
                echo $row_empresa['empresa']; ?>                                                            
                                </td>
                                <td class="text-center"><?php 
                $situacao_atual = $row_curriculos['area_id'];
                $result_area = "SELECT * FROM area WHERE id = '$situacao_atual'";
                $result_area = mysqli_query($conn, $result_area);
                $row_area = mysqli_fetch_assoc($result_area);
                echo $row_area['area']; ?>  
                            </td>
                                <td class="text-center"><?php
                $situacao_atual = $row_curriculos['cargo_id'];
                $result_cargo = "SELECT * FROM cargo WHERE id = '$situacao_atual'";
                $result_cargo = mysqli_query($conn, $result_cargo);
                $row_cargo = mysqli_fetch_assoc($result_cargo);
                echo $row_cargo['cargo']; ?>
                                </td>   
                                <td class="text-center"><?php echo $row_curriculos["telefone"]; ?></td>
                                <td class="text-center"><?php echo $row_curriculos["mensagem"]; ?></td>
                                <td class="text-center"><?php 
                $situacao_atual = $row_curriculos['situacao_id'];
                $result_situacao = "SELECT * FROM situacao_curriculo WHERE id = '$situacao_atual'";
                $result_situacao = mysqli_query($conn, $result_situacao);
                $row_situacao_curriculo = mysqli_fetch_assoc($result_situacao);
                echo $row_situacao_curriculo['nome']; ?></td>
                                <td class="text-center"><?php echo $row_curriculos["mensagem_rh"]; ?></td>


                              <td class="text-center">
                                    <a href="administrativo.php?link=17&id=<?php echo $row_curriculos["id"]; ?>">
                                        <button type="button" class="btn btn-xs btn-primary">
                                            Visualizar
                                        </button>
                                    </a>
                                    <a href="administrativo.php?link=18&id=<?php echo $row_curriculos["id"]; ?>">
                                        <button type="button" class="btn btn-xs btn-warning">
                                            Editar
                                        </button>
                                    </a>
                                    <a href="administrativo/processa/adm_apagar_curriculo.php?id=<?php echo $row_curriculos["id"]; ?>">
                                        <button type="button" class="btn btn-xs btn-danger">
                                            Apagar
                                        </button>
                                    </a>
                                </td>
                            </tr>
                        <?php } ?>
                    </tbody>
                </table>
            </div>
        </div>
                <?php
                    //Verificar a pagina anterior e posterior
                    $pagina_anterior = $pagina - 1;
                    $pagina_posterior = $pagina + 1;
                ?>
                <nav class="text-center">
                    <ul class="pagination">
                        <li>
                            <?php
                            if($pagina_anterior != 0){ ?>
                                <a href="pesquisar.php?pagina=<?php echo $pagina_anterior; ?>&pesquisar=<?php echo $valor_pesquisar; ?>" aria-label="Previous">
                                    <span aria-hidden="true">&laquo;</span>
                                </a>
                            <?php }else{ ?>
                                <span aria-hidden="true">&laquo;</span>
                        <?php }  ?>
                        </li>
                        <?php 
                        //Apresentar a paginacao
                        for($i = 1; $i < $num_pagina + 1; $i++){ ?>
                            <li><a href="pesquisar.php?pagina=<?php echo $i; ?>&pesquisar=<?php echo $valor_pesquisar; ?>"><?php echo $i; ?></a></li>
                        <?php } ?>
                        <li>
                            <?php
                            if($pagina_posterior <= $num_pagina){ ?>
                                <a href="pesquisar.php?pagina=<?php echo $pagina_posterior; ?>&pesquisar=<?php echo $valor_pesquisar; ?>" aria-label="Previous">
                                    <span aria-hidden="true">&raquo;</span>
                                </a>
                            <?php }else{ ?>
                                <span aria-hidden="true">&raquo;</span>
                        <?php }  ?>
                        </li>
                    </ul>
                </nav>
            </div>
            <!-- Bootstrap core JavaScript
            ================================================== -->
            <!-- Placed at the end of the document so the pages load faster -->
            <script>window.jQuery || document.write('<script src="../../assets/js/vendor/jquery.min.js"><\/script>')</script>
            <script src="../../js/bootstrap.min.js"></script>
            <script src="../../js/docs.min.js"></script>
            <!-- IE10 viewport hack for Surface/desktop Windows 8 bug -->
            <script src="../../js/ie10-viewport-bug-workaround.js"></script>
        </body>
    </html>

If I use the "name" field where it does the search until it goes, it works, but removing the OR company LIKE '% $ search_value' - works correct only by name.

$result_curriculos = "SELECT * FROM curriculos WHERE nome LIKE '%$valor_pesquisar%' limit $incio, $quantidade_pg";

What I need is that if the user performs a filter, per company or position or area it filters result,

For this I have the tables like this:

curriculum table Id columns | name | Contact Us | Phone | area_id | cargo_id | company_id | situacao_id

table area id columns | area

cargo table id columns | charge

Company table id columns | company

table_curricular_table id columns | name

I think I've shown everything I'm doing

And I still leave an image to illustrate how it's being done.

I hope someone can help me, because I'm here for a few days to finish a project.

Thank you

Follow the picture link

EDITED ------------------------------

I did what friend Jeferson suggested, but did not roll.

When I put the SELECT * FROM curricula as c, company as emp WHERE c.name LIKE '%'. $ search_value. search_value. "% '";

Maybe the syntax is wrong, I'll improve the reasoning to see if friends help. In the curriculum table I have the column empresa_id, in it search only the company ID referring to the company example company_id = 3 table

In the company table has the column ID | company - example id = 3 | company = SULMINAS

So in this table COMPANY I have this:
ID = 1 | company = NORTEMINAS
ID = 2 | company = LESEMINAS
ID = 3 | company = SULMINAS

In the CURRICULES table I have: ID = 1 | NAME = FULANO | [email protected] | area_id = 1 | Company_id = 3

So in the search I have a select option that pulls the curriculum table and when it informs the column empresa_id - pulls the ID of the company table, informing the name of the company

<select class="form-control" name="select_empresa">
                    <option value="">Selecione</option>
                    <?php
                    $result_empresa = "SELECT * FROM empresa";
                    $result_empresa = mysqli_query($conn, $result_empresa);
                    while($row_empresa = mysqli_fetch_assoc($result_empresa)){ ?> 
                        <option value="<?php echo $row_empresa['id']; ?>"<?php
                        if(!empty($_SESSION['value_select_empresa'])){
                            if($_SESSION['value_select_empresa'] == $row_empresa['id']){
                                echo 'selected';
                                unset($_SESSION['value_select_empresa']);
                            }
                        }
                        if(!empty($row_curriculos['empresa_id'])){
                            if($empresa_id == $row_empresa['id']){
                                echo 'selected';
                            }
                        }
                        ?> >                        
                        <?php echo $row_empresa['empresa']; ?></option>
                    <?php } ?>
                </select>

Then when selecting in the select option the company = SULMINAS and clicking the search button it should only display the result with the ID | name | Contact Us | area | company


When you click Search, you will only see the list of users who have selected the company in the register selection.

IhavethesetwoQuery

//Asqueselecionamtodososcurriculosdatabela$result_curriculos="SELECT * FROM curriculos WHERE nome LIKE '%$valor_pesquisar%'";
//se ela é usada assim e eu uso o campo nome TEXT (digitando qualquer letra, a pesquisa filtra o nome).

and I have these query's to display the search result

$result_curriculos = "SELECT * FROM curriculos WHERE nome LIKE '%$valor_pesquisar%' limit $incio, $quantidade_pg";
$resultado_curriculos = mysqli_query($conn, $result_curriculos);
$total_curriculos = mysqli_num_rows($resultado_curriculos);
  • I need to understand the syntax that pulls the value of the select option from the CURRICULOS table, column COMPANY - pulling the ID and the result is just that ID.
  • You need to understand the syntax that displays the search result.

  • I would like the search to work regardless of how it performs the selection in the Select Option - type if it uses only the company to display the result, or the company and charge combination. type I want results of the company SULMINAS in the position of ANALYST, or only display the result of the COMPANY or only of the POSITION.

I think the explanation is now much more complete.

It would greatly help a solution Thank you for your attention.

    
asked by anonymous 23.03.2017 / 13:09

1 answer

0

Considering that you are looking for Candidate Name or Company Name , I believe this is a small syntax error in your Query. In addition to not doing the join of the tables since name of the candidate is in a different table of company. Try:

 "SELECT * FROM curriculos as c, empresa as emp 
  WHERE c.nome LIKE '%".$valor_pesquisar."%' OR emp.empresa LIKE '%".$valor_pesquisar."%'";
    
23.03.2017 / 13:38