Export mysql results to CSV from PHP website

[labhijitp]

I have written a small query. I want to create link on my php site which on click will run this script automatically and export results to an excel file. I dont want to create a page to view the results.

Any help to this new bie will be very useful. I am not well conversed with PHP :( just know a little bit of mysql.

[vibhaJ]

This is simple code for exporting student table to excel.
Hope this helps you.

<?
	mysql_connect('localhost','root','');
	mysql_select_db('test');
	
	$sql = "select name,sirname from student";

	// Query Database
	$result=mysql_query($sql);
	$filename = 'file.xls';
		
	// Send Header
	header("Pragma: public");
	header("Expires: 0");
	header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
	header("Content-Type: application/force-download");
	header("Content-Type: application/octet-stream");
	header("Content-Type: application/download");;
	header("Content-Disposition: attachment;filename=$filename");
	header("Content-Transfer-Encoding: binary ");
	
	// XLS Data Cell
	
	xlsBOF();	  
	xlsWriteLabel(0,0,"Name : ");
	xlsWriteLabel(0,1,"Sirname :");
   
	$xlsRow = 1;
	while(list($name,$sirname)=mysql_fetch_row($result)) 
	{		
		++$i;
		xlsWriteLabel($xlsRow,0,"$name");
		xlsWriteLabel($xlsRow,1,"$sirname");                         
		$xlsRow++;
	}
	xlsEOF();
	exit();

function xlsBOF() {
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);  
    return;
}

function xlsEOF() {
    echo pack("ss", 0x0A, 0x00);
    return;
}

function xlsWriteNumber($Row, $Col, $Value) {
    echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
    echo pack("d", $Value);
    return;
}

function xlsWriteLabel($Row, $Col, $Value ) {
    $L = strlen($Value);
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
    echo $Value;
return;
} 

?>

[labhijitp]

Thanks vibha. I see the below error message:

Excel found unreadable content in file.xls

[vibhaJ]

This is because you may have incorrect data in query output.
comment all header lines and check mysql_fetch_row is returning proper output or not?
Also check all list function arguments.

while(list($name,$sirname)=mysql_fetch_row($result))

[labhijitp]

new error:

UserName : LearnerName :
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in path\\customreport.php on line 29

Also, can you explain me what i is xlsBOF() ann xlsEOF()? Do i need to modify it? If yes, how?

[vibhaJ]

Export your table structure and post it here.
I will give you code.

[rpv_sen]

Hi

If interested please try the below code for csv

<?php
  //
  // establish database connection
  //
  $conn = mysql_connect( 'hostname', 'username', 'password' ) or die( mysql_error( ) );
  mysql_select_db( 'dbname', $conn ) or die( mysql_error( $conn ) );
  //
  // execute sql query
  //
  $query = sprintf( 'SELECT * FROM tablename' );
  $result = mysql_query( $query, $conn ) or die( mysql_error( $conn ) );
  //
  // send response headers to the browser
  // following headers instruct the browser to treat the data as a csv file called export.csv
  //
  header( 'Content-Type: text/csv' );
  header( 'Content-Disposition: attachment;filename=filename.csv' );
  //
  // output header row (if atleast one row exists)
  //
  $row = mysql_fetch_assoc( $result );
  if ( $row )
  {
    echocsv( array_keys( $row ) );
  }
  //
  // output data rows (if atleast one row exists)
  //
  while ( $row )
  {
    echocsv( $row );
    $row = mysql_fetch_assoc( $result );
  }
  //
  // echocsv function
  //
  // echo the input array as csv data maintaining consistency with most CSV implementations
  // * uses double-quotes as enclosure when necessary
  // * uses double double-quotes to escape double-quotes 
  // * uses CRLF as a line separator
  //
  function echocsv( $fields )
  {
    $separator = '';
    foreach ( $fields as $field )
    {
      if ( preg_match( '/\\r|\\n|,|"/', $field ) )
      {
        $field = '"' . str_replace( '"', '""', $field ) . '"';
      }
      echo $separator . $field;
      $separator = ',';
    }
    echo "\r\n";
  }
?>

[labhijitp]

The CSV one worked. Thanks a lot!!

Abhijit

[rpv_sen]

Hi

thanks for your reply

pls marked the thread as solved

[leardas]

I tried using the CSV code, but when I navigate to the page with the code it's just blank and doesn't download anything? Is my browser supposed to just automatically download the CSV or what?