Why do I have a blank row at the top of this CSV export and how can I erase it?

Question

 $conn = mysql_connect($entr,$user,$cred);          //Set db connection
 mysql_select_db("webshops",$conn);
 $result = mysql_query('CALL storedproc');    


 $num_column = mysql_num_fields($result);                 //Get number of columns, for each column header get name and give them individual columns.    
 for($i=0;$i<$num_column;$i++) {
     $csv_header .= '"' . mysql_field_name($result,$i) . '";';
 }  
 $csv_header .= "\n";

 $csv_row ='';                               
 while($row = mysql_fetch_row($result)) {
     for($i=0;$i<$num_column;$i++) {
         $csv_row .= '"' . $row[$i] . '";';
     }
     $csv_row .= "\n";
 }
 ob_end_clean();

 header('Content-type: application/csv');
 header('Content-Disposition: attachment; filename=OrderExport.csv');
 echo $csv_header . $csv_row;
 exit;

Once this CSV is downloaded and opened in Excel, the first row is always blank. The headers begin on the 2nd row. How can I prevent this from happening and have the headers begin on the first? Many Thanks.

**Danger**: You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) which has been **removed** entirely from the latest version of PHP. You should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). — Quentin, Dec 13 '17 at 14:50
When will people stop trying to write their own bad csv code and start using PHP's built-in [fputcsv()](http://php.net/manual/en/function.fputcsv.php) function — Mark Baker, Dec 13 '17 at 14:51
@MarkBaker While I would never write my own csv paring code for production use, it does make for a good learning opportunity — William Perron, Dec 13 '17 at 14:53

gnusey · Answer 1 · 2018-01-04T10:44:58.160

You'd be better off using fputcsv http://php.net/manual/en/function.fputcsv.php. I've changed your code below but not I'm able to test it, so it may not work perfectly, but hopefully it will give you an idea. Also, as the others have said in the comments, you shouldn't be using mysql, as that API is obsolete. You should be using mysqli instead.

 $conn = mysql_connect($entr,$user,$cred);          
 mysql_select_db("webshops",$conn);
 $result = mysql_query('CALL storedproc');    
 $num_column = mysql_num_fields($result);                    

 $fp = fopen('mycsv.csv', 'w');
 $csv_header = array();

 for($i=0;$i<$num_column;$i++) {
     array_push($csv_header, mysql_field_name($result,$i));
 }  

 fputcsv($fp, $csv_header);

 while($row = mysql_fetch_row($result)) {
     $csv_row = array();          
     for($i=0;$i<$num_column;$i++) {
         array_push($csv_row, $row[i]);
     }
     fputcsv($fp, $csv_row);
 }
 ob_end_clean();

 header('Content-type: application/csv');
 header('Content-Disposition: attachment; filename=OrderExport.csv');
 fclose($fp);
 exit;

Professor Abronsius · Answer 2 · 2017-12-13T17:29:39.060

To output the results from the stored procedure in a more reliable and correct manner use mysqli or PDO to handle the database operations and the built-in fputcsv to format the data specifically for csv output.

<?php

    /* File will be called this when it is downloaded */        
    $filename = 'mycsvfile.csv';

    /* create db connection using mysqli ( or PDO ) but not mysql */    
    $dbhost =   'localhost';
    $dbuser =   'root'; 
    $dbpwd  =   'xxx'; 
    $dbname =   'xxx';
    $conn   =   new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );

    /* create sql query */
    $sql='call `storedproc`();';

    /* run the query */
    $result=$conn->query( $sql );
    if( $result ){

        /* store column names in this array - used to create csv headers */
        $cols=array();

        /* iterate through column names and add to array $cols */
        array_walk( $result->fetch_fields(), function( $value, $key, $cols ){
            $cols[]=$value->name;
        },&$cols );

        /* create an output stream to write csv to */
        $stream=fopen( 'php://output', 'w' );
        /* add column headers */
        fputcsv( $stream, $cols );

        /* iterate through the recordset returned by stored procedure */
        while( $rs=$result->fetch_assoc() ){
            fputcsv( $stream, array_values( $rs ) );
        }
        /* capture stream contents */
        $data=stream_get_contents( $stream );

        /* close the output stream */
        fclose( $stream );

        /* send headers and write content to browser */
        header('Content-Type: application/csv');
        header('Content-Disposition: attachment; filename=' . $filename );
        exit( $data );
    }
?>

Why do I have a blank row at the top of this CSV export and how can I erase it?

2 Answers2