Executing while / loop to get 10 random results
Hello, I am trying to make a script tag for my site, so every time a search engine comes to my site, 10 different tags will appear on my site.
These tags will be grabbed from db. So the moment I coded it, it only captures one. (because I don't know how to do while
)
Thus
$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 time to this so that it does it 10 times? For example, use the same echo, but print 10 results instead of 1 ... I changed the limit from 1 to 10, but it didn't work ... still showing one ...
source to share
Note, read before the real answer : for those that keep ignoring this answer. Read the heading (which starts with " Running time ") and the final part, the question ("Anyway, can I add a time so he does this 10 times?"). This answer is about iterating over a result set, not using the RAND function! The request doesn't even appear in my answer, and I also suggest a different approach at the end:
you just need to wrap your call in mysql_fetch_object in a loop
$result = mysql_query($sql);
while ($row = mysql_fetch_object($result))
{
echo "<a href='index.php'>" .$row->tagname. " </a>";
}
Next, edit Other considerations would be as follows:
- if the table contains a very large amount of data (but doesn't seem to be), then the order of rand () can have a bad effect on performance
- consider using pdo (or at least mysqli)
-
you should have some error handling even if the request seems perfect, at least
if (! $ result) {echo mysql_error (); die; }
source to share
Please stop usingORDER BY RAND()
. Just stop. This operation has complexity n*log2(n)
, which means that the time taken for the request will 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 that generates them. Something like this (code taken from this article that 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 have to write:
CALL get_rands(10);
SELECT * FROM rands;
As far as doing all of this on the PHP side, you should stop using the ancient API mysql_*
. It's over 10 years old and no longer supported. The community has even started a process to remove them. In 2012 there shouldn't be any more new code written with mysql_*
. You should use PDO or MySQLi instead . 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 , this would require two changes to PROCEDURE
:
-
The temporary table must ensure that the records are unique:
CREATE TEMPORARY TABLE rands ( tagname VARCHAR(63) UNIQUE);
It might also make sense to collect only identifiers and not values. Especially if you're looking for 10 unique articles, not just tags.
-
When inserting a duplicate value, the counter
cnt
must not decrease. This can be achieved by addingHANDLER
(before the definitionLOOP
) which "caught" the raised warning and adjusted the counter:DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET cnt = cnt + 1;
source to share