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);
?>
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);
}
}
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.
