CODEWITHSUNDEEP
phpmysqldatabaseexcelinput
nicole101
nicole101

Reputation: 187

Export query from user input to Excel - only HTML parts are visible

What I'm trying to do here is to let the user input DATEFROM and DATETO. Then when the user click the button extract to Excel file, the query will be executed where it will select all columns from the table where BETWEEN DATEFROM and DATETO. At the same time, it will download an excel file where the output should be there.

I was able to get the right query and output it in the html but I'm having problems in exporting it to Excel. Here are the problems:

  1. Exporting to Excel is working even though I don't click the button. When I run the program in localhost, it automatically export to Excel file.

  2. What is inside the Excel file are only the HTML part only.

        <form method="POST" action="">
    DATE FROM: <input type="date" name="datefrom"> TO: <input type="date" name="dateto"> <input type="submit" value="Extract excel file" name="extract"></input>
    </form>

    <?php   
    header("Content-type: text/csv; charset=UTF-8");
    header('Content-Disposition: attachment; filename=Export.csv');

    if(isset($_POST['extract'])){
        $con = mysql_connect("localhost", "root");
        if(!$con){
            echo "Error connection";
                }

        $select_db = mysql_select_db('sample', $con);
        if(!$select_db){
            echo "Error to select database";
                }
        mysql_set_charset("utf8", $con);

      $datefrom = $_POST['datefrom'];
      $dateto = $_POST['dateto'];

    $myquery = mysql_query("SELECT * FROM biometrics WHERE date_created BETWEEN '" . $datefrom . "' AND '" . $dateto . "' ORDER BY empno");

    //While loop to fetch the records
    $contents = "id_biometrics,empno,date_created,time_created,status\n";
    while($row = mysql_fetch_array($myquery))
    {
        $contents.=$row['id_biometrics'].",";
        $contents.=$row['empno'].",";
        $contents.=$row['date_created'].",";
        $contents.=$row['time_created'].",";
        $contents.=$row['status']."\n";

    }

    $contents_final = chr(255).chr(254).mb_convert_encoding($contents, "UTF-16LE","UTF-8");
    print $contents_final;
    }
    ?>
</body>

Upvotes: 1

Views: 1011

Answers (2)

Zarathuztra
Zarathuztra

Reputation: 3251

The reason the CSV file is being downloaded immediately is because of those headers you're setting as soon as the page loads. 

    header("Content-type: text/csv; charset=UTF-8");
    header('Content-Disposition: attachment; filename=Export.csv');

That's telling the browser to take what's coming and save it to a csv file named Export.csv. However, you've already output data in the response body (your HTML) so that's what the headers are going to send as data. When you use the PHP header() function it MUST be run before any output goes back through the pipe.

So, how do you fix this? At the very top of your php file, before that's where you want to put something to the effect of:

<?php
if(!empty($_POST)){
    header("Content-type: text/csv; charset=UTF-8");
    header('Content-Disposition: attachment; filename=Export.csv');
}

Then start the rest of your HTML elements and PHP code, or tell your script to end and allow the contents to be sent as the file. blahblahblah

So the takeaway here is that headers must be set before any content is sent back whatsoever, and that goes for anything you could ever want to do. Here are the full docs on php headers if you need a quick reference :)

I'll use the second part of my answer for two nitpicks: 1) The mysql_* set of functions is deprecated, switch to using mysqli_ as soon as possible!

2) Your query is open to SQL injection attacks, gotta scrub scrub scrub! And by that I mean use prepared statements

Upvotes: 1

Rob M.
Rob M.

Reputation: 36511

These lines:

header("Content-type: text/csv; charset=UTF-8");
header('Content-Disposition: attachment; filename=Export.csv');

Are causing the force download. They should be inside your if(isset($_POST....

Additionally, your PHP script needs to be at the very top of this file or this won't work. Ideally, you will have the following at the end inside of your if(isset($_POST

header("Content-type: text/csv; charset=UTF-8");
header('Content-Disposition: attachment; filename=Export.csv');
print $contents_final;
exit;

Upvotes: 1

Related Questions