How To Export Data From MySQL To Excel Using PHP

Sometimes we need export data from MySQL table. If you manage a corporate website then you need to export data. If you want to export data from MySQL table then you should choice how you read these data after exporting. I recommended using excel to read exported data. So now the point is how you export data from a specific table from MySQL? It's pretty easy to export data. Let's talk about this. You can export data from MySQL to CSV and you can also export data from MySQL to Excel. Let's see whole processes. First config database for that from where we export data. For database configuration, we are going to create a PHP file name as db.class.php then write down this codes into this file.
db.class.php

<?php
class DBactions {

public function __construct() {
$conn= mysqli_connect('dbhost','dbuser','dbpassword','dbname');
if(!$conn){@die('MySQL error!');}else{$this->conn = $conn;}
}
public function runQuery($Query)
{
$result = mysqli_query($this->conn,$Query);
while($row=mysqli_fetch_assoc($result)) {
$resultset[] = $row;
}
if(!empty($resultset))
return $resultset;
}
public function runAssoc($Query)
{
$query = mysqli_query($this->conn,$Query) or die(mysqli_error($this->conn));
$Assoc = mysqli_fetch_assoc($query);
return $Assoc;
}

public function rows($Query)
{
if ($result=mysqli_query($this->conn,$Query))
{
$rowcount=mysqli_num_rows($result);
return $rowcount;
mysqli_free_result($result);
}
}

public function query($Query)
{

if ($query = mysqli_query($this->conn,$Query))
{
return $query;
}
else {return mysqli_error($this->conn);}
}


}
?>
Change configurations details like dbname as database name, dbuser as your database user, dbpass as database password and dbhost as database host. If you complete this task you first step has completed. Now we are going to the next step where we export our database specific table using PHP. We have done it in a different file name as export.php. So, create a PHP file name as export.php and write down these code into this file.
export.php

<?php
@session_start();
require_once("db.class.php");
$db = new DBactions();
$DB_TBLName = "tablename";
$filename = "mydata-".date('d-m-y_H:i:s');
$query = "Select * from $DB_TBLName";
$found = $db->rows($query);
$file_ending = "csv";
header("Content-Type: text/csv");
header("Content-Disposition: attachment; filename=$filename.csv");
header("Pragma: no-cache");
header("Expires: 0");
$sep = "\t";
for ($i = 0; $i < mysqli_num_fields($result); $i++) {
echo mysqli_fetch_field($result,$i) . "\t";
}
print("
");

while($row = mysqli_fetch_row($result))
{
$schema_insert = "";
for($j=0; $j<mysqli_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert = preg_replace("/
|
\r|
|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "
";
}
?>
That is the code. You can customize this code as like you want. Here is a variable name $DB_TBLName you should replace it and set the name of your specific data where from you want to export data. We have used table name test change it as your table name. Now input some data in this table and run this script. This script will export data as CSV format. If you want to change destination file format that means if you want to export your database data in another format then you should change header information and file extension name. If you want to export database table data into Xls format then you should configure your file header like it.

header("Content-Type: application/vnd.ms-excel; charset=utf-8"); 
header("Content-Disposition: attachment; filename=$filename.xls");
header("Pragma: no-cache");
header("Expires: 0");
When you use this configuration and execute this script then this script will export you database table data into a Xls file. This is the script. Isn't it easy?
If you think this script will useful for you just customize and start using. If you like this article then please share this with others.

If you have any question or comment about this Export Data From MySQL To Excel Using PHP article please don't hesitate to do that, please comment your question in comment section. We always appreciate valuable comments.






Search on PHPAns
Subscribe to PHPAns
Join with our 32000+ subscribers and get our latest articles in your email inbox for free.
Delivered by FeedBurner
We always respect your privacy and take protecting it seriously.
Connect Social With PHPAns
Top