Selamat datang di tutorial singkat dan contoh cara mengekspor data dari MYSQL ke CSV di PHP. Jadi, Anda ingin mengekspor daftar dari database? . Mari kita telusuri contoh sederhana dalam panduan ini – Baca terus
ⓘ Saya telah menyertakan file zip dengan semua kode sumber contoh di awal tutorial ini, jadi Anda tidak perlu menyalin-tempel semuanya… Atau jika Anda hanya ingin langsung masuk
TLDR – SLIDE CEPAT
Unduh & Catatan
UNDUH & CATATAN
Pertama, berikut adalah tautan unduhan ke kode contoh seperti yang dijanjikan
CATATAN CEPAT
- Buat basis data pengujian dan impor 1-users.sql
- Ubah pengaturan basis data di 2a-export-download.php dan 2b-export-save.php menjadi milik Anda
- Jalankan 2a-export-download.php_ untuk demo tentang ekspor ke unduhan CSV, 2b-export-save.php untuk menyimpan ke file CSV di server
DOWNLOAD KODE CONTOH
Klik di sini untuk mengunduh kode sumber contoh, saya telah merilisnya di bawah lisensi MIT, jadi silakan membangun di atasnya atau menggunakannya dalam proyek Anda sendiri
EKSPOR DATA DARI MYSQL KE CSV
Baiklah, sekarang mari kita masuk ke contoh cara mengekspor data dari MySQL ke file CSV
LANGKAH 1) DATABASE PENGGUNA DUMMY
1-pengguna. sql
CREATE TABLE `users` ( `user_id` bigint(20) NOT NULL, `user_email` varchar(255) NOT NULL, `user_name` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE `users` ADD PRIMARY KEY (`user_id`), ADD UNIQUE KEY `user_email` (`user_email`), ADD KEY `user_name` (`user_name`); ALTER TABLE `users` MODIFY `user_id` bigint(20) NOT NULL AUTO_INCREMENT; INSERT INTO `users` (`user_id`, `user_email`, `user_name`) VALUES (1, 'Jane Doe', 'jane@doe.com'), (2, 'Joe Doe', 'joe@doe.com'), (3, 'John Doe', 'john@doe.com');Untuk demo ini, kami akan menggunakan tabel pengguna sederhana ini dengan hanya 3 kolom
- user_id ID pengguna. Kunci utama, peningkatan otomatis
- <?php // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN! $dbHost = "localhost"; $dbName = "test"; $dbChar = "utf8"; $dbUser = "root"; $dbPass = ""; try { $pdo = new PDO( "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar", $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED ]); } catch (Exception $ex) { exit($ex->getMessage()); } // (B) HTTP CSV HEADERS header("Content-Type: application/octet-stream"); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"export.csv\""); // (C) GET USERS FROM DATABASE + DIRECT OUTPUT $out = fopen("php://output", "w"); $stmt = $pdo->prepare("SELECT * FROM `users`"); $stmt->execute(); while ($row = $stmt->fetch()) { fputcsv($out, $row); } fclose($out);0 Alamat email pengguna
- <?php // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN! $dbHost = "localhost"; $dbName = "test"; $dbChar = "utf8"; $dbUser = "root"; $dbPass = ""; try { $pdo = new PDO( "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar", $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED ]); } catch (Exception $ex) { exit($ex->getMessage()); } // (B) HTTP CSV HEADERS header("Content-Type: application/octet-stream"); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"export.csv\""); // (C) GET USERS FROM DATABASE + DIRECT OUTPUT $out = fopen("php://output", "w"); $stmt = $pdo->prepare("SELECT * FROM `users`"); $stmt->execute(); while ($row = $stmt->fetch()) { fputcsv($out, $row); } fclose($out);1 Nama pengguna
LANGKAH 2A) EKSPOR CSV (UNDUH FILE CSV)
2a-ekspor-unduh. php
<?php // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN! $dbHost = "localhost"; $dbName = "test"; $dbChar = "utf8"; $dbUser = "root"; $dbPass = ""; try { $pdo = new PDO( "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar", $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED ]); } catch (Exception $ex) { exit($ex->getMessage()); } // (B) HTTP CSV HEADERS header("Content-Type: application/octet-stream"); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"export.csv\""); // (C) GET USERS FROM DATABASE + DIRECT OUTPUT $out = fopen("php://output", "w"); $stmt = $pdo->prepare("SELECT * FROM `users`"); $stmt->execute(); while ($row = $stmt->fetch()) { fputcsv($out, $row); } fclose($out);_Ya, itu saja untuk apa yang disebut "ekspor yang sulit ke CSV". Tidak akan menjelaskan baris demi baris, ini seharusnya cukup mudah
- Sambungkan ke database, ingat untuk mengubah pengaturan ke milik Anda
- Keluarkan tajuk HTTP "simpan sebagai file CSV".
- Terakhir, kami mengambil pengguna dari database dan menampilkannya menggunakan <?php // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN! $dbHost = "localhost"; $dbName = "test"; $dbChar = "utf8"; $dbUser = "root"; $dbPass = ""; try { $pdo = new PDO( "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar", $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED ]); } catch (Exception $ex) { exit($ex->getMessage()); } // (B) HTTP CSV HEADERS header("Content-Type: application/octet-stream"); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"export.csv\""); // (C) GET USERS FROM DATABASE + DIRECT OUTPUT $out = fopen("php://output", "w"); $stmt = $pdo->prepare("SELECT * FROM `users`"); $stmt->execute(); while ($row = $stmt->fetch()) { fputcsv($out, $row); } fclose($out);2
LANGKAH 2B) EKSPOR MYSQL KE CSV (SIMPAN KE FILE DI SERVER)
2b-ekspor-simpan. php
<?php // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN! $dbHost = "localhost"; $dbName = "test"; $dbChar = "utf8"; $dbUser = "root"; $dbPass = ""; try { $pdo = new PDO( "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar", $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED ]); } catch (Exception $ex) { exit($ex->getMessage()); } // (B) CREATE EMPTY CSV FILE ON SERVER $handle = fopen("export.csv", "w"); if ($handle === false) { exit("Error creating $csvFile"); } // (C) GET USERS FROM DATABASE + WRITE TO FILE $stmt = $pdo->prepare("SELECT * FROM `users`"); $stmt->execute(); while ($row = $stmt->fetch()) { fputcsv($handle, $row); } fclose($handle); echo "DONE!";Berikut alternatif yang akan menyimpan file CSV ke server sebagai gantinya
- Hubungkan ke database, seperti biasa
- Buat dan buka file CSV kosong <?php // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN! $dbHost = "localhost"; $dbName = "test"; $dbChar = "utf8"; $dbUser = "root"; $dbPass = ""; try { $pdo = new PDO( "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar", $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED ]); } catch (Exception $ex) { exit($ex->getMessage()); } // (B) HTTP CSV HEADERS header("Content-Type: application/octet-stream"); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"export.csv\""); // (C) GET USERS FROM DATABASE + DIRECT OUTPUT $out = fopen("php://output", "w"); $stmt = $pdo->prepare("SELECT * FROM `users`"); $stmt->execute(); while ($row = $stmt->fetch()) { fputcsv($out, $row); } fclose($out);3
- Dapatkan pengguna dari database, gunakan <?php // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN! $dbHost = "localhost"; $dbName = "test"; $dbChar = "utf8"; $dbUser = "root"; $dbPass = ""; try { $pdo = new PDO( "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar", $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED ]); } catch (Exception $ex) { exit($ex->getMessage()); } // (B) HTTP CSV HEADERS header("Content-Type: application/octet-stream"); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"export.csv\""); // (C) GET USERS FROM DATABASE + DIRECT OUTPUT $out = fopen("php://output", "w"); $stmt = $pdo->prepare("SELECT * FROM `users`"); $stmt->execute(); while ($row = $stmt->fetch()) { fputcsv($out, $row); } fclose($out);_2 untuk menuliskannya ke dalam file CSV
EXTRA) MENGHADAPI EKSPOR BESAR
<?php // (A) SET TIME LIMIT (SECONDS) + KEEP RUNNING EVEN IF PAGE CLOSED set_time_limit(0); ignore_user_abort(true); // (B) PREVENT MULTIPLE EXPORT BY CREATING A "LOCK FILE" if (file_exists("LOCK-USER-ID.TXT")) { exit("EXPORT ALREADY RUNNING"); } else { file_put_contents("LOCK-USER-ID.TXT", "1"); } // (C) SAVE INTO CSV FILE ON SERVER AS USUAL // SELECT * FROM `TABLE` // FPUTCSV(...) // (D) SEND EMAIL NOTIFICATION TO USER WHEN DONE mail("USER@MAIL.COM", "SUBJECT", "DONE - YOU CAN DOWNLOAD REPORT AT LINK HERE"); // (E) UNLOCK unlink("LOCK-USER-ID.TXT");_Contoh di atas bekerja dengan cukup baik, tetapi selalu mengharapkan hal-hal yang kacau. Terutama saat mengekspor kumpulan data besar, skrip PHP kemungkinan besar akan memakan waktu lama dan waktu habis. Inilah solusi yang mungkin
- Kita dapat menggunakan <?php // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN! $dbHost = "localhost"; $dbName = "test"; $dbChar = "utf8"; $dbUser = "root"; $dbPass = ""; try { $pdo = new PDO( "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar", $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED ]); } catch (Exception $ex) { exit($ex->getMessage()); } // (B) HTTP CSV HEADERS header("Content-Type: application/octet-stream"); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"export.csv\""); // (C) GET USERS FROM DATABASE + DIRECT OUTPUT $out = fopen("php://output", "w"); $stmt = $pdo->prepare("SELECT * FROM `users`"); $stmt->execute(); while ($row = $stmt->fetch()) { fputcsv($out, $row); } fclose($out);_5 untuk mengizinkan skrip berjalan selamanya dan <?php // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN! $dbHost = "localhost"; $dbName = "test"; $dbChar = "utf8"; $dbUser = "root"; $dbPass = ""; try { $pdo = new PDO( "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar", $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED ]); } catch (Exception $ex) { exit($ex->getMessage()); } // (B) HTTP CSV HEADERS header("Content-Type: application/octet-stream"); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"export.csv\""); // (C) GET USERS FROM DATABASE + DIRECT OUTPUT $out = fopen("php://output", "w"); $stmt = $pdo->prepare("SELECT * FROM `users`"); $stmt->execute(); while ($row = $stmt->fetch()) { fputcsv($out, $row); } fclose($out);6 untuk tetap berjalan bahkan setelah pengguna menutup jendela
- Pengguna yang tidak sabar akan memukul ulang, menyebabkan banyak ekspor muncul. Kita dapat mencegah hal ini dengan membuat boneka <?php // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN! $dbHost = "localhost"; $dbName = "test"; $dbChar = "utf8"; $dbUser = "root"; $dbPass = ""; try { $pdo = new PDO( "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar", $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED ]); } catch (Exception $ex) { exit($ex->getMessage()); } // (B) HTTP CSV HEADERS header("Content-Type: application/octet-stream"); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"export.csv\""); // (C) GET USERS FROM DATABASE + DIRECT OUTPUT $out = fopen("php://output", "w"); $stmt = $pdo->prepare("SELECT * FROM `users`"); $stmt->execute(); while ($row = $stmt->fetch()) { fputcsv($out, $row); } fclose($out);7. Jika "file kunci" ini ada, kami tidak mengizinkan pengguna melakukan ekspor lagi
- Ekspor ke file CSV seperti biasa, tetapi simpan ke drive pribadi yang aman atau area yang dilindungi kata sandi
- Saat ekspor selesai, kirim email ke pengguna dengan tautan unduhan
- Hapus <?php // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN! $dbHost = "localhost"; $dbName = "test"; $dbChar = "utf8"; $dbUser = "root"; $dbPass = ""; try { $pdo = new PDO( "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar", $dbUser, $dbPass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED ]); } catch (Exception $ex) { exit($ex->getMessage()); } // (B) HTTP CSV HEADERS header("Content-Type: application/octet-stream"); header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\"export.csv\""); // (C) GET USERS FROM DATABASE + DIRECT OUTPUT $out = fopen("php://output", "w"); $stmt = $pdo->prepare("SELECT * FROM `users`"); $stmt->execute(); while ($row = $stmt->fetch()) { fputcsv($out, $row); } fclose($out);_7 untuk “membuka” dan izinkan proses ekspor lainnya
BIT & LINK EKSTRA
Itu saja untuk contohnya, dan berikut adalah beberapa tambahan kecil yang mungkin berguna bagi Anda
LINK dan REFERENSI
- Buka File – PHP
- File Masukan CSV – PHP
- Tetapkan Batas Waktu – PHP
- PDO (Objek Basis Data) – PHP
VIDEO TUTORIAL
LEMBAR CHEAT INFOGRAFIS
Terima kasih telah membaca, dan kami telah sampai di akhir panduan ini. Saya harap ini membantu Anda dengan proyek Anda, dan jika Anda ingin berbagi apa pun, jangan ragu untuk berkomentar di bawah ini. Semoga berhasil dan selamat membuat kode