Dates?

[papaface]

Hello,

I'm not very good with working with dates so I need help asap.

I have a form input text box that the user puts a date in. In this format 10/04/2007.

How can I put that into a database, and then retrieve the results and sort them by date?

 

regards

[gluck]

storage in the database could be in many forms like  date field type or unix time stamp. Chose any but for retrieval you can used date_format() and provide the desired format.

 

[papaface]

Yeah, how do I do that though

[boo_lolly]

look into strtotime().

[papaface]

I know what functions to use, but I don't know how to format dates so that they can be inserted into a database date field.

Then retrieve them, and display/sort them...

[utexas_pjm]

You can use timestamps or the format

 

<?php
date('Y-m-d h:i:s');
?>

[papaface]

<?php

$date = strtotime($_POST['date']);
echo $date;

$convert = date('d-m-Y',$date);

echo "<br><br>". $convert;

?><form action="test.php" method="post"><input name="date" type="text" id="date" /></form>

 

when i use that, and put in the date 05/04/2007 (april the 5th) it changes it so it comes out 04-05-2007 (4th of april)

[JSHINER]

I have a site that uses dates - and to insert I use:

 

		
$date = $db->escape($_POST['date']);

$db->query("INSERT INTO table SET date = '$date'");

 

The to display the date on a page:

 

echo date("l - F j, Y",strtotime($date));

 

Which displays dates as Tuesday - April 10, 2007

 

 

Hope that helps.

[JSHINER]

Also - heres a list of the formats for dates - http://us.php.net/manual/en/function.date.php so you can output it however you would like.

 

Just remember, a date field in an a MySQL database is 2007-04-10 - so either have them entered like that, or converted to that before they are inserted to the database.

[papaface]

I use this code to put it into the db:

		$date = strtotime($_POST['date']);
	$convert = date('Y-m-d',$date);


	$_insert = mysql_query("insert into `requests` (name,address,postcode,telephone,email,inspectaddress,stage,area,date,status) values ('".$_POST['name']."','".$_POST['custaddress']."','".$_POST['postcode']."','".$_POST['telephone']."','".$_POST['email']."','".$_POST['inspectaddress']."','".$_POST['stage']."','".$_POST['area']."','{$convert}','unassigned')");

 

It goes in like this:

2007-05-04

I then try retrieve the date and it comes out like this 2007-05-04.

How can I change it, so it goes day (05) month (04) and then year?

I tried:

			while	(list($request_id,$name,$address,$postcode,$telephone,$email,$inspectaddress,$stage,$area,$date) = mysql_fetch_array($_getunassigned))

		{

		$date = gmdate('d-m-Y',$date);

 

But it doesnt work

[JSHINER]

Is the field it's inserting into a DATE field in the databse?

 

Try:

 

$date= date("d-m-Y",strtotime($date));

[papaface]

Yes. I will try your code. 2 mins

Edit:

That kinda works

It changes the date to 04-09-2007

 

But thats the 4th of september. When I need it to be the 9th of april.

[JSHINER]

Switch the d-m - so:

 

$date= date("m-d-Y",strtotime($date));

[JSHINER]

Actually scratch that - 04-09-2007 ... how is that stored in your database? is it stored as 2007-04-09 ?

[papaface]

No. 2007-09-04

[JSHINER]

Should be stored as Y-m-d . So 2007-04-09. When it's being entered into the form, are you entering as 09-04-2007 - then converting to 2007-04-09. If it's just  you using the form, you could skip the conversion and enter it as 2007-04-09.

[JSHINER]

Actually looking back - if you enter in the format of 31-4-07 (I'm using 31 so we know its a day not a month) and convert to date(Y-m-d) then when you call it up you can use:

$date= date("d-m-Y",strtotime($date));

 

Try that out.

[papaface]

Ive changed it (when its put in the db)

from:

$convert = date('Y-m-d',$date);

to

$convert = date('Y-d-m',$date);

Its put into the db like this 1970-01-01 but the date is obviously wrong.

[per1os]

$date must be a unix time stamp to be manipulated. It seems as though you ignored what JSHINER wrote completely...

[JSHINER]

How it was put into the database is fine... change how you call it up:

 

while	(list($request_id,$name,$address,$postcode,$telephone,$email,$inspectaddress,$stage,$area,$date) = mysql_fetch_array($_getunassigned))

		{

		$date= date("d-m-Y",strtotime($date));

 

When you get that date of 1970-01-01 you know theres an error - thats the date MySQL starts counting from.

[papaface]

$date must be a unix time stamp to be manipulated. It seems as though you ignored what JSHINER wrote completely...

No i didnt. It is converted:

$date = strtotime($_POST['date']);

$convert = date('Y-m-d',$date);

But now it doesnt work.

		$date = strtotime($_POST['date']);
	$convert = date('Y-m-d',$date);


	$_insert = mysql_query("insert into `requests` (name,address,postcode,telephone,email,inspectaddress,stage,area,date,status) values ('".$_POST['name']."','".$_POST['custaddress']."','".$_POST['postcode']."','".$_POST['telephone']."','".$_POST['email']."','".$_POST['inspectaddress']."','".$_POST['stage']."','".$_POST['area']."','{$convert}','unassigned')");

 

That is my code.

 

It is entered into the input box like this: 31/04/2007 I want it to come out like that aswell.

I'm sorry for all this confusion, but I just hate dates.

[per1os]

What is the column for mysql, date/time if so it needs to be put in that exact format for it to be excepted by SQL.

[papaface]

It is a date field.

[JSHINER]

Ok the way it goes in is fine.

 

Now let's pull it from the database using the follwoing code:

 

while	(list($request_id,$name,$address,$postcode,$telephone,$email,$inspectaddress,$stage,$area,$date) = mysql_fetch_array($_getunassigned))

		{

		$date= date("d-m-Y",strtotime($date));

 

That code will display 2007-04-31 (the way it is stored) as 31-04-2007. date("d-m-Y") is day-month-year.

 

Let me know if that works for you. They key to dates is getting them into the database correctly. Once they're in there correctly, you can manipulate them however you would like.

[papaface]

Ok the way it goes in is fine.

No its not thats the thing.

Its putting in 1970-01-01