How to export MySQL data to Excel file with send excel content to email in PHP?

Here we learn how to export the MySQL data to export file and after that send excel content to email through Phpmailer.

Also here is enter the captcha code before export the data to excel file.

It's very easy stepd to export the MySQL data to PHP excel file. Please follow below step by step and see screenshots.

• Capcha code HTML form

• Captcha validation

• Excel sheet screenshot

Step 1 : Create MySQL Database Table

Firstly we create MySQL database table using below query to export data into excel file.

CREATE TABLE `users` (
  `users_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(50) NOT NULL DEFAULT '',
  `lastname` varchar(50) NOT NULL DEFAULT '',
  `email_address` varchar(70) NOT NULL DEFAULT '',
  `city` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`users_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 2 : Make MySQL Server database connection

Now, first we make MySQL Server database connection from the PHP file. So, add the server name, username, password and database name.

<?php
$DB_Server = "localhost"; //mysql Server 
$DB_Username = "root"; //mysql Username   
$DB_Password = ""; //mysql Password   
$DB_DBName = "users"; //mysql Database Name

//create mysql connection
$connect = mysqli_connect($DB_Server, $DB_Username, $DB_Password, $DB_DBName);
if (mysqli_connect_errno()){
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
} ?>

Step 3: Get MySQL Data

we make Query which will select the firstname, lastname, email_address and city from the database table. As show below:

$sql = "Select * from users";
$result = mysqli_query($Connect, $sql);

Step 4: Export the data to excel file 

We make the query and collect the database data and use file_put_contents php function to export the data in excel file

$sql = "Select * from users";
$result = mysqli_query($Connect, $sql); 

$header = "User ID" . "\t";
$header .= "Firstname" . "\t";
$header .= "Lastname" . "\t";
$header .= "City" . "\t";
$header .= "Country" . "\t";

$data = '';
while( $row = mysql_fetch_array($result)){
$row1 = array();
$row1[] = $row['users_id'];
$row1[] = $row['firstname'];
$row1[] = $row['lastname'];
$row1[] = $row['city'];
$row1[] = $row['country'];
$data .= join("\t", $row1)."\n";}

file_put_contents("excel/report1.xlsx","$header\n$data");

Step 5: Send the excel file content to email

For send excel file content to email through phpmailer class

require("phpmailer.php");
$mail = new PHPMailer();
$mail->SetFrom('manson1306@gmail.com', 'Manoj Son'); //Name is optional
$mail->Subject   = 'Test email';
$mail->Body      = "Test Message";
$mail->AddAddress( 'manson1306@gmail.com' );
$first_report = 'excel/report1.xls';
$second_report = 'excel/report2.xls';
// If checked both checkboxes to send both attachment
if(isset($_POST["reports"][0]) && isset($_POST["reports"][1])){
$mail->AddAttachment( $first_report );
$mail->AddAttachment( $second_report );
} else { // If checked single checkbox to send single attachment
$mail->AddAttachment( $first_report );
}
if($mail->Send()) {
$success_message = "Email send successfully.";
} else {
echo "\r\nMail not sent. " .  $mail->ErrorInfo;
}

Step 6: HTML code with Captcha and export button

<form name="frmExcel" method="post" action="">
<table border="0" cellpadding="10" cellspacing="1" width="100%" class="demo-table">
<?php
if(isset($error_message)) { ?>
<tr id="error-captcha" class="demo-error"><td><?php  echo $error_message; ?></td></tr>
<?php }?>
<tr class="tablerow">
<td>Captcha Code: <br/>
<input name="captcha_code" type="text" class="demo-input captcha-input">
</td>
<td><br/><input type="submit" name="submit" value="Submit" class="demo-btn"></td>
</tr>
</table>
</form>

Step 7: CSS code

<style>
body {
font-family:calibri;
width:610px;
}
.demo-error {
display:inline-block;
color:#FF0000;
background: #dcfddc;
}
.demo-input {
    width: 100%;
    border-radius: 5px;
    border: #CCC 1px solid;
    padding: 10px;
    margin-top: 5px;
}
.demo-btn {
padding: 10px;
    border-radius: 5px;
    background: #478347;
    border: #325a32 1px solid;
    color: #FFF;
    font-size: 1em;
    width: 100%;
    cursor:pointer;
}
.demo-table {
    background: #dcfddc;
    border-radius: 5px;
    padding: 10px;
}
.demo-success {
    color: #FF0000;
    border-radius: 5px;
}
.captcha-input {
background:#FFF url('captcha_code.php') repeat-y;
padding-left: 85px;
}
</style>

Step 8: Captcha code

Please create captch_code.php file and add below code

<?php
session_start();
$random_alpha = md5(rand());
$captcha_code = substr($random_alpha, 0, 6);
$_SESSION["captcha_code"] = $captcha_code;
$target_layer = imagecreatetruecolor(70,30);
$captcha_background = imagecolorallocate($target_layer, 255, 160, 119);
imagefill($target_layer,0,0,$captcha_background);
$captcha_text_color = imagecolorallocate($target_layer, 0, 0, 0);
imagestring($target_layer, 5, 5, 5, $captcha_code, $captcha_text_color);
header("Content-type: image/jpeg");
imagejpeg($target_layer);
?>

Step 9: Full PHP, HTML and CSS code

Here is database table connection, PHP, HTML, CSS with captcha validation code

<?php
$DB_Server = "localhost"; //mysql Server 
$DB_Username = "root"; //mysql Username   
$DB_Password = ""; //mysql Password   
$DB_DBName = "users"; //mysql Database Name

//create mysql connection
$connect = mysqli_connect($DB_Server, $DB_Username, $DB_Password, $DB_DBName);
if (mysqli_connect_errno()){
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

if(count($_POST)>0) {

if($_POST["captcha_code"] == $_SESSION["captcha_code"]){

$sql = "Select * from users";
        $result = mysqli_query($Connect, $sql);   

$header = "User ID" . "\t";
$header .= "Firstname" . "\t";
$header .= "Lastname" . "\t";
$header .= "City" . "\t";
$header .= "Country" . "\t";

$data = '';
while( $row = mysql_fetch_array($result)){
$row1 = array();
$row1[] = $row['users_id'];
$row1[] = $row['firstname'];
$row1[] = $row['lastname'];
$row1[] = $row['city'];
$row1[] = $row['country'];
$data .= join("\t", $row1)."\n";}

@file_put_contents("excel/report1.xlsx","$header\n$data");

/* email start here */
require("phpmailer.php");
$mail = new PHPMailer();
$mail->SetFrom('manson1306@gmail.com', 'Manoj Son'); //Name is optional
$mail->Subject   = 'Test email';
$mail->Body      = "Test Message";
$mail->AddAddress( 'manson1306@gmail.com' );
$first_report = 'excel/report1.xls';
$second_report = 'excel/report2.xls';
// If checked both checkboxes to send both attachment
if(isset($_POST["reports"][0]) && isset($_POST["reports"][1])){
$mail->AddAttachment( $first_report );
$mail->AddAttachment( $second_report );
} else { // If checked single checkbox to send single attachment
$mail->AddAttachment( $first_report );
}
if($mail->Send()) {
$success_message = "Email send successfully.";
} else {
echo "\r\nMail not sent. " .  $mail->ErrorInfo;
}

}else{
$error_message = "Please enter Captcha Code.";
}
 }
?>
<style>
body {
font-family:calibri;
width:610px;
}
.demo-error {
display:inline-block;
color:#FF0000;
background: #dcfddc;
}
.demo-input {
    width: 100%;
    border-radius: 5px;
    border: #CCC 1px solid;
    padding: 10px;
    margin-top: 5px;
}
.demo-btn {
padding: 10px;
    border-radius: 5px;
    background: #478347;
    border: #325a32 1px solid;
    color: #FFF;
    font-size: 1em;
    width: 100%;
    cursor:pointer;
}
.demo-table {
    background: #dcfddc;
    border-radius: 5px;
    padding: 10px;
}
.demo-success {
    color: #FF0000;
    border-radius: 5px;
}
.captcha-input {
background:#FFF url('captcha_code.php') repeat-y;
padding-left: 85px;
}
</style>
<form name="frmExcel" method="post" action="">
<table border="0" cellpadding="10" cellspacing="1" width="100%" class="demo-table">
<?php
if(isset($error_message)) { ?>
<tr id="error-captcha" class="demo-error"><td><?php  echo $error_message; ?></td></tr>
<?php }?>
<tr class="tablerow">
<td>Captcha Code: <br/>
<input name="captcha_code" type="text" class="demo-input captcha-input">
</td>
<td><br/><input type="submit" name="submit" value="Submit" class="demo-btn"></td>
</tr>
</table>
</form>

That's it...