Making an art gallery.

I want to be able to store a date in my database, but also want to be able to sort by it to show the newer ones first.

Normally I just make date a varchar, and sort by ID, but I may want to add older things later, but don't want them to appear at the beginning.

So I guess I need to figure out how to store the date, and then order the query by it.

And then also display it on the page like 6/17/2009.

Use a timestamp, or datetime type, not varchar.
The db is then able to order the date easily, without you to mind for it.

and use the date_format() sql function to retrieve it in the format you want.
http://dev.mysql.com/doc/refman/5.1/...on_date-format

I agree with tripy but I find this page and examples a bit easier to read: http://www.php.net/manual/en/function.date.php

Originally Posted by tripy

Use a timestamp, or datetime type, not varchar.
The db is then able to order the date easily, without you to mind for it.

and use the date_format() sql function to retrieve it in the format you want.
http://dev.mysql.com/doc/refman/5.1/...on_date-format

so It looks like I can store it in the database as "YYYY-MM-DD"

I want to input dates with "M/D/YYYY" (php: "n/j/Y")
(8 not 08)

How do I convert this to the right format to put into the database?

And then to convert it back, I use date_format() in the sql statement?
Like mine will be "SELECT * FROM art WHERE id = '$id'" for actually calling it, where I need to print it out, where does that go? Just at the end?

and "SELECT * FROM art ORDER BY date" <- right?

halp

so It looks like I can store it in the database as "YYYY-MM-DD"

I want to input dates with "M/D/YYYY" (php: "n/j/Y")

No, what you see when you do a select is a representation of the internal format of the date.
If you want the select output to be different, you must apply a format to the date field.
You do that with the function I gave a link to in my initial post.

SELECT field1, field2, field3, date_format(dtm_column,'%m/%d/%Y') as  dtm_column
FROM art WHERE id = '$id'
ORDER BY dtm_field

Remember, that "date" is a reserved word. I do not recommand to use it as a column name, altough you can escape it with backticks:

select `date` from tbl

Originally Posted by tripy

No, what you see when you do a select is a representation of the internal format of the date.
If you want the select output to be different, you must apply a format to the date field.
You do that with the function I gave a link to in my initial post.

SELECT field1, field2, field3, date_format(dtm_column,'%m/%d/%Y') as  dtm_column
FROM art WHERE id = '$id'
ORDER BY dtm_field

Remember, that "date" is a reserved word. I do not recommand to use it as a column name, altough you can escape it with backticks:

select `date` from tbl

I changed date to creationdate

so like this? to get the information?

SELECT id, url, prevurl, date_format(creationdate,'%c/%e/%Y') as  creationdate FROM art WHERE type = 'pixel' ORDER BY creationdate

I'm not sure I know what you mean by dtm_column...


and then the code for storing it..

it's getting info via post like "8/30/2010"

INSERT INTO art (creationdate, url, prevurl, title, type, height, width)
VALUES (DATE_FORMAT($date, '%c/%e/%Y'), $url, $prevurl, $title, $type, $height, $width)

um, maybe?

I'm not sure I know what you mean by dtm_column...

It was simply a made up column name, nothing more.

so like this? to get the information?

Exactly!

and then the code for storing it..

Nope, here, you must give a valid date in a format mysql recognize.
Here they are:
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

• As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.
• As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.
• As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
• As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
• As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
• As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.
• As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE.

So, in your case, I'd say:

INSERT INTO art (creationdate, url, prevurl, title, type, height, width)
VALUES ('$date', $url, $prevurl, $title, $type, $height, $width)

where $date is formatted via PHP to 'YYYY/MM/DD'
Or, if the "creationdate" field should have the value of the time of the insert:

INSERT INTO art (creationdate, url, prevurl, title, type, height, width)
VALUES (NOW(), $url, $prevurl, $title, $type, $height, $width)

as now() is an SQL function that return the current time the query is run at. No need to PHP for that.

Originally Posted by tripy

where $date is formatted via PHP to 'YYYY/MM/DD'

is there a way to reformat dates in php?

like take a string, get the format, change the format, and reoutput it?

date: http://www.php.net/manual/en/function.date.php
strftime: http://www.php.net/manual/en/function.strftime.php
mktime: http://www.php.net/manual/en/function.mktime.php

Originally Posted by tripy

date: http://www.php.net/manual/en/function.date.php
strftime: http://www.php.net/manual/en/function.strftime.php
mktime: http://www.php.net/manual/en/function.mktime.php

I seriously don't get it, none of those let you input a string, they seem to all just have the current time.

EDIT: this.

$timestamp = "9/16/2007";
$timestamp = strtotime($timestamp);
$makedate = date("Y-m-d", $timestamp);
echo $makedate;

So basically you're wanting something to where someone, for instance an artist, could put what the date was that they made their creation?

Edit: You answered my question before I could ask it. You could do something along the lines of:

$date=date("Y-m-d",strtotime($_REQUEST['date']));

'date' in the request would need to be replaced with whatever value you are using in your form to submit to the page. You could also use $_GET or $_POST if you want to specify how it's coming in. Then just use that variable in your SQL statement that you already have put together.

Originally Posted by Skeddles

I seriously don't get it, none of those let you input a string, they seem to all just have the current time.

I admit that I prefer to use mkdate(), and specify every arguments.
Living in a zone where the dates are represented DD.MM.YYYY, when I see something like 1985-12-03, or 1985/12/03 I am never sure if it's the 12 of march, or the third of december...
So mkdate() allows me to avoid that situation.

Originally Posted by tripy

when I see something like 1985-12-03, or 1985/12/03 I am never sure if it's the 12 of march, or the third of december...
So mkdate() allows me to avoid that situation.

I have the same problem as i am sure all people do,

Some timezones are day/month/year some are month/day/year it all comes down to what the author decides to use i suppose..

anyway here is how i would tackle formatting across a site and save myself a lot of typing.

PHP Code:

<?php
function formattedDate($time){
      $formatting = "D / M / Y";  // choose your own format options
return date($formatting, $time);
}
?>

there you have it, call the funtion with the $time var filled in with whatever time you need to format, be it a timestamp from a database/file or the current timestamp your function will format it into your chosen format.

time now

PHP Code:

echo formattedDate(time());  (bear in mind this is the server time as set by the administrator generally based on its location and may not be your time.) 


timestamp from file or database

PHP Code:

echo formattedDate($timestamp); 


As a side note $timestamp = "4/9/2010"; isnt a timestamp its formatted time, a time stamp is actually just seconds a hell of a lot of them.
EG $timestamp = "7463220463252";

feel free to try the two out and see what happens