How to limit items from while loop

Question

This is my while loop from my project :

<?php
   $select = "SELECT * FROM nk_showcase";
   $query = $db->rq($select);
   while ($user = $db->fetch($query)) {
?>

    <div class="index">
        <a href="details.php?id=<?php echo $user['id']; ?>"><img width="200" height="171" alt="<?php echo $user['title']; ?>" src="<?php echo $url; ?>/images/niagakit/<?php echo $user['thumb']; ?>"/></a>
        <h3><a href="<?php echo $url; ?>/"><?php echo $user['title']; ?></a></h3>
        <p><a href="<?php echo $url; ?>/"><?php echo $user['url']; ?></a></p>
    </div>

<?php } ?>

As you already know, this while loop will loop for all items they found in my database, so my quuestion is, how to limit this loop only for 10 items only from my database and how to rotate that items every refresh?

Answer 1

In SQL:

$select = "SELECT * FROM nk_showcase LIMIT 0,10";

or in PHP:

$counter = 0;
$max = 10;

 while (($user = $db->fetch($query)) and ($counter < $max))
  {
   ... // HTML code here....

   $counter++;
  }

As to the rotating, see @Fayden's answer.

Answer 2

Rotate as in random, or as the next 10 elements ?

Most RDBMS allow you to order rows by random :

-- MySQL
SELECT * FROM nk_showcase ORDER BY RAND() LIMIT 10
-- PostgreSQL
SELECT * FROM nk_showcase ORDER BY RANDOM() LIMIT 10

Which would select 10 random rows every time you refresh the page

If you want to show the next 10 elements, you would have to paginate the page (and use the LIMIT X OFFSET Y syntax)

Answer 3

You have to change your query $select, try using LIMIT to 10 if you just need the 10 first items or try also with OFFSET if you need to paginate the results.

$select.=" OFFSET $start LIMIT $range;";

Then you need to control the $start and $range variables like:

$size_page=10;
 if (!$page) {
             $start = 0;
             $page=1;
        }
        else {
            $start = ($page - 1) * $size_page;
        } 

You can notice that $range should be the same value of $size_page and just you need to calculate the $start value for each iteration taking into account the #pages