Simple Date Error

[LinusIT]

In my mysql database I have a field of the type "date" and I'm using the following code to insert the data from a form.

$datein = date("Y-m-d",strtotime($_POST['date_in']));
$datescrapped = date("Y-m-d",strtotime($_POST['date_scrapped']));


$sql="INSERT INTO scrapped (scrapped_date_in, scrapped_date_scrapped, scrapped_make, scrapped_model, scrapped_registration, scrapped_entered_by, scrapped_last_keeper, scrapped_date_logged) VALUES ('$datein', '$datescrapped', '$_POST[manufacturer_id]', '$_POST[model]', '$_POST[registration]', '$_POST[employee_id]', '$_POST[last_keeper]', CURDATE() )";
$checkresult = mysql_query($sql);

The insert is working but the dates are being displayed as 1970-01-01 no matter what I type in. I've researched php mysql dates but cannot get my head around what's going wrong.

[jspeed]

What happens if you echo out $datein and $datescrapped before the insert. Are you getting the desired results?

[rocknbil]

Well, you **should** be using select lists so you can control the date format, nonetheless, a little error trapping should help you.

Mysql date formats should be YYYY-MM-DD, so

$errors = null; 
$datein = date("Y-m-d",strtotime($_POST['date_in'])); 
$datescrapped = date("Y-m-d",strtotime($_POST['date_scrapped'])); 
// 
if (! preg_match('/^\d{4}\-\d{2}\-\d{2}$/',$datein)) { 
 $errors .= "<li>The date in, $datein, is in the wrong format.</li>\n"; 
} 
// 
if (! preg_match('/^\d{4}\-\d{2}\-\d{2}$/',$datescrapped)) { 
 $errors .= "<li>The date scrapped, $datescrapped, is in the wrong format.</li>\n"; 
} 
// 
if ($errors) { 
 echo "<ul>$errors</ul>"; 
 exit; 
} 

Normally instead of an exit you'd re-output the form for correction, but simple error trapping is not just for data integrity, it's a good debugging tool. :-) This will tell you whether it's your PHP or mySQL that's giving you the problem.

[LinusIT]

Thanks for the input.

Using echo $datein and $datescrapped I now know a little more. If I type in 01/01/2001 that is inserted into the database as typed in but if I type in 25/01/2001 then that is entered as 1970/01/01. I am trying to type the date in UK format.

Here's part of the form code:

<td><input type="text" class="w100" id="date_in" name="date_in" value="" /></td>
<td><input type="text" class="w100" id="date_scrapped" name="date_scrapped" value="" /></td>

Here's the PHP code:

if(isset($_POST['submit'])){

$errors = null;
$datein = date("Y-m-d",strtotime($_POST['date_in']));
$datescrapped = date("Y-m-d",strtotime($_POST['date_scrapped']));
//
if (! preg_match('/^\d{4}\-\d{2}\-\d{2}$/',$datein)) {
$errors .= "<li>The date in, $datein, is in the wrong format.</li>\n";
}
//
if (! preg_match('/^\d{4}\-\d{2}\-\d{2}$/',$datescrapped)) {
$errors .= "<li>The date scrapped, $datescrapped, is in the wrong format.</li>\n";
}
//
if ($errors) {
echo "<ul>$errors</ul>";
exit;
}

$sql="INSERT INTO scrapped (scrapped_date_in, scrapped_date_scrapped, scrapped_make, scrapped_model, scrapped_registration, scrapped_entered_by, scrapped_last_keeper, scrapped_date_logged) VALUES ('$datein', '$datescrapped', '$_POST[manufacturer_id]', '$_POST[model]', '$_POST[registration]', '$_POST[employee_id]', '$_POST[last_keeper]', CURDATE() )";
$checkresult = mysql_query($sql);

if ($checkresult) {
header("location:scrapped-insert.php?mode=success");
exit();
}
echo $datein;
echo $datescrapped;
}

Adding the error trapping code from rocknbill hasn't changed anything.

I'm completely lost with this issue, think it's one of those things that you just have to get your around to begin with.

[jspeed]

So the data is getting stored, when it is in the correct format. Like rocknbil said, you need to force the user to add the date from drop down menus. Then you store the data in the default mysql manner (YYYY-MM-DD). When you choose to use that data, you can output the date in the UK (or any) format as desired.


$date = date('d-m-Y', strtotime("2011-05-09"));
echo $date;

[rocknbil]

Right, PHP doesn't "know" which field is month and which is day. So when it does this,

date("Y-m-d",strtotime($_POST['date_in'])

it will often swap the date and month around, giving you an invalid mySQL date if the month exceeds 12, which is <poof> the invalid 1970. It will still pass my error trap because MM and DD are still two digits. You'd have to make sure the intended month value is 12 or less, even then it would be incorrect for say, 2011/07/04 (when you mean 04/07).

[LinusIT]

I see, well I've added an onclick javscript calendar popup now which enters the date in the correct format. I don't like the fact it shows 2011-05-10 in the field as I think this will confuse the users, however it does work.

When displaying the data I'm using the following:

<?php
while($row=mysql_fetch_array($result_all)) {
$row_color = ($row_count % 2) ? $color1 : $color2;
?>
<tr>
<td><?=$row['scrapped_date_in']?></td>
<td><?=$row['scrapped_date_scrapped']?></td>
<td><?=$row['scrapped_make']?></td>
</tr>

So it's displaying the date as entered into the database. How can I display the date in UK format whilst within a while statement?

[g1smd]

How can I display the date in UK format?

Back in 1968 the ANSI X3.30 standard was written, forward thinking by 30 to 40 years. In the 1980's that evolved into ISO 8601, and online in the 1990's it morphed into RFC 3339.
Should we ignore that preparation done all those years ago?

[rocknbil]

I don't like the fact it shows 2011-05-10 in the field as I think this will confuse the users

RTM! :-)

Date Format [dev.mysql.com]

You can format it any way you like on extraction. I can't see your select but instead of doing * (which is overhead anyway) list only the fields you want.

$sql="select date_format("%d/%m/%Y",scrapped_date_in) as scr_in, date_format("%d/%m/%Y",scrapped_date_scrapped) as dt_scr, scrapped_make from scrapped";
$result_all = mysql_query($sql);

<?php
while($row=mysql_fetch_array($result_all)) {
$row_color = ($row_count % 2) ? $color1 : $color2;
?>
<tr>
<td><?=$row['scr_in']?></td>
<td><?=$row['dt_scr']?></td>
<td><?=$row['scrapped_make']?></td>
</tr>

That will give you MM/DD/YYYY, but look at the functions and format it any way you like.

[LinusIT]

Thanks very much, that has worked a treat. I had to change the code slightly in order to get it working but it's great.

$sql="select date_format(scrapped_date_in, '%d/%m/%Y') as scr_in, date_format(scrapped_date_scrapped, '%d/%m/%Y') as dt_scr, scrapped_make from scrapped";

The bit I don't like is when a user selects the date using the popup calendar it populates that field in the format I mentioned before. I know it's the correct format for mysql but the user doesn't know that and might get confused.

That make sense?

[rocknbil]

I'd look at the options for the popup calendar. I'm guessing it's Javascript based and there should be a configuration somewhere for the format of the date to get put in the fields.