tonesy

Joel24 does this help explain it any more?

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

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...

No-one able to help????

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

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!