How to get data in descending order?

Question

I've read everything I can find on this website, but haven't been able to find an answer. I have a pagination that displays in ascending order. I have noticed that when I enter a new file to my database, they don't show up first in the pagination result. Any help would be appreciated. Here is my pagination script:

<?php
include('connect.php'); 

$tableName="resumesearch";      
$targetpage = "searchresumes.php";  
$limit = 5; 

$query = "SELECT COUNT(*) as num FROM $tableName";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];

$stages = 3;
$page = mysql_escape_string($_GET['page']);
if($page){
    $start = ($page - 1) * $limit; 
}else{
    $start = 0; 
    }   

// Get page data
$query1 = "SELECT * FROM $tableName LIMIT $start, $limit";
$result = mysql_query($query1);

// Initial page num setup
if ($page == 0){$page = 1;}
$prev = $page - 1;  
$next = $page + 1;                          
$lastpage = ceil($total_pages/$limit);      
$LastPagem1 = $lastpage - 1;                    


$paginate = '';
if($lastpage > 1)
{   




    $paginate .= "<div class='paginate'>";
    // Previous
    if ($page > 1){
        $paginate.= "<a href='$targetpage?page=$prev'>previous</a>";
    }else{
        $paginate.= "<span class='disabled'>previous</span>";   }



    // Pages

    if ($lastpage < 7 + ($stages * 2))  // Not enough pages to breaking it up
    {   
        for ($counter = 1; $counter <= $lastpage; $counter++)
        {
            if ($counter == $page){
                $paginate.= "<span class='current'>$counter</span>";
            }else{
                $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
        }
    }
    elseif($lastpage > 5 + ($stages * 2))   // Enough pages to hide a few?
    {
        // Beginning only hide later pages
        if($page < 1 + ($stages * 2))       
        {
            for ($counter = 1; $counter < 4 + ($stages * 2); $counter++)
            {
                if ($counter == $page){
                    $paginate.= "<span class='current'>$counter</span>";
                }else{
                    $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
            }
            $paginate.= "...";
            $paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
            $paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";       
        }
        // Middle hide some front and some back
        elseif($lastpage - ($stages * 2) > $page && $page > ($stages * 2))
        {
            $paginate.= "<a href='$targetpage?page=1'>1</a>";
            $paginate.= "<a href='$targetpage?page=2'>2</a>";
            $paginate.= "...";
            for ($counter = $page - $stages; $counter <= $page + $stages; $counter++)
            {
                if ($counter == $page){
                    $paginate.= "<span class='current'>$counter</span>";
                }else{
                    $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
            }
            $paginate.= "...";
            $paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
            $paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";       
        }
        // End only hide early pages
        else
        {
            $paginate.= "<a href='$targetpage?page=1'>1</a>";
            $paginate.= "<a href='$targetpage?page=2'>2</a>";
            $paginate.= "...";
            for ($counter = $lastpage - (2 + ($stages * 2)); $counter <= $lastpage; $counter++)
            {
                if ($counter == $page){
                    $paginate.= "<span class='current'>$counter</span>";
                }else{
                    $paginate.= "<a href='$targetpage?       page=$counter'>$counter</a>";}                 
            }
        }
    }

            // Next
    if ($page < $counter - 1){ 
        $paginate.= "<a href='$targetpage?page=$next'>next</a>";
    }else{
        $paginate.= "<span class='disabled'>next</span>";
        }

    $paginate.= "</div>";       


}
 echo $total_pages.' Results';
 // pagination
 echo $paginate;
?>

score 1 · Accepted Answer · edited May 23 '17 at 12:12

The ordering of a query without an ORDER BY is unspecified.

Thus, ORDER BY (DESC) in the query is required - this means that there must be a column or set of columns that define a stable ordering. For instance, imagine is the query is updated to:

SELECT * FROM $tableName
ORDER BY createdAt DESC, id ASC
LIMIT $start, $limit

Here I conjecture that there is an createdAt column that represents the dateitem an item was added. The addition of the ordering over the id (presumably an auto-increment PK) is to ensure query stability in the case where multiple createdAt columns have the same value, which might be the case in highly concurrent or mass-insert situations.

Note that I do not simply use ORDER BY id DESC because even though id is (presumed to be) an auto-increment column it does not represent the same stable ordering of information. Databases are about information.

In addition,

MySQL has a clever way of returning the "total count" with a limit and;
Consider updating the code to use mysqli/PDO and prepared statements.

@user2990693 Also, make sure that the column(s) used in the ORDER BY have the appropriate *index* created over them - proper use of indices is what keeps query fast as the amount of data increases. — user2864740, Nov 14 '13 at 06:54

How to get data in descending order?

1 Answers1