How to Export Data to Excel in PHP and MySQL

Export Data to Excel in PHP and MySQL

Today I will explain how to Export data or records to Excel/CSV in PHP  and MySql. This topic is very important part of any web developer at beginner to advance level.

I know most of people recommended mysql database because it is free and easy to understand.


In this post, following file are required.
  • config.php
  • employee_add.php
  • employee_insert.php
  • employee_download.php
  • employee_export.php

Follow These Steps to Export Data or Records to Excel/CSV in PHP and MySQL

Step 1.   Crate a database , and give database name like tutorials

Note:- If you facing any problem to create database see my previous post like How to Insert Data in Database using PHP and mySql

Step 2.   Create a table under tutorials database and give table name like employee. This table having five fields like emp_id, emp_name, emp_gender, emp_email, emp_mobile, emp_address, emp_join_date so table structure shown below

Export Data to Excel in PHP and MySQL

Note:- If you facing any problem to create table under that database see my previous post like How to Insert Data in Database using PHP and mySql

Step 3.   Crate config.php file, code shown below

<?php

$servername = "localhost";
$username   = "root";
$password   = "";
$dbname     = "tutorials"; 

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);

?>

Step 4.   Crate employee_add.php file, code shown below

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Add Employee Records</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>  
  <script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>  
  <link rel="stylesheet" href="http://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
</head>
<body>
  <form action="employee_insert.php" method="post">
  <h2 align="center">Add Employees Record using PHP and MySQL</h2><hr /><br />
  <table align="center" border="1">
    <tbody>
    <tr>
      <td>Name</td>
      <td><input name="name" placeholder="Name" type="text" required></td>
    <tr>
      <td>Gender</td>
      <td>
      <input name="gender" type="radio" value="male" checked="">Male
      <input name="gender" type="radio" value="female">Female
      </td>
    </tr>
    <tr>
      <td>Email Id.</td>
      <td><input name="email" id="email" placeholder="Email Id."  type="text" required></td>
      </tr>
    <tr>
      <td>Mobile No.</td>
      <td><input name="mobile" id="mobile" placeholder="Mobile No." type="text" maxlength="10" required></td>
    </tr>
    <tr>
      <td>Address:</td>
      <td><textarea name="address" placeholder="Address" rows="5" required></textarea></td>
    </tr>
    <tr>
      <td>Joining Date</td>
      <td><input name="joindate" id="joindate" type="text" required placeholder="Select Date"></td>
    </tr>
    <tr>
      <td></td>
      <td><input type="submit" name="submit" value="Submit"></td>
    </tr>
    </tbody>
  </table>
</form>
</body>
</html>
<script>  
  $(document).ready(function(){  
   $.datepicker.setDefaults({  
        dateFormat: 'yy-mm-dd'   
   });  
   $(function(){  
      $("#joindate").datepicker();    
   });    
  });  
</script>

Step 5.   Create employee_insert.php file, code shown below

<?php
include_once('config.php');

$name    = $_POST['name'];
$gender  = $_POST['gender'];
$email   = $_POST['email'];
$mobile  = $_POST['mobile'];
$address = $_POST['address'];
$joindate    = $_POST['joindate'];

$data = "insert into employee (emp_name, emp_gender, emp_email, emp_mobile, emp_address, emp_join_date) values ('$name', '$gender', '$email', '$mobile', '$address', '$joindate')";
$result = mysqli_query($conn, $data);

if(isset($result))
{
echo "Record Add Successfully...";
}
else
{
echo "Employee Entry not Inserted";
}
?>
<!DOCTYPE html>
<html>
<head>
<title>View Employees Record</title>
</head>
<body>
<form action="employee_download.php">
<br/>
  <input type="submit" name="view" value="View Employees Record">
</form>
</body>
</html>

Step 6.   Create employee_download.php file, code shown below

<?php 
include_once('config.php');

 $data = "select * from employee order by emp_id asc";  
 $result = mysqli_query($conn, $data);  
 ?>  
 <!DOCTYPE html>  
 <html>  
  <head>  
   <title>All Employee Records Using PHP and MySql</title>  
   <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>  
   <script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>  
   <link rel="stylesheet" href="http://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
   <style type="text/css">
  @media print{
    .print{
      display:none;
    }
  }
  </style>
  </head>
  <body>  
    <br /><br />
    <h2 align="center">All Employee Records</h2><hr /><br />
    <table border="0" align="center">
      <tbody>
      <tr>
      <td>
        <form action="employee_export.php" method="get" enctype="multipart/form-data">
          <button type="submit" name="export">Export</button>   
        </form>
      </td>
      </tr>
      </tbody>
    </table>     
    <br />
    <table border="1" align="center" id="table">  
      <tr>  
         <th width="5%">ID</th>  
         <th width="15%">Name</th>  
         <th width="10%">Gender</th>  
         <th width="25%">Email</th>  
         <th width="15%">Mobile</th>
         <th width="10%">Address</th>
         <th width="10%">Join Date</th>  
      </tr>  
     <?php  
     while($row = mysqli_fetch_array($result))  
     {  
     ?>  
      <tr>  
         <td><?php echo $row["emp_id"]; ?></td>  
         <td><?php echo $row["emp_name"]; ?></td>  
         <td><?php echo $row["emp_gender"]; ?></td>  
         <td><?php echo $row["emp_email"]; ?></td>  
         <td><?php echo $row["emp_mobile"]; ?></td>
         <td><?php echo $row["emp_address"]; ?></td>
         <td><?php echo $row["emp_join_date"]; ?></td>
      </tr>  
     <?php  
     }  
     ?>  
     </table> 
  </body>  
 </html>

Step 7.   Create employee_export.php file, code shown below

<?php

require_once('config.php');

 $file_type = "vnd.ms-excel";
 $file_ending = "xls";

 HEADER("Content-Disposition: attachment; filename=Employee Details .$file_ending");
 HEADER("Pragma: no-cache");

?>

<?php

$query="select * from employee order by emp_id ASC";
$result=  mysqli_query($conn, $query);

echo "S.No" ."\t". "Name" ."\t". "Gender" ."\t". "Email" ."\t". "Mobile No" ."\t". "Address" ."\t". "Join Date" . "\n";

$i=1; 
while ($rowtrans = mysqli_fetch_array($result))
{
$cno=$rowtrans['emp_id'];

    echo $i."\t".$rowtrans['emp_name']."\t".$rowtrans['emp_gender']."\t".$rowtrans['emp_email']."\t".$rowtrans['emp_mobile']."\t".$rowtrans['emp_address']."\t".$rowtrans['emp_join_date']."\n";

$i++; 
     
}

?>

Step 8.   Now go to browser and type localhost/tutorials/employee_add.php (here tutorilals is the root folder) and run screen shown below.


Export Data to Excel in PHP and MySQL

Step 9.   Now fill up values in required fields, screen shown below.


Export Data to Excel in PHP and MySQL

then click Submit button, screen shown below.


Export Data to Excel in PHP and MySQL

Step 10.   Now Click  View Employee Record button, screen shown below.


Export Data to Excel in PHP and MySQL


Step 11.   Now Click Download button, screen shown below.





Export Data to Excel in PHP and MySQL





                         I hope, it helped you to understand, how to Export data or records to Excel / CSV in PHP and MySql.......Thanks

About MSK TUTORIAL

Hi...I am Md Sahjad Karim and I am Engineering Graduate (B.E in Computer Science and Engineering) working as a Web Developer and write Blog Articles. http://www.msktutorial.com/ was launched on july 2016 to provide free online hints, tips, guides and tutorials related to technology(PHP, CodeIgniter, WordPress, Blogger etc). My primary goal is to provide the good, quality and easily to understand the articles to readers around the world.
    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment

Thanks For Visiting Blog...