yarin groiser yarin groiser - 7 months ago 26
PHP Question

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;
}
?>

Answer

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