Generate Excel report by filtering query in Mysql database

Question

Good afternoon, thanks for your help. Can you help me create this parameter search method please? I started editing it, but the problem is that I can’t generate this method using a variable that’s present on the screen. For example, I did as follows: $result = $obj->getAll('usuarios_id IN (SELECT usuarios_id FROM usuarios WHERE empresas_id = 12'); In this case it works, and displays the results of the company_id = 12. I’m trying to modify to pass a parameter instead of a fixed value (12). I tried this: $result = $obj->getAll('usuarios_id IN (SELECT usuarios_id FROM usuarios WHERE empresas_id = ". $empresa." '); It doesn’t return any results. I’m having difficulty passing this $company parameter. The method was like this: public Function excelRelatorio() { $arrayRow = array('Name','Form Payment','Status', 'KM','Value', 'Extra Value', 'Date'); $this->geraExcelRelatorio($arrayRow); }

private function geraExcel($arrayRow,$empresa)
{


    $this->load->helper('xls');

    $arr[] = $arrayRow;

    $obj = new Viagem();
    $result = $obj->getAll('usuarios_id IN (SELECT usuarios_id FROM usuarios WHERE empresas_id = ".$empresa."');


    if (isset($result) && count($result) > 0) {

        foreach ($result['rows'] as $v) {

            $arr[] = array(

                utf8_decode($v->getUsuario()->getNome()), $v->getFormaPagamento()->getNome(), $v->getStatus()->getNome(), $v->getDistancia(), $v->getValor(), $v->getValorExtra(), dataHoraBr($v->getDataCriado()), $v->getEntregador()->getNome()

            );

        }

    }

    array_to_xls($arr, 'financeiro'.date("m-d-y").'.xls');
}

Answer 1

Got it. Here is an example of a query with parameters:

$search_ID = 1; 
$search_product = "PD1001"; 

$query = "SELECT id, product_code, product_desc, price FROM products 
WHERE ID=? AND product_code=?";
$statement = $mysqli->prepare($query);
$statement->bind_param('is', $search_ID, $search_product);
$statement->execute();
$statement->bind_result($id, $product_code, $product_desc, $price);

print '<table border="1">';
while($statement->fetch()) {
    print '<tr>';
    print '<td>'.$id.'</td>';
    print '<td>'.$product_code.'</td>';
    print '<td>'.$product_desc.'</td>';
    print '<td>'.$price.'</td>';
    print '</tr>';

}   
print '</table>';

//close connection
$statement->close();

Answer 2

You are searching the trip data with a getAll() must be why it is returning all the data, create a method where you search only the filtered data, or pass by parameter to the method geraExcel() the data already filtered and manipulate that data.

Answer 3

@Mauricio3012. Your code makes no sense. You pass the variable as a function parameter $arrayRow. In the code snippet below, you pass its value to another variable, but in no time, use it:

$arr[] = $arrayRow;

In the section below, you apparently make a query:

$result = $obj->getAll();

In the getAll() method, no parameter is used, so the result is always the same.

Follows a model of how you can adjust your function:

private function geraExcel($parametros) { 

$this->load->helper('xls');     

$obj = new Viagem(); 
$result = $obj->metodoPesquisaPorParametro($parametros); 



if (isset($result) && count($result) > 0) { 

    foreach ($result['rows'] as $v) { 

    $arr[] = array( 

    utf8_decode($v->getUsuario()->getNome()), 
        $v->getFormaPagamento()->getNome(), 
        $v->getStatus()->getNome(),
        $v->getDistancia(), 
        $v->getValor(), 
        $v->getValorExtra(), 
        dataHoraBr($v->getDataCriado()), 
        $v->getEntregador()->getNome() 

    );}     
}     
    array_to_xls($arr, 'financeiro'.date("m-d-y").'.xls'); 
}

Answer 4

Change according to your needs.It seems to me the easiest way to do.

<?php 
    include_once('conetar.php');

    $query = "[Introduz o select que desejar]";

     $result = mysqli_query($conn, $query);
    $contar = mysqli_num_rows($result);//para contar o numero de linhas 

    //tabela HTML
    $html=[];
    for($i=0;$i<1;$i++){   
    $html[$i] = "";
        $html[$i] .= "<table>";
        $html[$i] .= "<tr>";
        $html[$i] .= "<td><b>Coluna1</b></td>";
        $html[$i] .= "<td><b>Coluna2</b></td>";
        $html[$i] .= "<td><b>Coluna3</b></td>";
        $html[$i] .= "</tr>";
        $html[$i] .= "</table>";
    }

    $i = 1;
    while($ret = mysqli_fetch_array($result)){
        error_reporting(E_ERROR | E_PARSE);
        $retorno_resultado1= $ret['Coluna1'];
        $retorno_resultado2= $ret['Coluna2'];
        $retorno_resultado3= $ret['Coluna3'];
        $html[$i] .= "<table>";
        $html[$i] .= "<tr>";
        $html[$i] .= "<td>".$retorno_resultado1."</td>";
        $html[$i] .= "<td>".$retorno_resultado2."</td>";
        $html[$i] .= "<td>".$retorno_resultado3."</td>";
        $html[$i] .= "</tr>";
        $html[$i] .= "</table>";
        $i++;
    }

    $arquivo = 'export.xls';
    header ("Expires: Mon, 26 Jul 2100 05:00:00 GMT");
    header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
    header ("Cache-Control: no-cache, must-revalidate");
    header ("Pragma: no-cache");
    header ("Content-type: application/x-msexcel");
    header ("Content-Disposition: attachment; filename={$arquivo}" );
    header ("Content-Description: PHP Generated Data" );

    for($i=0;$i<=$contar;$i++){  
        echo $html[$i];
    }

     ?>