CODEWITHSUNDEEP
phpmysqlsql
yarin groiser
yarin groiser

Reputation: 11

How to limit "SELECT" results?

I have the next code and he showing new categoires im making to the users. the problem I dont know how to limit the results and make like limit for 5 last categiroes? thanks! sorry about bad english. thanks

   <?php
    $stmt = $pdo->prepare('SELECT * FROM categories');
    $stmt->execute();
    echo $stmt->rowCount();
?>
</span>
</a>

<ul class="dropdown-menu extended notification">
    <li>
        <p>Categories</p>
    </li>

<?php
    if($stmt->rowCount() > 0) {
        foreach($stmt->fetchAll() as $row) {
            $html = '<li>';
            $html .= '<div class="alert alert-info clearfix">';
            $html .= '<span class="alert-icon"><i class="fa fa-flag"></i></span>';
            $html .= '<div class="noti-info">';
            $html .= 'Category #'.$row['CategoryID'].' '.$row['CategoryName'].'';
            $html .= '</div>';
            $html .= '</div>';
            $html .= '</li>';

            echo $html;
        }
    } else {
        $html = '<li>';
        $html .= '<div class="alert alert-danger clearfix">';
        $html .= '<span class="alert-icon"><i class="fa fa-flag"></i></span>';
        $html .= '<div class="noti-info">';
        $html .= '<a href="#"> There are no available categories.</a>';
        $html .= '</div>';
        $html .= '</div>';
        $html .= '</li>';

        echo $html;
    }
?>

Upvotes: 1

Views: 66

Answers (2)

Pedro Lobito
Pedro Lobito

Reputation: 99071

Use the ORDER BY, DESC and LIMIT on your query, i.e.:

SELECT * FROM categories ORDER BY ColumnNameEx DESC LIMIT 5;

ORDER BY

In some cases, MySQL can use an index to satisfy an ORDER BY clause without doing extra sorting.
The index can also be used even if the ORDER BY does not match the index exactly, as long as all unused portions of the index and all extra ORDER BY columns are constants in the WHERE clause

http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

DESC

The default sort order is ascending, with smallest values first. To sort in reverse (descending) order, add the DESC keyword to the name of the column you are sorting by.

http://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html

LIMIT

If you need only a specified number of rows from a result set, use a LIMIT clause in the query, rather than fetching the whole result set and throwing away the extra data.

https://dev.mysql.com/doc/refman/5.5/en/limit-optimization.html

Upvotes: 2

SEUH
SEUH

Reputation: 324

Add at the end of the query "LIMIT x", x is the amount of results you want to get

Upvotes: 0

Related Questions