How We Ca Download Data In Excel From Php Table UPDATED
How We Ca Download Data In Excel From Php Table
Database Data Export to Excel File using PHP
past Vincy. Final modified on May 18th, 2021.
Database data export is a familiar operation for many of u.s.. phpMyAdmin is the go to pick for a database client in PHP. It provides database administration tools and also allows exporting the data. The exported information can be in various formats similar SQL, CSV as selected.
When you demand a PHP script, that can consign database information (records) to an excel file, then this article will assist you. It has detailed step by pace clarification with case code. Information technology is compact and lightweight code that can be integrated in your PHP spider web applications.
Nosotros have already seen several case code for implementing database export using PHP.
Database import and export with PHP and MySQL volition give you lot a basic cognition nigh the export operation.
This export operation is for taking database backup. We can apply this fill-in afterward for various purposes. For instance, this volition assistance to reuse backup for restoring the database in the futurity.
If you want to study how to split the excel export into multiple files read the linked commodity. Information technology contains a perfect example and a downloadable source code for free.
Nosotros can also perform this consign action via programming. In PHP, the code implementation of creating a database export tool is simple.
In this example, we are going to create PHP code for taking fill-in by exporting a database.
This article will help y'all to create a PHP code to build your own database consign system.
What is within?
- About this PHP MySQL database information export example
- Pop database clients with an consign tool
- File structure
- Database structure
- HTML lawmaking to display database results with consign control
- Libraries to render tabular information with export choice
- How to perform database export with PHP and MySQL
- Output screenshot with export action response
About this PHP MySQL database data export example
This tutorial will help yous to understand how the database export to an Excel file. I used PHP and MySQL to perform this export option via programming.
We have seen an example before to write the exported data into a target CSV file using the PHP fputcsv() function. PHP provides powerful born functions for handling file operations.
I accept created a production database tabular array for this example. The landing folio will display this dynamic production data with an export option. The export control is a submit button and triggers the action on class submit.
In PHP, it handles the form submit to process consign. Kickoff, we retrieve the production results by accessing the database. And so, then nosotros pass the resultant product assortment every bit an argument to the export function.
There are two PHP classes in this example. Those are Production and DataSource. The Product form contains two functions. One is to get production results from the database and the other is to export database results.
The DataSource grade is for creating a database connection and handling queries.
Popular database clients with export tool
In that location are diverse database clients available with advanced tools to execute database operations.
In this section, let usa see how to consign from the database clients phpMyAdmin and SQLyog. It also describes the available customization provided with the client interface.
Database export using phpMyAdmin
This customer supports to select data/structure of the database, to specify format and more than. The following effigy shows the possible customization with the export tool in phpMyAdmin.
Export via SQLyog
In SQLyog, we take to choose Fill-in/Export by correct-clicking the Object browser. Then a dialogue volition show options to choose format, escape sequences, SQL options and more than.
File structure
Database Construction
This section shows the production database table and its data dump. Run the SQL script to execute this example in your development environs.
This database script is added to the downloadable source code as schema.sql.
-- -- Tabular array structure for table `tbl_products` -- CREATE TABLE `tbl_products` ( `id` int(eight) Not Nada, `proper noun` varchar(255) NOT Cipher, `price` double(x,2) Non NULL, `category` varchar(255) Not Nil, `product_image` text Not NULL, `average_rating` float(iii,1) NOT Naught ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `tbl_products` -- INSERT INTO `tbl_products` (`id`, `proper name`, `cost`, `category`, `product_image`, `average_rating`) VALUES (1, 'Tiny Handbags', 100.00, 'Fashion', 'gallery/handbag.jpeg', five.0), (2, 'Men\'s Spotter', 300.00, 'Generic', 'gallery/watch.jpeg', iv.0), (3, 'Trendy Lookout', 550.00, 'Generic', 'gallery/trendy-picket.jpeg', four.0), (4, 'Travel Bag', 820.00, 'Travel', 'gallery/travel-pocketbook.jpeg', five.0), (5, 'Plastic Ducklings', 200.00, 'Toys', 'gallery/ducklings.jpeg', 4.0), (half-dozen, 'Wooden Dolls', 290.00, 'Toys', 'gallery/wooden-dolls.jpeg', five.0), (vii, 'Advanced Camera', 600.00, 'Gadget', 'gallery/photographic camera.jpeg', four.0), (8, 'Jewel Box', 180.00, 'Fashion', 'gallery/gem-box.jpeg', v.0), (9, 'Perl Jewellery', 940.00, 'Manner', 'gallery/perls.jpeg', 5.0); -- -- Indexes for tabular array `tbl_products` -- ALTER Tabular array `tbl_products` Add together Primary Central (`id`); Change Table `tbl_products` MODIFY `id` int(eight) NOT Zip AUTO_INCREMENT, AUTO_INCREMENT=x; COMMIT;
HTML lawmaking to display database results with export command
Below code shows the HTML with the tabular production result and an consign button. The product information is dynamic from the database.
In the landing page, I remember the database results and store it into a variable. A PHP array variable holds the product database results.
For forming this product listing HTML, I embedded PHP code to iterate the production result assortment. On each iteration, the appropriate row information forms the product record in the HTML view.
This page also contains a HTML button below the product listing view to consign the data. This is a submit button enclosed in a HTML course container. Past clicking this button, a PHP endpoint will handle the form post activeness.
<?php require_once __DIR__ . './grade/Product.php'; $product = new Product(); $productResult = $product->getAllProduct(); require_once "./view/product-listing.php"; ?>
product-list.php
<html> <head> <meta name="viewport" content="width=device-width, initial-scale=1"> <link href="./view/css/style.css" type="text/css" rel="stylesheet" /> </head> <trunk> <div id="table-container"> <?php if (! empty($productResults)) { ?> <tabular array id="tab"> <thead> <tr> <th width="35%">Product Name</th> <thursday width="xx%">Price</thursday> <thursday width="25%">Category</th> <th width="20%">Average Rating</th> </tr> </thead> <tbody> <?php foreach ($productResult as $central => $value) { ?> <tr> <td><?php echo $productResult[$key]["proper name"]; ?></td> <td><?php echo $productResult[$key]["price"]; ?></td> <td><?php echo $productResult[$key]["category"]; ?></td> <td><?php echo $productResult[$key]["average_rating"]; ?></td> </tr> <?php } ?> </tbody> </tabular array> <div class="btn"> <form action="" method="mail"> <push button type="submit" id="btnExport" name='export' value="Consign to Excel" class="btn btn-info">Export to Excel</button> </form> </div> <?php } else { ?> <div grade="empty-tabular array"> <div class="svg-icon"> <svg xmlns="http://world wide web.w3.org/2000/svg" width="48" superlative="48" viewBox="0 0 24 24"><circumvolve cx="12" cy="nineteen" r="2"/><path d="M10 3h4v12h-4z"/><path fill="none" d="M0 0h24v24H0z"/></svg> </div> No records plant</div> <?php } ?> </div> </torso> </html> Beneath is the list of styles created for this example. These are some basic CSS styles which are to make the output view presentable to your the users.
body { font-size: 0.95em; font-family: arial; color: #212121; } th { background: #E6E6E6; edge-bottom: 1px solid #000000; } #tabular array-container { max-width: 850px; margin: 50px auto; } table#tab { border-collapse: plummet; width: 100%; } tabular array#tab th, table#tab td { edge: 1px solid #E0E0E0; padding: 8px 15px; text-align: left; font-size: 0.95em; } .btn { padding: 8px 4px 8px 1px; } #btnExport { padding: 10px 40px; background: #499a49; border: #499249 1px solid; colour: #ffffff; font-size: 0.9em; cursor: pointer; } .empty-table { border: #e4e4e4 1px solid; padding: 20px; background: #F0F0F0; text-marshal: eye; font-size: i.5em; colour: #CCC; } .svg-icon { fill: #CCC; margin-bottom: 20px; } .svg-icon svg { background: #e2e2e2; edge-radius: 50%; padding: 10px; } Libraries to render tabular data with export option
This is an example of edifice a custom code to create data with consign pick. At that place are pop libraries supports data handling with import, export, CRUD action controls.
For case, Datatables is ane of the almost pop libraries support advanced data handling. In a previous example, we accept seen how to render Datatables and enable export on the interface. Data tables provide to cull the export format among CSV, Excel, PDF and more.
How to perform database export with PHP and MySQL
This is the PHP code that handles the export subsequently form submit. This PHP lawmaking applies condition on the course mail service data to check whether it is clicked or not.
If the condition returns truthful then the PHP code will invoke the consign function.
<?php namespace Phppot; apply \Phppot\Product; require_once __DIR__ . './class/Product.php'; $product = new Product(); if (isset($_POST["export"])) { $product->exportProductDatabase($productResult); } The exportProductDatabase() is at the Product form. It receives the array of product results as its parameter.
In this consign lawmaking, it iterates the production array to output the file with the data.
The product array keys contain the column names. For the get-go fourth dimension, the code implodes the column name array to print the Excel header.
Later on writing the header, it prints the row-data on each iteration. Earlier press I applies PHP array implode the with the comma(,).
In this code, it uses the PHP echo statement to write the header and the row data. Before that, we have to set the header with Content-Blazon and Content-Disposition
I used the PHP header role to ready these attributes to download the output excel file.
Product.php
The following code shows the Product.php course. It contains the getAllProduct() function to retrieve the database results.
In the class constructor, the DataSource instantiation returns the connexion. This object is to invoke query handlers defined in the DataSource.
<?php namespace Phppot; use \Phppot\DataSource; class Product { private $ds; function __construct() { require_once __DIR__ . './DataSource.php'; $this->ds = new DataSource(); } public function getAllProduct() { $query = "select * from tbl_products"; $event = $this->ds->select($query); render $result; } public function exportProductDatabase($productResult) { $timestamp = time(); $filename = 'Export_excel_' . $timestamp . '.xls'; header("Content-Type: awarding/vnd.ms-excel"); header("Content-Disposition: attachment; filename=\"$filename\""); $isPrintHeader = false; foreach ($productResult every bit $row) { if (! $isPrintHeader) { echo implode("\t", array_keys($row)) . "\n"; $isPrintHeader = true; } echo implode("\t", array_values($row)) . "\north"; } go out(); } } DataSource.php
This is the PHP class used to handle the database connection and to execute queries. The select() method fetches the information from the database and returns results as a PHP array.
Information technology as well contains many functions to perform insert and to get records count. This is the common class that nosotros have created for many PHP MySQL examples.
<?php namespace Phppot; /** * Generic datasource class for handling DB operations. * Uses MySqli and PreparedStatements. * * @version 2.3 */ class DataSource { // PHP 7.1.0 visibility modifiers are allowed for class constants. // when using above vii.1.0, declare the beneath constants as private const HOST = 'localhost'; const USERNAME = 'root'; const PASSWORD = ''; const DATABASENAME = 'phpsamples'; private $conn; /** * PHP implicitly takes care of cleanup for default connection types. * So no demand to worry about closing the connection. * * Singletons not required in PHP equally in that location is no * concept of shared retentiveness. * Every object lives only for a asking. * * Keeping things uncomplicated and that works! */ function __construct() { $this->conn = $this->getConnection(); } /** * If connection object is needed utilise this method and get admission to it. * Otherwise, use the beneath methods for insert / update / etc. * * @return \mysqli */ public function getConnection() { $conn = new \mysqli(cocky::HOST, self::USERNAME, cocky::PASSWORD, cocky::DATABASENAME); if (mysqli_connect_errno()) { trigger_error("Problem with connecting to database."); } $conn->set_charset("utf8"); return $conn; } /** * To go database results * @param string $query * @param string $paramType * @param array $paramArray * @render assortment */ public function select($query, $paramType="", $paramArray=array()) { $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) { $this->bindQueryParams($sql, $paramType, $paramArray); } $stmt->execute(); $event = $stmt->get_result(); if ($result->num_rows > 0) { while ($row = $result->fetch_assoc()) { $resultset[] = $row; } } if (! empty($resultset)) { return $resultset; } } /** * To insert * @param cord $query * @param string $paramType * @param array $paramArray * @render int */ public function insert($query, $paramType, $paramArray) { print $query; $stmt = $this->conn->ready($query); $this->bindQueryParams($stmt, $paramType, $paramArray); $stmt->execute(); $insertId = $stmt->insert_id; return $insertId; } /** * To execute query * @param string $query * @param string $paramType * @param array $paramArray */ public role execute($query, $paramType="", $paramArray=array()) { $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) { $this->bindQueryParams($stmt, $paramType="", $paramArray=assortment()); } $stmt->execute(); } /** * 1. Prepares parameter binding * ii. Bind prameters to the sql argument * @param string $stmt * @param cord $paramType * @param assortment $paramArray */ public office bindQueryParams($stmt, $paramType, $paramArray=array()) { $paramValueReference[] = & $paramType; for ($i = 0; $i < count($paramArray); $i ++) { $paramValueReference[] = & $paramArray[$i]; } call_user_func_array(assortment( $stmt, 'bind_param' ), $paramValueReference); } /** * To become database results * @param string $query * @param cord $paramType * @param assortment $paramArray * @render array */ public function numRows($query, $paramType="", $paramArray=array()) { $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) { $this->bindQueryParams($stmt, $paramType, $paramArray); } $stmt->execute(); $stmt->store_result(); $recordCount = $stmt->num_rows; return $recordCount; } } Download
↑ Back to Tiptop
DOWNLOAD HERE
Posted by: celiabrieser38.blogspot.com
Post a Comment for "How We Ca Download Data In Excel From Php Table UPDATED"