Php mysql выгрузка файла

How to Export MySQL Database Data to Excel/CSV Using PHP

Recently I published an article on How to Read CSV and Excel Files in PHP Using PhpSpreadsheet. One of the readers asked how to use PhpSpreadsheet to export MySQL database records to the Excel file. The user may need their MySQL data in the Excel or CSV file to read or share it easily. In this article, we discussed how one can export data from a database to Excel and CSV files using PHP. In addition to this, we will also study how one can send this exported file as an attachment in the email.

Getting Started

For getting started, you first need to install the PhpSpreadsheet library. I recommend using Composer for the installation of the library. Open the terminal in your project root directory and run the command:

composer require phpoffice/phpspreadsheet

PhpSpreadsheet is the library that provides support for reading and writing different types of file formats. Below is the screenshot of the supported file formats.

Our end goal is to export database table records to the Excel/CSV file. For this, we require a few entries in our database. As an example, I am creating the ‘products’ table by running the below SQL query.

CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `product_name` varchar(255) NOT NULL, `product_sku` varchar(255) NOT NULL, `product_price` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Next, add some dummy entries in the table so you will see a few records in the exported file.

INSERT INTO `products` (`product_name`, `product_sku`, `product_price`) VALUES ('Apple', 'app_111', '$1000'), ('Lenovo', 'len_222', '$999'), ('Samsung', 'sam_689', '$1200'), ('Acer', 'ace_620', '$900');

After this create a config.php file where we write the code for database connection.

connect_error)< die("Unable to connect database: " . $db->connect_error); >

Make sure to replace the placeholders with the actual values in the above code. This file will be included later to fetch records from the products table.

In the next part of the tutorial, I am allocating a separate section for exporting data to Excel, exporting data to CSV, and sending the email with an exported file as an attachment.

Export MySQL Database Data to Excel Using PHP

You have installed the library and also have a database table with few entries. Now you can go ahead and write the actual code which will export an Excel file with data filled in it.

Create export-to-excel.php file in the root directory. In this file, I will write a SQL query to fetch data from the database. This fetched data will be written to Excel and sent the Excel file to the browser to download automatically.

export-to-excel.php

setActiveSheetIndex(0); $activeSheet = $spreadsheet->getActiveSheet(); $activeSheet->setCellValue('A1', 'Product Name'); $activeSheet->setCellValue('B1', 'Product SKU'); $activeSheet->setCellValue('C1', 'Product Price'); $query = $db->query("SELECT * FROM products"); if($query->num_rows > 0) < $i = 2; while($row = $query->fetch_assoc()) < $activeSheet->setCellValue('A'.$i , $row['product_name']); $activeSheet->setCellValue('B'.$i , $row['product_sku']); $activeSheet->setCellValue('C'.$i , $row['product_price']); $i++; > > $filename = 'products.xlsx'; header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename='. $filename); header('Cache-Control: max-age=0'); $Excel_writer->save('php://output');

When you run this code on the browser, an Excel file will be downloaded automatically and the Excel sheet will have the following entries.

Export MySQL Database Data to CSV Using PHP

In the previous section, we exported data to an Excel file. If someone is looking to export data in a CSV file then you need to change a few lines in the above code.

export-to-csv.php

setActiveSheetIndex(0); $activeSheet = $spreadsheet->getActiveSheet(); $activeSheet->setCellValue('A1', 'Product Name'); $activeSheet->setCellValue('B1', 'Product SKU'); $activeSheet->setCellValue('C1', 'Product Price'); $query = $db->query("SELECT * FROM products"); if($query->num_rows > 0) < $i = 2; while($row = $query->fetch_assoc()) < $activeSheet->setCellValue('A'.$i , $row['product_name']); $activeSheet->setCellValue('B'.$i , $row['product_sku']); $activeSheet->setCellValue('C'.$i , $row['product_price']); $i++; > > $filename = 'products.csv'; header('Content-Type: application/text-csv'); header('Content-Disposition: attachment;filename='. $filename); header('Cache-Control: max-age=0'); $Excel_writer->save('php://output');

Send an Email with the File as an Attachment

We have seen how to download the file with data in Excel/CSV format. Some users may want to send the exported file as an attachment in the email. Let’s see how to achieve it.

First, install the PHPMailer library using the command:

composer require phpmailer/phpmailer

After installing the library, you can use any SMTP server to send an email. It’s up to you. You may use your hosting’s SMTP server, AWS SES, or Gmail SMTP server. If you are going with the Gmail SMTP server read our article Send Email Using Gmail API and PHPMailer which explains the configuration needed for it.

The previous code download Excel/CSV file automatically. But now, instead of making it downloadable, we will save the file in the directory and then send it as an attachment. In short, we will write the code below for saving the file.

. . $filename = 'products.csv'; if (!file_exists('files')) < mkdir('files', 0755); >$Excel_writer->save('files/'.$filename);

Our final code to send the attachment in an email will be as follows:

setActiveSheetIndex(0); $activeSheet = $spreadsheet->getActiveSheet(); $activeSheet->setCellValue('A1', 'Product Name'); $activeSheet->setCellValue('B1', 'Product SKU'); $activeSheet->setCellValue('C1', 'Product Price'); $query = $db->query("SELECT * FROM products"); if($query->num_rows > 0) < $i = 2; while($row = $query->fetch_assoc()) < $activeSheet->setCellValue('A'.$i , $row['product_name']); $activeSheet->setCellValue('B'.$i , $row['product_sku']); $activeSheet->setCellValue('C'.$i , $row['product_price']); $i++; > > $filename = 'products.csv'; if (!file_exists('files')) < mkdir('files', 0755); >$Excel_writer->save('files/'.$filename); // send as an attachment $mail = new PHPMailer(true); try < $mail->isSMTP(); $mail->Host = 'SMTP_HOST'; $mail->SMTPAuth = true; $mail->Username = 'SMTP_USERNAME'; //username $mail->Password = 'SMTP_PASSWORD'; //password $mail->SMTPSecure = 'ssl'; $mail->Port = 465; $mail->setFrom('FROM_EMAIL_ADDRESS', 'FROM_NAME'); $mail->addAddress('RECEPIENT_EMAIL_ADDRESS', 'RECEPIENT_NAME'); $mail->addAttachment('files/'.$filename); $mail->isHTML(true); $mail->Subject = 'Products Sheet'; $mail->Body = 'Products Sheet'; $mail->send(); echo 'Message has been sent'; > catch (Exception $e) < echo 'Message could not be sent. Mailer Error: '. $mail->ErrorInfo; >

Replace the placeholders with the actual values and an email will be sent with an attachment to the recipient’s email address.

I hope you understand how to export MySQL database data to the Excel or CSV file and also send it as an attachment. Please share your thoughts and suggestions in the comment section below.

Related Articles

If you liked this article, then please subscribe to our YouTube Channel for video tutorials.

Источник

Экспорт данных из таблицы MySQL в Excel

Если нужно быстро и единоразово выгрузить данные из таблицы MySQL в Exel файл, то помогут следующие способы:

Экспорт через PHPMyAdmin

В PHPMyAdmin при экспорте можно выбрать формат «CSV for MS Excel»:

Результат:

Экспорт через HTML таблицу

prepare(«SELECT * FROM `test`»); $sth->execute(); $items = $sth->fetchAll(PDO::FETCH_ASSOC); ?>

ID Категория Название Описание

Результат работы скрипта:

Экспорт через PHPExcel

//spl_autoload_unregister('autoload'); require_once __DIR__ . '/PHPExcel/Classes/PHPExcel.php'; require_once __DIR__ . '/PHPExcel/Classes/PHPExcel/Writer/Excel2007.php'; $xls = new PHPExcel(); $xls->setActiveSheetIndex(0); $sheet = $xls->getActiveSheet(); // Шапка $sheet->getStyle("A1:D1")->getFont()->setBold(true); $sheet->setCellValue("A1", 'ID'); $sheet->setCellValue("B1", 'Категория'); $sheet->setCellValue("C1", 'Название'); $sheet->setCellValue("D1", 'Описание'); // Выборка из БД $dbh = new PDO('mysql:dbname=db_name;host=localhost', 'логин', 'пароль'); $sth = $dbh->prepare("SELECT * FROM `test`"); $items = $sth->fetch(PDO::FETCH_ASSOC); $index = 2; foreach ($items as $row) < $sheet->setCellValue("A" . $index, $row['id']); $sheet->setCellValue("B" . $index, $row['category']); $sheet->setCellValue("C" . $index, $row['name']); $sheet->setCellValue("D" . $index, $row['name']); $index++; > // Отдача файла в браузер header("Expires: Mon, 1 Apr 1974 05:00:00 GMT"); header("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT"); header("Cache-Control: no-cache, must-revalidate"); header("Pragma: no-cache"); header("Content-type: application/vnd.ms-excel" ); header("Content-Disposition: attachment; filename=prods.xlsx"); $objWriter = new PHPExcel_Writer_Excel2007($xls); $objWriter->save('php://output'); exit(); 

Источник

Простой импорт/экспорт в CSV для PHP & MySQL

В ходе разработки сервиса по расчете статистики по управлению запасами для интернет-магазинов возникла задача быстро организовать импорт/экспорт таблиц между разными MySQL серверами. Поскольку надо было сделать просто и прозрачно — оптимизация будет впереди — решил воспользоваться авторскими рекомендация из документации по MySQL 5.0.

В качестве формата обмена данными решил принять CSV именно по причине простоты реализации.

В итоге, получилось две функции

 function export_csv( $table, // Имя таблицы для экспорта $afields, // Массив строк - имен полей таблицы $filename, // Имя CSV файла для сохранения информации // (путь от корня web-сервера) $delim=',', // Разделитель полей в CSV файле $enclosed='"', // Кавычки для содержимого полей $escaped='\\', // Ставится перед специальными символами $lineend='\\r\\n') < // Чем заканчивать строку в файле CSV $q_export = "SELECT ".implode(',', $afields). " INTO OUTFILE '".$_SERVER['DOCUMENT_ROOT'].$filename."' ". "FIELDS TERMINATED BY '".$delim."' ENCLOSED BY '".$enclosed."' ". " ESCAPED BY '".$escaped."' ". "LINES TERMINATED BY '".$lineend."' ". "FROM ".$table ; // Если файл существует, при экспорте будет выдана ошибка if(file_exists($_SERVER['DOCUMENT_ROOT'].$filename)) unlink($_SERVER['DOCUMENT_ROOT'].$filename); return mysql_query($q_export); >

• Файл можно создать на том же хосте, где расположен MySQL. Если ОС настроена с возможностью на сетевой диск с общим доступом, можно писать и на другой сервер.
• Если поле в таблице равно NULL, в CSV файле будет выведено \N.
• Для записи файла на локальный диск на сервере пользователю требуются права FILE не на уровне БД, а глобально на уровне сервера MySQL. Можно установить через PHPMyAdmin или запросом

GRANT FILE ON * . * TO 'username'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

 function import_csv( $table, // Имя таблицы для импорта $afields, // Массив строк - имен полей таблицы $filename, // Имя CSV файла, откуда берется информация // (путь от корня web-сервера) $delim=',', // Разделитель полей в CSV файле $enclosed='"', // Кавычки для содержимого полей $escaped='\\', // Ставится перед специальными символами $lineend='\\r\\n', // Чем заканчивается строка в файле CSV $hasheader=FALSE) < // Пропускать ли заголовок CSV if($hasheader) $ignore = "IGNORE 1 LINES "; else $ignore = ""; $q_import = "LOAD DATA INFILE '". $_SERVER['DOCUMENT_ROOT'].$filename."' INTO TABLE ".$table." ". "FIELDS TERMINATED BY '".$delim."' ENCLOSED BY '".$enclosed."' ". " ESCAPED BY '".$escaped."' ". "LINES TERMINATED BY '".$lineend."' ". $ignore. "(".implode(',', $afields).")" ; return mysql_query($q_import); >

1. Короткие и очень быстрые функции, за счет того, что выполняются одним MySQL запросом.
2. Довольно гибкая реализация — можно легко управлять множеством параметров, в том числе и списком полей
3. Для экспорта: путем изменения списка полей в массиве полей

или использования подзапроса вместо имени таблицы (тогда в массиве будут указаны поля этого подзапроса) — например,

(select field1, field1 from table2) t

array("column1", "@dummy", "column2", "@dummy", "column3")

PS. На самом эти команды MySQL имеют более богатый синтаксис с дополнительными настройками, так что поле для улучшения этого кода ограничено только необходимостью и фантазией.

Источник