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 ?> |
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.








