Recursive function returns nothing

Question

I have a database full of autopart numbers that needs old part numbers prices updated. Each row features a part number (field name "master") a price and a field with superseded (newer) part numbers (field name "pnc"). The script needs to check if the "pnc" field isn't empty. If it's not, it should go and grab the price for that number. Easy enough.

However, some part numbers have an unknown level of numbers to go through until it reaches the most current part number and price. So, I figured a recursive function would be the best solution. However, it's not working correctly. Here's the code:

    public function updatePricing()
    {
        //method to update pricing by referencing supersession prices
        $sql = "SELECT * FROM prices";
        $result = mysql_query($sql);
        $num_rows = mysql_num_rows($result);

        for($i=0;$i<2000;$i++) //using 2000 for testing, use $num_rows after
            {
                $row = mysql_fetch_array($result);
                $id = $row['id'];
                $super_num = $row['pnc'];

                //if there is a supersession in this row find original
                if(!empty($super_num))
                    {
                        $final_super_price = $this->findSuperPrice($super_num);

                        echo "partnum: " . $row['master'];
                        echo "&nbsp;&nbsp;";
                        echo "price: " . $final_super_price . "<br /><br />";
                    }
            }
    }

public function findSuperPrice($part_num)
    {
        $sql = "SELECT * FROM prices WHERE master='" . $part_num . "'";
        $result = mysql_query($sql);
        $row = mysql_fetch_array($result);

        if (empty($row['pnc'])) //if there aren't any supersession numbers
            {
                $final_price = $row['list'];
                return $final_price;
            }
        else //recursively call itself until we find the last number
            {
                $this->findSuperPrice($row['pnc']);
            }
    }

What's happening is the updatePricing() function runs until it finds a row that has an entry in the "pnc" field. When it does, the findSuperPrice() function is called. The findSuperPrice() function should run recursively until the "pnc" field is empty. When that happens a number is returned.

However, if it actually reaches the else part of the if statment in findSuperPrice(), it doesn't return anything. Basically, if it's more than one level deep. I'm not getting any errors, it's just returning a blank statement. I've verified there is information there that it should be returning also. Thanks.

Also, I should mention this is inside of a larger class. The rest of the class has no bearing on these two methods.

Consider using a `do {} while()` loop instead. While your DB structure is recursive, there's no need to use an actual recursive call to work your way up the tree. — Marc B, Oct 31 '11 at 19:50

score 4 · Accepted Answer · answered Oct 31 '11 at 19:47

You need to return a value. Change this code:

else //recursively call itself until we find the last number
    {
      $this->findSuperPrice($row['pnc']);
    }

To this:

else //recursively call itself until we find the last number
    {
      return $this->findSuperPrice($row['pnc']);
    }

score 2 · Answer 2 · answered Oct 31 '11 at 19:47

2

You currently do not get a return value because the result of findSuperPrice is ignored if $row['pnc'] is not empty. Correctly return the value of the recursive calls:

return $this->findSuperPrice($row['pnc']);

answered Oct 31 '11 at 19:47

Lekensteyn

64,486
22
159
192

score 1 · Answer 3 · edited May 23 '17 at 12:27

You are missing a return statement in the else case inside findSuperPrice.

But... it looks like you are pumping a whole lot of data to look at/manipulate only a single field. You could also write function in mysql, similar to the one I posted here: MySQL: Get Root Node of Parent-Child Structure

That could enable you to query the required value directly from the database. If you have the structure for your tables I'd be happy to help out.

Recursive function returns nothing

3 Answers3