Hello,

I am trying to do an export from a table within a mysql database into a csv file where my parameters are a start date and end date...but I am experiencing a few problems...

Background:
I have 2 files; testGCR.php and gcrFunction.php.

testGCR.php has my form where the user enters the relevant dates (I have a inserted calender to enter the dates...the date when chosen from the calender looks like "dd-mm-yyyy"), this is to be passed to the query...

gcrFunction.php has the validation of the data entered in the date boxes as well as the export code (which I got off the net :icon_redface: )

Problem 1:
when the date is entered in the select query, only the column names are displayed with no data in the csv (even though the table has alot of data)

Problem 2:
in the csv file, just above the column names are the entire chunk of code from testGCR.php....it's not supposed to do that...it's only supposed to show me the results...

Any suggestions/comments are most welcome...

Thanks in advance
May

Recommended Answers

All 111 Replies

Just a tip to help you bypass the frustration:
Usually when I am exporting to csv, excel, pdf or anything, I always have two different modes, production mode and development mode.

In production mode, of course I am outputting the desired output like normal. In development mode I am just outputting straight to the browser or textarea or whatever allows me to analyze my output the easiest. I usually have this set in a variable at the top.

This allows you to analyze the actual behavior of your code without involving any more variables then necessary at the time.

Anyway, Post your code and I'll look at it.

Hi, here are the 2 files...
Instead of having it output into the csv file, when i echoed it to the screen, it only shows the column headings...

testGCR.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Test Document</title>
<script language="javascript" type="text/javascript" src="datetimepicker.js">

//Date Time Picker script- by TengYong Ng of http://www.rainforestnet.com
//Script featured on JavaScript Kit (http://www.javascriptkit.com)
//For this script, visit http://www.javascriptkit.com 

</script>

</head>

<body>
	<?php
		include("./gcrFunction.php");
	?>
	<form method="post">
		<table>					
		  <tr>
			  <td width="747">
				  <p align="left"><span class="style4 style25 style28 style6">Generate Chargeback Report</span></p>
				  <p align="left"><span class="style21 style29 style5">The Chargeback Reporter works in 2 steps.</span></p>					 							  
			  </td>
		  </tr>
		  <tr><td><p>&nbsp;</p></td></tr>
		  <tr>
			<td>
			  <table width="648" border="0" align="center" cellpadding="1" cellspacing="0" bordercolor="#FFFFFf">
				<tr>
				  <td bgcolor="#99bebe" colspan="4">
					<p align="left" class="style8 style7 style32"><strong>
								  <span class="style21  style33 style11">Step 1: Select Start and End Date</span></strong></p>								  
				  </td>
				</tr>
				<tr>								  
				  <td width="100" bgcolor="#99bebe" scope="row">
				  	<div align="left" class="style8 style7 style31 style29 style13">
				    	<div align="right"><strong>Start Date: </strong></div>
				  	</div>								  </td>							  
				  <td width="220" bgcolor="#99bebe">
				  	<input name="sDate" id="sDate" type="text" size="25">
						<a href="javascript:NewCal('sDate','ddmmyyyy')">
							<img src="images/cal.gif" width="16" height="16" border="0" alt="Pick a Start Date">											
						</a>									</td>																
				  <td width="100" bgcolor="#99bebe" scope="row">
				  	<div align="left" class="style8 style7 style31 style29 style13">
				    	<div align="right"><strong>End Date: </strong></div>
				  </div>								  </td>
				  <td width="220" bgcolor="#99bebe">
				  	<input name="eDate" id="eDate" type="text" size="25">
						<a href="javascript:NewCal('eDate','ddmmyyyy')">
							<img src="images/cal.gif" width="16" height="16" border="0" alt="Pick an End Date">										
						</a>									</td>
				</tr>
				<tr>
				  <td bgcolor="#99bebe" colspan="4">
					<p align="left" class="style5">
			      <span class="style21 style30 style11">Step 2: Generate the Chargeback Report </span></p>																	  		  		  </td>
				</tr>
				<tr>
				  <td bgcolor="#99bebe"><p>&nbsp;</p></td>
				  <td bgcolor="#99bebe" colspan="3">
					  <div align="left">
						<strong>
							<input type="submit" value="Generate" name="btnSubmit" />
						</strong>
					  </div>								  
				  </td>
				</tr>								
			</table>
  		</td>
	   </tr>
	  </table>
	 </form>	
	<? 	
			validateData();
	?> 

</body>
</html>

gcrFunction.php

<?
include('./connect.php');

error_reporting(E_ALL ^ E_NOTICE);

function validateData()
{
	global $conn;
	
	$sDate=$_POST['sDate'];
	$eDate=$_POST['eDate'];
	//echo $sDate;
	//echo $eDate;

	$btn=$_POST['btnSubmit'];
		
	if(isset($btn))
	{				
		if(!$sDate)
		{
			echo '<center><font face=Verdana color=Red size="2"><b>Start Date field is empty!</b></font></center></td>
				  </tr><tr bgcolor="#ffffff"><td>
				  <table width="760" border="0" align="center" cellpadding="0" cellspacing="0" bordercolor="#FFFFFf">
				  <tr><td width="760" bgcolor="#336666" scope="col">&nbsp;</td></tr></table></td></tr><tr bgcolor="#ffffFF">
				  <td><p align="center" class="style30 style4" >&copy;ITT Department, Petrotrin. </p></td></tr></table>';
			die("Problem 1");
		}
		else if(!$eDate)
		{
			echo '<center><font face=Verdana color=Red size="2"><b>End Date field is empty!</b></font></center></td>
				  </tr><tr bgcolor="#ffffff"><td>
				  <table width="760" border="0" align="center" cellpadding="0" cellspacing="0" bordercolor="#FFFFFf">
				  <tr><td width="760" bgcolor="#336666" scope="col">&nbsp;</td></tr></table></td></tr><tr bgcolor="#ffffFF">
				  <td><p align="center" class="style30 style4" >&copy;ITT Department, Petrotrin. </p></td></tr></table>';
			die("Problem 2");
		}
		else
		{
			
			function parseCSVComments($comments) 
			{ 
				// First off escape all " and make them "" 
				$comments = str_replace('"', '""', $comments); 
				if(eregi(",", $comments) or eregi("\n", $comments)) 
				{ // Check if I have any commas or new lines 
				return '"'.$comments.'"'; // If I have new lines or commas escape them 
				} 
				else 
				{ 
					return $comments; // If no new lines or commas just return the value 
				} 
			} 
			
			// Start our query of the database 
			$sql = mysql_query("SELECT * FROM tsttbills where date between '$sDate' and '$eDate'", $conn) ; 
			$numberFields = mysql_num_fields($sql); // Find out how many fields we are fetching 
			
			if($numberFields) 
			{ // Check if we need to output anything 
				for($i=0; $i<$numberFields; $i++) 
				{ 
					// Create the headers for each column, this is the field name in the database 
					$head[] = mysql_field_name($sql, $i); 
				} 
				$headers = join(',', $head)."\n"; // Make our first row in the CSV 
			
				while($info = mysql_fetch_object($sql)) 
				{ 
					foreach($head as $fieldName) 
					{ // Loop through the array of headers as we fetch the data 
						$row[] = parseCSVComments($info->$fieldName); 
					} // End loop 
					$data .= join(',', $row)."\n"; // Create a new row of data and append it to the last row 
					$row = ''; // Clear the contents of the $row variable to start a new row 
				} 
				// Start our output of the CSV 
				$filename = $file."_".date("Y-m-d_H-i",time());
				header("Content-type: application/x-msdownload"); 
				header("Content-Disposition: attachment; filename=".$filename.".csv");
				header("Pragma: no-cache"); 
				header("Expires: 0");
				echo $headers.$data; 
			} 
			else 
			{ 
				// Nothing needed to be output. Put an error message here or something. 
				echo ("There is no data to create a CSV file."); 
			} 	
		}	//close the else
	}//close the if(isset($btn)) statement
}



?>

few slight changes, was getting headers already sent error: fixed
that's probably what was messing with the excel parsing.
that's about the only thing though, nice script.

liked the way you used $head[] = mysql_field_name($sql, $i); gonna have to try that.


testGCR.php

<?
include("./gcrFunction.php");
if(count($_POST) == 0)
{
?>
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
	<title>Test Document</title>
	<script language="javascript" type="text/javascript" src="datetimepicker.js">
	
	//Date Time Picker script- by TengYong Ng of http://www.rainforestnet.com
	//Script featured on JavaScript Kit (http://www.javascriptkit.com)
	//For this script, visit http://www.javascriptkit.com 
	
	</script>
	
	</head>
	
	<body>
		
		<form method="post" target="_blank">
			<table>					
			  <tr>
				  <td width="747">
					  <p align="left"><span class="style4 style25 style28 style6">Generate Chargeback Report</span></p>
					  <p align="left"><span class="style21 style29 style5">The Chargeback Reporter works in 2 steps.</span></p>					 							  
				  </td>
			  </tr>
			  <tr><td><p>&nbsp;</p></td></tr>
			  <tr>
				<td>
				  <table width="648" border="0" align="center" cellpadding="1" cellspacing="0" bordercolor="#FFFFFf">
					<tr>
					  <td bgcolor="#99bebe" colspan="4">
						<p align="left" class="style8 style7 style32"><strong>
									  <span class="style21  style33 style11">Step 1: Select Start and End Date</span></strong></p>								  
					  </td>
					</tr>
					<tr>								  
					  <td width="100" bgcolor="#99bebe" scope="row">
						<div align="left" class="style8 style7 style31 style29 style13">
							<div align="right"><strong>Start Date: </strong></div>
						</div>								  </td>							  
					  <td width="220" bgcolor="#99bebe">
						<input name="sDate" id="sDate" type="text" size="25">
							<a href="javascript<b></b>:NewCal('sDate','ddmmyyyy')">
								<img src="images/cal.gif" width="16" height="16" border="0" alt="Pick a Start Date">											
							</a>									</td>																
					  <td width="100" bgcolor="#99bebe" scope="row">
						<div align="left" class="style8 style7 style31 style29 style13">
							<div align="right"><strong>End Date: </strong></div>
					  </div>								  </td>
					  <td width="220" bgcolor="#99bebe">
						<input name="eDate" id="eDate" type="text" size="25">
							<a href="javascript<b></b>:NewCal('eDate','ddmmyyyy')">
								<img src="images/cal.gif" width="16" height="16" border="0" alt="Pick an End Date">										
							</a>									</td>
					</tr>
					<tr>
					  <td bgcolor="#99bebe" colspan="4">
						<p align="left" class="style5">
					  <span class="style21 style30 style11">Step 2: Generate the Chargeback Report </span></p>																	  		  		  </td>
					</tr>
					<tr>
					  <td bgcolor="#99bebe"><p>&nbsp;</p></td>
					  <td bgcolor="#99bebe" colspan="3">
						  <div align="left">
							<strong>
								<input type="submit" value="Generate" name="btnSubmit" />
							</strong>
						  </div>								  
					  </td>
					</tr>								
				</table>
			</td>
		   </tr>
		  </table>
		 </form> 
	
	</body>
	</html>
<?
}
validateData();
?>

gcrFunction.php

<?
include("inc/functions.inc");
$conn = mysql_dbconn();

error_reporting(E_ALL ^ E_NOTICE);

function validateData()
{
	global $conn;
	
	$sDate=$_POST['sDate'];
	$eDate=$_POST['eDate'];
	//echo $sDate;
	//echo $eDate;

	$btn=$_POST['btnSubmit'];
		
	if(isset($btn))
	{				
		if(!$sDate)
		{
			echo '<center><font face=Verdana color=Red size="2"><b>Start Date field is empty!</b></font></center></td>
				  </tr><tr bgcolor="#ffffff"><td>
				  <table width="760" border="0" align="center" cellpadding="0" cellspacing="0" bordercolor="#FFFFFf">
				  <tr><td width="760" bgcolor="#336666" scope="col">&nbsp;</td></tr></table></td></tr><tr bgcolor="#ffffFF">
				  <td><p align="center" class="style30 style4" >&copy;ITT Department, Petrotrin. </p></td></tr></table>';
			die("Problem 1");
		}
		else if(!$eDate)
		{
			echo '<center><font face=Verdana color=Red size="2"><b>End Date field is empty!</b></font></center></td>
				  </tr><tr bgcolor="#ffffff"><td>
				  <table width="760" border="0" align="center" cellpadding="0" cellspacing="0" bordercolor="#FFFFFf">
				  <tr><td width="760" bgcolor="#336666" scope="col">&nbsp;</td></tr></table></td></tr><tr bgcolor="#ffffFF">
				  <td><p align="center" class="style30 style4" >&copy;ITT Department, Petrotrin. </p></td></tr></table>';
			die("Problem 2");
		}
		else
		{
			
			function parseCSVComments($comments) 
			{ 
				// First off escape all " and make them "" 
				$comments = str_replace('"', '""', $comments); 
				if(eregi(",", $comments) or eregi("\n", $comments)) 
				{ // Check if I have any commas or new lines 
					return '"'.$comments.'"'; // If I have new lines or commas escape them 
				} 
				else 
				{ 
					return $comments; // If no new lines or commas just return the value 
				} 
			} 
			
			// Start our query of the database 
			$sql = mysql_query("SELECT * FROM tsttbills where date between '$sDate' and '$eDate'", $conn) ; 
			$numberFields = mysql_num_fields($sql); // Find out how many fields we are fetching 
			
			if($numberFields) 
			{ // Check if we need to output anything 
				for($i=0; $i<$numberFields; $i++) 
				{ 
					// Create the headers for each column, this is the field name in the database 
					$head[] = mysql_field_name($sql, $i); 
				} 
				$headers = join(',', $head)."\n"; // Make our first row in the CSV 
			
				while($info = mysql_fetch_object($sql)) 
				{ 
					foreach($head as $fieldName) 
					{ // Loop through the array of headers as we fetch the data 
						$row[] = parseCSVComments($info->$fieldName); 
					} // End loop 
					$data .= join(',', $row)."\n"; // Create a new row of data and append it to the last row 
					$row = ''; // Clear the contents of the $row variable to start a new row 
				} 
				// Start our output of the CSV 
				$filename = $file."_".date("Y-m-d_H-i",time());
				header("Content-type: application/x-msdownload"); 
				header("Content-Disposition: attachment; filename=".$filename.".csv");
				header("Pragma: no-cache"); 
				header("Expires: 0");
				echo $headers.$data; 
			} 
			else 
			{ 
				// Nothing needed to be output. Put an error message here or something. 
				echo ("There is no data to create a CSV file."); 
			} 	
		}	//close the else
	}//close the if(isset($btn)) statement
}



?>

Hi,

When I applied the changes you did, I only get a blank page and not the page to select the dates...

liked the way you used
$head[] = mysql_field_name($sql, $i); gonna have to try that.

Thanks...

May

Oops, did you replace the top two lines in gcrFunction.php:
include("inc/functions.inc");
$conn = mysql_dbconn();

with your connection file
include('./connect.php');

Yea, I did and there's still a blank page...

from the file testGCR.php....with the following piece of code....isn't there supposed to be an open and closed square brackets?

<?
include("./gcrFunction.php");
if(count($_POST[]) == 0)
{
?>

move this line:
error_reporting(E_ALL ^ E_NOTICE);
on gcrFunction.php to just before the include statement on line 2 of testGCR.php
and comment out these lines.

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/x-msdownload"); 
header("Content-Disposition: attachment; filename=".$filename.".csv");
header("Pragma: no-cache"); 
header("Expires: 0");

Yea, I did and there's still a blank page...

from the file testGCR.php....with the following piece of code....isn't there supposed to be an open and closed square brackets?

<?
include("./gcrFunction.php");
if(count($_POST[]) == 0)
{
?>

I never include them. You could also try an if(isset($_POST)) statement

commented: R0bb0b was most helpful to me in my problem. Great guy! Great Work! +1

what is varname?

could be "btnSubmit", but I can't see that making a difference because if isset($_POST) then count($_POST) > 0 = true

hmmm.....well I put in and it displays the form now...I will check it now for functionality...


I just tested it, and the .csv file is cleaned of the code...so I am seeing the table headers now....but no data....I verified that there is data stored in the table for the particular date range selected for testing...

When you test it, and output it to the browser, do you see the rows or just the column headers?

Just the column headers

The while loop that starts on line 68, replace it with

while($info = mysql_fetch_object($sql)) 
{ 
	print_r($info);
	echo "<br />";
}

and keep the header lines commented out. So we can see that the data is really there.

only the column names are displayed...

echo your query and post it.

ok, so I echoed the query

// Start our query of the database 
  $sql = mysql_query("SELECT * FROM tsttbills where date >= '$sDate' and date <= '$eDate'", $conn) ; 
  echo $sql;

Here is the result:

Resource id #6account,service,date,time,timebandtype,destination,callednumber,mins,airtimecharge,tollcharge,amt,privatenumber

echo this:
"SELECT * FROM tsttbills where date >= '$sDate' and date <= '$eDate'"

This is a valid query, I just think the format of the dates may be incorrect or maybe you don't have any dates that fall between these dates. One of the two.

Here is the result:

Resource id #6account,service,date,time,timebandtype,destination,callednumber,mins,airtimecharge,tollcharge,amt,privatenumber

I do have data with the dates falling into the range I selected while testing...

my dates are being selected from a javascript....datetimepicker.js

are the dates going in like 'yyyy-mm-dd'?

nope, they are in the format 'dd-mm-yyyy'

aaaaahaaaaaaaaah! :)

try this:

$sql = mysql_query("SELECT * FROM tsttbills where DATE_FORMAT(date, '%d-%m-%Y') >= '$sDate' and DATE_FORMAT(date, '%d-%m-%Y') <= '$eDate'", $conn);

just edited this to replace the "/" with "-"

:D
Ok, let me try...


still no change...here is the result:

Resource id #6account,service,date,time,timebandtype,destination,callednumber,mins,airtimecharge,tollcharge,amt,privatenumber

How did it go?

same as before...

Result:

Resource id #6account,service,date,time,timebandtype,destination,callednumber,mins,airtimecharge,tollcharge,amt,privatenumber

OK, post your table create statement.

my table create statement???

Run this query in your database manager and post your results:

desc tsttbills;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.