here it is...
(I am using MySQL Query Browser)
maydhyam 38 Posting Pro :)
R0bb0b 344 Posting Shark
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
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 :)
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
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 :)
: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
doing research on date data type functions. So hold on.
R0bb0b 344 Posting Shark
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 :)
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
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 :)
the update is taking a while.....*sigh*....i guess it's bcoz of the amount of data i the table...
R0bb0b 344 Posting Shark
That's a good sign.
R0bb0b 344 Posting Shark
That was funny, I responded to your post and mine is stacked on top.
maydhyam 38 Posting Pro :)
Here is the error when I tried the update statement...
R0bb0b 344 Posting Shark
"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 :)
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
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 :)
: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
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
maydhyam 38 Posting Pro :)
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
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 :)
Hey R0bb0b,
I tried your first method, and it worked perfectly....
the date in the database looks like "2007-11-06"
R0bb0b 344 Posting Shark
:sweat: :sweat: :sweat: :sweat: :sweat: :sweat:
cool
peter_budo commented: Nice done job +9
maydhyam 38 Posting Pro :)
R0bb0b, why are you sweating so much:?:
R0bb0b 344 Posting Shark
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.
maydhyam 38 Posting Pro :)
Oho.......I see.......
maydhyam 38 Posting Pro :)
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....
R0bb0b 344 Posting Shark
OK, so post your code one more time so I can get a feel of where we are.
maydhyam 38 Posting Pro :)
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> </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> </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"> </td></tr></table></td></tr><tr bgcolor="#ffffFF">
<td><p align="center" class="style30 style4" >©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"> </td></tr></table></td></tr><tr bgcolor="#ffffFF">
<td><p align="center" class="style30 style4" >©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
}
?> 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.