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

Question

R0bb0b 344 Posting Shark

15 Years Ago

:sweat: :sweat: :sweat: :sweat: :sweat: :sweat:
cool

peter_budo commented: Nice done job +9

R0bb0b 344 Posting Shark

15 Years Ago

Just relieved to see that the data is consistent. You never really know with a varchar since there really is no structure to keep it in the correct format, you could have had a lot of work ahead of you.

R0bb0b 344 Posting Shark

15 Years Ago

OK, so post your code one more time so I can get a feel of where we are.

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.

maydhyam 38 Posting Pro :) · Answer 1 · 2008-07-22T21:01:23+00:00

maydhyam 38 Posting Pro :)

15 Years Ago

here it is...
(I am using MySQL Query Browser)

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with msword files.

desc_tsttbills.doc (35.5 KB)

R0bb0b 344 Posting Shark · Answer 2 · 2008-07-22T21:25:04+00:00

Oh, date is a varchar, you need to change the date data type to "date". Here is a reference:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

R0bb0b 344 Posting Shark · Answer 3 · 2008-07-22T21:28:46+00:00

If you have just test data in the table you should probably do a "delete from tsttbills;" and then run "ALTER TABLE tsttbills CHANGE `date` `date` date;"

and personally, I would change the column name to something similar like tstdate, in that case you would run "ALTER TABLE tsttbills CHANGE `date` `tstdate` date;".

FYI: the reason that we are doing this is that mysql doesn't do date calculation with a text field like a varchar, as far as I know anyway.

maydhyam 38 Posting Pro :) · Answer 4 · 2008-07-22T21:39:40+00:00

geez.....it's not test data....and I was given the database as it is....hmmm, can't I alter it with the data in it?

R0bb0b 344 Posting Shark · Answer 5 · 2008-07-22T21:57:21+00:00

if the data in it matches the datatype then yes, otherwise you may get an error. I would say, make sure you back up the table first, then try it.

maydhyam 38 Posting Pro :) · Answer 6 · 2008-07-23T00:28:08+00:00

:S I have a concern.....the data that is in the table was entered by the previous person working on this proj (I do not know how they got the data into the table)...therefore I created an upload script (this script uploads the data from a csv file and directly into the table...no formatting of the data was done)........now to change the table's date field datatype to 'date' would then also affect the uploading of the data apart from the downloading of the data...am I correct? and if I am, how can I avoid any problems there?
(sorry for back-tracking there....) :S

:-/ I am thinking of formatting the date before it uploads to the standard for mysql.....
not too sure if I am making sense...:icon_redface:

the date format when uploaded is : "dd/mm/yyyy"
the date format from the javascript calendar : "dd-mm-yyyy"

R0bb0b 344 Posting Shark · Answer 7 · 2008-07-23T00:53:54+00:00

doing research on date data type functions. So hold on.

R0bb0b 344 Posting Shark · Answer 8 · 2008-07-23T00:57:17+00:00

Try this first:

update tsttbills set date = STR_TO_DATE(date, '%m/%d/%Y');
ALTER TABLE tsttbills CHANGE `date` `date` date;

Any questions or Concerns?

maydhyam 38 Posting Pro :) · Answer 9 · 2008-07-23T01:14:16+00:00

I changed the name of the field in my table from 'date' to 'billDate'

so should it be:

update tsttbills set billDate = STR_TO_DATE(billDate, '%m/%d/%Y');

ALTER TABLE tsttbills CHANGE `billDate` `billDate` date;

???

R0bb0b 344 Posting Shark · Answer 10 · 2008-07-23T01:20:03+00:00

I changed the name of the field in my table from 'date' to 'billDate'
so should it be:
update tsttbills set billDate = STR_TO_DATE(billDate, '%m/%d/%Y');
ALTER TABLE tsttbills CHANGE `billDate` `billDate` date;
???

you have the table backed up, right? Then yes.

maydhyam 38 Posting Pro :) · Answer 11 · 2008-07-23T01:42:58+00:00

the update is taking a while.....*sigh*....i guess it's bcoz of the amount of data i the table...

R0bb0b 344 Posting Shark · Answer 12 · 2008-07-23T01:42:22+00:00

R0bb0b 344 Posting Shark

15 Years Ago

That's a good sign.

R0bb0b 344 Posting Shark · Answer 13 · 2008-07-23T01:45:12+00:00

That was funny, I responded to your post and mine is stacked on top.

maydhyam 38 Posting Pro :) · Answer 14 · 2008-07-24T02:14:44+00:00

Here is the error when I tried the update statement...

R0bb0b 344 Posting Shark · Answer 15 · 2008-07-24T04:16:06+00:00

"incorrect datetime value '13/09/2007' for function str_to_time"
Is "13/09/2007" the value in the billdate field, that looks like "dd/mm/yyyy" not "mm/dd/yyyy". If that is the value in the billdate column, I think this will work:

update tsttbills set billdate = STR_TO_DATE(billdate, '%d/%m/%Y');
ALTER TABLE tsttbills CHANGE `billdate` `billdate` date;

maydhyam 38 Posting Pro :) · Answer 16 · 2008-07-24T18:05:43+00:00

When I ran

update tsttbills set billdate = STR_TO_DATE(billdate, '%d/%m/%Y');

I got the message

Query returned no resultset.

Is that supposed to happen?

R0bb0b 344 Posting Shark · Answer 17 · 2008-07-24T20:49:22+00:00

I believe so, since there is no result set to return. Now do a desc tsttbills; and confirm that the column is now a date field and kind of scan through a sample of the data to verify that the data is still intact and that none of the billdates were truncated or altered in any way besides the format change to 'yyyy-mm-dd'.

maydhyam 38 Posting Pro :) · Answer 18 · 2008-07-24T21:28:05+00:00

:S Well......the date in the billDate field were erased and now the column displays 'null' :S .....but on the good side the billDate field datatype is now date...

R0bb0b 344 Posting Shark · Answer 19 · 2008-07-24T22:10:12+00:00

I guess we go with option B then. Delete data and import your csv. The date column should be like this, STR_TO_DATE('13/09/2007', '%d/%m/%Y') .

jencinas69 0 Junior Poster in Training · Answer 20 · 2008-07-24T22:16:20+00:00

Try this

http://www.ineedtutorials.com/articles/export-mysql-data-to-csv-php-tutorial

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

I guess we go with option B then. Delete data and import your csv. The date column should be like this, STR_TO_DATE('13/09/2007', '%d/%m/%Y') .

Where do I use that?
Is it when I am loading the data into the database from the csv file?........

Chunk of upload code:

if(count($_FILES) > 0)			
			{					
				$ext = "";				
				$ext = substr(trim($_FILES["file"]["name"]), -4);				
				$allowedext = array(".txt", ".csv", ".sql"); 				
				
				if(in_array($ext, $allowedext))				
				{					
					$filename = $_FILES['file']['tmp_name'];				
					$handle = fopen($filename, "r"); 			
					
					while (($data = fgetcsv($handle, 100000, ',', '"')) !== FALSE)					
					{
						//print_r($data);
						//echo("<br />"); 
						$value1 = $data[0];
						$value2 = $data[1];
						$value3 = $data[2];
						$value4 = $data[3];
						$value5 = $data[4];
						$value6 = $data[5];
						$value7 = $data[6]; 
						$value8 = $data[7]; 
						$value9 = $data[8];
						$value10 = $data[9]; 
						$value11 = $data[10]; 
						$value12 = $data[11]; 
						//echo("<br />"); 
										
						$query = "INSERT INTO testtsttbills (account,service,billDate,time,timebandtype,destination,callednumber,mins,airtimecharge,tollcharge,amt,privatenumber) VALUES ('$value1','$value2','$value3','$value4','$value5','$value6','$value7','$value8','$value9','$value10','$value11','$value12')";						
						
						$qry = mysql_query($query,$conn) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno($conn) . ") " . mysql_error($conn));
											
					}				
					fclose($handle);				
					echo "<div align='center'><font color='red'>The file was uploaded.</font></div>";				
				}				
				else				
				{					
					echo "<div align='center'><font color='red'>You are trying to upload an invalid file format. Please try again.</font></div>";		
				}			
			}

R0bb0b 344 Posting Shark · Answer 22 · 2008-07-25T00:57:08+00:00

You should be able to do it one of two ways:

Number 1: (adjust '%d/%m/%Y' to match up to the format that is on the csv, it is currently looking for "dd/mm/yyyy")

if(count($_FILES) > 0)			
			{					
				$ext = "";				
				$ext = substr(trim($_FILES["file"]["name"]), -4);				
				$allowedext = array(".txt", ".csv", ".sql"); 				
				
				if(in_array($ext, $allowedext))				
				{					
					$filename = $_FILES['file']['tmp_name'];				
					$handle = fopen($filename, "r"); 			
					
					while (($data = fgetcsv($handle, 100000, ',', '"')) !== FALSE)					
					{
						//print_r($data);
						//echo("<br />"); 
						$value1 = $data[0];
						$value2 = $data[1];
						$value3 = $data[2];
						$value4 = $data[3];
						$value5 = $data[4];
						$value6 = $data[5];
						$value7 = $data[6]; 
						$value8 = $data[7]; 
						$value9 = $data[8];
						$value10 = $data[9]; 
						$value11 = $data[10]; 
						$value12 = $data[11]; 
						//echo("<br />"); 
										
						$query = "INSERT INTO testtsttbills (account,service,billDate,time,timebandtype,destination,callednumber,mins,airtimecharge,tollcharge,amt,privatenumber) VALUES ('$value1','$value2',STR_TO_DATE('$value3', '%d/%m/%Y'),'$value4','$value5','$value6','$value7','$value8','$value9','$value10','$value11','$value12')";						
						
						$qry = mysql_query($query,$conn) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno($conn) . ") " . mysql_error($conn));
											
					}				
					fclose($handle);				
					echo "<div align='center'><font color='red'>The file was uploaded.</font></div>";				
				}				
				else				
				{					
					echo "<div align='center'><font color='red'>You are trying to upload an invalid file format. Please try again.</font></div>";		
				}			
			}

Number 2: (adjust list($dy, $mo, $yr) to match up to the format that is on the csv, it is currently looking for "dd/mm/yyyy")

if(count($_FILES) > 0)			
			{					
				$ext = "";				
				$ext = substr(trim($_FILES["file"]["name"]), -4);				
				$allowedext = array(".txt", ".csv", ".sql"); 				
				
				if(in_array($ext, $allowedext))				
				{					
					$filename = $_FILES['file']['tmp_name'];				
					$handle = fopen($filename, "r"); 			
					
					while (($data = fgetcsv($handle, 100000, ',', '"')) !== FALSE)					
					{
						//print_r($data);
						//echo("<br />"); 
						$value1 = $data[0];
						$value2 = $data[1];
						list($dy, $mo, $yr) = explode("/", $data[2]);
						$value3 = $yr."-".$mo."-".$dy;
						$value4 = $data[3];
						$value5 = $data[4];
						$value6 = $data[5];
						$value7 = $data[6]; 
						$value8 = $data[7]; 
						$value9 = $data[8];
						$value10 = $data[9]; 
						$value11 = $data[10]; 
						$value12 = $data[11]; 
						//echo("<br />"); 
										
						$query = "INSERT INTO testtsttbills (account,service,billDate,time,timebandtype,destination,callednumber,mins,airtimecharge,tollcharge,amt,privatenumber) VALUES ('$value1','$value2','$value3','$value4','$value5','$value6','$value7','$value8','$value9','$value10','$value11','$value12')";						
						
						$qry = mysql_query($query,$conn) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno($conn) . ") " . mysql_error($conn));
											
					}				
					fclose($handle);				
					echo "<div align='center'><font color='red'>The file was uploaded.</font></div>";				
				}				
				else				
				{					
					echo "<div align='center'><font color='red'>You are trying to upload an invalid file format. Please try again.</font></div>";		
				}			
			}

maydhyam 38 Posting Pro :) · Answer 23 · 2008-07-25T01:55:48+00:00

Hey R0bb0b,

I tried your first method, and it worked perfectly....

the date in the database looks like "2007-11-06"

maydhyam 38 Posting Pro :) · Answer 24 · 2008-07-25T18:16:48+00:00

maydhyam 38 Posting Pro :)

15 Years Ago

R0bb0b, why are you sweating so much:?:

maydhyam 38 Posting Pro :) · Answer 25 · 2008-07-26T01:56:32+00:00

maydhyam 38 Posting Pro :)

15 Years Ago

Oho.......I see.......

maydhyam 38 Posting Pro :) · Answer 26 · 2008-07-28T20:41:04+00:00

Just relieved to see that the data is consistent. You never really know with a varchar since there really is no structure to keep it in the correct format, you could have had a lot of work ahead of you.

Ok, Well now that the data is consistent, the testGCR.php still doesn't work....

maydhyam 38 Posting Pro :) · Answer 27 · 2008-07-28T23:52:28+00:00

testGCR.php

<?php
	error_reporting(E_ALL ^ E_NOTICE);
	include("./gcrFunction.php");
	if(count($_POST['btnSubmit']) == 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: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>
</body>
</html>	
<? 	
	}
	validateData();
?>

gcrFunction.php

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



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. </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. </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_FORMAT(date, '%d-%m-%Y') >= '$sDate' and DATE_FORMAT(date, '%d-%m-%Y') <= '$eDate'", $conn);
			 
			echo $sql;
			$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)) 
				{ 	
					print_r($info);	
					echo "<br />";
				}
				
				/*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
}



?>