The forums ran from 2008-2020 and are now closed and viewable here as an archive.

Home Forums Back End PHP: Exporting Data to Excel

  • This topic is empty.
Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
  • #172508

    i want to export data in to excel. i have the code but it is downloading empty file.
    please help me to down load with data from database
    here is the code

        $con = mysql_connect("localhost","root","");
        $sql = "select * from issues";
        $qur = mysql_query($sql);
      function cleanData(&$str)
        $str = preg_replace("/\t/", "\\t", $str);
        $str = preg_replace("/\r?\n/", "\\n", $str);
        if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
        $filename = "AuditReport.xls"; 
        header("Content-Type: text/plain");
        header("Content-Disposition: attachment; filename=\"$filename\""); 
        header("Content-Type: application/;charset=UTF-16LE");
    $flag = false;
    while($row = mysql_fetch_assoc($qur)) 
        echo "Hello";
            echo implode("\t", array_keys($row)) . "\r\n";
            $flag = true;
            array_walk($row, 'cleanData');
            echo implode("\t", array_values($row)) . "\r\n";

    @chriscoyier, your “Securi Website Firewall” is misinterpreting my post as an SQL injection attack and blocking it. I think it’s because I used the words “select”, “into”, and “outfile” consecutively.

    If the output is empty, the two most likely reasons are (1) the query returned no results, or (2) there was an error (e.g., if mysql_query failed, then $qur would be false and trying to fetch from it would cause a fatal error; your browser would have already received the download headers, and, if errors were configured not to display, you would end up with a “blank” file).

    Before you spend too much time on this, however, you should consider using a select ... into out file query.*

    * “out file” should be one word, i.e., “outfile.” the css-tricks website is giving me a hard time about this. go read the mysql manual page for more info.

    Before doing that, if you are doing this on a local machine (i.e., you don’t need to do it over a web server), you should use mysqldump from the cli.

    Chris Coyier

    your “Securi Website Firewall” is misinterpreting my post as an SQL injection attack and blocking it.

    That’s a bummer that it blocked you, but it’s been a good thing to have in place for the most part. If it happens again, maybe just dump it in a Gist and paste link to it.


    Good morning,

    i could complete my task.
    thanks for help.

    I have saved the data from database in to excel using PHP code. now i want to send that file from the specific folder location which i am giving in code.
    i have code. code get the same file from location even send a mail as well but file dont have content. however the file resides in audit folder have the data.

    here is the code.

    please help me out.

    <title>Sending attachment using PHP</title>
    $to = “[email protected]”;
    $subject = “Audit_report”;
    $message = “Hello All, PFA audit report .”;
    # Open a file
    $file = fopen( “G:/Smart solution/Audit/Audit_report.csv”, “r” );
    if( $file == false )
    echo “Error in opening file”;
    # Read the file into a variable
    $size = filesize(“G:/Smart solution/Audit/Audit_report.csv”);
    $content = fread( $file, $size);

    # encode the data for safe transit
    # and insert \r\n after every 76 chars.
    $encoded_content = chunk_split( base64_encode($content));

    # Get a random 32 bit number using time() as seed.
    $num = md5( time() );

    # Define the main headers.
    $header = “From:[email protected]\r\n”;
    $header .= “MIME-Version: 1.0\r\n”;
    $header .= “Content-Type: multipart/mixed; “;
    $header .= “boundary=$num\r\n”;
    $header .= “–$num\r\n”;

    # Define the message section
    $header .= “Content-Type: text/plain\r\n”;
    $header .= “Content-Transfer-Encoding:8bit\r\n\n”;
    $header .= “$message\r\n”;
    $header .= “–$num\r\n”;

    ini_set("sendmail_from","[email protected]");
    ini_set("sendmail_path", "C:\wamp\bin\sendmail.exe -t");    

    # Define the attachment section
    $header .= “Content-Type: multipart/mixed; “;
    $header .= “name=\”test.csv\”\r\n”;
    $header .= “Content-Transfer-Encoding:base64\r\n”;
    $header .= “Content-Disposition:attachment; “;
    $header .= “filename=\”Audit_report.csv\”\r\n\n”;
    $header .= “$encoded_content\r\n”;
    $header .= “–$num–“;

    # Send email now
    $retval = mail ( $to,$subject,””,$header);
    if( $retval == true )
    echo “Message sent successfully…”;
    echo “Message could not be sent…”;
    <?php header(‘location:issues.php’);

    Thanks in advance

Viewing 4 posts - 1 through 4 (of 4 total)
  • The forum ‘Back End’ is closed to new topics and replies.