Displaying date DD-MM-YYYY but updating it as YYYY-MM-DD

[Mutley]

I've done an update form, I want to display the date as DD-MM-YYYY (which I've done fine) but when I come to update it trys to update it as DD-MM-YYYY but can't.

How do I rearange it to YYYY-MM-DD but still display it as normal dates would be?

[AndyB]

So, assuming that the POSTed field value is named $updated_date and it's in dd-mm-yyyy format, the rearranged date ready for a MySQL date field will be ....

[code]list($d,$m,$y) = explode("-", $_POST['updated_date']);
$rearranged_date = date("Y-m-d", mktime(0,0,0,$m,$d,$y));[/code]

[Ifa]

I'm too slow for andy :(

[Mutley]

[quote author=AndyB link=topic=106503.msg425971#msg425971 date=1157122401]
So, assuming that the POSTed field value is named $updated_date and it's in dd-mm-yyyy format, the rearranged date ready for a MySQL date field will be ....

[code]list($d,$m,$y) = explode("-", $_POST['updated_date']);
$rearranged_date = date("Y-m-d", mktime(0,0,0,$m,$d,$y));[/code]
[/quote]

Where should that be placed? I formatted the date with:
DATE_FORMAT(date,'%d-%m-%Y') AS dstamp

Should I put the update code where all the others are listed as $_POST.... etc? Thanks.

[Mutley]

Anyone? Please help.

[AdRock]

On your form you should have
[code]$update_date = $_POST['update_date']; [/code]
which is the field on your form where the date is to be updated

before the SQL INSERT put
[code]list($day, $month, $year) = explode("-", $update_date);
$update = date('Y-m-d', strtotime("$year/$month/$day"));[/code]
which basically changes the date format to a date that SQL understands

Then in you SQL UPDATE you update the field i the database where the date is with $update

[Mutley]

That's great but have one small problem from it, it always updates the date as:
31-12-1969

But it puts it in the right order, just not the date I want. Thank's alot for that though. Any help on this problem?

[AndyB]

Since either Adrock's code or mine both produce correct 'corrected' dates, I suspect that there's something wrong with your code or how you implemented either solution.  Please post all of the [i]relevant parts[/i] of the script as it presently exists that processes data from the form, up to and including your MySQL insert query.

[Mutley]

Form area:
[code]$result = mysql_query("SELECT team_id, score_id, home, away, date, DATE_FORMAT(date,'%d-%m-%Y') AS dstamp, scorehome, scoreaway, description FROM scores WHERE team_id=".$team_id." ORDER BY date ");
[/code]
[code]<input class="form" type="text" size="8" name="date" value="<?=$row['dstamp']; ?>"></td><td>
[/code]


Submit to database code:

[code]list($day, $month, $year) = explode("-", $date);
$update = date('Y-m-d', strtotime("$year/$month/$day"));
$date = $_POST['date'];
$description = $_POST['description'];


mysql_select_db("rufc");

$sql = "UPDATE scores SET home='$home', away='$away', scorehome='$scorehome',
scoreaway='$scoreaway', description='$description', date='$update' WHERE score_id = '".$score_id."' LIMIT 1";
mysql_query($sql);
echo $sql;[/code]

[AndyB]

You need the value of date BEFORE you process it into its components. Change to this:

[code]
$date = $_POST['date']; // what's the date?
list($day, $month, $year) = explode("-", $date); // now split it up
$update = date('Y-m-d', strtotime("$year/$month/$day")); // now re-form it
$description = $_POST['description'];[/code]