help PHP code to convert a MySQL query to CSV or excel

General PHP Help
#1

trying to create a button that exports the results on the screen to a csv or excel file
im stuck ive been struggling to figure this out
searching the internet i see solutions using mysql functions , but i need to use sqlsrv_ functions instead

heres my code to display the query results on the screen (sorry if its hard to follow)
but i need help on coding the button. any help

[php]

<?php //include functions file require_once ('/includes/function.php'); //include header file include_once ('/includes/header.php'); ?>
Overview Page #form_link{display:inline;} #button_link{background:none;border:none;color:#0000FF;cursor:pointer;text-decoration:underline} <?PHP if (empty($_GET['source'])){$qsrch = "like '%'";} elseif (count($_GET['source'])>1){ $index = 1; $gsourcet = '=\''; Foreach ($_GET['source'] as $gsource){ if($index == 1){$gsourcet = $gsourcet.$gsource; } if($index > 1){ $gsourcet = $gsourcet."' or source = '".$gsource; } $index++; } $qsrch = $gsourcet."'"; } Elseif (count($_GET['source'] == 1)){ Foreach ($_GET['source'] as $gsource){ {$qsrch = "= '".$gsource."'";}} } if ($_GET['style']==''){$ssrch = "like '%'";} if ($_GET['style']!=''){$ssrch = "= '".$_GET['style']."'";} if ($_GET['cat']==''){$csrch = "like '%' ";} if ($_GET['cat']!=''){$csrch = "= '".$_GET['cat']."'";} if ($_GET['prodgen']==''){$psrch = "like '%'";} if ($_GET['prodgen']!=''){$psrch = "= '".$_GET['prodgen']."'";} if ($_GET['text']==''){$tsrch = "like '%'";} if ($_GET['text']!=''){$tsrch = "like '%".$_GET['text']."%'";} if ($_GET['eu']==''){$eusrch = "like '%'";} if ($_GET['eu']!=''){$eusrch = "like '%".$_GET['eu']."%'";} ?>
<?php

$query =“SELECT distinct ID, Source, ProductID,Summary,SubmissionDate, ProductName, ReviewText FROM Data
Left Outer join armoury
on left(data.ProductID,7) = armoury.Style
WHERE
(Data.Source “. $qsrch.”) and isnull(Data.ProductCategory,’’) “.$csrch. " and isnull(Data.ProductID,’’) “.$ssrch. " and isnull(armoury.EndUse,’’) “.$eusrch.” and Data.SubmissionDate >= '”.$_GET[‘startdate’].”’ and Data.SubmissionDate <= '”.$_GET[‘enddate’]."’ and isnull(Data.ProductGender,’’) ".$psrch. " and (isnull(Data.Summary,’’) “.$tsrch.” or isnull(Data.ReviewText,’’) ".$tsrch. " or isnull(Data.Comments,’’) ".$tsrch. “) order by data.submissiondate desc”;

if(!empty($_POST[‘query’])){
$query =$_POST[‘query’];
}

$result = exe($query);

if( $result === false)
{
echo “Error in query preparation/execution.\n”;
die( print_r( sqlsrv_errors(), true));
}
?>

div.header th {font-size: 12px;font-weight:bold; font-family:Verdana,serif} td {font-size: 12px; font-family:Verdana,serif} <?php $index=0; if(sqlsrv_num_rows($result)> 0){ echo '
'; echo "

"; echo sqlsrv_num_rows($result); echo " records returned"; echo '

'; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; $colortrigger = 0; While ($resultitem = (sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC))){ if ($colortrigger == 0){$rowbgcolor = "#808080";} elseif ($colortrigger == 1){$rowbgcolor = "#404040";} echo ''; if ($colortrigger ==0){ $colortrigger = 1;} elseif ($colortrigger ==1){ $colortrigger = 0;} $rowid =$resultitem['ID']; foreach ($resultitem as $rkey=>$value){ if ($rkey == 'SubmissionDate'){ echo ""; $index=0; echo ""; } echo "
IDSourceStyleSummary
DateProduct NameReview Text
"; echo date_format($value,'m/d/y'); } Elseif($rkey == 'ID') { echo " "; echo ' '; } Elseif($rkey =='Summary') { echo " "; if (!is_null($value)){ echo substr($value,0,100); if (strlen($value)>100){ echo '[...]'; }} } Elseif($rkey =='ReviewText') { echo " "; $str = $str ." ".strtolower($value); if (!is_null($value)){ echo substr($value,0,300); if (strlen($value)>300){ echo '[...]';}} } Else{ echo " "; echo $value; } $index++;} echo"
" ; } else{ echo "

Sorry, no records returned. Please try adjusting your search parameters."; } [/php]

#2

try this http://stackoverflow.com/questions/125113/php-code-to-convert-a-mysql-query-to-csv

#3

thanks but that uses the mysql_ , i need to use the sqlsrv_ func

#4

I use this code for mysql, it should be easy for you to change the query to sqlsrv version. The code is commented so you should be able to work out what it is doing.

[php]
//CSV export

	if (isset($_POST['csv_export']))
	{
		$select = "SELECT * FROM $fn WHERE ID > $rec_from ";

//run mysql query and then count number of fields
$export = mysql_query ( $select )
or die ( "Sql error : " . mysql_error( ) );
$fields = mysql_num_fields ( $export );

//create csv header row, to contain table headers
//with database field names
for ( $i = 0; $i < $fields; $i++ ) {
$hdr .= mysql_field_name( $export , $i ) . “,”;
}

//this is where most of the work is done.
//Loop through the query results, and create
//a row for each
while( $row = mysql_fetch_row( $export ) ) {
$line = ‘’;
//for each field in the row
foreach( $row as $value ) {
//if null, create blank field
if ( ( !isset( $value ) ) || ( $value == “” ) ){
$value = “,”;
}
//else, assign field value to our data
else {
$value = str_replace( ‘"’ , ‘""’ , $value );
$value = ‘"’ . $value . ‘"’ . “,”;
}
//add this field value to our row
$line .= $value;
}
//trim whitespace from each row
$data .= trim( $line ) . “\n”;
}
//remove all carriage returns from the data
$data = str_replace( “\r” , “” , $data );

//create a file and send to browser for user to download
header(“Content-type: application/vnd.ms-excel”);
header(“Content-disposition: csv” . date(“Y-m-d”) . “.csv”);
header(“Content-disposition: filename=”.$file_name.".csv");
print “$hdr\n$data”;
exit;

	}

[/php]

#5

thanks alot
i got it to work but now im having trouble on getting the file to save in the Y M D format

[php]
header(“Content-type: application/vnd.ms-excel”);

header("Content-disposition: csv" . date("Y.M.d") . ".csv");
header("Content-disposition: filename=" . $file_name.".csv");
print "$hdr\n$data"; 
exit;

[/php]

it exports when i hit hte button but the file is named " .csv "
and when i keep doing it. “-1.csv”, -2.csv and so on
i tried “y-m-d " , tried 'test” didnt change anything
any idea how to fix this?

#6

scrooge,

I don’t see anyplace where $file_name is set. See if the following works for you:[php]header(“Content-type: application/vnd.ms-excel”);
header(“Content-Disposition: attachment; filename=”.date(“YMd”).“csv”);
[/php]
I removed the periods from the date, as I don’t like to use periods in file names (other that for extensions).

This should create a file name like “2012Sep17.csv” Please let me know if I am misunderstanding what you want the file name to be.

#7

o wow completely forgot to set filename
thanks alot it seems to be working now

#8

Glad you got it working - headers can be a pain sometimes!

Sponsor our Newsletter | Privacy Policy | Terms of Service