everything to do with date?

[jgmills]

OK I need to sort this out in my mind once and for all.

When i am displaying the date for some reson on my site i use date(M:Y); yer we all know that one.

OK now i have never got it right when i use dates with databases so i need to learn.

For example.
I have an events table in my database. tblEvents
With eventId, eventName, evenDate, eventTime

No i want to be able to insert new events right and i want the user to be able to add teh add th date but then whats the best way to add this to the db?

Once thing i dont want to display events that are i nteh past right you see, this is where i am getting confused as to how to store. But i also wat to be able to display teh date in normal format on the site.

The other thing is i have a news table and i want to capture the date and time when the news was added.

Help and advise would be great, thanks.

 

[sleipnir214]

database" is a very general term. Particularly when you're talking about PHP.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[jgmills]

MySQL

:S

 

[sleipnir214]

All dates in MySQL must be inserted in the format "YYYY-MM-DD" or "YYYY/MM/DD". Although MySQL will not generate errors on other formats, neither will it insert the correct value unless you use one of the formats above.

In terms of storing dates, MySQL provides the DATE and DATETIME. I can't answer more specifically to your question, as I'm having trouble parsing your sentence: Once thing i dont want to display events that are i nteh past right you see, this is where i am getting confused as to how to store.

To know when a record was added or updated, add a column of type TIMESTAMP to your table. MySQL will automatically update that column to the server's current time and date when you insert or update that record.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sleipnir214]

But then, most of what I've said is documented in theMySQL online manual section titled "Overview of date and time types"



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[LTeeple]

Also, if you don't want to show anything that occurred in the past, there is a CURDATE() function.

So the following select statement will only select present and future items:
$sql = "SELECT * FROM table WHERE evenDate >= CURDATE()";


Cheers!
Laura

 

[jgmills]

thats a great help........

one more question...
i will from now on use the TIMESTAMP and also use dates in the format YYYY-MM-DD right, this might sound stupid.... but do i set the field type to DATE in the MySQL database?

and if yes when i get the date and print $dateAdded right will it be YYYY-MM-DD

 

[sleipnir214]

but do i set the field type to DATE in the MySQL database?

I don't understand your question. Modifying tables, either by altering a column type or by adding a column, is handled in MySQL through the use of the ALTER TABLE query.

If you want to have a column of type DATE at table-creation time, you specify that one of the columns be of type date.


If you get it right, the date returned by MySQL will be in YYYY-MM-DD format. You can also use MySQL's date_format() function to get MySQL to return the date [nearly] any way you want.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[jgmills]

OK yer I know what i am saying but i am never very good at getting it across to others.

What you have said in this post has helped me alot.

Thank you for your time.

this is what i am working on

http://www.jgmwebdesign.co.uk/prototypes/cairnsandhickeywines/news_events_offers.php

 

[jgmills]

OK one more question.

eg:

tableEvents right
id | eventDate (DATE) | dateAdded (TIMESTAMP)

ok TIME STAMP adds YYYYMMDD auto
and DATE adds my inputted date formatted like YYYY-MM-DD

Now when i read these from the database i get them like
$eventDate
$dateAdded

Now can I format this date somehow to get the dat outputted like 1st January 2004

Thank you.

 

[sleipnir214]

You can format date values in MySQL (look for a link with text "date_format()" in my last post for more information)

Conversely you can format them using PHP's strotime() and date() functions.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[jgmills]

ok well i am still none the wiser now.....

dateAdded is TIMESTAMP(8)
which is YYYYMMDD

thats fine because i can use that to filter stuff and also use it for eventDate to just show future events and not events in the past etc...

but i want to actually display a nice date on the site
somthing like:??

$dateAdded = date("d, m, Y",$dateAdded); print "$dateAdded";

but with a database vaue of 20040702
this outputs 20, 08, 1970

 

[sleipnir214]

Conversely you can format them using PHP's strotime() and date() functions.

If you look at the documentation of the date() function, you will see that the second optional parameter must be an integer, not a string.

If you look at the online documentation of the strtotime() function, you will see that it takes a date string and returns an integer.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[jgmills]

got it

$q = functionQuery("SELECT DATE_FORMAT(dateAdded, '%W %M %Y') FROM offers");
$result = mysql_fetch_row($q);
$dateAddedFormatted = $result[0];
print "$dateAddedFormatted";


nice one thanks
still need to get my head around it a bit more but i am getting there thanks alot people

 

[jgmills]

ok although i have that one sorted now can you explain why...

When i use TIMESTAMP you get YYYYMMDD
but when you use DATE it uses YYYY-MM-DD

I can get dateAdded in nice format (Friday 2nd July 2004) using:
SELECT DATE_FORMAT(dateAdded, '%W %M %Y') FROM offers
But when i try that on offerEndDate (which is DATE) it dont work people!

So when i input my offerEndDate format YYYYMMDD it auto changes it to YYYY-MM-DD

Thanks in advance.

 

[sleipnir214]

When i use TIMESTAMP you get YYYYMMDD
but when you use DATE it uses YYYY-MM-DD

That's because they are different columntypes and that's the way the programmers at MySQL, AB wanted them to work. This behavior is described in a link to the MySQL online manual I posted early in this thread.

So when i input my offerEndDate format YYYYMMDD it auto changes it to YYYY-MM-DD

That is by design.

But when i try that on offerEndDate (which is DATE) it dont work people!

Definde "dont work". MySQL's date_format() function will work with columns of type DATE.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[jgmills]

Do you ever post anything but the obvious!

Definde "dont work". MySQL's date_format() function will work with columns of type DATE.

I just need to find out how to get offerEndDate (DATE) (YYYY-MM-DD) outputted like Friday 2nd July 2004.

When i do this
$q = functionQuery("SELECT DATE_FORMAT(offerEndDate, '%W %M %Y') FROM offers");
$result = mysql_fetch_row($q);
$offerEndDateFormatted = $result[0];
print "$offerEndDateFormatted ";

it does not display anything.
all i can think is that DATE_FORMAT is expecting to find YYYYMMDD (TIMESTAMP) not YYYY-MM-DD (DATE)

 

[KarveR]

i'm lost

 $q = functionQuery("SELECT DATE_FORMAT(offerEndDate, '%W %M %Y') FROM offers");

surely you mean:
$q="SELECT DATE_FORMAT(offerEndDate, '%W %M %Y') FROM offers";
$result=mysql_query($q);
$row=mysql_fetch_row($result);
echo $row[0];
[/code]


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[ascikey]

i think functionQuery() is a php function that he has done

 

[jgmills]

thats correct!

function functionQuery($sql){
// Database connection
include ("include/dbConnection.php");
// Query databse with sql that is passed to function
$q = mysql_query($sql, $link) or die(mysql_error());
// Reture result
return $q;
}