MySQL get a random value between two values

by

Actually, ROUND((RAND() * (max-min))+min) is the best way in MySQL to do what you’d like. It is also the best way in ActionScript, JavaScript, and Python. Honestly, I prefer it to the PHP way because it is more convenient.

Because I don’t know how many rows you’ll be returning, I can’t advise you whether it is better to use PHP or MySQL for this, but if you’re dealing with a large number of values you probably are better off using MySQL.

Addendum

So, there was a question as to whether this is better in PHP or MySQL. Instead of getting into a debate on principles, I ran the following:

<pre><?php

$c = mysql_connect('localhost', 'root', '');

if(!$c) die('!');
echo mysql_select_db('test', $c)?'Connection':'Failure';
echo PHP_EOL;

echo ':::::::::::::::::::::::::BEGINNING MYSQL RAND::::::::::::::::::::::::::::::'.PHP_EOL;
$start = microtime(1);
for( $i = 0; $i < 100000; $i++ )
{
    $r = mysql_query( 'SELECT ROUND(RAND() * (200-10) + 10) FROM dual' );
    $r = mysql_fetch_array( $r );
}
$end = microtime(1);

echo  ($end - $start) . " for MySQL select".PHP_EOL;

echo ':::::::::::::::::::::::::BEGINNING PHP RAND::::::::::::::::::::::::::::::' .PHP_EOL;
$start = microtime(1);
for( $i = 0; $i < 100000; $i++ )
{
    $r = mysql_query( 'SELECT 200 AS two, 10 AS tem FROM dual' );
    $r = mysql_fetch_array( $r );
    $r[2]= rand($r[0], $r[1]);
}
$end = microtime(1);

echo  ($end - $start) . " for PHP select".PHP_EOL;

MySQL is faster by about 2-3%.

If you use this, however (note, more columns return by MySQL):

<pre><?php

$c = mysql_connect('localhost', 'root', '');

if(!$c) die('!');
echo mysql_select_db('test', $c)?'Connection':'Failure';
echo PHP_EOL;

echo ':::::::::::::::::::::::::BEGINNING MYSQL RAND::::::::::::::::::::::::::::::'.PHP_EOL;
$start = microtime(1);
for( $i = 0; $i < 100000; $i++ )
{
    $r = mysql_query( 'SELECT ROUND(RAND() * (200-10) + 10) as rd, 200 as two, 10 as ten FROM dual' );
    $r = mysql_fetch_array( $r );
}
$end = microtime(1);

echo  ($end - $start) . " for MySQL select".PHP_EOL;

echo ':::::::::::::::::::::::::BEGINNING PHP RAND::::::::::::::::::::::::::::::' .PHP_EOL;
$start = microtime(1);
for( $i = 0; $i < 100000; $i++ )
{
    $r = mysql_query( 'SELECT 200 AS two, 10 AS tem FROM dual' );
    $r = mysql_fetch_array( $r );
    $r[2]= rand($r[0], $r[1]);
}
$end = microtime(1);

echo  ($end - $start) . " for PHP select".PHP_EOL;

MySQL comes out behind by 3-4% (very inconsistent results) (about the same results if you don’t use an array index assignment for $r[2]).

The major difference, it seems, comes from the number of records return to PHP and not the randomization system itself. So, if you need column A, column B, and a random value, use PHP. If you only need the random value, then use MySQL.

More Related Contents:

  1. Php date() Date and time is stuck [closed]
  2. inserting information and image in php and mysql
  3. CodeIgniter – foreach loop
  4. mysqli or die, does it have to die?
  5. Project Links do not work on Wamp Server
  6. PHP/MySQL insert row then get ‘id’
  7. Show values from a MySQL database table inside a HTML table on a webpage [duplicate]
  8. How can I store and retrieve images from a MySQL database using PHP?
  9. PHP file cannot enter some part of code
  10. Laravel migration: unique key is too long, even if specified
  11. Laravel: PDOException: could not find driver
  12. Laravel Eloquent select all rows with max created_at
  13. MySQL – count total number of rows in php
  14. Advantages Of MySQLi over MySQL [closed]
  15. What does a successful MySQL DELETE return? How to check if DELETE was successful?
  16. Grouping WHERE clauses in Codeigniter
  17. Using if(isset($_POST[‘submit’])) to not display echo when script is open is not working
  18. check if the query results empty row mysqli [duplicate]
  19. PHP – Import CSV file to mysql database Using LOAD DATA INFILE
  20. double results in my array ( mysql_fetch_array )
  21. How best to get someone’s ‘rank’ from a scores table with php and mysql without looping
  22. MySQL error when inserting data containing apostrophes (single quotes)?
  23. Connect to remote MySQL server with SSL from PHP
  24. Using MySQL’s TIMESTAMP vs storing timestamps directly
  25. Using utf8mb4 with php and mysql
  26. how do I get month from date in mysql
  27. MySQL, how to insert null dates
  28. Can I detect and handle MySQL Warnings with PHP?
  29. How to write a stored procedure in phpMyAdmin?
  30. mysql count into PHP variable

Leave a Comment