already did.

I have a timestamp but I don t get your idea: I have: 4:35 HELLO! 4:38 WASSUP? 5:10 FINE! 7:30 OLA! 8:00 BONSOIR! supposing I`ll output 3 messages max. why select top 3 messages here and sort desc? it`ll be: 5:10 FINE! 4:38 WASSUP? 4:35 HELLO! when I want: 5:10 FINE! 7:30 OLA! 8:00 BONSOIR! this works great and keeps the table clean: $result=$this->db->query("SELECT * FROM messages WHERE chat_room_id='$chat_room_id'"); $chat_num=$result->num_rows(); if ($chat_num>30) { $to_delete=$chat_num-30; $query=$this->db->query("DELETE FROM messages WHERE chat_room_id='$chat_room_id' LIMIT $to_delete"); $result=$this->db->query("SELECT * FROM messages WHERE chat_room_id='$chat_room_id'"); } return $result;

modified on Tuesday, August 2, 2011 8:58 AM

I found the best way..since I`ll have to clean up the tables once in a while...if the select returns too many rows I start deleting from the top and select again.

what if I create a temp table with all the selected rows and then return it`s last rows? :) Is that a good practice?

I`m building a web chat service and I use this query: SELECT * FROM messages WHERE chat_room_id='$chat_room_id' But I need to display no more than 20 rows (last 20 rows). Can`t use LIMIT because it will return the first 20 rows. Is there a sql command? or do I have to sort it out in php?