Hi there,
In this tutorial you will learn how to export to excel using php mysql.
File Structure:
Step1: Creating the database and connection:
Create database myinfo;
use myinfo;
-- phpMyAdmin SQL Dump
-- version 4.8.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Dec 05, 2020 at 05:24 PM
-- Server version: 10.1.31-MariaDB
-- PHP Version: 7.2.4
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `myinfo`
--
-- --------------------------------------------------------
--
-- Table structure for table `user_master`
--
CREATE TABLE `user_master` (
`id` int(11) NOT NULL,
`name` varchar(60) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
`phone` varchar(10) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='my user info';
--
-- Dumping data for table `user_master`
--
INSERT INTO `user_master` (`id`, `name`, `email`, `phone`, `created_at`) VALUES
(1, 'Stephen', 'stephen@gmail.com', '1112223334', NULL),
(2, 'Saravanan', 'Sara.12@gmail.com', '1112223335', NULL),
(3, 'Rishi', 'rishi@gmail.com', '1112223336', NULL);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `user_master`
--
ALTER TABLE `user_master`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `user_master`
--
ALTER TABLE `user_master`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
To create the connection copy/paste the code below and name it config.php
config.php:
<?php
$servername = "localhost";
$username = "root";
$password = "";
try {
$conn = new PDO("mysql:host=$servername;dbname=myinfo", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
?>
Step2:Show records from database with export button
To display all data from mysql copy/paste below code to index.php
<?php
require ('config/config.php');
?>
<!DOCTYPE html>
<html>
<head>
<title></title>
<link rel="stylesheet" type="text/css" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<h2>How to Export Data to Excel in PHP</h2>
<?php
$output = '';
$sql = $conn->prepare("SELECT id, name, email, phone FROM user_master");
$sql->execute();
if ($sql->rowCount() > 0) {
$output.= '
<table class="table table-striped table-bordered table-responsive">
<tr>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
</tr>
<tbody>';
while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
$output.= '
<tr>
<td>' . $row["name"] . '</td>
<td>' . $row["email"] . '</td>
<td>' . $row["phone"] . '</td>
</tr>
';
}
$output.= '</tbody></table>';
echo $output;
}
?>
<div class="col-sm-12">
<div class="btn-group pull-right">
<a href="export.php" class="btn btn-info">Export to excel</a>
</div>
</div>
</div>
</body>
</html>
Step3: Implement excel export function
copy/paste below code to export.php
<?php
require ('config/config.php');
?>
<?php
$output = '';
$sql = $conn->prepare("SELECT id, name, email, phone FROM user_master");
$sql->execute();
if ($sql->rowCount() > 0) {
$output.= '
<table border="1">
<tr>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
</tr>
<tbody>';
while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
$output.= '
<tr>
<td>' . $row["name"] . '</td>
<td>' . $row["email"] . '</td>
<td>' . $row["phone"] . '</td>
</tr>
';
}
$output.= '</tbody></table>';
header('Content-Type: application/xls');
header('Content-Disposition: attachment; filename=download.xls');
echo $output;
}
?>
Comments
Post a Comment