The page I am working on has a javascript function executed to print parts of the page. For some reason, printing in Safari, causes the window to somehow update. I say somehow, because it does not really refresh as in reload the page, but rather it starts the "rendering" of the page from start, i.e. scroll to top, flash animations start from 0, and so forth. The effect is reproduced by this fiddle: http://jsfiddle.net/fYmnB/ Clicking the print button and finishing or cancelling a print in Safari causes the screen to "go white" for a sec, which in my real website manifests itself as something "like" a reload. While running print button with, let's say, Firefox, just opens and closes the print dialogue without affecting the fiddle page in any way. Is there something with my way of calling the browsers print method that causes this, or how can it be explained - and preferably, avoided? P.S.: On my real site the same occurs with Chrome. In the ex
SELECT * INTO OUTFILE "c:/mydata.csv"
ReplyDeleteFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM my_table;
(the documentation for this is here: http://dev.mysql.com/doc/refman/5.0/en/select.html)
or:
$select = "SELECT * FROM table_name";
$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );
$fields = mysql_num_fields ( $export );
for ( $i = 0; $i < $fields; $i++ )
{
$header .= mysql_field_name( $export , $i ) . "\t";
}
while( $row = mysql_fetch_row( $export ) )
{
$line = '';
foreach( $row as $value )
{
if ( ( !isset( $value ) ) || ( $value == "" ) )
{
$value = "\t";
}
else
{
$value = str_replace( '"' , '""' , $value );
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );
if ( $data == "" )
{
$data = "\n(0) Records Found!\n";
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
Check out this question / answer. It's more concise than @Geoff's, and also uses the builtin fputcsv function.
ReplyDelete$result = $db_con->query('SELECT * FROM `some_table`');
if (!$result) die('Couldn\'t fetch records');
$num_fields = mysql_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++) {
$headers[] = mysql_field_name($result , $i);
}
$fp = fopen('php://output', 'w');
if ($fp && $result) {
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="export.csv"');
header('Pragma: no-cache');
header('Expires: 0');
fputcsv($fp, $headers);
while ($row = $result->fetch_array(MYSQLI_NUM)) {
fputcsv($fp, array_values($row));
}
die;
}
Look at the documentation regarding the SELECT ... INTO OUTFILE syntax.
ReplyDeleteSELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
If you'd like the download to be offered as a download that can be opened directly in Excel, this may work for you: (copied from an old unreleased project of mine)
ReplyDeleteThese functions setup the headers:
function setExcelContentType() {
if(headers_sent())
return false;
header('Content-type: application/vnd.ms-excel');
return true;
}
function setDownloadAsHeader($filename) {
if(headers_sent())
return false;
header('Content-disposition: attachment; filename=' . $filename);
return true;
}
This one sends a CSV to a stream using a mysql result
function csvFromResult($stream, $result, $showColumnHeaders = true) {
if($showColumnHeaders) {
$columnHeaders = array();
$nfields = mysql_num_fields($result);
for($i = 0; $i < $nfields; $i++) {
$field = mysql_fetch_field($result, $i);
$columnHeaders[] = $field->name;
}
fputcsv($stream, $columnHeaders);
}
$nrows = 0;
while($row = mysql_fetch_row($result)) {
fputcsv($stream, $row);
$nrows++;
}
return $nrows;
}
This one uses the above function to write a CSV to a file, given by $filename
function csvFileFromResult($filename, $result, $showColumnHeaders = true) {
$fp = fopen($filename, 'w');
$rc = csvFromResult($fp, $result, $showColumnHeaders);
fclose($fp);
return $rc;
}
And this is where the magic happens ;)
function csvToExcelDownloadFromResult($result, $showColumnHeaders = true, $asFilename = 'data.csv') {
setExcelContentType();
setDownloadAsHeader($asFilename);
return csvFileFromResult('php://output', $result, $showColumnHeaders);
}
For example:
$result = mysql_query("SELECT foo, bar, shazbot FROM baz WHERE boo = 'foo'");
csvToExcelDownloadFromResult($result);
// Export to CSV
ReplyDeleteif($_GET['action'] == 'export') {
$rsSearchResults = mysql_query($sql, $db) or die(mysql_error());
$out = '';
$fields = mysql_list_fields('database','table',$db);
$columns = mysql_num_fields($fields);
// Put the name of all fields
for ($i = 0; $i < $columns; $i++) {
$l=mysql_field_name($fields, $i);
$out .= '"'.$l.'",';
}
$out .="\n";
// Add all values in the table
while ($l = mysql_fetch_array($rsSearchResults)) {
for ($i = 0; $i < $columns; $i++) {
$out .='"'.$l["$i"].'",';
}
$out .="\n";
}
// Output to browser with appropriate mime type, you choose ;)
header("Content-type: text/x-csv");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=search_results.csv");
echo $out;
exit;
}
By the way, I realise I am replying about a year late to RonP's question, but it has not been answered yet, and others who found this page via Google, like me, may appreciate an answer.
ReplyDeleteIn reply to RonP's question, if you want each $row output on a separate line, you need to add the line breaks in somewhere. From memory I think these are one of two options:
\n or \r\n
The reason for the confusion is that \r\n should work on windows, but linux line breaks are slightly different, and I'm guessing it was \n, but could be wrong. google it! :)
**Edit: See this for more info on linebreaks: PHP Echo Line Breaks
From briefly looking at the code, you should replace the following line in csvFromResult():
fputcsv($stream, $row);
with:
fputcsv($stream, $row."\r\n");
If I'm wrong, please correct me :)
An update to @jrgns (with some slight syntax differences) solution.
ReplyDelete$result = mysql_query('SELECT * FROM `some_table`');
if (!$result) die('Couldn\'t fetch records');
$num_fields = mysql_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++)
{
$headers[] = mysql_field_name($result , $i);
}
$fp = fopen('php://output', 'w');
if ($fp && $result)
{
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="export.csv"');
header('Pragma: no-cache');
header('Expires: 0');
fputcsv($fp, $headers);
while ($row = mysql_fetch_row($result))
{
fputcsv($fp, array_values($row));
}
die;
}