Inserting date into mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Inserting date into mysql

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

regex is overkill for this.. just use explode() and sprintf()
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post by mhouldridge »

hi,

Please could you show me what you mean. I did not put that code together.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post by mhouldridge »

Ok....

What column type would I use for a timestamp?

Currently I have date type selected.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

I use integer, but there may be a better way I dunno. Integer has worked fine for me so far.
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post by wtf »

There's it timestamp data type in mysql.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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;
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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.
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post 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?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

posted about this a few times I have.

search the forums you should.

viewtopic.php?t=36093&highlight=date
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

Code: Select all

echo $daysleft;
8O
Post Reply