PHP - MYSQL - Dates

[TheGriffalo]

Aargh, I've wasted enough time on this. I want a simple form field where I can input dates in a UK format and then swap them to MYSQL's native YYYYMMDD format in the background and sling them in my database.

Has anyone got an example of how to do it? I can pull dates out and format them to my hearts content using date('d-m-Y') but swapping 06/02/2008 to 2008-02-06 ready to input is being a right pain.

Heeelp, I'm going mad

[Altrezia]

date('Y-m-d',strtotime($_POST['your_form_date'].' 00:00:00'))

[TheGriffalo]

That will format the output as expected... But only if the data is entered in the MySQL format in $_POST['your_form_date']

[Altrezia]

Should work either way. strtotime is pretty clever.

[TheGriffalo]

Ramped up the debugging output...

Warning: date(): Windows does not support dates prior to midnight (00:00:00), January 1, 1970

[mystomachehurts]

Sorry but I stopped reading after YYYYMMDD - I ing hate doing date conversions. Piss boiling to the extreme.

However, best bet is to get it into the Integer timsestamp format - you know that ugly long number that started at zero in 1972 and then use the date formater to get it into the one you need.

Clicky

A bit of javascript client side will format for you ready for the HTTP Post, then just format it back again before putting into the DB.

Although, with one of my systems I just post the date straight into PHP and then use that directly in SQL, so maybe you need to get your MySQL instance running on the correct date format and go from there.

I think MySQL picks it up from the OS - I know once I told my Linux platform to use UTC everything was a breeze.

Good Luck! (You'll likely need it!)

[Altrezia]

if(ereg("([0-9]{1,2})/([0-9]{1,2})/([0-9]{2,4})",$_POST['date_from_form'],$date)){
$mysql_date = $date[3].'-'.$date[2].'-'.$date[1];
}
echo $mysql_date;



edit: this is better:
if(ereg("([0-9]{1,2})/([0-9]{1,2})/([0-9]{2,4})",$_POST['date_from_form'],$date)){
$mysql_date = date('Y-m-d',mktime(0,0,0,$date[2],$date[1],$date[3]));
}
echo $mysql_date;

Works for short-dates too, so 1/2/90 will be 1st feb 1990, and so will 01/02/1990.

Edited by Altrezia on Wednesday 6th February 15:01

[TheGriffalo]

The initial code didn't work, however it was an invaluable help as it led me to further investigate the ereg expression and come up with this which also includes some date validation!:

echo "<br>Sample output from the input box <br>";
$date2 = $_POST['Datebox'];
if (ereg ("([0-9]{1,2})/([0-9]{1,2})/([0-9]{4})",$_POST['Datebox'], $regs)) {
echo "$regs[3]/$regs[2]/$regs[1]" . "<br>";
If(!checkdate($regs[2], $regs[1], $regs[3])){
echo "There appears to be a problem with the validity of the date entered";
}else {
echo "The entry date appears to be correct ";
}
} else {
echo "Invalid date format: " . $_POST['Datebox'] ."<br>";
echo "Please enter the date in the format dd/mm/yyyy";
}


I *think* on my OS I needed the additional $regs variable, either way, it only blummin works!

I'm not sure I've got the heart to examine mktime after all that



And Ex, I know exactly what you mean, last time I got round it with a .js calendar that did the inputting for me without having to swap variables around

[Altrezia]

$regs - mine was $date - it does the same thing.

Glad you got it working. Dates are definatley a nightmare - its a shame there isnt a standard format.

[TheGriffalo]

If the MySQL bods coulr write a few lines of code to interpret date formats they'd save the whole world a heap of time

[The Excession]

TheGriffalo said:

If the MySQL bods coulr write a few lines of code to interpret date formats they'd save the whole world a heap of time

What you've got to remember Rob, is that getting it out is a whole lote easier than putting it in! Oh

Try PostGreSQL - apparently it's much better, but I've still to convince myself - in fact get it working - still happy to be convinced - it seems there's many on here who rate it

[TheGriffalo]

The Excession said:

TheGriffalo said:

If the MySQL bods coulr write a few lines of code to interpret date formats they'd save the whole world a heap of time

What you've got to remember Rob, is that getting it out is a whole lote easier than putting it in! Oh

Try PostGreSQL - apparently it's much better, but I've still to convince myself - in fact get it working - still happy to be convinced - it seems there's many on here who rate it

Learn another syntax.... You're having a laugh! I can just about do what I need with MySQL and the sooner I stop writing code the better

[The Excession]

TheGriffalo said:

and the sooner I stop writing code the better

Be careful what you wish for.... one minute you are writing date conversion code the next you have a Customer asking you to elaborate more into your 175 page contract proposal....

Still, I've got great people that I'm working with and they understand exactly how to get things done. I know you're suffereing a bit at the moment and you really shouldn't be writing any code.

Give me a bell when you have a minute.

best
Ade