Problems with exporting from a mysql database to a .csv

Question

maydhyam 38 Posting Pro :)

16 Years Ago

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

mysql php

3 Contributors
111 Replies
1K Views
2 Months Discussion Span
Latest Post 16 Years Ago Latest Post by R0bb0b

R0bb0b 344 Posting Shark

16 Years Ago

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

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

Reply to this topic

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.

R0bb0b was most helpful to me in my problem. Great guy! Great Work!

R0bb0b 344 Posting Shark · Answer 1 · 2008-07-19T00:49:37+00:00

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.

maydhyam 38 Posting Pro :) · Answer 2 · 2008-07-19T00:58:54+00:00

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
}



?>

R0bb0b 344 Posting Shark · Answer 3 · 2008-07-19T02:37:33+00:00

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
}



?>

maydhyam 38 Posting Pro :) · Answer 4 · 2008-07-21T20:33:13+00:00

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

R0bb0b 344 Posting Shark · Answer 5 · 2008-07-21T22:28:37+00:00

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');

maydhyam 38 Posting Pro :) · Answer 6 · 2008-07-21T23:03:36+00:00

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)
{
?>

R0bb0b 344 Posting Shark · Answer 7 · 2008-07-21T23:04:52+00:00

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");

maydhyam 38 Posting Pro :) · Answer 8 · 2008-07-21T23:24:23+00:00

maydhyam 38 Posting Pro :)

16 Years Ago

what is varname?

R0bb0b 344 Posting Shark · Answer 9 · 2008-07-21T23:30:38+00:00

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

maydhyam 38 Posting Pro :) · Answer 10 · 2008-07-21T23:44:23+00:00

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...

R0bb0b 344 Posting Shark · Answer 11 · 2008-07-21T23:54:08+00:00

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

maydhyam 38 Posting Pro :) · Answer 12 · 2008-07-22T00:11:27+00:00

maydhyam 38 Posting Pro :)

16 Years Ago

Just the column headers

R0bb0b 344 Posting Shark · Answer 13 · 2008-07-22T00:26:34+00:00

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.

maydhyam 38 Posting Pro :) · Answer 14 · 2008-07-22T00:33:40+00:00

maydhyam 38 Posting Pro :)

16 Years Ago

only the column names are displayed...

R0bb0b 344 Posting Shark · Answer 15 · 2008-07-22T00:39:54+00:00

R0bb0b 344 Posting Shark

16 Years Ago

echo your query and post it.

maydhyam 38 Posting Pro :) · Answer 16 · 2008-07-22T00:58:30+00:00

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

R0bb0b 344 Posting Shark · Answer 17 · 2008-07-22T01:04:23+00:00

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.

maydhyam 38 Posting Pro :) · Answer 18 · 2008-07-22T01:17:27+00:00

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

R0bb0b 344 Posting Shark · Answer 19 · 2008-07-22T01:23:29+00:00

R0bb0b 344 Posting Shark

16 Years Ago

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

maydhyam 38 Posting Pro :) · Answer 20 · 2008-07-22T01:58:34+00:00

maydhyam 38 Posting Pro :)

16 Years Ago

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

R0bb0b 344 Posting Shark · Answer 21 · 2008-07-22T02:04:28+00:00

R0bb0b 344 Posting Shark

16 Years Ago

aaaaahaaaaaaaaah! :)

R0bb0b 344 Posting Shark · Answer 22 · 2008-07-22T02:10:48+00:00

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 "-"

maydhyam 38 Posting Pro :) · Answer 23 · 2008-07-22T02:18:38+00:00

: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

R0bb0b 344 Posting Shark · Answer 24 · 2008-07-22T10:32:19+00:00

R0bb0b 344 Posting Shark

16 Years Ago

How did it go?

maydhyam 38 Posting Pro :) · Answer 25 · 2008-07-22T17:39:32+00:00

same as before...

Result:

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

R0bb0b 344 Posting Shark · Answer 26 · 2008-07-22T20:41:58+00:00

R0bb0b 344 Posting Shark

16 Years Ago

OK, post your table create statement.

maydhyam 38 Posting Pro :) · Answer 27 · 2008-07-22T20:45:43+00:00

maydhyam 38 Posting Pro :)

16 Years Ago

my table create statement???

R0bb0b 344 Posting Shark · Answer 28 · 2008-07-22T20:50:42+00:00

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

desc tsttbills;