using rand() with an array lookup

[MikeM2468]

I need to use rand() to generate pseudorandom numbers. They won't be used for cryptographic purposes so the true randomness is not a concern - they just need to be different from previous ones. What I need to do is to generate numbers that do not appear in a mysql query. I thought I would load the numbers into an array then compare rand() output to the array. Is there a better way to do it?

 

[jpadie]

you can probably do this just in mysql.

but sure that's not an unreasonable way to do it. Probably i'd generate a whole bunch of numbers then use them in an IN query and filter the results based on that. but it's horses for courses.

 

[feherke]

Hi

May depend on how complex is the query that checks the random number and how big is the list of unacceptable numbers.

Maybe would be better to not transfer those data between MySQL and PHP and solve it all in MySQL :

[b]drop[/b] [b]function[/b] [b]if[/b] [b]exists[/b] rand_not_in_db;

[b]delimiter[/b] $$

[b]create[/b] [b]function[/b] rand_not_in_db(max [b]int[/b])
[b]returns[/b] [b]int[/b] [b]reads[/b] [b]sql[/b] [b]data[/b]
[b]begin[/b]
  [b]repeat[/b]
    [b]set[/b] @rand=floor(rand()*max);
    [b]select[/b] count(*) [b]into[/b] @found [b]from[/b] [i]your_table[/i] [b]where[/b] [i]your_field[/i]=@rand;
  [b]until[/b] @found=0 [b]end[/b] [b]repeat[/b];

  [b]return[/b] @rand;
[b]end[/b]
$$

[b]delimiter[/b] ;

Then use it like :

[b]select[/b] rand_not_in_db(10);

Note that the above is just an example. Better take care to not hit infinite loop ( for example, your_table.your_field has values from 0 to 9 and you specify max 10 ) before using it for anything.


Feherke.
feherke.ga

 

[jpadie]

Feher
I think you can do this all in one query

Select 
(Select min(mycolumn) from mytable) 
+
Floor(select Max(mycolumn) - min(mycolumn) + 1 from mytable)
As myrand
From mytable
Where "myrand" not in (select distinct mycolumn from mytable)
Limit 1000

Not tested as I am out of the office only on mobile.

 

[feherke]

Hi

Justin, I gave your code a try with the MySQL 5.5.29 I have installed. Some thoughts :
[ul]
[li]MySQL does not like the query inside the function call. ( The famous and useless "You have an error in your SQL syntax". )[/li]
[li]You call no [tt]rand()[/tt] function. I suppose you intended to call it inside [tt]floor()[/tt].[/li]
[li]myrand is actually one of the returned columns, defined too late to be used in a [tt]where[/tt] clause. ( Leave out those quotes ( " ) and will get "Unknown column 'myrand' in 'IN/ALL/ANY subquery'". )[/li]
[/ul]

So finally reached to this :

[b]select[/b]
max(mycolumn) - min(mycolumn) + 1

[b]into[/b] @difference

[b]from[/b] mytable;

[b]select[/b]
*

[b]from[/b] (
  [b]select[/b]
  ([b]select[/b] min(mycolumn) [b]from[/b] mytable) + floor(rand() * @difference) [b]as[/b] myrand

  [b]from[/b] mytable
) foo

[b]where[/b] myrand [b]not[/b] [b]in[/b] ([b]select[/b] [b]distinct[/b] mycolumn [b]from[/b] mytable)

[b]limit[/b] 1000

This works correctly, but with a caveat : there is a rare chance that all chosen values fall out on the [tt]not[/tt] [tt]in[/tt] and nothing gets returned. But this may be a problem just in my adaptation.


Feherke.
feherke.ga

 

[jpadie]

sorry Feher i was on the move and not typing nor thinking straight

this was the query i was thinking of (selecting random numbers between the min and max of the relevant comparison comment.

select 
Floor(RAND() * (Select max(myColumn) - min(myColumn) + 1 FROM myTable) + (Select min(myColumn) from myTable)) as 'myRand'
from myTable
where 'myRand' NOT IN (select distinct myColumn from myTable)
LIMIT 1000

confirmed that this works fine in my test suite (at least so far as I can tell ...)

 

[jpadie]

ah yes. it only 'seems' to work as the column reference in the where clause is evaluating to a string. I had assumed that the parser had worked out that the where clause should be evaluated later. My bad.

so yes - i agree that that a single query might be an impossible solution for this requirement.

going back to the original question array_diff may help you. However the conundrum is that you may end up with less unique numbers in the resulting array than you need after filtering the two arrays. Which means you would need to build an iterative process; which may slow the script down quite a bit. Better might be to create an arbitrarily large initial array of unique numbers, filter them and then cut off the keys that are above where you need. You'd still need the iterations to be sure of having enough, but getting the right balance of size will help with script execution time.

but I'd think it better to use mysql natively.

one way of optimising the mysql process would be to create a table seeded with random numbers, create a trigger on insert of the comparison table such that corresponding numbers in the random table would be removed then select random rows from the random table as and when you need (or if you don't mind repetition, just select the first X rows each time). Doing this would help speed up by avoiding the relatively expensive (computational) process of comparing each result with an existing dataset.

 

[MikeM2468]

The following appears to work for what I need:

$count = 1;
$randomnumbers = array();
while ($count < 11) {
   array_push($randomnumbers, (rand(1000, 9999)));
   $count++;
}
foreach($randomnumbers as $rnumber) {
   $numberlookup = mysql_query("SELECT usednumber from usednumbers where usednumber = '$rnumber'");
   if (mysql_num_rows($numberlookup) == 0)  {
      unset($matchfound);
      echo "Not a match";
      break;
   } else {
      $matchfound = 1;
      echo "Match found";
   }
}

This gives me 10 random numbers to check against the list and it stops after the first non-match. Since this will be used very rarely, and the usednumbers count is less than 500 out of 10,000 (or 9,000 in this case), I foresee no performance problem.

 

[jpadie]

a couple of things:

1. you might get duplicates in your original array of random numbers. so probably best to use array_unique in the loop.

do{
  $randomnumbers[] = rand(1000, 9999);
  $randomnumbers = array_unique($randomnumbers);
} while (count(randomnumbers) < 10 );

2. looping the numbers is inefficient. instead use this query

$query = "select count(*) as c from usednumbers where usednumber in (" . implode(',', $randomnumbers) .")";
$result = mysql_query($query);
$row = mysql_fetch_object($result);
if($row->c > 0 ): 
 echo 'match found';
endif;

you could expand the loop too

do{
[indent]
do{
  $randomnumbers[] = rand(1000, 9999);
  $randomnumbers = array_unique($randomnumbers);
} while (count(randomnumbers) < 10 );

$query = "select usednumber as c from usednumbers where usednumber in (" . implode(',', $randomnumbers) .")";
$result = mysql_query($query);
$randomnumbers = array_flip($randomnumbers);
while($row = mysql_fetch_object($query) unset($randomnumbers[$row->c]);
$randomnumbers = array_flip($randomnumbers);
[/indent]
}while(count($randomnumbers) < 10 );

 

[MikeM2468]

In your example, how would I get it to just spit out the first number that didn't match? All I'm trying to do is find one number that isn't matched. In my example, it is very unlikely that it would get past the first or second loop. I'm not so concerned about randomness, just as long as a number is created that doesn't match one found in the db.

 

[jpadie]

'most unlikely' is possible, but not definite - so if you care at all AND you need 10 newly random numbers, then the above works fine.

if you must have it stop at any match (and then restart) then you could change to this code

do{

do{
$randomnumbers[] = rand(1000, 9999);
$randomnumbers = array_unique($randomnumbers);
} while (count(randomnumbers) < 10 );

$query = "select usednumber as c from usednumbers where usednumber in (" . implode(',', $randomnumbers) .") [red]LIMIT 1[/red]";
$result = mysql_query($query);
$randomnumbers = array_flip($randomnumbers);
while($row = mysql_fetch_object($query):
unset($randomnumbers[$row->c]);
[red] die('match found');
endwhile; [/red]
$randomnumbers = array_flip($randomnumbers);
}while(count($randomnumbers) < 10 );

echo 'match not found'.PHP_EOL;
print_r($randomnumbers);
[/code]