Improve a Bank Search

Asked

Viewed 113 times

1

I would like an improvement help in a Search Query today every Search the user makes in the search field I give an OR in my Where I wonder if you have any better way to do this because I will need to put but 5 search options for the user

A single search for everything according to the image below

inserir a descrição da imagem aqui

// CONFIGURAÇÃO DO FILTRO

if(isset($_POST['filtro_busca'])){

  // Recupero os valores
  $conteudo['value_busca']  = $this->input->post('filtro_busca'     , TRUE);

}

A part of the Controller where I want to put 5 more parameters in this search or 5 more "OR"

$where .= " AND (
     nome like '%{$condicao['value_busca']}%'
  OR email like '%{$condicao['value_busca']}%'
  OR telefone_1 like '%{$condicao['value_busca']}%'
  OR rg like '%{$condicao['value_busca']}%'
  OR cpf like '%{$condicao['value_busca']}%'
  OR codigo like '%{$condicao['value_busca']}%'
) ";
php mysql oop codeigniter

  • From what I understand you are making the search return values that should not, because by my understanding should not use Like in the fields Telefone, RG, CPF, CODIGO, for being whole fields;

  • 1

    @Apprentice can’t claim they’re whole, I put as myself VARCHAR because I prefer that the masks are saved with on the bench. Shaolin, are you using a search field for all these items? Got a little confused.

  • That’s right @Diéfanifavaretopiovezan a search field for all items actually this code already running a while ago was done by another guy wanted to give an improved leave automated

  • What I mean, could check what the user typed, if it was a number then only search in certain fields, since the fields are all VARCHAR so it would greatly reduce the response time

  • You can make a field that has all the strings of the other fields and just search for this big field. You can use a database Rigger to update this search field, or change the field at the time of update and Insert.

  • Guy talking about autonomy, just do it with one item, you’ll know what size of your bank, if it’s big you’ll run the whole bank to find one person with all these columns

Show 1 more comment

2 answers

2

If you do not know in which field will be the value searched or even know but want to do a general search in the fields using the value typed, the ideal is to make a SELECT and use CONCAT within the clause WHERE, this way the fields you need will be concatenated and the search will be done inside them. It is important to place single quotes after each name of the fields to separate the values. In general the SELECT it should be like this:

SELECT * FROM tabela WHERE CONCAT(Campo1, '', Campo2, '', Campo3, '', Campon) LIKE '%valor%'

For your most accurate, your code will be

$where .= " AND CONCAT(nome, '', email, '', telefone, '', rg, '', cpf, '', codigo) LIKE '%{$condicao['value_busca']}%' )";

If there are problems with Case Sensitivity, you can make it all Case Insensitive 

$where .= " AND LOWER(CONCAT(nome, '', email, '', telefone, '', rg, '', cpf, '', codigo)) LIKE LOWER('%{$condicao['value_busca']}%' ))";

I hope I’ve helped.

1

You can create a <select> for the user to choose the filter you want to use.

For example:

    <select name="filtro">
       <option value="fNomeEmail">Nome e E-Mail</option>
       <option value="fTel">Telefone</option>
       <option value="fRgCpf">RG/CPF</option>
       <option value="fCod">Código</option>
    </select>

Hence you mount SQL only with the desired filter not having to search in all fields of the table.

   $where .= " AND (";
   if ($_POST['filtro'] == 'fNomeEmail') $where .= "nome like '%{$condicao['value_busca']}%' OR email like '%{$condicao['value_busca']}%' ";
   if ($_POST['filtro'] == 'fTel') $where .= "OR telefone_1 like '%{$condicao['value_busca']}%' ";
   if ($_POST['filtro'] == 'fRgCpf') $where .= "OR rg like '%{$condicao['value_busca']}%' OR cpf like '%{$condicao['value_busca']}%' ";
   if ($_POST['filtro'] == 'fCod') $where .= "OR codigo like '%{$condicao['value_busca']}%'";
    $where .= ")";

Or you can do it this way too by checking if he typed numbers and so mount SQL:

   $caracteres = array("(", ")", "-", ".", "+", " ");
   $soNumero = str_replace($caracteres, "", $condicao['value_busca']);

   $where .= " AND (";
   if (is_numeric($soNumero)) {
     $where .= "telefone_1 LIKE '%{$condicao['value_busca']}%' OR rg LIKE '%{$condicao['value_busca']}%' OR cpf LIKE '%{$condicao['value_busca']}%' OR codigo LIKE '%{$condicao['value_busca']}%'";
   } else {
     $where .= "nome LIKE '%{$condicao['value_busca']}%' OR email LIKE '%{$condicao['value_busca']}%'";
   }            
   $where .= ")";

Browser other questions tagged php mysql oop codeigniter

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