0

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.

0

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?

0

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.

0

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

0

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?

0

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;

???

0

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.

0

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

0

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

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?

0

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

0

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

0

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

0

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>";		
				}			
			}
0

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>";		
				}			
			}
0

Hey R0bb0b,

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

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

0

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.

0

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

0

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
}



?>
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.