How to create a dynamic query in php

Asked

Viewed 666 times

0

Hello I would like your help , I am creating a form with php and mysql , in this form has the request screen , type has the fields to be filled with the month and name , on the hill has the month and year and in the name the person enters the name or the number of the card .

But here comes the problem I’m facing , when the person enters next month the month she requested , but when you do not type comes all the data every month , until there is right because I want it to come like this , but I want the name field stay like this too , when you type the name come only the name and when you don’t type come all , have you to help me .

There’s the part about Query . the $datcadastro is the date this is right but the $nomfuncionario only returns when you type, I want when you do not type anything come all and when you type the name only come the name typed. inserir a descrição da imagem aqui

public function RetornaRelatorios($datcadastro,$nomfuncionario)
{

    if(!empty($datcadastro)&& !empty($nomfuncionario)){
        $WHERE = "WHERE DATE_FORMAT(a.datcadastro,'%Y-%m') = '$datcadastro' AND (a.nomfuncionario) = '$nomfuncionario' ";


    }else{
        $WHERE = "";


    }

    $this->sql = "SELECT 
                date_format(a.datcadastro,'%d/%m/%Y %H:%i:%s') as datcadastro,
                a.beneficiario,
                a.intercambio,
                a.procedimento,
                date_format(a.datprocedimento,'%d/%m/%Y') as datprocedimento,
                a.numguia,
                a.retrospectivo,
                a.presencial,
                a.nomfuncionario,
                a.audmedico,
                a.hospital
                    FROM opme.auditoria a
                        $WHERE

                ;";
}

php mysql

  • To me, it’s duplicate: Search returns equal data and the solution is really quite simple there, but as you want to insist on doing it your way, I leave it up to the community to answer and/or decide whether it is duplicate or not. I just hope that doesn’t start to appear answer that already has in the other, because there is mess.

  • @Bacco I’ve made your way only one thing I don’t understand what I have to put inside the $WHERE = array();

  • It’s not for nothing, you should only touch these lines: if( !empty( $nome ) ) $condicoes[] = "nome LIKE '$nome'"; and repeat for all fields. Each row of these is for a separate field. The rest need not touch, just hit the $query with the table name.

  • Something like this: http://pastebin.com/0K3FugYM

  • @Bacco worth is working .

  • try to understand the logic, you can do really cool things if you understand how it works.

Show 1 more comment

1 answer

0


This happens by the search operator you are using, the same "=" makes a search for the exact content of the input, use the like with '%':

campoTabela like '%$variavel%'

This way the system will perform searches related to the typed content, including bringing all clients when leaving the field empty.

  • @Bacco hello at datcadastro is certain missing only for a employee . the only thing I wanted to change is this part of the code $WHERE = "WHERE DATE_FORMAT(a.datcadastro,'%Y-%m') = '$datcadastro' " AND a.nomfuncionario = '$nomfuncionario'" type when one is filled and the other does not bring the results .

  • @Bacco saw there and left the query so $WHERE = "WHERE DATE_FORMAT(a.datcadastro,'%Y-%m') = '$datcadastro' AND (a.nomfuncionario) LIKE '%$nomfuncionario%' ";

  • @Bacco changed what had to change but does not return the expected , is there any way I can change just something in that line to return the expected ? $WHERE = "WHERE DATE_FORMAT(a.datcadastro,'%Y-%m') = '$datcadastro' AND (a.nomfuncionario) = '$nomfuncionario' "; if you have it, you would be very grateful

  • @Bacco Fiz dessa forma $WHERE = array();
 if( !empty( $nomfuncionario ) ) $WHERE[] = "nomfuncionario LIKE '$nomfuncionario'";
 if( !empty( $datcadastro ) ) $WHERE[] = "DATE_FORMAT(datcadastro,...) = '$datcadastro'";


 $query = 'SELECT FROM ';
 if( !Empty($WHERE) $query .= 'WHERE '.implode(' AND ', $WHERE ); but thanks for everything .

  • @Allan don’t forget the fields and table name in the initial $query

  • @Bacco did his way , but it didn’t work , the way I did is working almost everything , the only thing that does is when I leave the field of the date blank and fill the name it still comes all that was for came only the name I put .

  • Lincoln, I formatted your answer and I took out the extra spaces, see if the adjustment is correct.

Show 2 more comments

Browser other questions tagged php mysql

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