Export MySQL database to an MS Excel format

Here is an updated version of the script I had at PHP-Help.net which exports a specified MySQL table. After couple of requests I got, I made some changes to the script to download all the tables of a specific database. Let me know if u catch any bugs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
<?php
#Name of the Database to export can be sent via GET variable called 'db'
$dbToExport=(isset($_GET["db"]))?$_GET["db"]:"test";
 
mysql_connect("localhost","root","******") or die("Connection to MySQL failed");
mysql_select_db($dbToExport) or die("Couldn't connect to DB");
 
#Get all tables in the database
$mTblQuery="show tables";
$mTblResult=mysql_query($mTblQuery);
 
$dataStr="";
#Loop through the table names
while($tblRow=mysql_fetch_assoc($mTblResult)){
	#Store output of the table name
	$dataStr.="Table : \t".$tblRow["Tables_in_".$dbToExport]."\r\n";
 
	#Select all records from the table
	$mQuery="select * from `".$tblRow["Tables_in_".$dbToExport]."`";	
	$mResult=mysql_query($mQuery);
 
	#Get no of fields in the table
	$numFields=mysql_num_fields($mResult) or die(mysql_error());
 
	#Get all fields in the table
	$tblFields=array();
	for($i=0;$i<$numFields;$i++){
		$tblFields[]=mysql_field_name($mResult,$i);
	}
 
	#Store output of fieldnames
	$dataStr.=implode("\t",$tblFields);
	$dataStr.="\r\n";
 
	#Store output of all the records
	while($row=mysql_fetch_assoc($mResult)){
		$rec=array();
		foreach($tblFields as $tblField){
			$recData=str_replace("\r\n"," ",$row[$tblField]);
			$recData=str_replace("\n"," ",$recData);
			$recData=str_replace("\t"," ",$recData);
 
			$rec[]=$recData;
		}
		$dataStr.=implode("\t",$rec);
		$dataStr.="\r\n";
	}
	$dataStr.="\r\n";
	$dataStr.="\r\n";
}
 
#Force the browser to download the file
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=export_$dbToExport.xls");
header("Pragma: no-cache");
header("Expires: 0");
 
echo $dataStr;//Display Stored Output
?>
Share and Enjoy:
  • Twitter
  • Facebook
  • Digg
  • del.icio.us
  • FriendFeed
  • Google Bookmarks
  • Technorati
General

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Leave Comment

(required)

(required)


Spam protection by WP Captcha-Free