export excel with php and sql server

Question

export excel with php and sql server

Navigation

#1 by (2 votes)

1

Hi, I would like to export php excel with sql server: The error that is coming to me is this:

sqlsrv_query () expects parameter 1 to be resource, string given in

sqlsrv_fetch_array () expects parameter 1 to be resource, boolean given in

This is what I have done.

        <?php


$serverName = '172.16.1.124';
$uid = 'sa';
$pwd = 'SA123456789*';
$databaseName = 'DBRevisoria';
$connectionInfo = array( 'UID'=>$uid,'PWD'=>$pwd,'Database'=>$databaseName);



$conn = sqlsrv_connect($serverName,$connectionInfo);

$sql="select cast(fechadesembolso as varchar(250)) as fechadesembolso ,cast(fecharevision as varchar(250)) as fecharevision ,NumeroCredito,Zona,Equipo,jefeoperacion,Catalogo1,DescripcionCatalogo1,Catalogo2,DescripcionCatalogo2,Catalogo3,DescripcionCatalogo3,Catalogo4,DescripcionCatalogo4,comentario from reporteexcel_Gerente";




 header("Content-Type: application/xls"); 
   header("Content-Disposition: attachment; filename=fulldetails-".$ts.".xls");  
   header("Pragma: no-cache"); 
   header("Expires: 0");
   header("Content-Transfer-Encoding: binary ");

   $sep = "\t"; //tabbed character

  $result =sqlsrv_query($sql,$conn);

  print("\n");   
while ($finfo = sqlsrv_fetch_array($result))
  {
    $schema_insert = "";
    for($j = 0; $j < sqlsrv_num_fields($result); $j++)
    {
      if(!isset($row[$j]))
        $schema_insert .= "NULL".$sep;
      elseif ($row[$j] != "")
        $schema_insert .= "$row[$j]".$sep;
      else
        $schema_insert .= "".$sep;
    }
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    $schema_insert .= "\t";
    print(trim($schema_insert));
    print "\n";
  }
  ?>

sql php sql-server

asked by PieroDev 30.06.2018 в 16:48

source

1 answer

error 'EditItem' is not allowed for this view when applying a filter to a WPF data grid Intent android error

score 2 · Accepted Answer

The error:

sqlsrv_query() expects parameter 1 to be resource, string given in

is because you are passing the parameters upside down here:

$result =sqlsrv_query($sql,$conn);

If you check the documentation you'll see that it indicates that the connection must first be passed and then the query:

$result =sqlsrv_query($conn,$sql);

Then, to save the result in an Excel, you can do the following:

$arrResult=array();
while( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC) ) 
{ 
    $arrResult[]=$row; 
} 

$out = fopen("php://output", 'w'); 
/*Encabezados*/
$headers= array_keys($arrResult[0]); 
fputcsv($out,$headers,"\t"); 

foreach ($arrResult as $row) 
{ 
    /*Filas con datos*/
    fputcsv($out, $row,"\t"); 
} 
fclose($out);

Here's how the code should look, so there's no confusion:

<?php


$serverName = '172.16.1.124';
$uid = 'sa';
$pwd = 'SA123456789*';
$databaseName = 'DBRevisoria';
$connectionInfo = array( 'UID'=>$uid,'PWD'=>$pwd,'Database'=>$databaseName);    
$conn = sqlsrv_connect($serverName,$connectionInfo);

$sql="SELECT 
                cast(fechadesembolso as varchar(250)) as fechadesembolso,
                cast(fecharevision as varchar(250)) as fecharevision,
                NumeroCredito,
                Zona,
                Equipo,
                jefeoperacion,
                Catalogo1,
                DescripcionCatalogo1,
                Catalogo2,
                DescripcionCatalogo2,
                Catalogo3,
                DescripcionCatalogo3,
                Catalogo4,
                DescripcionCatalogo4,
                comentario 
        FROM
             reporteexcel_Gerente";

header("Content-Type: application/xls"); 
header("Content-Disposition: attachment; filename=fulldetails-".$ts.".xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
header("Content-Transfer-Encoding: binary ");

$sep = "\t"; //tabbed character

$result =sqlsrv_query($conn,$sql);

$arrResult=array();
while( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC) ) 
{ 
    $arrResult[]=$row; 
} 

$out = fopen("php://output", 'w'); 
/*Encabezados*/
$headers= array_keys($arrResult[0]); 
fputcsv($out,$headers,$sep); 

foreach ($arrResult as $row) 
{ 
    /*Filas con datos*/
    fputcsv($out, $row,$sep); 
} 
fclose($out);

?>