mysql SELECT WHERE
-
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?
-
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?
the concept of first row/last row does not exist, unless you have an ORDER BY clause, without that the rows are unordered i.e. they come in an arbitrary order. So whatever criterion it is you want to define the "last rows", write the inverse and use LIMIT(n) or TOP(n) to get the first N rows of that. Use TOP in T-SQL, LIMIT in MySQL. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
the concept of first row/last row does not exist, unless you have an ORDER BY clause, without that the rows are unordered i.e. they come in an arbitrary order. So whatever criterion it is you want to define the "last rows", write the inverse and use LIMIT(n) or TOP(n) to get the first N rows of that. Use TOP in T-SQL, LIMIT in MySQL. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
what if I create a temp table with all the selected rows and then return it`s last rows? :) Is that a good practice?
-
what if I create a temp table with all the selected rows and then return it`s last rows? :) Is that a good practice?
No. Look at a table as a pile of rows, not an ordered set of rows. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
what if I create a temp table with all the selected rows and then return it`s last rows? :) Is that a good practice?
select top 20 * from messages where chat_room_id=$chat_room_id ORDER BY yourTimeStampField DESC This makes the HUGE assumption that you have a timestamp field defined in your messages table. Give it a shot. :thumbsup: Crap. I think I just replied to the wrong thread. This reply was meant for the original poster. Sorry for any confusion. It's early for me.
-
No. Look at a table as a pile of rows, not an ordered set of rows. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
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.
-
select top 20 * from messages where chat_room_id=$chat_room_id ORDER BY yourTimeStampField DESC This makes the HUGE assumption that you have a timestamp field defined in your messages table. Give it a shot. :thumbsup: Crap. I think I just replied to the wrong thread. This reply was meant for the original poster. Sorry for any confusion. It's early for me.
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 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
:doh: Try it.
-
:doh: Try it.
already did.
-
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?
Further to Luc's excellent advice, I would add that you should avoid the death trap
SELECT * FROM ...syntax. It's quick now but will come back and bite your head off in the future. Name the columns you want explicitly and query nothing else.
Panic, Chaos, Destruction. My work here is done. Drink. Get drunk. Fall over - P O'H OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre I cannot live by bread alone. Bacon and ketchup are needed as well. - Trollslayer Have a bit more patience with newbies. Of course some of them act dumb - they're often *students*, for heaven's sake - Terry Pratchett