Page 1 of 1

Recursive call to database

Posted: Sat Apr 12, 2008 9:09 am
by eboraks
I am trying to write a recursive function that call a database. I have a table of zipcodes and their latitudes and attitudes. In addition I have a store procedure that accept zipcode and radius, and return the near by zipcodes. I need a function that will return only 7 zipcodes. To achieve this I trying to write recursive function that will change the radius according to the number of zipcodes return. But, I could not make the function work. Below is the code and the error I got from PHP. I will appreciate any suggestions.

Code: Select all

//Connect to database
$conn = mysql_connect($DBHOST, $DBUSER, $DBPASS, 0, 65536);
if(!$conn){
    exit('Error connecting to databse'. mysql_error());
}
 
//Open database
$db_select = mysql_select_db($DBNAME);
if(!$db_select){
        exit('Error opening to databse: '. mysql_error());
}
    
//Set radius variable
$radius = 10; 
 
$zipcode = '02138';
 
GetNearZipCodes($zipcode, $radius);
 
function GetNearZipCodes($zip, $rad){
 
    global $conn;
    
    echo 'functions param: '.$zip.'  '.$rad;
    
    $zip = mysql_real_escape_string($zip);
    
    $result = mysql_query("CALL GetNearbyZipCodes('{$zip}', {$rad})", $conn);
 
    if(isset($result)){
        
        $num_rows = mysql_num_rows($result);
        
        if($num_rows > 7){
            
            //reduce the size of radius
            --$rad;
                        
            GetNearZipCodes($zip, $rad);
        }else{
            while ($row = mysql_fetch_assoc($result)){
            echo '<br />'.$row[zipcode];
            }//end while
        }//end else
    }
}
 
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\workspace\project3\GetZipcodes.php on line 32

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\workspace\project3\GetZipcodes.php on line 41

Re: Recursive call to database

Posted: Sun Apr 13, 2008 2:33 pm
by shiznatix
change:

$result = mysql_query("CALL GetNearbyZipCodes('{$zip}', {$rad})", $conn);

to:

$result = mysql_query("CALL GetNearbyZipCodes('{$zip}', {$rad})", $conn) or die(mysql_error());

and you will get a surprise.

Re: Recursive call to database

Posted: Sun Apr 13, 2008 7:01 pm
by s.dot
OUCH, recursive function involving queries. Especially looking up zip code data :P That seems like it will grind an application to a halt.

Re: Recursive call to database

Posted: Mon Apr 14, 2008 2:35 am
by onion2k
What you're describing certainly doesn't sound like it needs recursion.