We help IT Professionals succeed at work.

how to export to excel from mysql database using php

BR
BR asked
on
Dear Experts,
I use PHP 7 and mysql database.
I searched on the web how to export data to excel , I found many old codes with mysql_connect...
I need to use mysqli not mysql

Do you recomend me some working code?
Comment
Watch Question

Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi!

The simplest way to export data to excel is to export to csv using mysqli like this

<?php 
$con = new mysqli($host, $user, $password, $database);
 
// Check connection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}
$result = mysqli_query($con, 'SELECT * FROM table');
if (mysqli_num_rows($result) > 0) {
$fp = fopen('file.csv', 'w');
while ($row = mysqli_fetch_assoc($result)) {
  fputcsv($fp, $row);
}
fclose($fp);
}
?>

Open in new window


Regards,
   Tomas Helgi
BRDigital Marketing

Author

Commented:
Dear Thomas, Thank you, but I already have a wonderful export as csv code ( thanks to Ray Paseur )

However I need to export as excel ( not come seperated values )
Do you have code for that? Thank you
Database Administrator / Software Engineer
Commented:
Hi!

Something like this for writing to excel.

<?php 
$con = new mysqli($host, $user, $password, $database);
$filename = "sampledata.xls"; // File Name
// Download file
header("Content-Disposition: attachment; filename=\"$filename\""); 
header("Content-Type: application/vnd.ms-excel");
 
// Check connection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}
$flag = false;
$result = mysqli_query($con, 'SELECT * FROM table');

if (mysqli_num_rows($result) > 0) {

while ($row = mysqli_fetch_assoc($result)) {
if(!$flag) {
      // display field/column names as first row
      echo implode("\t", array_keys($row)) . "\r\n";
      $flag = true;
    }
    echo implode("\t", array_values($row)) . "\r\n";
}

}
?>

Open in new window


Regards,
   Tomas Helgi
BRDigital Marketing

Author

Commented:
Dear Tomas Helgi, thank you for the wonderful code. One more thing I'd like to ask you about this code.

the letters like Ö, İ, Ş , Ğ is not shown properly on the Excel. Instead of this letters, it shows ? ( question mark ) on the excel table?

How can I correct this? Thank you
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi!

For that you will probably include correct language in the header of the PHP/HTTP response.
header("Content-Language: <your language>")

Open in new window


See further here on what other headers you could use.
http://php.net/manual/en/function.header.php
http://www.jonasjohn.de/snippets/php/headers.htm

Regards,
     Tomas Helgi
BRDigital Marketing

Author

Commented:
Thank you so much Tomas Helgi Johannsson