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. :oops:

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! 8)