Recursive call to database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
eboraks
Forum Newbie
Posts: 1
Joined: Sat Apr 12, 2008 8:32 am

Recursive call to database

Post 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
Last edited by Weirdan on Sat Apr 12, 2008 9:41 am, edited 1 time in total.
Reason: php tags
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Recursive call to database

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Recursive call to database

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Recursive call to database

Post by onion2k »

What you're describing certainly doesn't sound like it needs recursion.
Post Reply