we have page created query single table plain "select * <table_name>;" , provide output in xsl format. code works fine in our dev environment fails in prod.
<?php require_once('connections/cnca.php'); //create query select data table $select = "select * fevisitdetails "; //run mysql query , count number of fields $export = mysql_query ( $select ) or die ( "sql error : " . mysql_error( ) ); $fields = mysql_num_fields ( $export ); //create csv header row, contain table headers //with database field names ( $i = 0; $i < $fields; $i++ ) { $header = mysql_field_name( $export , $i ) . ","; } //this of work done. //loop through query results, , create //a row each while( $row = mysql_fetch_row( $export ) ) { $line = ''; //for each field in row foreach( $row $value ) { //if null, create blank field if ( ( !isset( $value ) ) || ( $value == "" ) ){ $value = "\t"; } //else, assign field value our data else { $value = str_replace( '"' , '""' , $value ); $value = '"' . $value . '"' . "\t"; } //add field value our row $line .= $value; } //trim whitespace each row $data .= trim( $line ) . "\n"; } //remove carriage returns data $data = str_replace( "\r" , "" , $data ); //create file , send browser user download header("content-type: application/octet-stream"); header("content-disposition: attachment; filename=fedetails.xls"); header("pragma: no-cache"); header("expires: 0"); print "$header\n$data";exit; ?>do think need change parameter mysql/php going?
pls help...
as alternative original code seemed fail in production might able make use of following. uses mysqli connection & functions - ( mysql_* family of functions deprecated advised not continue using them anyway ) , has several advantages on mysql functions ~ instance fetch_fields()
rather generating , concatenating complex string use fputcsv - designed job should save many headaches.
i cannot guarantee resolve issues faced works me in test , generates 5000 row csv file quickly.
ob_clean(); /* edit db connection details */ $dbhost = 'xxx'; $dbuser = 'xxx'; $dbpwd = 'xxx'; $dbname = 'xxx'; /* safer & simpler mysqli */ $conn = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname ); $sql="select * `fevisitdetails`;"; $res=$conn->query( $sql ); if( $res ){ $headers=array(); /* create temporary file */ $tmp = tempnam( sys_get_temp_dir(), 'csv' ); $out = fopen( $tmp, 'w'); /* fetch array of objects - columns */ $fields = $res->fetch_fields(); /* column / field names */ foreach( $fields $field ) $headers[]=$field->name; /* add column headers */ fputcsv( $out, $headers ); /* add each row ( array ) csv */ while( $row=$res->fetch_array( mysqli_assoc ) ){ fputcsv( $out, $row ); } /* close file handle */ fclose( $out ); /* set headers , send csv data ~ works better `.csv` extension */ header("content-type: application/octet-stream"); header("content-disposition: attachment; filename=fedetails.csv"); header("pragma: no-cache"); header("expires: 0"); /* echo data csv */ echo file_get_contents( $tmp ); @unlink( $tmp ); } /* close db conn */ $conn->close(); exit(); 
Comments
Post a Comment