0

I have identical tables in MySQL and Microsoft SQL Server:

  • ID = 1
  • Column1 = Hello world
  • Column2 = How are you today

I am able to use the PHP fputcsv function to display the data from MySQL in a Web browser. To isloate the cause of the problem I am facing, I have commented out the header, to force the fputcsv function to display the data in the Web browser instead of the CSV file. The following PHP will produce the following output in a Web browser:

<?php
//header("Content-Type: text/csv;charset=utf-8");

$con = new mysqli('domain','username','password','database');
$select = "select * from tablename";
$select_query = mysqli_query($con, $select);
$fp= fopen('php://output', 'w');

while ($row = mysqli_fetch_array($select_query,MYSQL_ASSOC)) {
fputcsv($fp, array_values($row));
}

fclose($fp);
?>

enter image description here

When using similar PHP to display data in a Microsoft SQL Server table using the fputcsv function, the Web browser display a 500 error such as Page cannot be displayed.

<?php
//header("Content-Type: text/csv;charset=utf-8");

$ServerName = "domain";
$ConnectionString = array("Database"=>"Database", "UID"=>"username", "PWD"=>"password");
$con = sqlsrv_connect($ServerName, $ConnectionString);
$select = "select * from tablename";
$select_query = sqlsrv_query($con, $select);
$fp= fopen('php://output', 'w');

while ($row = sqlsrv_fetch_array($select_query, SQLSRV_FETCH_ASSOC)) {
fputcsv($fp, array_values($row));
}

fclose($fp);
?>

Inside the while loop, if I replace fputcsv($fp, array_values($row)); with echo $row['column1'];, the 500 error no longer appears, and Hello world is displayed. This tells me the while loop is able to fetch the array, and there is some issue with this line of code when using SQL Server: 

fputcsv($fp, array_values($row));

I noticed this post on StackOverflow uses array_keys and a foreach loop instead of array_values. When I use array_keys and a foreach loop, the SQL Server table columns are displayed.

while ($row = sqlsrv_fetch_array($select_query, SQLSRV_FETCH_ASSOC)) {
  fputcsv($fp, array_values($row));
    foreach ($row as $data) {
       fputcsv($fp, $data);
    }
}

enter image description here

I noticed the first answer at this post on StackOverflow also uses array_keys instead of array_values. This gives me assurance that array_key should be used with SQL Server.

I am not certain why the Web browser is displaying the column names instead of the data when using array_keys and SQL Server. If there are any tips or suggestions or thoughts that might lead me to my next troubleshooting step here, I will definitely be appreciative.

Community
  • 1
  • 1
JeremyCanfield
  • 633
  • 11
  • 24
  • 1
    A 500 error in PHP means you must check your web server's error log for details. Always when developing and testing code, enable PHP's error display. At the top of your script `error_reporting(E_ALL); ini_set('display_errors', 1);` – Michael Berkowski Apr 02 '16 at 14:57
  • 1
    The issue is probably not with any array function. More likely, something has gone wrong writing to the output buffer with `$fp`. There should be more info in the error report. By the way, it displays column names from `array_keys()` because `$row` is an associative array and the function returns just an array of the associative keys. – Michael Berkowski Apr 02 '16 at 14:59
  • Thank you very much Michael. Your tip has indeed helped me to take the next step in troubleshooting this issue. When adding error reporting and display errors, an error was detected with the foreach loop. "Warning: Invalid argument supplied for foreach() in C:\path\to\stage.php on line 17." Thank you also for the explanation as to why the column names are displaying. I really appreciate the tips to guide me to my next steps here. I will respond once I identify what the cause of my unique experience is :) – JeremyCanfield Apr 02 '16 at 15:18
  • 1
    The foreach in your last posted example doesn't really make much sense, and is likely not doing anything helpful because it is causing a scalar (non-array value) to be passed to the inner `fputcsv()`, which is an error. If the error is actually pointing to a _different_ foreach loop in your code, please post that relevant code. – Michael Berkowski Apr 02 '16 at 15:31
  • I also found the foreach loop to be a tad confusing. MySQL did not require a foreach loop. I decided to give the foreach loop a try, because the following post on StackOverflow has an answer with over 150 votes that the answer was useful, and the answer had a foreach loop, so I started to suspect that perhaps a foreach loop was necessary (http://stackoverflow.com/questions/4249432/export-to-csv-via-php). I have actually been bouncing between using a foreach loop and not using a foreach loop, in my troubleshooting :). Thank you for sharing your thoughts on the foreach loop! :) – JeremyCanfield Apr 02 '16 at 16:00
  • Ah, I see. In that context `foreach ($array as $row) {` you should assume that `$array` was a 2-dimensional array and therefore filling the same role as your `while` loop for fetching. Each iteration of both types of loops produces a 1D array, passed to fputcsv(), so stick with your `while` loop and remove the foreach, then check reported errors. – Michael Berkowski Apr 02 '16 at 16:05

0 Answers0