Problems with exporting from a mysql database to a .csv - Page 4

Question

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.

maydhyam 38 Posting Pro :) · Answer 1 · 2008-08-08T01:10:11+00:00

*sigh*...ok, so I looked over the syntax in the db manager program, and I realized that I actually copied it twice and then tried to execute that...:$ :icon_redface: :$

It works, and I am getting the 10072 records returned...

R0bb0b 344 Posting Shark · Answer 2 · 2008-08-08T01:18:12+00:00

In that case, this makes no sense because the whole thing should be working. You are really going to have to pound that part of the script. We have verified that the field is of date data type, have eliminated the date picker as the problem, the sql query is formatted correctly, I don't see any changes to those variables before you insert them into the query so I'm sure that the query is the same from your script. You are going to have to start from the query in your script. Add some test outputs like

echo $query . "<br /><br />" . mysql_num_rows($sql);

after your mysql_query() function.

This should be working.
:confused:

maydhyam 38 Posting Pro :) · Answer 3 · 2008-08-08T01:33:13+00:00

I added it here:

.
.
.
			include('./connect.php');
			global $conn;
			
			// Start our query of the database 
			$query = "SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') >= '$sDate' and DATE_FORMAT(billDate, '%b-%e-%Y') <= '$eDate'";
			
			$sql = mysql_query($query, $conn);	
			
			echo $query . "<br /><br />" . mysql_num_rows($sql);	
.
.
.

and this is the result:

SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') >= 'Nov-1-2007' and DATE_FORMAT(billDate, '%b-%e-%Y') <= 'Nov-1-2007'

maydhyam 38 Posting Pro :) · Answer 4 · 2008-08-08T01:44:13+00:00

I think the problem lies here

$sql = mysql_query($query, $conn);

it doesn't echo anything, but if I remove the $conn, I get the "Resource ID#6" error being displayed...:confused: :confused: :confused:

R0bb0b 344 Posting Shark · Answer 5 · 2008-08-12T01:44:54+00:00

You know, I think you're right. that it has something to do with the resource. go ahead and substitute these two lines for your database connection.

include('./connect.php');
			global $conn;

So what I mean take your connect and select db lines from ./connect.php and paste it right there.

maydhyam 38 Posting Pro :) · Answer 6 · 2008-08-12T02:00:09+00:00

I have those 2 statements already...and it doesn't echo anything...

R0bb0b 344 Posting Shark · Answer 7 · 2008-08-12T02:21:42+00:00

for the time being, but a die statement after your mysql_query.

$sql = mysql_query($query, $conn) or die(mysql_error());

maydhyam 38 Posting Pro :) · Answer 8 · 2008-08-12T02:30:02+00:00

Here is the code....and still nothing shows...

.
.
.
	include('./connect.php');
                global $conn;
			
	// Start our query of the database 
	$query = "SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') >= '$sDate' and DATE_FORMAT(billDate, '%b-%e-%Y') <= '$eDate'";
			
	$sql = mysql_query($query, $conn) or die("Error: ".mysql_error());	
	echo $sql;
.
.
.

R0bb0b 344 Posting Shark · Answer 9 · 2008-08-12T02:38:27+00:00

The only thing that I can think of is that connection script because we have been through and verified everything else.

maydhyam 38 Posting Pro :) · Answer 10 · 2008-08-12T02:43:29+00:00

but the connection script works properly for all the other parts of the application...

R0bb0b 344 Posting Shark · Answer 11 · 2008-08-12T02:55:33+00:00

try moving the die statement onto the mysql_num_rows function since that is where it seems to be hanging up. Se if we can get an error out of it.

maydhyam 38 Posting Pro :) · Answer 12 · 2008-08-12T03:35:20+00:00

.
.
.
	include('./connect.php');
	global $conn;
			
	// Start our query of the database 
	$query = "SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') >= '$sDate' and DATE_FORMAT(billDate, '%b-%e-%Y') <= '$eDate'";
			
	$sql = mysql_query($query, $conn);	
	//echo $sql;
	echo $query . "<br /><br />" . mysql_num_rows($sql) or die("Error: ".mysql_error());					
.
.
.

the result displayed is 1

R0bb0b 344 Posting Shark · Answer 13 · 2008-08-12T03:40:24+00:00

The only thing that I can think of is your connection object because we have already tested your query and it works fine. I will test it tonight and post the entire working script tomorrow.

R0bb0b 344 Posting Shark · Answer 14 · 2008-08-12T16:44:51+00:00

made a couple of adjustments. to eliminate the "headers already sent" errors, I put ob_start() at the top of the script. Other wise, everything is as we have been talking about and it works fine for me.
connect.php

<?php
define('SQL_HOST', 'servername');
define('SQL_USER', 'username');
define('SQL_PASS', 'password');
define('SQL_DBOC', 'dbname');
define('SQL_DBM', 'mysql');

$conn=mysql_connect(SQL_HOST, SQL_USER, SQL_PASS) OR die('Could not connect to the database. ' .				mysql_error());
mysql_select_db(SQL_DBOC,$conn) or die(mysql_error());

?>

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 
			$query = "SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') >= '$sDate' and DATE_FORMAT(billDate, '%b-%e-%Y') <= '$eDate'";
			$sql = mysql_query($query, $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
}



?>

testGCR.php

<?
ob_start();
?>
<!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" value="Nov-1-2007">
						<a href="javascript<b></b>:NewCal('sDate','mmmddyyyy')">
							<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" value="Nov-1-2007">
						<a href="javascript<b></b>:NewCal('eDate','mmmddyyyy')">
							<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>

maydhyam 38 Posting Pro :) · Answer 15 · 2008-08-12T20:50:00+00:00

Hi R0bb0b,

Firstly, let me thank you for all that you've done till now, I am truely grateful for your guidance.... :)

Now the problem at hand:
I made the necessary changes and it works to an extent...here are the outcomes:

When I select the dates: Nov-1-2007 and Nov-9-2007, I am getting all the records in the table (which is wrong) but when I select the dates: Nov-1-2007 and Nov-1-2007, I get the records for that date only. :confused:
When I save the .csv file, I cannot open it in a MS Excel (this was working earlier on in this thread), I am getting a file is corrupt message, but the .csv file can only open in notepad/any text application. :confused:
The .csv file not only has the records from the table but also the entire code from the testGCR.php file. :confused:

If I am not mistaken, some of these problems we got before (earlier on in this thread), because that was the reason behind the change of the billDate data-type etc.

:confused: :confused: :confused:

2 questions though...

why did you use the ob_start()?
and why didn't you use the ob_end_flush()?

May

R0bb0b 344 Posting Shark · Answer 16 · 2008-08-13T01:00:11+00:00

The only reason I used ob_start() is because you are sending the .csv header after you have posted to the page to keep from getting an error. I probably should have used ob_end_flush() as well, just didn't think of it.

And you are doing something very strange if you are seeing php code in the .csv file, which would be why it is corrupt since it is no longer in .csv format. If you read the documentation on datatypes and functions, you will see that the sql query is in the right format. While the program is in production, you should be echoing that query every time so you can see what it looks like when ever something strange happens.

maydhyam 38 Posting Pro :) · Answer 17 · 2008-08-13T01:24:16+00:00

Strange?!? :-/ ...I'm just editing my script with the changes you said to, and saving it in the folder on the server...and running it on the browser...I selected the dates from the pop-up calender, and click the generate button...that's all...

i have now added the ob_end_flush(); line at the end of the testGCR.php script, as well as set the code to only echo the data found, not to download it into the .csv...let me run that and let you know...

__________________________________________________________________________________
In my output, I am seeing the form as well as the results...can't figure out why, but still looking

maydhyam 38 Posting Pro :) · Answer 18 · 2008-08-14T01:14:25+00:00

Hello R0bb0b,

I was able to remove the testGCR.php code from the results page and I tested it with the following data:

Start Date = Nov-1-2007
End Date = Nov-1-2007
I got all the records with the billDate = Nov-1-2007
Start Date = Nov-2-2007
End Date = Nov-2-2007
I got all the records with the billDate = Nov-2-2007
Start Date = Nov-1-2007
End Date = Nov-9-2007
I did not get all the records with the billDate between the start and end date stated above, instead I got all the data from the table...

Here is my sql query:

$query = "SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') between '$sDate' and '$eDate'";

R0bb0b 344 Posting Shark · Answer 19 · 2008-08-14T03:06:38+00:00

You are going to have to analyze the queries that are being sent to mysql by outputing the query to the browser and pasting it into mysql. I think that will clear this up since this doesn't make a whole lot of sense otherwise.

maydhyam 38 Posting Pro :) · Answer 20 · 2008-08-14T17:51:56+00:00

Hi R0bb0b,

I echoed my $query and this is how it looks:

SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') between 'Nov-1-2007' and 'Nov-9-2007'

When I copied this query and pasted it into the MySQL Query Browser and ran it, I got the entire table of data displaying...

maydhyam 38 Posting Pro :) · Answer 21 · 2008-10-07T19:09:59+00:00

I got the problem sorted out...honestly I did not do anything to it...what happened is that a technician came and installed the cd/dvd writer on the machine, I started the machine and loaded dreamweaver (like I do every morning) but dreamweaver said that it was reloading the cache...I didn't know that needed to happen!!! anyways, when I typed in the date in the format mysql has it stored (I was trying it without the calendar), it worked perfectly...so I tried using the calendar (with the date format syntax in the mysql syntax) and it didn't work....so i started hunting online for a calendar to enter the date in the format that mysql stores the date...and presto! i got 1, and now my caledar works like a charm...

R0bb0b 344 Posting Shark · Answer 22 · 2008-10-07T20:23:54+00:00

I got the problem sorted out...honestly I did not do anything to it...what happened is that a technician came and installed the cd/dvd writer on the machine, I started the machine and loaded dreamweaver (like I do every morning) but dreamweaver said that it was reloading the cache...I didn't know that needed to happen!!! anyways, when I typed in the date in the format mysql has it stored (I was trying it without the calendar), it worked perfectly...so I tried using the calendar (with the date format syntax in the mysql syntax) and it didn't work....so i started hunting online for a calendar to enter the date in the format that mysql stores the date...and presto! i got 1, and now my caledar works like a charm...

Wow, cache huh? That sucks and is great at the same time.