如何排序和限制PHP / HTML / MySQL表

Question

I have two problems and have no idea how to solve them. I have the following PHP/MySQL table on one of my sites:

   <table>
     <thead>
       <tr>
         <th>1</th>
         <th>2</th>
         <th>Coins</th>
         </tr>
       </thead>
     <tbody>
    <?php
    error_reporting(E_ALL);
    define ( 'MYSQL_HOST',      'localhost' );
    define ( 'MYSQL_BENUTZER',  'USER' );
    define ( 'MYSQL_KENNWORT',  'PASSWORD' );
    define ( 'MYSQL_DATENBANK', 'DATABASE' );
    $db_link = mysqli_connect (
                         MYSQL_HOST, 
                         MYSQL_BENUTZER, 
                         MYSQL_KENNWORT, 
                         MYSQL_DATENBANK
                        );

    $sql = "SELECT * FROM TABLE";
    $db_erg = mysqli_query( $db_link, $sql );
    if ( ! $db_erg )
    {
      die('Ungültige Abfrage: ' . mysqli_error());
    }
    while ($zeile = mysqli_fetch_array( $db_erg, MYSQL_ASSOC))  
    {
           echo "<tr>";
           echo "<td>". $zeile['1'] . "</td>";
           echo "<td>". $zeile['2'] . "</td>";
           echo "<td>". $zeile['coins'] . "</td>";
           echo "</tr>";
           }
           mysqli_free_result( $db_erg );
           ?>
  </tbody>
 </table>

Row 3 only contains numbers. How do I make the table:

1. Sort the the numbers in the column coins from largest to smallest [According to the size]
2. Only display 20 rows after getting the "best" ones?

Answer 1

Change your MySQL code from this:

$sql = "SELECT * FROM TABLE";

To this:

$sql = "SELECT * FROM TABLE ORDER BY the_name_of_the_column_name_to_sort LIMIT 0,20";

The problem is you are not saying what column name will sort this when you say “Sort the biggest numbers from the database to be the first ones.” Which column has these numbers? Change the query example above and replace the_name_of_the_column_name_to_sort with the actual name of the column you want to sort on.

The part that reads LIMIT 0,20 will return 20 items beginning from the first item; aka: 0. So you could increment/paginate the list by changing that to LIMIT 20,20, LIMIT 40,20 and so on.

EDIT: From the comment left by the original poster:

It contains only numbers. It seems like this is sorting it after the 1 letter of the number.

Go into the database & make sure that column is an INT value and not a text or VARCHAR column. If it is a number it should sort numerically using the example I provided.