Create a button to export database to excel file

[ryanduong]

Hi, I hope someone here can help me with this problem.

I want to create a button in my site such that when it's pressed, the site database will be exported to an excel file. The codes to export database I got works fine but when I try to merge it with html, the html got into the file as well. How can I get rid of the html?

A few ways you can do this:

file_put_contents() is probably the easiest.

[ryanduong]

can you explain more about how to use the function?

I think you're capable of searching the online php manual yourself:

http://uk3.php.net/manual/en/function.file-put-contents.php

No point in me copy and pasting it. Google is also good for doing your own research.

[chrishea]

Have a look at this:
Desktop Write

[ryanduong]

Ok here's my code:

<form name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
<input type="submit" name="export" value="Export database" /><br /><br />
</form>

// Some html code omitted

function cleanData(&$str) {
	// escape tab characters 
	$str = preg_replace("/\t/", "\\t", $str); 
	// escape new lines 
	$str = preg_replace("/\r?\n/", "\\n", $str); 
	// convert 't' and 'f' to boolean values 
	if($str == 't') $str = 'TRUE'; 
	if($str == 'f') $str = 'FALSE'; 
	// force certain number/date formats to be imported as strings 
	if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) 
		{ $str = "'$str"; } 
	// escape fields that include double quotes 
	if(strstr($str, '"')) 
		$str = '"' . str_replace('"', '""', $str) . '"'; 
} 

function export() {
        // file name for download 
        $filename = "membership_data_" . date('Ymd') . ".xls"; 
        header("Content-Disposition: attachment; filename=\"$filename\""); 
        header("Content-Type: application/vnd.ms-excel"); 

        $flag = false; 
        $result = mysql_query("SELECT * FROM table ORDER BY field") or die('Query failed!'); 
        while(false !== ($row = mysql_fetch_assoc($result))) { 
	        if(!$flag) { 
		        // display field/column names as first row 
		        echo implode("\t", array_keys($row)) . "\n"; $flag = true; 
	        }         
	        array_walk($row, 'cleanData'); 
	        echo implode("\t", array_values($row)) . "\n"; 
        } 
}

if(isset($_REQUEST['export'])) {
        export();
}

when I call the function export() to export my table to an excel file, the html codes get into the file as well and mess up the whole thing. Any idea on why that happens?

Well of course they do, you've printed them into the page.

Have your form handling file separate to the form. Send the form to that handler file.
You only need php in the handler - no html.

You can use the CSV method in MySQL.

[chrishea]

To create an Excel file you actually create an html file with a .xls suffix and Excel takes care of the rest. If you aren't getting a good result, then having html in the file isn't the problem. I haven't tried to debug your code but if the html was in quotes and Excel wans't seeing it as HTML then that probably would be a problem. Otherwise, if it is valid html, Excel should take care of it. You should examine the content of the file carefully.

[ryanduong]

I understand it now. I create a handler file and redirect the form to that file. It takes care of the problem. Thank you guys!