SQLite DATETIME

[maxim]

Hi first post :D

I wrote a really simple script. its a script to print a News Topic and content to a web page.

I store the news in a SQlite databse like so..

news_id INT
news_topic as VARCHAR(100)
news_content as TEXT
date as DATETIME

so far mt simple script can post the news topic and content fine, i use the news_id to sort it in DESC order so the latest news gets displayed first.

i have the date coulm sitting there doing nothing at the moment. how would i go about puttin the date and time in there ?

i know how to use PHP's date() function ok, but what format dose it have to be in ? will SQLite allow any date and time format to be put in there ?

basicly how do you guys/girls reccomend i approch this ?

[trq]

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]will SQLite allow any date and time format to be put in there ?[/quote]
Yes it will. Sqlite doesn't actually have any data types. Well it does, but they are all the same. So specifying a field as VARCHAR or TEXT is not really required either as internally its all the same. Strange little db that one. Very usefull though.

But anyway... yeah, I prefer to create my dates using a unix timestamp. You can do this with the mktime() function. Then, to display them, I use the date() function to format them.

[maxim]

[!--quoteo(post=380063:date=Jun 5 2006, 09:03 AM:name=thorpe)--][div class=\'quotetop\']QUOTE(thorpe @ Jun 5 2006, 09:03 AM) [snapback]380063[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Yes it will. Sqlite doesn't actually have any data types. Well it does, but they are all the same. So specifying a field as VARCHAR or TEXT is not really required either as internally its all the same. Strange little db that one. Very usefull though.

But anyway... yeah, I prefer to create my dates using a unix timestamp. You can do this with the mktime() function. Then, to display them, I use the date() function to format them.
[/quote]

yes i understand you can put basicly anything in any datatype in sqlite. but i am using PEAR DB to comunicate
with my database - so if i need to run my scripts on a diffrent server with say MySql, i should basicly only have to change one line of code.
the line that connects to the actual database.

so putting a unix timestamp is ok for the DATETIME datatype in most database software ?

ill give it a try and see how i go.

[trq]

You'll want to use TIMESTAMP then.

Also, just on a side note. Have you looked into PDO? It is IMO much better than PEAR DB.

[maxim]

[!--quoteo(post=380092:date=Jun 5 2006, 11:04 AM:name=thorpe)--][div class=\'quotetop\']QUOTE(thorpe @ Jun 5 2006, 11:04 AM) [snapback]380092[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You'll want to use TIMESTAMP then.

Also, just on a side note. Have you looked into PDO? It is IMO much better than PEAR DB.
[/quote]

ok cool, i didnt even know there was a TIMESTAMP data type. Ill look into it.

i have heard of PDO. its my understanding thats its pretty new. So with me being new my self to PHP programing i follow alot of books that talk about PEAR because PDO didnt exist at the time they were writen. so ill stick with it for now untill im comfortable with PHP in general

[trq]

Yeah no worries. PDO has actually been around for as long as php5. Its part of php5, well, its an extension anyway.

I was only suggesting it because being written as an extension it should be quicker than anything PEAR. Anyway.... just letting you know whats out there.

[maxim]

well i succesfully implemted the time stamp thing, and i format it with th date() function for displaying it.

ill definatly take at look in PDO

thanks for the help. i was thinking tho its a unix style timestamp so the script wont work with windows

[trq]

It'll work fine in windows, mktime() is part of a standard php install.