export a csv file from the mysql database using php

Question

<?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.

what does SelectQry() do? Write return on doSelectCsv() also check fputcsv — xkeshav
– xkeshav, Commented Aug 25, 2014 at 12:39
Rather than write your own CSV writer, use PHP's built-in fputcsv() function — Mark Baker
– Mark Baker, Commented Aug 25, 2014 at 12:44
Read the red box on each of the mysql_* pages: the extension is deprecated. Don't use it anymore. Use the replacement(s): PDO or mysqli_* (where the i stands for improved) — Elias Van Ootegem
– Elias Van Ootegem, Commented Aug 25, 2014 at 12:45
i put select query to select the datas in the database. i put return on the function, but it cannot works sir. — hari krishnan
– hari krishnan, Commented Aug 25, 2014 at 12:45

Community · Accepted Answer · 2023-11-17 19:26:01Z

2

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

edited Nov 17, 2023 at 19:26

CommunityBot

11 silver badge

answered Aug 25, 2014 at 12:43

chwcz7F

2,1901 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

hari krishnan Over a year ago

select query worked correctly. but there is no data will be fetched on mysql_fetch_assoc( $strSelect ) function.

chwcz7F Over a year ago

@harikrishnan I've added to my answer to address the MySQL problem.

hari krishnan Over a year ago

it selects my whole coding in the file. but it could not select the datas in the database

hari krishnan Over a year ago

please give some alternations in the coding to export the file.

hari krishnan Over a year ago

how to i use fputcsv() in this coding?... please help me.

Syed Ibrahim · Accepted Answer · 2014-08-26 08:33:42Z

Just use it brother :)

<?php

if($_POST["download"]){ 
        $output="";
        $line_termineted="\n";

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

Collectives™ on Stack Overflow

export a csv file from the mysql database using php

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related