tonesy Posted October 7, 2010 Share Posted October 7, 2010 Hello... First I should explain what is wrong. I have a database with a table called subs... Within this table I have a unique field called ID, then a fields called member, date(unix timestamp) amount, month, year... HOWEVER for each month and year there is several entries all with different date stamps. How can I extract the entry with the most recent date??? However there is a catch. I want to view payments made since a certain date but only one per month... Below is my code... I thnk I need to add or change something slightly but i am fairly new to PHP and am totally stuck... MANY THANKS IN ADVANCE!!! [code=php:0]$query="SELECT * FROM records WHERE section='B' OR section='C' OR section='S' order by section, surname"; $result=mysql_query($query); for ($row=0;$row<mysql_num_rows($result);$row++){ $forename=mysql_result($result,$row,'forename'); $surname=mysql_result($result,$row,'surname'); $id=mysql_result($result,$row,'id'); $ref="19nx".$id.substr($forename,0,2).substr($surname,0,2); $section=mysql_result($result,$row,'section'); $giftAid=mysql_result($result,$row,'giftAid'); if ($giftAid>1){$day=date('d',$giftAid);$month=date('m',$giftAid);$year=date('y',$giftAid);}else{$day="";$month="";$year="";} $giftAidName=mysql_result($result,$row,'giftAidName'); $giftAidComment=mysql_result($result,$row,'giftAidComment'); $subdate=mktime(0,0,0,$submonth,$subday,$subyear); $query="SELECT * FROM subs WHERE member='$id' AND date>$subdate Order BY id DESC"; $subResult=mysql_query($query); $subs=""; for($ss=0;$ss<mysql_num_rows($subResult);$ss++){ $amount=mysql_result($subResult,$ss,'amount'); if ($amount==""){$amount='25';} $date=date("M/Y",mysql_result($subResult,$ss,'date')); $subs=$subs."<a title='$date' alt='$date'>$amount</a>,"; } [/code] This outputs a line of results which is right except it shows 2 or 3 for april, 3 or 4 for may anthoer 2 or 3 for june etc... I hope someone gets my drift! Quote Link to comment Share on other sites More sharing options...
joel24 Posted October 8, 2010 Share Posted October 8, 2010 don't really understand what you're trying to achieve, but in terms of getting the most recent you can use ORDER BY date DESC//or this to order by section, and then the dates of each entry for the sectionORDER BY section, date DESC also, since DATE is a predefined mysql variable/term you may have to rename this field. Could you post your mysql table schema (fields, field types etc) and explain what you want to do exactly? Quote Link to comment Share on other sites More sharing options...
Pawn Posted October 8, 2010 Share Posted October 8, 2010 This may of be some help in returning one row per month (if I've correctly understood your problem). SELECT * FROM table GROUP BY MONTH(date_field) ORDER BY date_field Quote Link to comment Share on other sites More sharing options...
tonesy Posted October 8, 2010 Author Share Posted October 8, 2010 Hello again. I am sorry I know its a little hard to understand. My code may be a little unconventional too.... $sql = "\n" . " CREATE TABLE `nxuk_19nxuk688686`.`subs2` ( `id` int( 11 ) NOT NULL default \'0\',\n" . " `member` int( 11 ) NOT NULL default \'0\',\n" . " `date` int( 11 ) NOT NULL default \'0\',\n" . " `amount` text NOT NULL ,\n" . " `chequeNum` text NOT NULL ,\n" . " `month` int( 11 ) NOT NULL default \'0\',\n" . " `year` int( 11 ) NOT NULL default \'0\' ) ENGINE = MyISAM DEFAULT CHARSET = latin1;"; Is this what you mean Joel24? MANY THANKS Quote Link to comment Share on other sites More sharing options...
tonesy Posted October 8, 2010 Author Share Posted October 8, 2010 No-one able to help???? Quote Link to comment Share on other sites More sharing options...
joel24 Posted October 9, 2010 Share Posted October 9, 2010 yes that's what I wanted, now what exactly are you trying to do?? Quote Link to comment Share on other sites More sharing options...
tonesy Posted October 9, 2010 Author Share Posted October 9, 2010 Using the table structure as above I have several thounsand records... ID is simply an incremented number... the important fields to me for this specific task are member, date, month and year. At the moment I am using the query; $query="SELECT * FROM records WHERE section='B' OR WHERE section='C' ORWHERE section='S' order by section, surname"; This query works fine and give me my list of members. Each member has an Id and runs through a loop like this... $result=mysql_query($query); for ($row=0;$row<mysql_num_rows($result);$row++){ $forename=mysql_result($result,$row,'forename'); $surname=mysql_result($result,$row,'surname'); $id=mysql_result($result,$row,'id'); $ref="19nx".$id.substr($forename,0,2).substr($surname,0,2); $section=mysql_result($result,$row,'section'); $giftAid=mysql_result($result,$row,'giftAid'); if ($giftAid>1){$day=date('d',$giftAid);$month=date('m',$giftAid);$year=date('y',$giftAid);}else{$day="";$month="";$year="";} $giftAidName=mysql_result($result,$row,'giftAidName'); $giftAidComment=mysql_result($result,$row,'giftAidComment'); $subdate=mktime(0,0,0,$submonth,$subday,$subyear); ------------------------------------------------------------------------------------------------------------------------------------------------- next I want view payments for each member from a specific date.... I have the following query... $query="SELECT * FROM subs WHERE member='$id' AND date>$subdate Order BY date ASC"; This shows me all the payments recieved from that member and after $subdate. This again goes through another loop so I am given a list from of members with payments beside them... ... HOWEVER Within the database there is multiple entries for each member. What I want to do is adjust the query so that it looks at the subs table and selects only one payment for each members from each month. So that only the most recent 'date' entry for each month and year is selected... Month and year is in seperate. e.g. if there is 4 entries where month = 8 and year = 2010 and amount = 8. Then select the highest(most recent date) output it and move on to the nect month. hard to explain so i HOPE someone gets my drift! MANY MANY THANKS for your patience... Quote Link to comment Share on other sites More sharing options...
joel24 Posted October 9, 2010 Share Posted October 9, 2010 i still haven't got my head around what exactly you want to do and how it correlates to your existing code, I noticed the field type for DATE, MONTH & YEAR are all INT(11)... unix timestamps I presume? Why do you have date, month & year fields? If they're all unix timestamps aren't they all the same value? //this query might get you started SELECT MAX(amount) FROM rosters GROUP BY MONTH(from_unixtime(date)) ORDER BY date DESC LIMIT 1; Quote Link to comment Share on other sites More sharing options...
tonesy Posted October 11, 2010 Author Share Posted October 11, 2010 Hello Again. I am sorry but I do not know how I can explain this further. As for your question, the month and year fields are simply an integer. The date is a unix timestamp but the month is simply a number being 6 or 9 or 12. The year is a year in 4 digits i.e. 1999 or 2010. These colums are used by a different page and code makeup. Please try to imagine this.... id member date amount month year 9977 31 (unix stamp) 8 6 2010 -------------------------------------------------------------------------------------------- 9978 22 (unix stamp) 8 8 2010 -------------------------------------------------------------------------------------------- 9979 69 (unix stamp) 8 8 2010 -------------------------------------------------------------------------------------------- 9980 31 (unix stamp) 8 6 2010 -------------------------------------------------------------------------------------------- 9981 32 (unix stamp) 8 9 2010 -------------------------------------------------------------------------------------------- 9982 69 (unix stamp) 8 6 2010 -------------------------------------------------------------------------------------------- 9983 31 (unix stamp) 8 7 2010 -------------------------------------------------------------------------------------------- 9984 31 (unix stamp) 8 7 2010 -------------------------------------------------------------------------------------------- Lets say i have approx 9000 records with the above format. Take a look at member 31 for example. There are 4 entries for member 31 in just this small example. I want the code to look at member 31. Then look at the month. Note there are two entries for 6 and two entries for 7. I then want it to look at the time stamp and see which month 6 entry was most recent and output. (at the moment the code outputs both of them. up to 6 of them depending on the member... ) Then I want it to +1 to the month and do the same... Does this make more sense... OR could I look at the time stamp month and year and pick to most recent from there and totally ignore month and year colulm for this task??? from the code above in a earlier post I get a list which is similar to this below... name section gift aid name comment subs ---------------------------------------------------------------------------------------------------------------- joe blogs B Mr Bloggs A comment 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, ---------------------------------------------------------------------------------------------------------------- Bill Gates C Mr Gates A comment 8, 8, 8, 8, 8, 8, ---------------------------------------------------------------------------------------------------------------- Charlie Shu S Mrs Shu A comment 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, ---------------------------------------------------------------------------------------------------------------- When a user hovers over the figure 8. It shows the date the £8 was made month and year. hope this makes sense... And helps you understnad my problem! MANY THANKS Quote Link to comment Share on other sites More sharing options...
tonesy Posted October 13, 2010 Author Share Posted October 13, 2010 Joel24 does this help explain it any more? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.