Select last 20 order by ascending - PHP/MySQL

Viewed 44 times

This is my table structure

MyTable
ID[P.K][auto increment]   TopicID   UID   Comment

Now i want to get the last 20 comment for a TopicID but it should be sorted in ascending order !

[Just like Facebook by default shows last 20 comment only]

I am looking for an optimized version, i can do this with 2/3 query and php sort array, but looking for some better alternative

Sample Result with data

MyTable  
ID TopicID UID Comment  
1  1       10  AAAA   
2  1       11  BBBB  
3  1       10  CCCC  
4  1       10  dddd   
5  1       11  EEEE  
6  1       10  FFFF

I want to get the last 3 result for a TopicID, the result should be

4  1       10  dddd   
5  1       11  EEEE  
6  1       10  FFFF

and not

6  1       10  FFFF  
5  1       11  EEEE  
4  1       10  dddd

Answers

First, select last 20 entries. Then sort them in ascending order. You can easily do this in a single query (with subquery):

select * from (
    select * from your_table order by id desc limit 20
) tmp order by tmp.id asc

Monday, September 19, 2022

dqxbqhthb-36f2c7750a1a

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
}
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Saturday, November 5, 2022

klas_lindbäck

$this->db->select('str_to_date('.$oBy.', "%d-%b-%Y") day',false);//select your colum as new column name wich is converted as str ot date
//you can do select more.
$this->db->order_by('day','ASC');
$query = $this->db->get('books');

This will solve your problem

Sunday, November 13, 2022

effeffe

The function you're looking for is find_in_set:

 select * from ... where find_in_set($word, pets)

for multi-word queries you'll need to test each word and AND (or OR) the tests:

  where find_in_set($word1, pets) AND find_in_set($word2, pets) etc

Wednesday, August 17, 2022

thecontstruct

No, that behavior cannot be relied on. The order is determined by the way the query planner has decided to build up the result set. simple queries like select * from foo_table are likely to be returned in the order they are stored on disk, which may be in primary key order or the order they were created, or some other random order. more complex queries, such as select * from foo where bar < 10 may instead be returned in order of a different column, based on an index read, or by the table order, for a table scan. even more elaborate queries, with multipe where conditions, group by clauses, unions, will be in whatever order the planner decides is most efficient to generate.

The order could even change between two identical queries just because of data that has changed between those queries. a "where" clause may be satisfied with an index scan in one query, but later inserts could make that condition less selective, and the planner could decide to perform a subsequent query using a table scan.

To put a finer point on it. RDBMS systems have the mandate to give you exactly what you asked for, as efficiently as possible. That efficiency can take many forms, including minimizing IO (both to disk as well as over the network to send data to you), minimizing CPU and keeping the size of its working set small (using methods that require minimal temporary storage).

without an ORDER BY clause, you will have not asked exactly for a particular order, and so the RDBMS will give you those rows in some order that (maybe) corresponds with some coincidental aspect of the query, based on whichever algorithm the RDBMS expects to produce the data the fastest.

If you care about efficiency, but not order, skip the ORDER BY clause. If you care about the order but not efficiency, use the ORDER BY clause.

Since you actually care about BOTH use ORDER BY and then carefully tune your query and database so that it is efficient.

Tuesday, August 2, 2022

geg

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

Select last 20 order by ascending - PHP/MySQL

Answers

php,mysql