Page 1 of 1
recursive function failed! please help!
Posted: Tue Jul 15, 2008 2:06 am
by dc2698
Hi I have a MySQL table that contains records of numbers i.e. products_num 1, products_num 2 now in the test table products
I tried to write a recursive function which check a randomly generated number against each products_num for no duplicates:
Code: Select all
function gen_rand_products_num() {
$rand1 = mt_rand(1, 3); //rand gen 1, 2, or 3
$get_all_products_num = mysql_query("select products_num from products");
$num_of_products_nums = mysql_num_rows($get_all_products_num );
for ($i=0; $i<$num_of_products_nums; $i++) { //0 to 1 ($num_of_products_nums = 2)
if (!mysql_data_seek($get_all_products_num , $i)) {
continue;
}
if (!($row1 = mysql_fetch_assoc($get_all_products_num ))) {
continue;
}
if ((int)$row1['products_num'] != (int)$rand1) {
$rand_products_num = $rand1;
return $rand_products_num;
} else {
gen_rand_products_num();
}
}
}
echo gen_rand_products_num();
The function above is supposed to return a 3 only, that is not a duplicate of products_num 1 and products_num 2 of the products table.
But it still returns 1 or 2, along with 3. So please help me fix the function! Thanks in advance!
Re: recursive function failed! please help!
Posted: Tue Jul 15, 2008 2:11 am
by VladSun
Much easier:
[sql]SELECT *FROM productsORDER BY RAND()LIMIT 3[/sql]
No need of using PHP at all
EDIT: Hm, maybe I didn't understand your question correctly ...
Re: recursive function failed! please help!
Posted: Tue Jul 15, 2008 2:34 am
by VladSun
Maybe this will be more helpful:
[sql]SELECT producs.product_num + 1FROM productsLEFT JOIN products AS products_missing ON producs.product_num + 1 = products_missing.product_numWHERE products_missing.product_num IS NULLORDER BY RAND()LIMIT 1[/sql]
I would strongly advice you that whenever possible you should use the DB engine and not PHP code to perform selects on a database. Also, using recursive functions is not a good practice.
Re: recursive function failed! please help!
Posted: Tue Jul 15, 2008 2:51 am
by dc2698
Thank you, VladSun, for your quick reply and tips. However, that's not what I need.
If incrementing by 1 does the thing, then I could just use autonumber when setting up the products_num.
If, for example, products_num 1, 2, 3 exist and when 3 is deleted then next generated number will be 2+1=3 which has already been duplicated and for deletion.
Actually the $rand_products_num is for external use (e.g. to be used in a javascript thing). So that must be random while not equal to any of the products_num's in the table products.
Perhaps I should have mentioned that the select statement is fixed, cannot be changed. Just change the PHP code to get my function to work.
Thanks, anyway

Re: recursive function failed! please help!
Posted: Tue Jul 15, 2008 2:59 am
by VladSun
dc2698 wrote:So that must be random while not equal to any of the products_num's in the table products.
This is exactly what the query does.

That's why the "ORDER BY RAND()" is there.
Also, the result will be between 0 and MAX(product_num)+1
dc2698 wrote:Perhaps I should have mentioned that the select statement is fixed, cannot be changed.
It does what it is expected to do, but whenever you want to change it - just use PHP to change it

E.g.
Code: Select all
$random_numbers_count = 3;
$sql = "
SELECT
producs.product_num + 1
FROM
products
LEFT JOIN
products AS products_missing ON producs.product_num + 1 = products_missing.product_num
WHERE
products_missing.product_num IS NULL
ORDER BY
RAND()
LIMIT
".$random_numbers_count ;
dc2698 wrote: Just change the PHP code to get my function to work.
No, I wouldn't do that. It will be a waste of CPU time and resources.
Re: recursive function failed! please help!
Posted: Tue Jul 15, 2008 3:26 am
by dc2698
VladSun your MAX(product_num)+1 method is great for performance but it does not generate a number randomly. It just returns a 3 all the time.
Imagine products_num column has 1000 rows. Each of which is not an increment of one another. And each products_num now consists of 6 digits. Then merely incrementing by 1 is not random enough. So, I made a change to your +1 increment to make the query result return a truely random non-duplicated 6 digit number.
Code: Select all
SELECT products.products_num + FLOOR(1 + (RAND() * 111111)) as rand_gen_products_num from products LEFT JOIN products AS products_missing ON products.products_num + FLOOR(1 + (RAND() * 111111)) = products_missing.products_num WHERE products_missing.products_num IS NULL ORDER BY RAND() LIMIT 1
But I still can't get the reason why my recursive function didn't work as expected..............

Re: recursive function failed! please help!
Posted: Tue Jul 15, 2008 3:50 am
by VladSun
OK, it's clear now

Your query is not correct because on every RAND() call a new random number is generated, so the random numbers in the "select" clause and in the "on" clause will be different, which leads to a possibility of having existing product_num returned.
Because, right now I can't figure out a MySQL solution to the problem, I'll try to give a PHP solution:
Code: Select all
$i = 0;
while(true)
{
$i = mt_rand(1, $records_count + 1);
$result = mysql_query("select count(product_num) from products where product_num=".$i);
$count = mysql_fetch_array($result);
if ($count[0] == 0)
break;
}
echo $i;
Re: recursive function failed! please help!
Posted: Tue Jul 15, 2008 3:58 am
by VladSun
The decision which one to use must be based on the product_num dispersion - for high dispersion use the PHP based solution, and for low dispersion use the SQL based solution - it will be not so "true random", but it will be *much* faster than the PHP solution.
The dispersion will be easily calculated by:
Code: Select all
dispersion = ( MAX(product_num) - MIN(product_num) ) / COUNT(product_num)
Re: recursive function failed! please help!
Posted: Tue Jul 15, 2008 4:14 am
by VladSun
hm, maybe:
[sql]SELECT floor(rand()*(next_products.product_num - products.product_num - 2 )) + products.product_num + 1 AS random_numFROM productsINNER JOIN products AS next_products ON products.product_num < next_products.product_numGROUP BY products.product_numHAVING random_num > 0ORDER BY rand()[/sql]
EDIT: I think you must have an index on product_num for having this query working correctly - that's because of the "hidden" order by performed in the JOIN clause.
Re: recursive function failed! please help!
Posted: Tue Jul 15, 2008 9:05 am
by dc2698
VladSun, your dispersion-wise does not work for me because I do not have an index on product_num.
I prefer your PHP solution more and it is more flexible than my poor and unsuccessful recursive function.
Oh for FLOOR(1 + (RAND() * 111111)), I can use a PHP variable $rand1 = mt_rand(1, 111112) and put the $rand1 in the two instances in the SELECT statement.
Thank you very much for your awesome help! You are a really helpful DevNet Resident here!
