Export Data from MySQL to CSV Using PHP

2 years ago

3 Comments

by Yogesh Koli

10,263 Views

8 months ago

Written by Yogesh Koli

A step-by-step guide to export data to CSV from MySQL using PHP

It is a basic task for any application that needs a reporting feature to CSV; here I am going to explain how to generate a CSV file from the MySQL records.

Let’s get started:

Step 1 – Create Sample Database and Required Table along with the data:

Create Database table with sample records, here I am going to use a users table, which is going to have users details, go ahead and use following SQL statements to create table and insert the records. (Note: you can skip this step if you have your existing database tables)

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLE IF NOT EXISTS `users` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `first_name` varchar(40) NOT NULL,   `last_name` varchar(40) NOT NULL,   `email` varchar(50) NOT NULL,   PRIMARY KEY (`id`) );   INSERT INTO `users` (`id`, `first_name`, `last_name`, `email`) VALUES (1, 'Jerry', 'McKenny', 'jerrymckenny@gmail.com'), (2, 'Lillian', 'Evans', 'lillianevans@gmail.com'), (3, 'Eva', 'Osborne', 'evaosborne@gmail.com'), (4, 'Darlene', 'Edwards', 'darleneedwards@gmail.com'), (5, 'Bill', 'White', 'billwhite@gmail.com');

Users Table Structure

Existing Mysql Records to Export

Step 2 Create PHP to MySQL Database Connection Script:

Create Database configuration file, which is going help us to connect to MySQL from PHP file.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

<?php /* * iTech Empires:  Export Data from MySQL to CSV Script * Version: 1.0.0 * Page: DB Connection */   // Connection variables $host = "localhost"; // MySQL host name eg. localhost $user = "root"; // MySQL user. eg. root ( if your on localserver) $password = ""; // MySQL user password  (if password is not set for your root user then keep it empty ) $database = "test"; // MySQL Database name   // Connect to MySQL Database $con = new mysqli($host, $user, $password, $database);   // Check connection if ($con->connect_error) {     die("Connection failed: " . $con->connect_error); } ?>

Step 3 – Add Sample Index Page to List out the Records:

Create index.php file and list records.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

<?php /* * iTech Empires:  Export Data from MySQL to CSV Script * Version: 1.0.0 * Page: Index */   // Database Connection require("db_connection.php");   // List Users $query = "SELECT * FROM users"; if (!$result = mysqli_query($con, $query)) {     exit(mysqli_error($con)); }   if (mysqli_num_rows($result) > 0) {     $number = 1;     $users = '<table class="table table-bordered">         <tr>             <th>No.</th>             <th>First Name</th>             <th>Last Name</th>             <th>Email</th>         </tr>     ';     while ($row = mysqli_fetch_assoc($result)) {         $users .= '<tr>             <td>'.$number.'</td>             <td>'.$row['first_name'].'</td>             <td>'.$row['last_name'].'</td>             <td>'.$row['email'].'</td>         </tr>';         $number++;     }     $users .= '</table>'; }   ?> <!doctype html> <html lang="en"> <head>     <meta charset="UTF-8">     <title>Export Data from MySQL to CSV Tutorial | iTech Empires</title>     <!-- Bootstrap CSS File  -->     <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css"/> </head> <body> <div class="container">     <!--  Header  -->     <div class="row">         <div class="col-md-12">             <h2>Export Data from MySQL to CSV</h2>         </div>     </div>     <!--  /Header  -->       <!--  Content   -->     <div class="form-group">         <?php echo $users ?>     </div>     <div class="form-group">         <button onclick="Export()" class="btn btn-primary">Export to CSV File</button>     </div>     <!--  /Content   -->       <script>         function Export()         {             var conf = confirm("Export users to CSV?");             if(conf == true)             {                 window.open("export.php", '_blank');             }         }     </script> </div> </body> </html>

Step 4- Exporting MySQL Data to CSV file PHP Script:

Create export.php file to export data from Mysql to CSV.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

<?php /* * iTech Empires:  Export Data from MySQL to CSV Script * Version: 1.0.0 * Page: Export */   // Database Connection require("db_connection.php");   // get Users $query = "SELECT * FROM users"; if (!$result = mysqli_query($con, $query)) {     exit(mysqli_error($con)); }   $users = array(); if (mysqli_num_rows($result) > 0) {     while ($row = mysqli_fetch_assoc($result)) {         $users[] = $row;     } }   header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename=Users.csv'); $output = fopen('php://output', 'w'); fputcsv($output, array('No', 'First Name', 'Last Name', 'Email'));   if (count($users) > 0) {     foreach ($users as $row) {         fputcsv($output, $row);     } } ?>

Click OK to export the data to CSV and your file get’s downloaded

If you check download file, you get records listed, as showing below:

Records from CSV files

We are done, you can download the complete source for reference use following links to download or checkout live demo.

searching values on array

search if values of an array are present on other array
<?php
$arr1=array("From","source","NT");

$os = array("Mac", "NT", "Irix", "Linux");


foreach($arr1 as $value){
if (in_array($value,$os)) {
    echo "Found $value <br>";
}

else {
echo " Value  $value not found<br>";
}

}

?>


<?php
function version

function removeheader($header,$arr) {


foreach($header as $value){
if (in_array($value,$arr)) {
    echo "Found $value <br>";
}

else {
echo " Value  $value not found<br>";
}

}


}


removeheader($arr1,$os);

?>

php mailer

1) install mailer

  composer require phpmailer/phpmailer

2) run the code  php send.php ambiorixg12@gmail.com

<?php

/**

 * This example shows sending a message using a local sendmail binary.

 */

//Import the PHPMailer class into the global namespace

use PHPMailer\PHPMailer\PHPMailer;

require './vendor/autoload.php';

//Create a new PHPMailer instance

$mail = new PHPMailer;

// Set PHPMailer to use the sendmail transport

$mail->isSendmail();

//Set who the message is to be sent from

$mail->setFrom('from@example.com', 'First Last');

//Set an alternative reply-to address

$mail->addReplyTo('replyto@example.com', 'First Last');

//Set who the message is to be sent to

$mail->addAddress("$argv[1]", 'Ambiorix Rodriguez');

//Set the subject line

$mail->Subject = 'PHPMailer sendmail test';

//Read an HTML message body from an external file, convert referenced images to embedded,

//convert HTML into a basic plain-text alternative body

//$mail->msgHTML(file_get_contents('contents.html'), __DIR__);

//Replace the plain text body with one created manually

//$mail->AltBody = 'This is a plain-text message body';

//plain text body

 $mail->Body='This is a plain-text message body';

//Attach an image file

//$mail->addAttachment('images/phpmailer_mini.png');

//send the message, check for errors

if (!$mail->send()) {

    echo "Mailer Error: " . $mail->ErrorInfo;

} else {

    echo "Message sent!";

}

?>
https://github.com/PHPMailer/PHPMailer
https://github.com/PHPMailer/PHPMailer/tree/master/examples