[SOLVED] last 5 records

[AV1611]

is there an easy way to grab the last 5 distinct records from an unindexed database?  (php/mysql)

 

 

[trq]

Depends what you mean by last 5. If they have a timestamp, use it.

 

SELECT DISTINCT foo FROM bar ORDER BY stamp DESC LIMIT 5

[AV1611]

That's the problem thorpe, there are only two fields.  not index, no stamp, just two fields.

 

select `name`, `number` from table...

[trq]

Then there is no way of determining last. Databases are unordered storage.

[AV1611]

what if I do something like this?

 

$result = mysql_query("select distinct * from caught_archive");

$num_rows = mysql_num_rows($result);

$nr=$num_rows-5;

 

echo "<table border='1'><td>Name</td><td>GUID</td></tr>";

$result = mysql_query("select distinct * from caught_archive LIMIT $nr,5");

$row=array_reverse($row);

while($row=mysql_fetch_array($result)){

echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>";

}

echo "</table>";

 

//EDIT: Seems to work, but he array reverse doesn't work.  How can I do array_reverse on mysql query?

[trq]

There is no guarantee new records will be added to what you consider to be the last row. Once again... Databases are unordered storage.

[AV1611]

In other words, are you saying that "natural order" is not consistent?  My example above assumes "natural order" would always be chronological in an unindexed database... is that not true?

[trq]

If you only add records and never delete them, chances are the records will be in the order of oldest to newest (doing SELECT * FROM foo). But when you delete a record, the table is not compacted, leaving the deleted records spot empty, next time you add a record (this spot may be used and) your oldest to newest order is lost.

[AV1611]

OK, thanks... I'm querying a log table that has data appended to it, never deleted so I'm ok.  Any idea how to do that array_reverse on the query result?

[trq]

You could maybe try something like....

 

SELECT DISTINCT foo FROM bar LIMIT (SELECT COUNT(*) bar)-5;

 

Not sure that is even valid syntax but its worth a shot... otherwise, you'll need to get all the results into an array and let php sort them out.