How to make the return of a Query (PDO)

Question

How to make the return of a Query (PDO)

Navigation

#1 by (0 votes)

1

How can I make the selected input month to swallow me the selected user's month and year? It is returning the user but when I do the input month to bring the user data for a certain month it does not come.

thisisthequery

publicfunctionRetornaAtualizacoesFuncionarios($data,$codusuario){$WHERE=array();if(!empty($codusuario)){$WHERE[]="codusuario = :codusuario";}else{ $WHERE[] = "codusuario != '37'"; };
        if( !empty( $data   ) ) $WHERE[] = "DATE_FORMAT(data,'%Y-%m') = :data";         
    try {
         $Query = "SELECT 
        DISTINCT(usuario),
                date_format(data,'%d/%m/%Y %H:%i:%s') as data,
                codigo,
                nome 
                    FROM funcionarios
                         WHERE codusuario = '$codusuario'
                             ORDER BY data DESC  ";
                             if( !empty($WHERE) ) $this->$Query .= ' WHERE '.implode(' AND ', $WHERE );

//echo "<pre>"; print_r($_POST); exit;
        include_once $_SESSION['pmodel'].'/mysqlconnection_class.php';
               $p_sql = MysqlConnection::getInstance()->prepare($Query);

                $_retorno = array(); 

                if($p_sql->execute()) {
                    while ($_result = $p_sql->fetch(PDO::FETCH_ASSOC))  {
                        $_retorno[] = $_result; 
                    }
                }

                return $_retorno;
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }

public function RetornaUsuarios(){
    try {
        $Query = "SELECT distinct(funcionarios.codusuario),funcionarios.usuario 
        FROM funcionarios 
            LEFT JOIN usuarios ON funcionarios.usuario=usuarios.nome
            WHERE funcionarios.codusuario != '37'
                  ";
        include_once $_SESSION['pmodel'].'/mysqlconnection_class.php';
               $p_sql = MysqlConnection::getInstance()->prepare($Query);

                $_retorno = array(); 

                if($p_sql->execute()) {
                    while ($_result = $p_sql->fetch(PDO::FETCH_ASSOC))  {
                        $_retorno[] = $_result; 
                    }
                }

                return $_retorno;
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }


    }

and this is the input

<form class="form"  id="exibefuncionarios" method="post" target='_blank' action="gerar">

        <fieldset>
            <div style="border-radius:5px;padding:10px;">
                <table class="custom-select" cellspacing='0' width='100%' border ='0'>
                    <tr>
                        <td class='label_1'>Período:</td> 
                        <td>
                            <input style="width: 290px;" name="data" type='month'/>
                        </td>
                    </tr>
                </table>
            </div>
        </fieldset>

        <fieldset>
            <div style="border-radius:5px;padding:10px;">
                <table class="custom-select" cellspacing='0' width='100%' border ='0'>
                    <tr>
                        <td class='label_1'>Funcionário:</td> 
                        <td>
                            <select style="width: 290px;" name="codusuario" id="codusuario">
                                    <option value=''>--Selecione--</option>
                                        <?php foreach($lista_funcionarios as $funcionario){ ?>
                                    <option value='<?php echo $funcionario["codusuario"]; ?>'><?php echo $funcionario["usuario"]; ?></option>
                                <?php } ?>
                              </select>
                        </td>
                    </tr>
                </table>
            </div>
        </fieldset>

             <br/>
        <button id="gera_rel_funcionario" class="BlueButton" type="submit">Gera Relatório</button>
        </form>
        </div>  
    </div>

php mysql pdo

asked by anonymous 03.10.2016 / 23:00

1 answer

Open file in Modal Dialog Doubt about Zend Framework

score 0 · Accepted Answer

Can not have two where in sql and also can not after order by

I'm going to do so by way of query or by employee or by code, which I recommend you to separate one method to filter by date and another per user

public function RetornaAtualizacoesFuncionarios($data,$codusuario){ 

    try {

        $Query = "SELECT 
                DISTINCT(usuario), 
                date_format(data,'%d/%m/%Y %H:%i:%s') as data,
                codigo,
                nome 
                FROM funcionarios";

        if(!empty($data)){

            $Query .= "WHERE data = $data";

        }else {

            if(!empty($codusuario) && empty($data)){
                $Query .=  WHERE codusuario = '$codusuario';
            }

        }   
        $Query .=  "ORDER BY data DESC  ";


        //echo "<pre>"; print_r($_POST); exit;
        include_once $_SESSION['pmodel'].'/mysqlconnection_class.php';
        $p_sql = MysqlConnection::getInstance()->prepare($Query);

        $_retorno = array(); 

        if($p_sql->execute()) {


            while ($_result = $p_sql->fetch(PDO::FETCH_ASSOC))  {
                    $_retorno[] = $_result; 
                }
            }
        }

            return $_retorno;
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }

If you are returning a single result, you do not need a while and only

$ _ result = $ p_sql-> fetch (PDO :: FETCH_ASSOC then returns result;