Ok, a little more clear: $query = "SELECT * //Change tempdate to the table name of your board document table FROM `tempdate` //Change temptext to the field name of the date in your board doc table WHERE CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2), SUBSTRING(temptext, 1, 2)) < '060703' //Same here ORDER BY CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2), SUBSTRING(temptext, 1, 2)) DESC LIMIT 1";
If this does not help, give the table structure and I create the query for you. My suggestion is to write this query in MySQL directly first, do not use PHP to try to get a query to work. Try to understand the SUBSTRING command and CONCAT command first. Read the documentation on www.mysql.com. /Peter -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 02, 2006 5:07 PM To: Peter Lauri Subject: Re: Getting the previous months documents That looks very complicated but I suppose it converts the date and compares. What does it return? I need the returned result to be an associative array of the previous date. Should it be "select * FROM board_papers concat......" This is my code with your query in it. But it doesn't return anything. $query= "SELECT CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2), SUBSTRING(temptext, 1, 2)) AS docdate FROM `tempdate` WHERE CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2), SUBSTRING(temptext, 1, 2)) < '060703' ORDER BY CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2), SUBSTRING(temptext, 1, 2)) DESC LIMIT 1"; $result= mysql_query($query); while ($row = @mysql_fetch_array($result, MYSQL_ASSOC)){ echo $row['doc_date']; $row['fileSize'] = $row['fileSize']/ 1024; $row['fileSize']= number_format($row['fileSize'], 0); $size= $row['fileSize']; $name = str_replace("_", " ", $row['fileName']); $name = str_replace(".pdf", "", $name); $link= $row['content']; $id=$row['id']; ?> Thanks for your help. ----- Original Message ----- From: "Peter Lauri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <mysql@lists.mysql.com> Sent: Tuesday, May 02, 2006 10:20 AM Subject: RE: Getting the previous months documents >I did this, sorry for bad format. This will do it for you. > > SELECT CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2), > SUBSTRING(temptext, 1, 2)) AS docdate > FROM `tempdate` WHERE CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, > 4, 2), SUBSTRING(temptext, 1, 2)) < '060703' ORDER BY > CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2), > SUBSTRING(temptext, 1, 2)) DESC LIMIT 1 > > /Peter > > > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 02, 2006 3:28 PM > To: Peter Lauri; mysql@lists.mysql.com > Subject: Re: Getting the previous months documents > > 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] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
