Re: last record

neil wrote:

Thanks Peter The reason I was wanting to do it in php was because the sql query is quite complex and variable depending on the input from a form. When the result page is presented I want to provide a link to the last record at the top of the page. After connecting and selecting the table I am constructing the query I then want pick off the last record and then get all the records the rather cludgy way I am doing it is this: First run $result = mysql_query($sqlstr." desc limit 0,1"); $row = mysql_fetch_assoc($result); Second run $result = mysql_query($sqlstr); while ($row = mysql_fetch_assoc($result)) { This is fine while there is only one order by appended to the query but if there is none or more than one it doesn't work so well ^ it doesn't work so well because you're only desc ordering the last item, instead of all of them. You'll want to track all the items you're trying to order by, and build a separate ending for the two queries from them.

This will work well if mysql can and actually does cache the result - but if it doesn't, then it will have to analyze and run both queries in full. Is there any reason you can't pull all the data in from the query - tossing it in an array, then emit the last row, then move on to looping over the array? Those are pretty much your options, as I see it - either burn memory in php storing the result set, or do the query twice. Are you memory limited or cpu limited? :) There might be a way to move back and forth along a mysql result set - maybe you can push all the way to the end, then reset its 'pointer' and get it from the beginning again... [from the manual] mysql_data_seek() moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number. The next call to mysql_fetch_row() would return that row. Row_number starts at 0. The row_number should be a value in the range from 0 to mysql_num_rows - 1. [/snip] So - move to mysql_num_rows() - 1, get the row, then move to 0 and do your while. Neat - learned something new. Cheers, -- - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital.