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.

6
  • 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); Commented Apr 2, 2016 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. Commented Apr 2, 2016 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 :) Commented Apr 2, 2016 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. Commented Apr 2, 2016 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 (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! :) Commented Apr 2, 2016 at 16:00

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.