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.

 

[KarveR]

in that case, just humour me here but try my code I think it may be how the string is passed through your function that could be the problem.

if mine works, try
$sql='SELECT DATE_FORMAT(offerEndDate, "%W %M %Y") FROM offers';

$q=functionQuery($sql);

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

 

[jgmills]

thanks people thought i had already said i had sorted it

in the end the code did work but because i was running two quires in a while the $sql and $result etc were getting mixed up each time :S

<?php
$qDate2 = functionQuery("SELECT DATE_FORMAT('$offerEndDate', '%W %D %M %Y') FROM offers");
$rDate2 = mysql_fetch_row($qDate2);
$offerEndDateFormatted = $rDate2[0];
print "$offerEndDateFormatted"; ?>

had to change $qDate2 etc.....

thanks alot

 

[DonP]

Personally, I prefer to set the field type to integer and insert the Unix timestamp as it makes life and coding much easier in the long run. You can still view it on the screen in any format you like and/or pull out only the year or month (for example) if needed. It seems to me to be much more flexible than using a datetime field type.

Don
contact@pc-homepage.com

http://www.pc-homepage.com/

Experienced in HTML, Perl, PHP, VBScript, PWS, IIS and Apache and MS-Access, MS-SQL, MySQL databases

 

[sleipnir214]

The applicability of that suggestion depends on the nature of the dates you're storing. The limits of dates available for storage in a datetime field are a lot farther apart than the limits of a unix timestamp.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[DonP]

I appreciate your input to my two-cents' worth and I fully agree with you. Although I do not want to advertise someone else's code here, there is a great and free includable file for PHP that can handle at least as wide a date range (including pre-1970 dates) as the datetime field, which is called adodb-time.inc. I use it in combination with a simple JavaScript Unix timestamp calculator for manually entering in past dates for a site I run and it works like a charm for the limited but pre-1970 ones that I need to have.

At least for my applications where I need to frequently compare dates and to have early ones back into the '40s, it seems to do a good job. Supposedly it can handle dates from 100 A.D. to 3000 A.D. but I haven't had a need to test it's range. It may, in fact, also work with the datetime field - I havent tried that either, but in using the Unix timestamp, you avoid all the conversions etc. that are needed when reading or writing one and it is especially useful when you need to do math on dates, which was my main point.

In reading all the nice answers above, I learned more than a thing or two, and found it all very interesting and useful.

Don
contact@pc-homepage.com

http://www.pc-homepage.com/

Experienced in HTML, Perl, PHP, VBScript, PWS, IIS and Apache and MS-Access, MS-SQL, MySQL databases

 

[jgmills]

yes i have learnt alot from all this!

thats why i posted

thanks people