Mysql select last 20 rows, with while loop

Question

I have 50+ rows and each have an id, how do i get the last 20 records and display each ones information with php.

Is the best way to use a loop? I want it to display the results quick and not miss any rows, is a loop the best way to go then?

This is the code that I have

$result = $mysqli_log->query("SELECT * FROM `$usern`");     
while( $row = $result->fetch_array() ) {
  $credit = $row['credit'];
  echo $credit;
}

what is the scenario for last 20 records by date,auto increment column or something other ? — M Khalid Junaid, Jul 09 '14 at 19:46
If you have some id that incremental, do an order by id desc and limit 0, 20 — Michal, Jul 09 '14 at 19:46
Have a look here http://stackoverflow.com/questions/20364349/multiple-pages-using-mysql-limit-offset — Roger, Jul 09 '14 at 19:48
your loop is basically what 99% of php programmers would use. it'll just keep fetching data until there's nothing left. — Marc B, Jul 09 '14 at 19:49

Cheruvian · Answer 1 · 2014-07-09T19:57:23.520

0

To limit the number of queries use Limit and order them desc by your ID

Select   *
From     `$usern`
Order By ID Desc
Limit 20

To Flip them back in the forward order you can use a derived table

Select *
From (Select ID, Test
      From    test
      Order By ID Desc
      Limit 3) d 
Order By ID Asc

edited Jul 09 '14 at 19:57

answered Jul 09 '14 at 19:49

Cheruvian

5,628
1
24
34

score 0 · Answer 2 · answered Jul 09 '14 at 19:56

The MySQL query being executed doesn't specify any "order" to the rows; MySQL is free to return the rows in any order it chooses, so it's possible that the "last 20" rows on one run of the query might differ from the "last 20" rows on a second run.

(We do observe repeated behavior when the statement is re-executed; it usually takes some DML operations, the addition of an index, or an OPTIMIZE table statement, to actually get a change in the results returned... but the point is, there is no "last 20" rows in the table. In MySQL, it's just a set of rows.)

To specify a specific sequence of the rows, add an ORDER BY clause to the query. Assuming that you want to use the unique id column to order the rows, and you want the last 20 rows, and you want them returned in ascending id sequence:

SELECT t.*
  FROM ( SELECT u.* 
           FROM `$usern` u
          ORDER BY u.id DESC
          LIMIT 20
       ) t
 ORDER BY t.id

And, yes, processing rows "in a loop" in PHP, just like you demonstrate, is a normative pattern.

score 0 · Answer 3 · answered Jul 09 '14 at 20:00

If you need the newest 20 records, you have to ORDER DESC the result set by ID and then LIMIT that set result to 20 records. So you can use something like this:

$result = $mysqli_log->query("SELECT * FROM `$usern` ORDER BY `ID` DESC LIMIT 20");     
while( $row = $result->fetch_array() ) {
  $credit = $row['credit'];
  echo $credit;
}

Another good approach, if you are using associative keys like $row['credit'], is to use featch_assoc instead of featch_array (if your framework provides such a function)

Mysql select last 20 rows, with while loop

3 Answers3