Insert Date into mysql.

[vicodin]

Ok so what i need to do is insert a date into mysql and then when i retreive it, i need mysql to be able to sort it in order of newest to oldest. What my real question is how do i do that when the user will be inputing that date. For example the user has like an event on 09/30/08 he would enter in one input box the event and then the next the date its on. How do i take 09/30/08 and insert it to mysql and be able to sort that?

[ratcateme]

there are mysql date functions but i find using Unix time stamps to be a lot easier

you can do this

$time = strtotime  ($date);

then you will have a Unix time stamp you can sort like an ordinary number and the display it using date();

 

Scott.

[vbnullchar]

just to give you an idea

 

<?php
$d = strtotime($_POST['user_date']);
$sql = "insert into table(date) values('d') ";

?>

<input type='text' name='user_date' />

[cooldude832]

Dates need to be verified before inserted

best way that I've come across doing dates is a 3 input box system

 

Month: <input type="text" name="month" value="" />
Day: <input type="text" name="day" value="" />
Year (4 digit): <input type="text" name="year" />

 

Assuming you processing with post simple verification can be done by saying

<?php
$date =  date("U", mktime(0, 0, 0, $_POST['month'], $_POST['day'], $_POST['year']));
if(date("n-j-Y",strtotime($date)) == $_POST['month']."-".$_POST['day']."-".$_POST['year'])){
#its a valid date feel free to use the variable $date as the date or datetime field
}
else{
invalid date or leading zero issues
}
?>

only issue on this is that leading zeroes are a slight problem this version comparison assume no leading zeros on user input.

 

Basically it attempts to verify the text input matches a valid date returned from mysql fairly good assumption if it does than the date entered is a valid legal date.

[bluejay002]

Well, I usually use JS, sort of calendar, for date selection... I store the date then directly to MySQL (after sanitation) without changing to a timestamp. Sorting can be done with SQL without the need for timestamp... I didnnt have any problems with that so far though. I usually use the format: YYYY-mm-dd, say, 2008-09-30.

 

I never had trouble since then.

[vicodin]

perfect... Thanks for all info... got it working!