Exporting to Excel using PHP

[DANDARSMASH]

Hello all-

I'm trying to export to excel from a php mysql_query. It opens excel, but all i get is "  " in Cell A1.

<?php
$str='select d.emailaddress, d.fullname, d.address, d.city, d.state, d.zip, d.makepayabletoname, d.makepayabletoaddress, d.makepayabletocity, d.makepayabletostate, d.makepayabletozip, p.filenumber, p.paymentstatus, p.paymentamount,p.paymentdate from payments p, dbase d where p.filenumber=d.filenumber and p.paymentstatus="PENDING" and p.paymentdate = "'.$date2.'"';
$arr=array('Payment Date','File Number','Payment Status','Payment Amount','E-mail Address','Full Name','Address','City','State','Zip','Payable Name','Payable Address','Payable City','Payable State','Payable Zip');
?>
 
<form action="export.php" method="post">
<input type="hidden" name="csv" value="yes" />
<input type="hidden" name="query" value="<? echo($str) ?>" />
<?
foreach ($arr as $key => $value)
{
echo '<input type="hidden" name="'.arr[$key].'" value="'.$value.'">\n';
}
?>
<input type="image" src="csv.jpg" width="35" height="35" />
</form>

With the export.php file:

<?
include('config.php');// database connectivity code
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data.csv');
$fp = fopen('php://output', 'w');
fputcsv($fp, $_POST['arr']);
$query=stripslashes($_POST['query']);
$q1=mysql_query($query)or die(mysql_error());
while ($row1 = mysql_fetch_assoc($q1)) fputcsv($fp, $row1);
?>

And config.php

<?php
// Connection's Parameters
$db_host="xxxxx";
$db_name="xxxxx";
$username="root";
$password="xxxxx";
$db_con=mysql_connect($db_host,$username,$password);
$connection_string=mysql_select_db($db_name);
// Connection
mysql_connect($db_host,$username,$password);
mysql_select_db($db_name);
?>

 

[jpadie]

remember to close the output connection

fclose ($fp);

I assume that you have echo'd the query ($query) and made sure it is what you expect? stripslashes is rarely important these days, assuming you have sensibly kept your php install upgraded and turned by magic quotes. remember too to ensure that all user supplied variables are fully escaped before use. the idea of uploading a query and running it as is is quite a security risk.

 

[DANDARSMASH]

I checked the query in mySQL workbench, it works fine. That is passed as a value for 'query'. I removed the stripslashes() because it didn't seem necessary. Still getting nothing in the excel export except "

 

[jpadie]

send the text to the screen instead and see what appears.

<?
include('config.php');// database connectivity code
//header('Content-Type: text/csv; charset=utf-8');
//[s][/s]header('Content-Disposition: attachment; filename=data.csv');
$fp = fopen('php://output', 'w');
fputcsv($fp, $_POST['arr']);
$query=stripslashes($_POST['query']);
$q1=mysql_query($query)or die(mysql_error());
while ($row1 = mysql_fetch_assoc($q1)) fputcsv($fp, $row1);
?>

 

[DANDARSMASH]

ugh... OK, it turns out that i was referencing a different export.php file that was already in the directory. This export.php gives me a whitescreen both with and without printing to excel.

<table width="300" border="0"><tr><td><img src="

http://3dgstudios.com/images/gorilla.jpg"></td><td>"Any

sufficiently advanced technology is indistinguishable from magic" [Arthur C. Clark]</td></tr></table>

 

[jpadie]

make sure you have error display and error reporting turned on

 

[spamjim]

Run your $str query through another DB tool. Your PHP may be fine. Your query may be simply F.A.D.

There are some odd quotation marks at the end of it.

 

[DANDARSMASH]

OK, I've found an even simpler way to do this. I can send an SQL query directly to a CSV file with the following PHP code. Problem is, I can't seem to get the syntax right. the lines ESCAPED BY '\\' and LINES TERMINATED BY '\n' have some special characters that i do not know how to escape. I'm getting the unexpected T_CONSTANT_ESCAPED_STRING error.

<?
$export= mysql_query("
  select d.emailaddress,
  d.fullname,
  d.address,
  d.city,
  d.state,
  d.zip,
  d.makepayabletoname,
  d.makepayabletoaddress,
  d.makepayabletocity,
  d.makepayabletostate,
  d.makepayabletozip,
  p.filenumber,
  p.paymentstatus,
  p.paymentamount,
  p.paymentdate
  INTO OUTFILE '/tmp/products.csv'
  FIELDS TERMINATED BY ','
  ENCLOSED BY '/"'
  ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
  FROM payments p, dbase d
  WHERE p.filenumber = d.filenumber
  AND p.paymentstatus = 'PENDING'
  END p.paymentdate = '".$date2."'");
?>

"Any sufficiently advanced technology is indistinguishable from magic" [Arthur C. Clark]</td></tr></table>

 

[jpadie]

try one of these instead

<?
/* EITHER */ 
$sql = <<<SQL
SELECT 		
						d.emailaddress,
			  			d.fullname,
			  			d.address,
			  			d.city,
			  			d.state,
			  			d.zip,
			  			d.makepayabletoname,
			  			d.makepayabletoaddress,
			  			d.makepayabletocity,
			  			d.makepayabletostate,
			  			d.makepayabletozip,
			  			p.filenumber,
			  			p.paymentstatus,
			  			p.paymentamount,
			  			p.paymentdate
  
INTO OUTFILE			'%s'
FIELDS TERMINATED BY 	','
ENCLOSED BY 			'"'
ESCAPED BY	 			'\'
LINES TERMINATED BY 	'\n'
FROM 					payments p
JOIN 					dbase d
ON						p.filenumber = d.filenumber
WHERE					p.paymentstatus = 'PENDING'
AND 					p.paymentdate = '%s'
SQL;

//connect to mysql database

$outfile = tmpnam( sys_get_temp_dir(), 'mysql_outfile_');
$query = sprintf($sql, $outfile, mysql_real_escape_string($date));
$result = mysql_query($sql);
if ($result):
	header('content-disposition: attachment; filename="mysqloutfile.csv"');
	header('content-type: application/vnd.ms-excel');
	header('content-length:' . filesize($outfile));
	readfile($outfile);
	@unlink($outfile);
	die;
endif;
echo 'problem';

/* 	OR	*/
ini_set('display_errors', true);
error_reporting(E_ALL);
$sql = <<<SQL
SELECT 		
						d.emailaddress,
			  			d.fullname,
			  			d.address,
			  			d.city,
			  			d.state,
			  			d.zip,
			  			d.makepayabletoname,
			  			d.makepayabletoaddress,
			  			d.makepayabletocity,
			  			d.makepayabletostate,
			  			d.makepayabletozip,
			  			p.filenumber,
			  			p.paymentstatus,
			  			p.paymentamount,
			  			p.paymentdate  
FROM 					payments p
JOIN 					dbase d
ON						p.filenumber = d.filenumber
WHERE					p.paymentstatus = 'PENDING'
AND 					p.paymentdate = '%s'
SQL;

//connect to mysql
$query = sprintf($sql, $outfile, mysql_real_escape_string($date));
$result = mysql_query($query) or die (mysql_error());
$outfile = tmpnam( sys_get_temp_dir(), 'mysql_outfile_');
$fh = fwrite($outfile, 'w') or die( 'cannot open export file ');
while ($row = mysql_fetch_assoc($result)):
	if ($header):
		fputcsv($fh, array_keys($row);
		$header = false;
	endif;
	fputcsv($fh, array_values($row));
endwhile;
fclose($fh);
if(file_exists($outfile) && is_readable($outfile)):
	header('content-disposition: attachment; filename="mysqloutfile.csv"');
	header('content-type: application/vnd.ms-excel');
	header('content-length:' . filesize($outfile));
	readfile($outfile);
	@unlink($outfile);
	die;
endif;
echo 'problem';
?>

 

[DANDARSMASH]

I was finally able to get it to work thanks to the code found Here. Thanks for all the help, I really appreciate it.

"Any sufficiently advanced technology is indistinguishable from magic" [Arthur C. Clark]</td></tr></table>

 

[jpadie]

i'm curious as to why either of the code snips I posted would not work for you. but i'm glad you have solved your issues.

 

[DANDARSMASH]

@jpadie- I tried the first one, i don't think I tried the second. I'm not saying that it didn't work, it's just that I came across a different way that worked. Thanks for the help.

"Any sufficiently advanced technology is indistinguishable from magic" [Arthur C. Clark]

 

[southbeach]

You know, I have found that simply writing a plain HTML form with all its CSS properties and instead of saving it as an HTML save it as excel (I forget the extension); excel will open it and it will look remarkably nice.

Of course, embedded formulas and macros are a totally different animal!

So, if all you're looking for is info and being able to open it in excel, give this a shot!


--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[jpadie]

yup. sending a table to excel will cause it to 'automatically' recognise the text as cells, likewise sending a csv.

assuming that the version of excel is within the last 7-10 years ( forget which was the first to support this)