Re: Date/Time Formatting

On Fri, 19 Jan 2001, Jamie wrote: > I'm fairly new to SQL and PHP and I'm haveing trouble useing the Date > functions of Both Systems, so I'd be greatfull if someone can help. > What I'm trying to do is have an 'administrator' be able to enter info > through a form to a mySQL database. Then on a seperate page have the info > reread and displayed. The basic outline is a News page which reads and > displays from the table and shows the News topics preceeding and inculding > the current date (ie if I enter a future Date eg 25/12/2001 and the comment > Merry christmas, in the databaes I don't want it to appear until the date > has occured) > > The Table looks as follows: > CREATE TABLE news ( > news_id INT NOT NULL primary key auto_increment, > topic VARCHAR(30) NOT NULL, > live_date date NOT NULL, > news BLOB NOT NULL, > user_id VARCHAR(20) NOT NULL); > > I've used the MYSQL date type as I think this would be easier for the users > to be able to manually enter the date in is form on the admin form - also I > don't think a more accurate date/time method will be more usful. > > Currently I'm using this SQL Line: > SELECT * FROM news ORDER BY live_date DESC LIMIT 4 > (where Live_Date is the date to display the news after) > > Also I'd like to if possible to be able to enter and display the date in > Australian / European Time format (DD,MM,YYYY) > I'm currently entering it on the form using three text fields and then > rearanging them to the Format in mySQL and indserting it as a string, but I > don't know how to 'break' up a mySQL Date value to rearange it. > > Hope these arn't in the FAQ as I have spent some time looking through PHP > script sites and have been unable to find anything that can help. > The reason you haven't found anything on PHP sites is that they're not PHP questions, they're MySQL questions :) For the future date, add "live_date<=NOW()" to your WHERE clause in your select queries. As for the date format, MySQL only supports ANSI format (YYYY-MM-DD) for input, but you can use the DATE_FORMAT() function to change the output. You'll have to use substr() in PHP to chop up the input string and rearrange it for MySQL. -- Ignacio Vazquez-Abrams <ignacio@openservices.net>