My Little Scripts - random-mysql-result-set.php

Scripts.com - Get the best scripts NOW!
HotScripts.com - Over 10 web rings.
more links...

Index News Samples Downloads E-Mail me Forum Sign Up

RANDOM-MYSQL-RESULT-SET.PHP

This sample script produces a result set of random rows from a table. It does so by populating one of the columns with a random number, and then doing a select statement that uses it to sort the results.

On the right, you will find the script along with the sql statements required to create the required table. Both files are just samples, and should be appropriately modified and integrated to suit any specific development requirement. Don't forget to initialize the variables at the beginning.

<?
$host = "127.0.0.1";
$user = "";
$pass = "";
$db   = "";

$connection = mysql_connect($host, $user, $pass);
mysql_select_db($db);

$number_of_scripts = 5;

//Randomise the table.
$update = "update scripts set random_order = 65536 * rand(random_order)";
mysql_query($update);

//Retrieve from the beginning and the end.
$upper_limit = floor($number_of_scripts / 2);
$lower_limit = $number_of_scripts - $upper_limit;
$select = "(select id, name from scripts order by random_order limit " . $upper_limit . ")" .
          " union " .
          "(select id, name from scripts order by random_order desc limit " . $lower_limit . ")" .
          " order by name";

$scripts = mysql_query($select);
$index = 0;
?>

<html>
<head>
<title>Random MySQL Result Set</title>
</head>

<body>
<? while($script = mysql_fetch_assoc($scripts)) { ?>
<a href="http://scripts.franciscocharrua.com/<?= $script["name"] ?>.php" target="blank">
<?= $script["name"] ?></a><br />
<? } ?>
</body
</html>

create table scripts
(
id int primary key auto_increment,
name tinytext,
random_order int
);

insert into scripts (name) values ('absolute-include');
insert into scripts (name) values ('database-file-upload-download');
insert into scripts (name) values ('email');
insert into scripts (name) values ('forum_threads');
insert into scripts (name) values ('link-words');
insert into scripts (name) values ('login');
insert into scripts (name) values ('params');
insert into scripts (name) values ('upload');
insert into scripts (name) values ('users');
insert into scripts (name) values ('mouse-events');
insert into scripts (name) values ('countdown-clock');
insert into scripts (name) values ('dhtml_layers');
insert into scripts (name) values ('javascript_cookies');
insert into scripts (name) values ('javascript-get-variables');
insert into scripts (name) values ('xray');
insert into scripts (name) values ('ssl-whm');
insert into scripts (name) values ('ftp-client');