Mysql search with select option filter

Asked

Viewed 1,284 times

1

I have a question. I have a php page with several select options to filter a search in the database. However, I cannot use the selected values in the query. I did as an example I saw on another site, an array to store only the values of the select options that were filled and concatenate this array in the 'Where' clause of my query... unsuccessfully ! 

$tipoentradalog = post('tipoentradalog');
    $usuario = post('usuario');
    $codcliente = post('codcliente');

    $where = Array();
    if ($tipoentradalog) {
        $where[] = " 'tipoentradalog' = '{$tipoentradalog}'";
    }
    if ($usuario) {
        $where[] = " 'usuario' = '{$usuario}'";
    }

    if ($codcliente) {
        $where[] = " 'codcliente' = '{$codcliente}'";
    }

$sql = "select
                            l.datacriacao,
                            tl.nome as tipo,
                            l.descricao,
                            cli.nome as cliente,
                            us.nome as usuario,
                            l.codigo
                            from log l
                            inner join cliente cli on cli.codcliente=l.codcliente
                            inner join usuario us on us.codusuario=l.codusuario
                            inner join empresa emp on emp.codempresa=l.codempresa
                            inner join tipoentradalog tl on tl.codtipoentradalog=l.codtipoentradalog;";
            if(sizeof($where)) {
                $sql .= ' WHERE ' . implode(' AND ', $where);
            }
            $rst = my_query($connR, $sql);

Can anyone help me? Thank you

php mysql

  • use $_POST instead of post, I think the first problem is this.

  • So, here where I work we already have a function defined for post and get methods... I think it is to avoid sql Injection rs I used this post in other parts of the code and it worked normally.

  • What are the other problems ? rsrs am new with web programming

  • try using the syntax alias.coluna. You are using alias for tables and select but not for conditions. Some column that appears in more than one table may be generating an exception.

  • Richard, I tried to use this alias scheme, but it didn’t happen... When I select the filters, the following error appears: 'WHERE 'tl.nome' = '46' AND 'cli.nome' = '172808''... I think he’s taking the primary keys of the records I need instead of the field I want to show... how do I change this ?

1 answer

1


The problem is you put quotation mark in the name of the fields, and this causes you to make an invalid comparison.

Fix as below that your query will work:

if ($tipoentradalog) {
        $where[] = " tipoentradalog = '{$tipoentradalog}'";
}
if ($usuario) {
        $where[] = " usuario = '{$usuario}'";
}
if ($codcliente) {
        $where[] = " codcliente = '{$codcliente}'";
}

If you want to put quotes, for these case, you should use the crase:

if ($tipoentradalog) {
        $where[] = " `tipoentradalog` = '{$tipoentradalog}'";
}
if ($usuario) {
        $where[] = " `usuario` = '{$usuario}'";
}
if ($codcliente) {
        $where[] = " `codcliente` = '{$codcliente}'";
}

  • All right, it worked. Thanks for your help !

Browser other questions tagged php mysql

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