Exporting data via php/mysql 1

[mrmtek]

On our web site I have to setup a export function on page driven by php with a mysql backend is it possible to export data via sql? for example:

$sql="SELECT a,b,a+b INTO OUTFILE 'c:\result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
FROM test_table;";

and execute the sql, can this be done?? at all??

any pointers would be most welcome.

 

[ingresman]

Have you tried it ?

 

[mrmtek]

yes can't select where it is to output the data, I was think of just opening a new page and outputting the data on the page and getting the user to save the file to a folder on there computer.

 

[caffrinho]

The following code runs an sql query that i've stored in $sql and outputs it as an xls file.
Could this be of any use?

$sql= "SELECT * FROM table";
$export = mysql_query($sql, $conn) or die (mysql_error());
$fields = mysql_num_fields($export);

for($i=0; $i<$fields; $i++){
   $header .= mysql_field_name($export, $i)."\t";
}

$row=mysql_fetch_array($export);

do{
   $line='';
   for($i=0; $i<$fields; $i++){
	        if((!isset($row[$i])) OR ($row[$i] =="")){
	            $value = "\t";
	        }else{
	            $value = str_replace('"','""', $row[$i]);
	            $value = '"'.$value.'"'."\t";
	        }
	    $line .=$value;
	 }
	 $data .= trim($line)."\n";

}while($row=mysql_fetch_array($export));

$data = str_replace("\r","",$data);

if($data==""){
	$data = "\n(0) Records Found!\n";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=results.xls");
header("Pragma: no-cache");
header("Expires: 0");

print $header."\n".$data;

 

[mrmtek]

thank you, you pointed me in the correct direction,
I am most impressed.