Export Mysql Data To Excel Using PHP

In this post we will learn how to export mysql data into excel using PHP. There are many uses of this type of functionality i.e. we can take backup on daily basis , can transfer data , export data from one source and import into another source.

Step 1 -> Create User Table.

CREATE TABLE user( id int AUTO_INCREMENT PRIMARY KEY, name VARCHAR(250) , mobileno bigint(10), email VARCHAR(50), reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )

Step 2 – > Insert Some Data Into Tabe.

INSERT INTO user(name,email,mobileno)VALUES ('test','test@gmail.com', '788768'), ('test1', 'test1@gmail.com', '788778')

Step 3 -> Write Php code to export data.

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment;Filename=report.xls"); 
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "testDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT *FROM user";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
echo "ID" ."\t" ."Name"."\t"."Email"."\t"."Mobile No"."\t"."\n";
    while($row = $result->fetch_assoc()) {
        echo $row['id']."\t".$row['name']."\t". 
       $row['email']."\t".$row['mobno']."\n";
    }
}