Php export data from mysql to excel : We sometimes need a script in php which can export the mysql table’s data into csv, excel or in some other format. Here in this tutorial we are going to create a script which will export the table’s data into excel file. In this tutorial we will go step by step with example and demo. You can download the complete script free.

Php export data from mysql to excel

Here are steps to export data from mysql to excel using PHP Script.

Table

First specify the table from which you want to export data in excel. Here we have created a script to create table in mysql. First of all please run the below script to create the table and insert the sample data so that we can proceed for php code which will pull data from this table and export it into the excel file.

CREATE TABLE IF NOT EXISTS `test_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8;

--
-- Dumping data for table `test_user`
--

INSERT INTO `test_user` (`id`, `name`, `email`, `phone`) VALUES
(1, 'John', 'johnk@yopemail.com', '121323232'),
(2, 'Kelly', 'kellyk@yopemail.com', '121212122'),
(3, 'Ryan', 'ryank@yopemail.com', '22222212'),
(4, 'Kojo', 'kojao@yopemail.com', '12144444'),
(5, 'Kinjal', 'kinjal@yopemail.com', '22555212'),
(6, 'Jonny', 'jonny@yopemail.com', '121334444'),
(7, 'Barak', 'barak@yopemail.com', '2444444512'),
(8, 'Uman', 'uman@yopemail.com', '12334444');

After running the above query in mysql you will have a table named test_user with above sample data.

Php Script To Export Data from Mysql Table to Excel File

Here we have created simple script to export data from mysql table to excel file-

		';
		$no++;
	}
	?>

    

    	
NO.
		
NAME
		
EMAIL
		
PHONE
	
	
			
'.$no.'
			
'.$data['name'].'
			
'.$data['email'].'
			
'.$data['phone'].'
		

Headers header(“Content-type: application/vnd-ms-excel”); & header(“Content-Disposition: attachment; filename=”.$fileName.”.xls”); are used to download convert the plain data in excel format and save it.

You can try and download the full code from the above link provided along with the try it button.

PHP script to detect mobile devices : You can detect the mobile devices using the php script. Here is an example of getting mobile devices which uses user agent and pattern to match the mobile device.

PHP script to detect mobile devices

Php function to get mobile devices –

function isMobileDevice() {
$match = preg_match("/(android|avantgo|blackberry|bolt|boost|cricket|docomo|fone|hiptop|mini|mobi|palm|phone|pie|tablet|up\.browser|up\.link|webos|wos)/i", $_SERVER["HTTP_USER_AGENT"]);
	return $match;
}

The function will return true if mobile device found else it will return false.

Php 5 String Functions Referencee : Here is the list of Php 5 String Functions

Php 5 String Functions Reference

[table caption=”Php 5 String FUnctions” width=”100%” colwidth=”20|100|200|200″ colalign=”left|left|left|left|left”]
no,Function,Description,Detail,Demo
1,addcslashes(),It Adds the backslash before the specified string,More Detail & Demo», Demo »
2,addslashes(),It adds backslash(\) before each single quote(‘) and double quote(“) in string,More Detail & Demo», Demo »
3,bin2hex(),bin2hex() converts the binary string to hexadecimal string,More Detail & Demo», Demo »
4,chop(),used to remove the whitespaces or predefined character from right,More Detail & Demo», Demo »
5,chr(),is used to convert an ASCII number to character,More Detail & Demo», Demo »
6,chunk_split(),chunk_split() Function splits the string in series of smaller parts.,More Detail & Demo», Demo »
7,convert_cyr_string(),Is Used to convert one character set to another character set.,More Detail & Demo», Demo »
8,convert_uuencode(),convert_uuencode() is used to encode a string using the uuencode algorithm.,More Detail & Demo», Demo »
9,convert_uudecode(),It is used to decode a string using the uudecode algorithm.,More Detail & Demo», Demo »
10,count_chars(),count_chars() Function splits the string in series of smaller parts.,More Detail & Demo», Demo »
11,crc32(),crc32() It creates the 32-Bit Cyclic redundancy checksum of a string.,More Detail & Demo», Demo »
12,crypt(),crypt() It encrypts the string using the DES Blowfish or MD5 algorithms.,More Detail & Demo», Demo »
13,echo(),echo() It Echo function is used to print the strings in php..,More Detail & Demo», Demo »
14,explode(),explode() converts string to array.,More Detail & Demo», Demo »
15,html_entity_decode(),html_entity_decode() It converts the html entities to its applicable characters,More Detail & Demo», Demo »
16,htmlspecialchars_decode(),htmlspecialchars_decode() It converts Some HTML entities to its characters equivalent.,More Detail & Demo», Demo »
17,htmlspecialchars(),htmlspecialchars() It converts Special Characters to HTML entities.,More Detail & Demo», Demo »
18,Implode(),htmlspecialchars_decode() It converts array into string.,More Detail & Demo», Demo »
19,number_format(),This function is used to format a number with grouped thousand.,More Detail & Demo», Demo »
20,number_format(),Is used to pad a string to new length with another string.,More Detail & Demo», Demo »
[/table]

Php implode array by newline break

$array = implode(PHP_EOL, $array);

Which will break the string at the break line.

Php explode array at line break Example

$array = array('a','b','c');
$string = explode(PHP_EOL, $array);
echo $string;
// will produce 
// a
// b
// c

Php explode string by newline break

$array = explode(PHP_EOL, $string);

Which will break the string at the break line.

Php explode array at line break Example

$string = " str1 
            str2
            str3";
$array = explode(PHP_EOL, $string);
print_r($array);
array('0'=>'str1','1'=>'str2','2'=>'str3');

Php ini_set display_errors
You can eanble error reporting in php as below :
Add the following code in the top of your file.