databases
|mySQL to excel
export a mySQL database table to an EXCEL file.
database table dump to WORD document possible also.
full source of mySQL to excel [ line 1 - 130 ] | download mySQL to excel
| 1 | <?php |
| 2 | //EDIT YOUR MySQL Connection Info: |
| 3 | $DB_Server = "localhost"; //your MySQL Server |
| 4 | $DB_Username = ""; //your MySQL User Name |
| 5 | $DB_Password = ""; //your MySQL Password |
| 6 | $DB_DBName = ""; //your MySQL Database Name |
| 7 | $DB_TBLName = ""; //your MySQL Table Name |
| 8 | //$DB_TBLName, $DB_DBName, may also be commented out & passed to the browser |
| 9 | //as parameters in a query string, so that this code may be easily reused for |
| 10 | //any MySQL table or any MySQL database on your server |
| 11 | //DEFINE SQL QUERY: |
| 12 | //you can use just about ANY kind of select statement you want - |
| 13 | //edit this to suit your needs! |
| 14 | $sql = "Select * from $DB_TBLName"; |
| 15 | //Optional: print out title to top of Excel or Word file with Timestamp |
| 16 | //for when file was generated: |
| 17 | //set $Use_Titel = 1 to generate title, 0 not to use title |
| 18 | $Use_Title = 1; |
| 19 | //define date for title: EDIT this to create the time-format you need |
| 20 | $now_date = date('m-d-Y H:i'); |
| 21 | //define title for .doc or .xls file: EDIT this if you want |
| 22 | $title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date"; |
| 23 | /* |
| 24 | Leave the connection info below as it is: |
| 25 | just edit the above. |
| 26 | (Editing of code past this point recommended only for advanced users.) |
| 27 | */ |
| 28 | //create MySQL connection |
| 29 | $Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) |
| 30 | or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); |
| 31 | //select database |
| 32 | $Db = @mysql_select_db($DB_DBName, $Connect) |
| 33 | or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); |
| 34 | //execute query |
| 35 | $result = @mysql_query($sql,$Connect) |
| 36 | or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); |
| 37 | //if this parameter is included ($w=1), file returned will be in word format ('.doc') |
| 38 | //if parameter is not included, file returned will be in excel format ('.xls') |
| 39 | if (isset($w) && ($w==1)) |
| 40 | { |
| 41 | $file_type = "msword"; |
| 42 | $file_ending = "doc"; |
| 43 | }else { |
| 44 | $file_type = "vnd.ms-excel"; |
| 45 | $file_ending = "xls"; |
| 46 | } |
| 47 | //header info for browser: determines file type ('.doc' or '.xls') |
| 48 | header("Content-Type: application/$file_type"); |
| 49 | header("Content-Disposition: attachment; filename=database_dump.$file_ending"); |
| 50 | header("Pragma: no-cache"); |
| 51 | header("Expires: 0"); |
| 52 | /* Start of Formatting for Word or Excel */ |
| 53 | if (isset($w) && ($w==1)) //check for $w again |
| 54 | { |
| 55 | /* FORMATTING FOR WORD DOCUMENTS ('.doc') */ |
| 56 | //create title with timestamp: |
| 57 | if ($Use_Title == 1) |
| 58 | { |
| 59 | echo("$titlenn"); |
| 60 | } |
| 61 | //define separator (defines columns in excel & tabs in word) |
| 62 | $sep = "n"; //new line character |
| 63 | while($row = mysql_fetch_row($result)) |
| 64 | { |
| 65 | //set_time_limit(60); // HaRa |
| 66 | $schema_insert = ""; |
| 67 | for($j=0; $j<mysql_num_fields($result);$j++) |
| 68 | { |
| 69 | //define field names |
| 70 | $field_name = mysql_field_name($result,$j); |
| 71 | //will show name of fields |
| 72 | $schema_insert .= "$field_name:t"; |
| 73 | if(!isset($row[$j])) { |
| 74 | $schema_insert .= "NULL".$sep; |
| 75 | } |
| 76 | elseif ($row[$j] != "") { |
| 77 | $schema_insert .= "$row[$j]".$sep; |
| 78 | } |
| 79 | else { |
| 80 | $schema_insert .= "".$sep; |
| 81 | } |
| 82 | } |
| 83 | $schema_insert = str_replace($sep."$", "", $schema_insert); |
| 84 | $schema_insert .= "t"; |
| 85 | print(trim($schema_insert)); |
| 86 | //end of each mysql row |
| 87 | //creates line to separate data from each MySQL table row |
| 88 | print "n----------------------------------------------------n"; |
| 89 | } |
| 90 | }else{ |
| 91 | /* FORMATTING FOR EXCEL DOCUMENTS ('.xls') */ |
| 92 | //create title with timestamp: |
| 93 | if ($Use_Title == 1) |
| 94 | { |
| 95 | echo("$titlen"); |
| 96 | } |
| 97 | //define separator (defines columns in excel & tabs in word) |
| 98 | $sep = "t"; //tabbed character |
| 99 | //start of printing column names as names of MySQL fields |
| 100 | for ($i = 0; $i < mysql_num_fields($result); $i++) |
| 101 | { |
| 102 | echo mysql_field_name($result,$i) . "t"; |
| 103 | } |
| 104 | print("n"); |
| 105 | //end of printing column names |
| 106 | //start while loop to get data |
| 107 | while($row = mysql_fetch_row($result)) |
| 108 | { |
| 109 | //set_time_limit(60); // HaRa |
| 110 | $schema_insert = ""; |
| 111 | for($j=0; $j<mysql_num_fields($result);$j++) |
| 112 | { |
| 113 | if(!isset($row[$j])) |
| 114 | $schema_insert .= "NULL".$sep; |
| 115 | elseif ($row[$j] != "") |
| 116 | $schema_insert .= "$row[$j]".$sep; |
| 117 | else |
| 118 | $schema_insert .= "".$sep; |
| 119 | } |
| 120 | $schema_insert = str_replace($sep."$", "", $schema_insert); |
| 121 | //following fix suggested by Josue (thanks, Josue!) |
| 122 | //this corrects output in excel when table fields contain n or r |
| 123 | //these two characters are now replaced with a space |
| 124 | $schema_insert = preg_replace("/rn|nr|n|r/", " ", $schema_insert); |
| 125 | $schema_insert .= "t"; |
| 126 | print(trim($schema_insert)); |
| 127 | print "n"; |
| 128 | } |
| 129 | } |
| 130 | ?> |
7 hits by 5 users in the last 30 minutes.