My sql is not so great. The dates are entered through a dropdown box in the
format dd/mm/yyyy (uk date format) as a VARCHAR.
I could use strrev and str_replace (PHP) to get it in the correct format but
will the mysql query you send work on a VARCHAR which it is it present or
will I have to change the field to DATE?
Ross
----- Original Message -----
From: "Peter Lauri" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, May 02, 2006 9:06 AM
Subject: RE: Getting the previous months documents
You should start by using MySQL date as the standard for date: YYYY-MM-DD
After that it is simple:
SELECT * FROM documents WHERE doc_date<2006-05-02 ORDER BY doc_date DESC
LIMIT 1;
Othervise you could use MySQL function to take sub strings and create a
field in the query that extract it as 06-05-02 and order by that.
/Peter
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:04 PM
To: mysql@lists.mysql.com
Subject: Getting the previous months documents
I have a database that stores documents relating to meetings. They have
all
the usual stuff agenda, minutes etc. There are 3 paper types for each date
agenda (1 only), minutes (1 only), and a bunch of general documents titled
'papers'.
I need to display all the documents for a specific date which is easy but
I
also need to retrieve the minutes for the PREVIOUS meeting which is
proving
more difficult. The documents are stored by date in the format dd/mm/yy.
If
someone wants the documents from 02/05/06 how do I find the minutes for
the
previous date when I do not know when it is?
Can I do this with mysql? Or will it be better with mktime and some php?
--
-- Table structure for table `board_papers`
--
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '0000-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default
'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
