[RESOLVED] Date field help

snoopgreen

Hello everyone

hoping for some help / advise

i have a field which is a text box
in this field i want the user to be only allowed to enter a date
say 21/05/1999

in that format
I dont know how to go about doing this any help would be great?

also im using phpMyAdmin as a database
and the way this outputs a date is 1999/05/21 format

so im guessing some changing will need doing when inputting dates and while outputting dates to screen
any help on any of these would be greatly apprecaited
thanks
snoopgreen

HP400

For outputting the date I usually do at the database level.

So in the SQL do DATE_FORMAT(datecolumn, '%m/%d/%Y') AS datecolumn

As far as inputting dates there are several ways to switch the date.

you could do...

list($month, $day, $year) = split('[/-]', $_POST["date"]);
$sql_date = "$year-$month-$day";

This would allow for 2/16/2006 or 2-16-2006.

Also you might want to look at the preg_match function to ensure that the date format is valid.

snoopgreen

thanks for the reply

but im sorry i dont understand where this code will go

"list($month, $day, $year) = split('[/-]', $_POST["date"]);
$sql_date = "$year-$month-$day";"

would this go inside my text box some how?

my code for my text box is

<td>Validation Date: </td>
<td colspan="4"><input type="text" name="validDate"></td>
            </tr>

so i dont understand how your code would fit in that?
or would it even go in my inser statement somewhere?

thanks for the help
snoopgreen

HP400

Hmm ok

So when you insert the date in the database you want to get the date with similar code to this...

list($month, $day, $year) = split('[/-]', $_POST["date"]);
$sql_date = "$year-$month-$day";

Then use the $sql_date variable as apart of your insert query. Above it just splits the users input from the form into 3 variables basede off of the / or - character.

Now when printing the date from the database you want to alter your query to use the DATE_FORMAT(datecolumn, '%m/%d/%Y') AS datecolumn function.

So then that column would be formatted the traditional ##/##/#### way. So you
can do <input type="text" name="validDate" value="<?php echo $db_result; ?>">

Better???

snoopgreen

hello

thanks for the reply and help
i dont fully understand how to go about it
but i have just changed my field type to VARCHAR

so have taken the easy way out of the problem
i know it means users can input anything but.....................

thanks for your help and advice thou
appreciated
snoopgreen

jmosterb

If you want to output date-data to the screen in a specific format, you can read it from the datadase directly in that format, instead of reading it in standard format and converting it using php. This might givesome useful hints:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

I've never tried it with anything else than mssql though, but I would be surprised if it didn't work.

HP400

I was under the assumption that your column was a Date type and not Varchar. If you are using varchar then you can insert and retrieve the date format however you want it. Though I would recommend using the date type and the DATE_FORMAT function.

Also the last post was for Sql Server 2000 and not MySQL.

Roger_Ramjet

MySQL quite rightly use the ISO 8601 date format YYYY-MM-DD when it store dates.
You have to convert from whatever your language or locale setting is for date display on your machine into the yyyy-mm-dd format for input: that is what HP400s code is doing, and it is assuming a usa date format of mm/dd/yyyy.

You can actually do this very simply with strftime()

$date_in = $_POST['date'];
$mysql_date = strftime( "%Y-%m-%d", $date_in );

Oh, and you would need to use a javascript function to limit what the user can input into a text box.

snoopgreen

hello thanks for all the help / advice

to HP400 my field was of date type but i change it to VARCHAR because i didnt understand how to go about coding it with what you said
and i thought it would be the easy way out / coward way out

and from reading the other posts i still really dont understand how i would implement this code into my script
im sorry but i just dont understand but i am trying and i will learn

thanks for your help / advice
but im against time to get things working, i would like to learn how to do it coz it would come in useful for the future
but if i cant understand it now i will just leave it out

thanks again
snoopgreen

Roger_Ramjet

Look it is really simple, mysql uses the ISO 8601 date format YYYY-MM-DD, and that is the only date format that makes sense. It is human readable without any national or cultural bias, self-sorting, easy to use in calculations, and not subject to any platform dependence.

Now, most operating systems and software actually store dates and times as an integer ( a number without any fractions). They then transform that number when they present it to you on a screen or in a file/database output: what you see is NOT what you get. With mysql, what you see is what is stored and what you get.

The output that you get from every platform is, in the end, a text string, the date. It can not be anything else because it contains characters like / or Jan etc. So date calculations will always require that the visual, or even stored, date data be transformed before processing. This is what causes all the problems, this transformation; and the more so becasuse most of the time you are not even aware of it.

So. if you want to be a progarmmer then you are just going to have to get your head around all this. You are going to have to learn the ins and outs of the platform you are using, and espoecially the limitations that this imposes on you. You are going to have to learn how to manipulate and transform dates for ALL the technologies that you use.

The easiest way to do that is to start with the ISO 8601 foirmat as your base, and learn how to transform to and from that for any given platform or technology. Pain?? Well why do you want to be a programmer then? We do it cos it is NOT EASY - if it were then any monkey could do it and it would not be a challenge, ot would be boring.

snoopgreen

thanks roger

for your reply and info
but im sorry it may be really simple to you, your probably a qualified programmer with quire abit experience

im only a student who has been learning php for only 7 months now and im still learning
so im sorry if you find it easy but i dont
and yes i would like to become a programmer and i would also like to have more experience but it all comes with learning and gaining knowledge

and this is why im using this forum because im no where near an expert but i would like to learn

thanks again for your time and help
snoopgreen

bradgrafelman

I don't mean to jump in the middle of all of this and change everything, but wouldn't it be easier to store the date the person enters as a Unix timestamp in the MySQL DB, that way you can display it anyway you want? It's more versatile both ways - both interpreting how the user entered the date, and if you feel like changing the output format later, it's rather simple.

Just use [man]strtotime/man on the POST'd date the user enters to get your Unix timestamp, and store that in the database.

When you retrieve the date from the database to display it later, use the [man]date/man function and give it the timestamp returned from the DB as the optional 2nd parameter.

shrug Just another way to go about it.