recursive function failed! please help!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dc2698
Forum Newbie
Posts: 7
Joined: Fri Jan 05, 2007 1:53 am

recursive function failed! please help!

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: recursive function failed! please help!

Post 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 ...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: recursive function failed! please help!

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
dc2698
Forum Newbie
Posts: 7
Joined: Fri Jan 05, 2007 1:53 am

Re: recursive function failed! please help!

Post 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 :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: recursive function failed! please help!

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
dc2698
Forum Newbie
Posts: 7
Joined: Fri Jan 05, 2007 1:53 am

Re: recursive function failed! please help!

Post 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.............. :?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: recursive function failed! please help!

Post 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;
 
Last edited by VladSun on Tue Jul 15, 2008 4:24 am, edited 3 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: recursive function failed! please help!

Post 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)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: recursive function failed! please help!

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
dc2698
Forum Newbie
Posts: 7
Joined: Fri Jan 05, 2007 1:53 am

Re: recursive function failed! please help!

Post 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)
Post Reply