Doing a while / loop to get 10 random results

Hello i'm trying to make a tag script for my website so each time a search engine comes to my website 10 different tags will show on my website.

These tags will be grabbed from the db. So at the minute i have coded it so it grabs only one. ( because i don't know how to do a while )

Like so

$sql = "SELECT tagname FROM tags ORDER BY rand() LIMIT 10";
$result = mysql_query($sql);
$row = mysql_fetch_object($result);
echo "<a href='index.php'>" .$row->tagname. " </a>";

Is there anyway i can add a while to that so it does it 10 times ? E.g use the same echo but print out 10 results instead of the 1 .... i have changed the limit from 1 to 10 but that did not work... still showing one...

Solution 1:

Please, stop using ORDER BY RAND(). Just stop. This operation has complexity of n*log2(n), which means that the time spent on query would grow "

    entries  |  time units
  -------------------------
         10  |         1     /* if this takes 0.001s */
      1'000  |       300
  1'000'000  |   600'000     /* then this will need 10 minutes */

If you want to generate random results, create a stored procedure, which generates them. Something like this (code taken from this article, which you should read):

DELIMITER $$
DROP PROCEDURE IF EXISTS get_rands$$
CREATE PROCEDURE get_rands(IN cnt INT)
BEGIN
  DROP TEMPORARY TABLE IF EXISTS rands;
  CREATE TEMPORARY TABLE rands ( tagname VARCHAR(63) );

loop_me: LOOP
    IF cnt < 1 THEN
      LEAVE loop_me;
    END IF;

    SET cnt = cnt - 1;

    INSERT INTO rands
       SELECT tags.tagname
         FROM tags 
         JOIN (SELECT (RAND()*(SELECT MAX(tags.id) FROM tags)) AS id) AS choices
        WHERE tags.id >= choices.id
        LIMIT 1;

  END LOOP loop_me;
END$$
DELIMITER ;

And to use it, you would write:

CALL get_rands(10);
SELECT * FROM rands;

As for executing it all on PHP side, you should stop using the ancient mysql_* API. It is more than 10 years old and no longer maintained. Community has even begun process for deprecating them. There should not be any more new code written with mysql_* in 2012. Instead you should use PDO or MySQLi. As for how to write it (with PDO):

// creates DB connection
$connection = new PDO('mysql:host=localhost;dbname=mydb;charset=UTF-8', 
                      'username', 'password');
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

// executes the procedure and creates select statement
$connection->exec('CALL get_rands(10)');
$statement = $connection->query('SELECT * FROM rands');

// performs query and collects all the info
if ($statement->execute())
{
    $tags = $statement->fetchAll(PDO::FETCH::ASSOC);
}

Update

If the requirement is to get not only 10 random results, but actually 10 UNIQUE random results, then it would require two changes to the PROCEDURE:

1. The temporary table should enforce the uniqueness of entries:

   CREATE TEMPORARY TABLE rands ( tagname VARCHAR(63) UNIQUE);
   

   It also might make sense to collect just IDs and not the values. Esspecially if what you are looking for are 10 unique articles, not just tags.

2. When inserting a duplicate value is found, the cnt counter should not decrease. This can be ensured by adding a HANDLER (before definition of LOOP), which would "catch" the raised warning, and adjust the counter:

   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET cnt = cnt + 1;