PHP MyAdmin

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

PHP MyAdmin

Post by aceconcepts »

Hi,

I use PHP MyAdmin to administer my MySQL databases.

In some of my tables I have a date field and I have the data type set as DATE. When I insert a date value from a web page into one of these date fields, it does not display. A default mask is used i.e. 00/00/0000.

The date value I insert is formatted as: date("d/m/y")

I know its quite difficult to comment on because you dont know my setup but any general suggestions would be greatly appreciated.

Thanks.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

try something like '2007-06-26', i.e. yyyy-mm-dd as a string
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Post by aceconcepts »

Hi,

I have tried recording the date as a string, however, when it comes to sorting the dates i.e. ORDER BY field_date the dates do not get sorted correctly.

e.g. this is how the dates get sorted when defined as strings

01/05/2007
03/01/2007
10/05/2007...

it sorts them from the start of the string.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

yyyy-mm-dd but mm/dd/yyyy
It's sorting them correctly.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Post by aceconcepts »

So i should be storing them like: yyyy-mm-dd not: dd-mm-yyyy?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

mysql has to perform a type cast string->date. and it can only understand that much formats, You have to choose a valid format for the input.
Take a look at http://dev.mysql.com/doc/refman/5.1/en/datetime.html
There's a section starting with
You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

when you add the date to the database add like

Code: Select all

<?php
// todays date
$today = date("Y-m-d");
?>
When you out put the date from the database use:

Code: Select all

<?php
// $date_field is the field name in the database
echo date("d M Y", strtotime($date_field)); 

?>
The database stores date in the format Y-m-d. If you are storing time and date, then store as:

Code: Select all

<?php
// todays date
// G is hour, i is minute, s is seconds 
$today = date("Y-m-d G:i:s");
?>
Read about time and date function here:
http://uk3.php.net/manual/en/function.date.php
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

A date filed in MySQL stores dates as YYYY-MM-DD by default. A DateTime field stores them as YYYY-MM-DD HH:MM:SS by default. When you insert dates into that database, it is best to insert them in the format that it is expecting otherwise it may have to guess. In which case it may guess wrong and you may get a 0000-00-00 date.
Post Reply