douzhantao2857
2014-08-25 12:38
浏览 66
已采纳

使用php从mysql数据库导出csv文件

<?php
    include("includes/config.php"); 
    if ($_POST['frmCsv']) {
        $strSelect = doSelectCsv();
        header( 'Content-Type: text/csv' );
        header( 'Content-Disposition: attachment;filename=export.csv' );
        $strRow = mysql_fetch_assoc( $strSelect );
        printArray($strRow);
        if ( $strRow ) {
            outputcsv( array_keys( $strRow ) );
        }
        while ( $strRow ) {
            outputcsv( $strRow ); 
        }
    }

    function doSelectCsv()
    {
        $strSql = "SELECT * FROM tbl_member";
        $strResult = SelectQry($strSql);
    }

    function outputcsv( $fields )
    {
        $separator = '';
        foreach ( $fields as $field ) {
            echo $separator . $field;
            $separator = ',';     
        }
    }
?>

This is my code to export a csv file from the database. but it could not select the datas. select query did not works correctly. please help me.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • drxkx6149 2014-08-26 08:33
    已采纳

    Just use it brother :)

    <?php
    
    if($_POST["download"]){ 
            $output="";
            $line_termineted="
    ";
    
            if( $_POST["download"] =="CSV") $field_termineted=","; else $field_termineted="\t"; 
                $enclosed='"';
                $escaped="\\";
    
                $export_schema="SR No".$field_termineted."Student ID".$field_termineted."First Name".$field_termineted."Middle Name".$field_termineted."Last Name";
                $dataQuery=mysql_query("select * from sof_student ");
                $output.=$export_schema;
                $p=0;
                while($data=mysql_fetch_array($dataQuery)) {
                 $p++;
                    $output.= $line_termineted.$p.$field_termineted;
                    $output.=$enclosed.$data["id"].$enclosed.$field_termineted;
                    $output.=$enclosed.$data["first_name"].$enclosed.$field_termineted;
                    $output.=$enclosed.$data["middle_name"].$enclosed.$field_termineted;
                    $output.=$enclosed.$data["last_name"].$enclosed.$field_termineted;
                  }
    
        header("Content-Description: File Transfer");
       if( $_POST["download"] =="CSV"){
            header("Content-Type: application/csv");
            header("Content-Disposition: attachment; filename=report".date("d_m_Y_H_i_s").".csv");
        } else {
            header("Content-Type: application/vnd.ms-excel");
            header("Content-disposition: attachment; filename=report".date("d_m_Y_H_i_s").".xls");
        }
    
        header("Content-Transfer-Encoding: binary");
        header("Expires: 0");
        header("Cache-Control: must-revalidate");
        header("Pragma: public");
        header("Content-Length: ".strlen($output));
        ob_clean();
        flush();
        echo $output;
        exit;
    }
     ?>
    
    已采纳该答案
    打赏 评论
  • du5910 2014-08-25 12:43

    One problem is that doSelectCsv() doesn't return anything, but you have the following code:

    $strSelect = doSelectCsv();
    

    and then below:

    $strRow = mysql_fetch_assoc( $strSelect );
    

    Without any knowledge about what SelectQry() does it's hard to really suggest a solution, but doSelectCsv() has to return a value to be used later on. Perhaps the following will suffice:

    function doSelectCsv()
    {
        $strSql = "SELECT * FROM tbl_member";
        $strResult = SelectQry($strSql);
        return $strResult; // added this line
    }
    

    Note: The MySQL family of PHP is deprecated and support thereof will disappear. Please look into PDO or Mysqli.

    However, if we look at the MySQL code there is a problem with the following:

        $strRow = mysql_fetch_assoc( $strSelect );
        printArray($strRow);
        if ( $strRow ) {
            outputcsv( array_keys( $strRow ) );
        }
        while ( $strRow ) {
            outputcsv( $strRow ); 
        }
    

    You're assigning the first row to $strRow with mysql_fetch_assoc() but then you never fetch the other rows. I believe the above could should be something like this instead:

        $strRow = mysql_fetch_assoc( $strSelect );
        printArray($strRow);
        if ( $strRow ) {
            outputcsv( array_keys( $strRow ) );
            while ($strRow = mysql_fetch_assoc($strSelect)) {
                outputcsv( $strRow ); 
            }
        }
    

    This will send the array keys to outputcsv() for the first row, and then we loop through all the other rows inside that if block to send the rest of the data to outputCsv. But again, for a serious application, please do look in the newer extensions that PHP offers, because the mysql_* functions will eventually disappear.

    Also, as someone hinted in the comments, PHP has a function write CSV data, namely fputcsv(). This will take care of any special cases (what if a data item has a comma in it?).

    打赏 评论

相关推荐 更多相似问题