Home » Support » Help Articles » Export Records from MySQL Database Table to Excel Sheet using PHP

Export Records from MySQL Database Table to Excel Sheet using PHP

Back to Help Articles Home


In this article, we shall guide you how to export records from a MySQL database table to an excel spreadsheet using PHP.

Step 1: Connect the Database

Use the following code to connect to the databse.

$sDBUser = 'XXXX'; //Database Username
$sDBPwd = 'XXXX'; //Database Password
$sDBName = 'XXXX'; //Database Name

$con = mysqli_connect("localhost",$sDBUser,$sDBPwd,$sDBName); //Open a connection to a MySQL Server

Step 2: Create a Function to Export Records

Use the following php code in your class file to export records.

function exportRecords()
{
	$sTablename = 'app_student'; //Name of Database table from which records need to be exported
	$sOutput = ''; //Variable declared to store the exported records
	
	$sSelectQry = mysqli_query($con,"SELECT * FROM $sTablename");//Select all columns from database table app_student
	
	$iTotalColumns = mysqli_num_fields($sSelectQry);//Get number of fields or columns in result
	
	for ($i = 0; $i < $iTotalColumns; $i++) 
	{
		$sHeading =	mysqli_fetch_field($sSelectQry, $i);//Get the name of the specified field or column in a result
		$sOutput .= '"'.$sHeading.'",';
	}
	$sOutput .="\n";//Create a new line after all fields or columns are added
	
	while ($sRow = mysqli_fetch_assoc($sSelectQry)) //Fetch a result row as an associative array, a numeric array, or both
	{
		for ($i = 0; $i < $iTotalColumns; $i++) 
		{
			$sOutput .='"'.$sRow["$i"].'",';
		}
		$sOutput .="\n";
	}
	
	//Create a csv file and write the output values
	$sFileName = 'student-list.csv';
		
	if(!$sHandle = fopen("{$sFileName}", 'w+'))
	{
		echo "Cannot open file $sFileName";
		exit();
	}
	if(fwrite($sHandle, $sOutput) === FALSE) 
	{
   		echo "Cannot write to file $sFileName";
   		exit();
	}
	fclose($sHandle);
	rename($sFileName, "student-list.xls");//Rename the csv file to excel file
}



Updated on Aug 31, 2016

The techReview is an online magazine by Batoi and publishes articles on current trends in technologies across different industry verticals and areas of research. The objective of the online magazine to provide an insight into cutting-edge technologies in their evolution from labs to market.

Visit techReview


English - IN (USD)
New Users? Signup.     Existing Users? Login.