Writing to a spreadsheet

[alanl1]

Hi Professionals.

 

I am trying to write records from the DB into a spreadsheet, but when I open this there is only the titles, could I be missing something here.

 

I have tested my query in the backend database and it works fine.

 

Here is my code so far.

 

 

<?php
 
// Connect and query the database
 include("header.php");
  include("footer.php");
  include("ConnectDB.php");
$sql = "SELECT productdescription, activeqty FROM SARTable ORDER BY productdescription";
$stmt = sqlsrv_query( $conn, $sql);
/*  Check the database connection is good */
/*******************************************************/
 if( $conn === false) {
     die( print_r( sqlsrv_errors(), true) );
 }
 
 /*  Prepare the Create table statement. */
/*******************************************************/
 if(!$stmt = sqlsrv_prepare( $conn, $sql))
  {
   die( print_r( sqlsrv_errors(), true));
  }
  /*  Execute the Create table statement. */
 if( !sqlsrv_execute( $stmt))
  {
       die( print_r( sqlsrv_errors(), true));   
  }
// Pick a filename and destination directory for the file
// Remember that the folder where you want to write the file has to be writable
$newfilename = "Test.csv";
$filename = "C:\\inetpub\\wwwroot\\cleansed\\".$newfilename;
echo $filename;
// Actually create the file
// The w+ parameter will wipe out and overwrite any existing file with the same name
$handle = fopen($filename, 'w+');
// Write the spreadsheet column titles / labels
fputcsv($handle, array('productdescription','activeqty'));
 
// Write all the user records to the spreadsheet
foreach($stmt as $row)
{
    fputcsv($handle, array($row['productdescription'], $row['activeqty']));
}
 
// Finish writing the file
fclose($handle);
 sqlsrv_free_stmt( $stmt);
?>
 

[requinix]

if(!$stmt = sqlsrv_prepare( $conn, $sql))

foreach($stmt as $row)

$stmt is a resource. You cannot foreach over it. You have to fetch from that resource, like

while($row = sqlsrv_fetch_array($stmt))

[alanl1]

fantastic that works, just one thing though my output in the csv file is like so.

 

Column1, Column2, column1 data, column2 data,column1 data2ndrow, column2data2ndrow and so on
 
I would prefer it to be
 
Column1, Column2
column1 data, column2 data
column1 data2ndrow,column2data2ndrow
and so on

Is there a way to put in a new line character

[requinix]

I don't see how it would be doing that. fputcsv() adds newlines for you.

 

What are you using to view the file?