PHP 7 Script to Export MySQL Table Rows to Excel (XLSX) File in Browser Using HTML5 & Javascript Full Project For Beginners

  • Post author:
  • Post category:PHP
  • Post comments:0 Comments

 

 

index.php

 

 

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.0/css/bootstrap.min.css" integrity="sha384-PDle/QlgIONtM1aqA2Qemk5gPOE7wFq8+Em+G/hmo5Iq0CCmYZLv3fVRDJ4MMwEA" crossorigin="anonymous">
    <title>Convert to Ecxel</title>
</head>
<body>
    <div class="container col-md-2"  style="margin-top: 30px;">
        <h3 align="center" style="margin-bottom: 20px;">Convert to Ecxel</h3>
        <form method="POST" action="lib/core.php">
            <div class="form-group "><label class="" for="datefrom">Data from:</label>
                <input type="date" name="datefrom" class="form-control" id="datefrom" required>
            </div>
            <div class="form-group">
				<div class="form-group "><label class="" for="dateto">Data to:</label>
                <input type="date" name="dateto" class="form-control" id="dateto" required>
            </div>
            <input type="submit" id="submit" 
                name="submit" class="btn btn-primary" value="Convert to XLS" />
        </form>
		<hr/>
		<p>База содержит записи с 01.02.2020 по 10.02.2020</p>
    </div>
</body>
</html>

 

 

data.sql

 

 

-- phpMyAdmin SQL Dump
-- version 4.9.0.1
-- https://www.phpmyadmin.net/
--
-- Хост: 127.0.0.1:3306
-- Время создания: Мар 05 2020 г., 19:03
-- Версия сервера: 5.7.25
-- Версия PHP: 7.3.9

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 */;

--
-- База данных: `test_filkos`
--

-- --------------------------------------------------------

--
-- Структура таблицы `orders`
--

CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  `phone` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Дамп данных таблицы `orders`
--

INSERT INTO `orders` (`id`, `order_date`, `phone`) VALUES
(1, '2020-02-01', '+79606359000'),
(2, '2020-02-02', '+79606359001'),
(3, '2020-02-03', '+79606359002'),
(4, '2020-02-04', '+79606359003'),
(5, '2020-02-05', '+79606359004'),
(6, '2020-02-06', '+79606359005'),
(7, '2020-02-07', '+79606359006'),
(8, '2020-02-08', '+79606359007'),
(9, '2020-02-09', '+79606359008'),
(10, '2020-02-10', '+79606359009');

--
-- Индексы сохранённых таблиц
--

--
-- Индексы таблицы `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT для сохранённых таблиц
--

--
-- AUTO_INCREMENT для таблицы `orders`
--
ALTER TABLE `orders`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
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 */;

 

See also  PHP 7 Website Alexa Rank Checker Script Using Alexa.com API Using HTML Form in Javascript Full Project For Beginners

 

lib/core.php

 

 

<?php

require_once 'db.php';

class Convert 
{
	private $dateFrom;
	private $dateTo;
	
	public function init()
	{
		$df = explode('-', $_POST['datefrom']);
		$dt = explode('-', $_POST['dateto']);

		if ( ! isset($_POST['datefrom']) || ! checkdate($df[1], $df[2], $df[0]) || 
			 ! isset($_POST['dateto']) || ! checkdate($dt[1], $dt[2], $dt[0])){
				 return false;
		}else{
			$this->dateFrom = $_POST['datefrom'];
			$this->dateTo = $_POST['dateto'];

			return true;
		}
	}
	

	public function select(){
		
		$db = new Connect;
		$output = '';	
		$data = $db->query('SELECT * FROM orders WHERE order_date BETWEEN "' . $this->dateFrom .'" AND "' . $this->dateTo .'"');
		if ($data) {
			$output .= '<table>
							<tr>
								<td>Order_date</td>
								<td>Phone</td>
							</tr>
								';
			while ($row = $data->fetch())
			{
				
				$output .= '<tr>
								<td>'.$row['order_date'].'</td>
								<td>'.$row['phone'].'</td>
							</tr>
				';					
			}
			$output .= '</table>';
		}
		$db = null;	
		
		header("Content-Type: application/xls");
		header("Content-Disposition: attachment; filename=download.xls");
		echo $output;

	}
}

$convert = new Convert;
if ($convert->init()){
	$convert->select();
			
			

}

 

 

lib/db.php

 

 

<?php

class Connect extends PDO 
{
	public function __construct()
	{
		parent::__construct("mysql:host=localhost;dbname=test_filkos", 'root', '',
			array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
	}
}

 

Leave a Reply