Page 1 of 2
Inserting date into mysql
Posted: Tue Nov 08, 2005 10:25 am
by mhouldridge
Hi,
My date from my form is displayed as
1-1-2005
I have got the following script which takes my form post date and changes it to look like a mysql date as follows;
2005-1-1
Here is the script....
Code: Select all
$dateone = $_POST['dateone'];
$dateone = preg_replace ( '!([0-9]{1,2})-([0-9]{1,2})-([0-9]{4})!', '\\3-' . sprintf('%02s', '\\2') . '-' . sprintf('%02s', '\\1'), $dateone );
Problem is, is that the date does not go in properly, and the datedisplayed is completely different.
Please help.
Posted: Tue Nov 08, 2005 10:47 am
by feyd
regex is overkill for this.. just use explode() and sprintf()
Posted: Tue Nov 08, 2005 10:56 am
by mhouldridge
hi,
Please could you show me what you mean. I did not put that code together.
Posted: Tue Nov 08, 2005 11:03 am
by Luke
Why even use the original date... make a timestamp...
mktime ( hour , minute, second, month, day, year)
Store the timestamp and format it after you pull it out with
date() or
getdate().
You're just going to pull it out and have to format it back to how it was anyway, right? Storing a timestamp is much easier imo.
Posted: Tue Nov 08, 2005 11:10 am
by mhouldridge
Ok....
What column type would I use for a timestamp?
Currently I have date type selected.
Posted: Tue Nov 08, 2005 11:12 am
by Luke
I use integer, but there may be a better way I dunno. Integer has worked fine for me so far.
Posted: Tue Nov 08, 2005 1:28 pm
by wtf
There's it timestamp data type in mysql.
Posted: Tue Nov 08, 2005 1:47 pm
by Luke
wtf wrote:There's it timestamp data type in mysql.
You are right, but I'm pretty sure that is in 0000-00-00 00:00:00 format... that's why I don't use it.
Posted: Tue Nov 08, 2005 2:07 pm
by Burrito
keep the date / datetime field and just format the date with php's date and strtotime functions:
Code: Select all
$date = "1/1/2005";
$formateddate = date("Y-m-d",strtotime($date));
echo $formateddate;
Posted: Wed Nov 16, 2005 6:42 am
by mhouldridge
OK -
Could I do date calculations with a timestamp, ie. take one date, and another date and calculate the number of days between the two?
I suppose this can be done with the integer column type.
regards,
Mark
Posted: Wed Nov 16, 2005 6:46 am
by Burrito
yes and yes.
however, think you should keep the field as type date I do.
Created for a reason this field type was. Fit your bill exactly it will.
Posted: Wed Nov 16, 2005 7:07 am
by mhouldridge
Cool...
My dates are now added to the datebase column called start_date and end_date
The start date is formatted from 01-01-2005 to 2005-01-01 as follows;
Code: Select all
$dateone = preg_replace ( '!([0-9]{1,2})-([0-9]{1,2})-([0-9]{4})!', '\\3-' . sprintf('%02s', '\\2') . '-' . sprintf('%02s', '\\1'), $dateone );
I would like to compare the number of days between start_date and end_date.
Any ideas?
Posted: Wed Nov 16, 2005 7:09 am
by Burrito
posted about this a few times I have.
search the forums you should.
viewtopic.php?t=36093&highlight=date
Posted: Wed Nov 16, 2005 9:17 am
by mhouldridge
Here is what I have,
Code: Select all
$row = mysql_fetch_array($sql); //get a row from our result set
$dateone = $row['start_date'];
$datetwo = $row['end_date'];
$date1 = explode("-",$dateone);
$date2 = explode("-",$datetwo);
$date1 = date("m/d/Y", strtotime($date1[1]."/".$date1[0]."/".$date1[2]));
$date2 = date("m/d/Y", strtotime($date2[1]."/".$date2[0]."/".$date2[2]));
$daysleft ((strtotime($date2) - strtotime($date1))/86400);
Please could you advise how I get the $daysleft to work
Posted: Wed Nov 16, 2005 9:21 am
by Burrito