php mysql export excel

Question

This code is working without problem if page doesn't contain any other content. When i add a html form, it gives only html content to excel. I need to use that form to customize query. How can i make this code working with html content?

<?php
if (isset($_POST['excel'])){
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("dbname") or die(mysql_error());
mysql_query("SET NAMES 'LATIN5'");
mysql_query("SET character_set_connection = 'LATIN5'");
mysql_query("SET character_set_client = 'LATIN5'");
mysql_query("SET character_set_results = 'LATIN5'");

$select = "query here";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );


$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data"; }

?>

It seems you are generating a CSV file? A CSV file can't contain HTML content. — Pekka
– Pekka, Commented Feb 17, 2013 at 11:51
Yes but it is getting html content as text. For example i adding <form></form> tag before this code, it is exporting a csv with one field that contains "<form></form>" text. — milesh
– milesh, Commented Feb 17, 2013 at 12:02

thaJeztah · Accepted Answer · 2013-02-17 12:29:00Z

2

As mentioned in the comments, combining HTML and CSV is not possible

However, it seems you're only outputing CSV if a form is posted ($_POST['excel']). You should 'stop' the PHP script after outputting the CSV, like this:

if($_POST['excel']) {
    // query and output CSV 
    //...


    exit(); //done outputting csv
}

// regular HTML form here; will only be output if NO form is sent

With this approach, your script will:

output a HTML form if visited without sending a form
output CSV if the form has been submitted.

However, you may re-consider putting both functionality in the same PHP script. It's probably a better approach to output the CSV in another script (e.g. download_csv.php) and set the 'action' of your form to that location;

<form action='download_csv.php'>

[update] To output HTML or CSV depending on the form-input, you may consider this:

if($_POST['excel']) {
    // query and output CSV 
    include 'csv-exporter.php';
    exit(); //done outputting csv
} else if ($_POST['html']) {
    // query and output HTML
    include 'html-exporter.php';
    exit(); //done outputting html
}

However, this way the 'query' part will be duplicated. In which case, it's probably best to separate the querying and 'formatting' parts. You can do so by putting them in a function and use the result of that, something like:

if($_POST['excel']) {
    outputCSV(getResultsFromDatabase());
    exit();
} else if ($_POST['html']{
    outputHtmlList(getResultsFromDatabase());
    exit();
}

Those functions can be put in separate files and included using the 'include' functionality of PHP.

Both are just for illustration all purposes, and a bit off-topic for your original question.

edited Feb 17, 2013 at 12:29

answered Feb 17, 2013 at 11:58

thaJeztah

29.3k9 gold badges77 silver badges92 bronze badges

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

4 Comments

milesh Over a year ago

It worked with exit();. And it s rendering html after form sent. Page will have a form with two submit; one for output csv, one for output html table of query.

thaJeztah Over a year ago

Glad it worked! Just wanted to point you to some other options. Keeping logic separated often keeps your code cleaner and easier to maintain :)

milesh Over a year ago

Is sending same form to different pages according to clicked submit button possible?

thaJeztah Over a year ago

Hm, you may be able to send a redirect when receiving the form, or, use separate files for generating different types of output and 'include' those. I'll add some info to my answer

Collectives™ on Stack Overflow

php mysql export excel

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related