Select a random row in MySQL

Written by Mfawa Alfred Onen on . Posted in MySQL, PHP

I know this topic is not a new one and I was playing with the solutions out there but most of them just don’t scale for me until I found this interesting was of doing it. It actually involves a three (3) query step process which you can checkout in the PHP script below. I have also attached a zip version of the script.

<?php
// Database related stuff
// =======================================
// CREATE TABLE `tickets` (
// `id` int(11) NOT NULL AUTO_INCREMENT,
// `phone_numbers` varchar(20) DEFAULT NULL,
// `redeem` int(2) NOT NULL,
// PRIMARY KEY (`id`)
// ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
// ======================================

$con = mysql_connect('localhost','root');
if($con){
mysql_select_db('raffle_demo') or die(mysql_error());
}

// Just a method for echo'ing stuff to the screen
function logger($str, $t = ''){
echo $str . '<br>';
}

// Set Initial Start Time
$time= microtime(true);

// Get the count of how rows in the Table
$sql='SELECT COUNT(*) FROM tickets';
$result= mysql_query($sql);
list($num_records) = mysql_fetch_row($result);
mysql_free_result($result); // Always Remember to free the memory consumed by MySQL

// Get a random ID associated with a phone number ensuring that null is not returned
$sql="SELECT id FROM tickets WHERE RAND()*$num_records != '' ORDER BY RAND() LIMIT 0,1";
$result= mysql_query($sql);
list($id) = mysql_fetch_row($result);
mysql_free_result($result); // Always Remember to free the memory consumed by MySQL

// Using the random ID, get a corresponding Phone Number
$sql="SELECT phone_numbers FROM tickets WHERE id = $id";
$result= mysql_query($sql);
list($phone_number) = mysql_fetch_row($result);
logger($phone_number,1);
mysql_free_result($result); // Always Remember to free the memory consumed by MySQL

// Subtract the Start Time from now to get Time Elapsed
$time= microtime(true)-$time;
logger("Time elapsed: <b>$time seconds</b>",1);

Trackback from your site.

Mfawa Alfred Onen

I am a System Administrator and I work at Bingham University.
  • This is a comment to the webmaster. Your website is missing out on at least 300 visitors per day. I came to this page via Google but it was difficult to find as you were not on the front page of search results. I have found a website which offers to dramatically increase your traffic to your website: http://voxseo.com/traffic/. I managed to get over 10,000 visitors per month using their services, you could also get lot more targeted traffic than you have now. Hope this helps 🙂 Take care.

  • That’s the prfecet insight in a thread like this.