how to export to excel from mysql database using php

BR
BR used Ask the Experts™
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial