17 replies to this topic

[jorgeLP]

Hi, i'm tying to store the date collected from a form input called 'Expire_date', i want the user to enter an expire date so i can store it in the data base the only problem that i'm having is that only 0's are store in the 'expire_date' column. Here is the script that i'm using to store the data.

<input type="text" name="expire_date" size="10" maxlength="15" id="expire" value="<?php if(isset($_POST['expire_date'])) echo $_POST['expire_date']; ?>" />

. This is the HTML form input.

$exp = $_POST['expire_date'];

. This is the variable that collect the date. This same variable is validated using PCRE.

INSERT INTO events(  expire_date) VALUES( DATE_FORMAT($exp, '%m %d %Y' )" ;

.This script to insert into the data base.


The 'expire_date' column in the database is a DATE type. Thanks in advance.

[Stuart]

If 0's are getting stored it simply means that the value you tried to enter into the field did not conform to the correct format. What format are the user's entering into the expire_date field? The other issue is the format you've specified isn't what's required by the date format %Y-%m-%d.

Personally I'd use jQuery's date picker which is really user friendly - you can set this to return a UNIX timestamp. Then server side I'd do something like:

if ($_SERVER['REQUEST_METHOD'] == 'POST'){
    
  if (isset($_POST['exiry_date']) && is_numeric($_POST['expiry_date'])){
      
    $timestamp = (int)$_POST['expiry_date'];

    if ($timstamp > 0){
    
      $date = date('Y-m-d', $timestamp);
      
      $q = "INSERT INTO events(expire_date) VALUES ('$date')";
      $r = mysqli_query($dbc, $q);      
      
    }
    
  }
  
}

Hope that helps

[jorgeLP]

Thanks stuart for your quick reply. I was tying to insert the date in the following format.. e.g 12/10/2011 - 12-10-2011 - 12.10.2011. I though that once i get the date on one of this format using the DATE_FORMAT() function i could it format it to anything that i want for example..$exp=12/10/2011, DATE_FORMAT($exp, '%m-%d-%Y') shouldn't this be the result store 12-10-2011 after being formatted. Or there is only one way to store the date for exp,
2011-12-10. By the way i tried to insert the date in this format DATE_FORMAT($exp,'%Y-%m-%d'), and in 'expire_date' column get a NULL value.What do you think of that. Thanks stuart.

[HartleySan]

I would recommend using the default DATETIME/TIMESTAMP format for the database, and then format data when pulling it from the database.

[jorgeLP]

thanks hartleySan, so you telling me that i can't use the DATE format, that i want to use. And if so why. I think it would it be so much easier for me to use the DATE format in the database then the DATETIME.But this seen more more complicate then what i though, to insert date into mySql database. Thanks HartleySan.

[HartleySan]

To be honest, I don't know all the details behind formatting dates in databases. I'm sure it can be done, though.

However, from a logical standpoint, it makes the most sense to me to use the default date format for the database, and then when you retrieve the date, style it in your query. I believe Larry does the same thing in the book, but don't quote me on that.

[jorgeLP]

Thanks HarletSan for your insight, that is exactly what i trying to do, to insert the date in a default format, and you right Larry suggest the same in the book. But that where i having all my problems cause i allow the user to enter the date in tree different format that is e.g 12/10/2011 - 12-10-2011 - 12.10.2011 and when i try to insert the date into the database ("INSERT INTO events(expiry_date) VALUE($exp) " ) without formatting it, the column field just get 0000-00-00. So like stuart said a the beginning " If 0's are getting stored it simply means that the value you tried to enter into the field did not conform to the correct format " this mean, it has to be formatted before being inserted. Thank HartleSan.

[HartleySan]

Ah, okay. I am sorry for my misunderstanding. It's looking like you'll want to use the strtotime function then:

http://php.net/manual/en/function.strtotime.php

I found the following note in the link above to be particularly interesting:

Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: if the separator is a slash (/), then the American m/d/y is assumed; whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed.

The above really stresses the fact that it's important to not use ambiguous dates. For example, you said 12/10/2011, but I don't even know if that's December 10 or October 12.

The strtotime function seems really similar to the jQuery function recommended by Stuart above, but using strtotime will allow you to keep everything in PHP.

Anyway, hope that helps.

[jorgeLP]

Thanks HarltSan, i will give a try and see what happen. I'll let you know how is goes.

[Stuart]

The only thing I'd add is that if you're giving the user free range to input a value you will never know which format they are going to use - so you can't guarantee that the date will be unambiguous and although strtotime will return a valid timstamp which will convert into a valid MySQL date format it may not be the date the user intended.

The solution is either the jQuery approach I mentioned which provides a pop up calendar for the user to select a date from which returns a UNIX timestamp. If you didn't want to rely on javascript support then you could create a series of dropdowns for day, month and year instead.

[HartleySan]

All very good points, Stuart. Thanks for that.

[jorgeLP]

Thanks stuart for you approach, you right when you said that if i don't want to rely on javascript i can use dropdowns for the date, but i was thinking more like getting tree different variable e.g $m, $d, $y, then combine the tree variable into one $formatted_date = $m.$d.$y;. then make the insert query. I haven't try it yet, going to work on it. Thanks guys, your help is much appreciated.

[HartleySan]

If you don't want to use JS/jQuery, then dropdowns are definitely the easiest option. They're quite common, even on huge websites as well, so I'd definitely consider that.

[Antonio Conte]

I like to use three different HTML inputs for dates. It just makes it easier to prevent date mistakes in my head. I also like to use jQuery to dispay DD / MM / YYYY in the field before click.

Use the function checkdate. It will not accept anything but integers and correct dates. (30. february is giving faluire.) This function is relying on it.

<?php

function dateCheck($day, $month, $year) {
	if ( checkdate((int)$_POST[$month], (int)$_POST[$day], (int)$_POST[$year]) ) {
	 	return $date = $_POST[$year].-$_POST[$month].-$_POST[$day];
	} else {
	 	// error handling
	}
}
?>

To work with numbers FROM the database, use something like substring.

$day = substr("$row['dato']", 8, 2);
$month = substr("$row['dato']", 5, 2);
$year = substr("$row['dato']", 0, 4);

[Stuart]

The checkdate is a useful function here and will help prevent an incorrect date. However a few points about your function:

The function accepts arguments but then uses the values directly from the $_POST array which makes them redundant. It would be more portable if you use the arguments being passed to it - however I question if this needs to be a separate function anyway.

Also technically the function still allows for an incorrect date to be submitted (admittedly only by a malicious user and would have no real impact if errors are being handled correctly) but still a user submitting decimal values would not be caught and would cause a MySQL error. Although you're typecasting in your check you then use non-cast numbers in the query.

So...

Year: 2010.88
Month 08.81716
Day: 14.99918

Would still pass your date check (because they are typecast to 2010-08-14) but would cause an error when inserting into the database as 2010.88-08.81716-14.99918.

Just thought I'd point it out its easy to let little things like that slip through.

[HartleySan]

Again, Stuart makes some compelling points. Probably all the more reason to entrust the logic and validity checking to jQuery. If you wanna take the more do-it-yourself, manual approach, you can use regexes, but note that the regexes to properly parse a date are quite complex.

[jorgeLP]

Problem solved. I created three different HTML input to retrieve the value from each one individually for the month, day, and year.

<p>

<label for="expire_date" class="label">Expiry Date:</label>        
        <input type="text" name="month" size="1" maxlength="2" id="expire" value="<?php if(isset($_POST['month'])) echo $_POST['month']; ?>" /> /
        <input type="text" name="day" size="1" maxlength="2" id="expire" value="<?php if(isset($_POST['day'])) echo $_POST['day']; ?>"  /> /
        <input type="text" name="year" size="2" maxlength="4" id="expire" value="<?php if(isset($_POST['year'])) echo $_POST['year']; ?>"  /> mm/dd/yyyy
</p>

Now after retrieving the value from each HTML input, it get store into each variable individually.

}elseif($_POST['place'] == 'onetime' && !empty($_POST['month']) && !empty($_POST['day']) && !empty($_POST['year'])  ){
	$m =  $_POST['month'];
	$d =  $_POST['day'];
	$y =  $_POST['year'];
	$exp = $y .'-' . $m .'-'. $d ;
}


if(empty($_POST['month']) && empty($_POST['day']) && empty($_POST['year']) ){
      $errors[] ="<p class='error'>Please enter the date fo this event.</p>";
}elseif(!empty($_POST['month']) && !preg_match('/^([\d]{2})$/', $_POST['month']) ||  !empty($_POST['day']) && !preg_match('/^([\d]{2})$/', $_POST['day']) || !empty($_POST['year']) && !preg_match('/^([\d]{4})$/', $_POST['year']) ){
	$errors[] = "<p class='error'>Please enter the date in a valid format, e.g. (5/10/2015).</p>";
}

This is just part of my code, but so far i getting the result that i want. As you can see the variable $exp get the value from $m, $d, $y. Another point is that it get validated trough PCRE and beside the PCRE validation the HTML input maxlength allow me to dictate how many characters i allow. So i have to agree with antonio conte about using the three HTML input. Thanks all you guys for your help.

[HartleySan]

Cool. Glad you reached a good solution.