I'm sure its been done to death, but here it is. Simple and it allows input of a table name for the user to export...
Turn off all error reporting
error_reporting(0);
db conn stuff
mysql_connect('host', 'user', 'passwrd') or die ("Error connecting to Database");
<?php
if (!$_POST['submit']){
show_form();
}else{
$table = @$_POST['mytable'];
if (!eregi("[a-z0-9_]{1,64}",$table)){
show_form();
die("disallowed_table name. if your tables names contain other characters, consider renaming them.");
}//end if
require("local_conn.php");
$sql = "select * from $table ";
if (!($conn2=mysql_connect($host, $username, $pwd))) {
printf("error connecting to DB by user = $username and pwd=$pwd");
exit();
}
$db2=mysql_select_db($dbname,$conn2) or die("Unable to connect to local database
$result = mysql_query($sql) or die ("Can't complete query because ".mysql_error());
if (($result)&&(mysql_num_rows($result)>1)){
return $result;
}else{
echo "No Results found. Check your table name";
die();
}
if (($result) && (mysql_num_rows($result)>0)){
//set the path and file name
//$dir="/path/to/file/";
$filename = 'test.txt';
if (!$handle = fopen($filename, 'w')) {
echo "Cannot open file ($filename)";
exit;
}
while ($rows = mysql_fetch_array($result)){
$fields = mysql_num_fields($result);
$content = "";
//calc the length of the line (number of elements)
for($x = 0; $x < $fields-1; $x++){
$content .= "" . $rows[$x] . " | ";
}//next
$content = substr($content, 0, strlen($content)-2). "\n"; //remove the last pipe as its not needed and adds the new line character
// Write $content to our opened file.
if (fwrite($handle, $content) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}
}//end while
fclose($handle);
//call the function to force the download the users computer
force_download($filename);
}//end if
}//end if
function force_download($file)
{
//$dir="/path/to/file/";
if (isset($file)) {
header("Content-type: application/force-download");
header('Content-Disposition: inline; filename="' . $file . '"');
header("Content-Transfer-Encoding: Binary");
header("Content-length: ".filesize($file));
header('Content-Type: application/octet-stream');
//header("Content-disposition: attachment; filename="".basename($file).""");
header('Content-Disposition: attachment; filename="' . $file . '"');
readfile("$file");
} else {
echo "No file selected";
} //end if
}//end function
function show_form()
{
echo "<html><body><h2> Table Data Exporter </h2>
<h4>Creates a pipe delimited file of the table contents.</h4>
<br><br><form name=myform action=".$_SERVER['PHP_SELF']." method=post>
Table Name:<input type=text name=mytable>
<input type=submit name=submit value=Submit>
</form></body></html>";
Ron Rutten wrote :1156
typo-error at line 25: missing "); after database
$db2=mysql_select_db($dbname,$conn2) or die("Unable to connect to local database");
matthew waygood wrote :1158
You should avoid saving the data to a single file. What if 2 people execute the script just after each other? the first person may get the second persons information. You would be better to either use a random filename (perhaps using microtime) and delete the file afterwards or just keep the data in memory and output it directly to the browser.
bastien koert wrote :1161
Hi Matt,
Your point is entirely valid. And in a normal operating environment I would do exactly that. However, the user that I worked on this for only needed the one file...
Like anything written here, its all adaptable and customizable to suit the users desired results.
