Exporting data from sql with php to excel

Question

I am trying to export sql to csv with php and this is what I managed to do. It works well without 2 things: 1. Before the sql data that is outputted in the csv I get the HTML code of the current page for some reason. 2. How can I change the table header rows? Want to rename the table header column in the csv.

$result = $db->query("SELECT * FROM user_history WHERE member_id = '$user_id'");
$num_fields = $result->field_count;
$headers = array();
$headers[] = "Number";
for($i = 0; $i < $num_fields; $i++ ) {
    $Obj = $result->fetch_field_direct($i);
    $headers[] = $Obj->name."\t";
}
$current_date = date("y/m/d");
$filename = "MyFileName" . $current_date . ".csv";
$fp = fopen('php://output', 'w');
if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename='.$filename);
    header('Pragma: no-cache');
    header('Expires: 0');
    echo "Title of Your CSV File\n\n";
    // Write mysql headers to csv
    fputcsv($fp, $headers);
    $row_tally = 0;
    // Write mysql rows to csv
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
    $row_tally = $row_tally + 1;
    echo $row_tally.",";
        fputcsv($fp, array_values($row));
    }
    die;
}

So what happens if you do $header .= $result->fetch_field_direct($i)."\t"; as shown in that question? While changing all \\n to \n and "\\t" to "\t" and "\\r" to "\r" — Funk Forty Niner
– Funk Forty Niner, Commented Sep 22, 2014 at 17:55

Paul Carlton · Accepted Answer · 2014-09-22 18:12:09Z

1

fetch_field_direct returns an object with no __toString() method. According to this you need to change the code:

$header .= $result->fetch_field_direct($i)."\\t";

To:

$Obj = $result->fetch_field_direct($i);
$header .= $Obj->name."\t";

Secondly if you print "\\t" then you will literally get \t. print "\t"; will give you the tab character.

answered Sep 22, 2014 at 18:12

Paul Carlton

3,0032 gold badges30 silver badges49 bronze badges

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

4 Comments

Adrian Over a year ago

Changed accordingly, but now I am getting Notice: Undefined variable: header

Adrian Over a year ago

Managed to fix that by declaring header and data before the while, and a download box with reports.xls appear, but the contents are not ok, it prints out the contents of the current page and the data from the query but in a non-readable style. It;s like a coding screen-shoot.

Paul Carlton Over a year ago

Make sure you concatenate "\t" and "\n" and not "\\t" and "\\n".

Adrian Over a year ago

Hey, I updated, tried with another script but encountered the problems updated 1 and 2.

Collectives™ on Stack Overflow

Exporting data from sql with php to excel

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related