Link to home
Start Free TrialLog in
Avatar of cbeaudry1
cbeaudry1

asked on

Zip Code Search for PHP/MSSQL

I'm running Apache/PHP on Windows with a separate MSSQL Server. Everything is the latest version. I'm trying to code the PHP page that will do a reseller search but am having trouble connecting to the SQL Server.

The PHP code:

//Simulate data entry
      $zipCode = "19107";
      $radius = "25";

      //Connect to database
      $db = new COM("ADODB.Connection");
      $dsn = "DRIVER={SQL Server}; SERVER=server\dev;UID=uname;PWD=pass; DATABASE=database";
      $db->Open($dsn);
      
      function inRadius($zipCode,$radius)
      
       {
      global $db;
      global $zipCode;
      global $radius;
      
             $query=$db->Execute("SELECT * FROM PostalCodes WHERE PostalCode='$zipCode'");
             $result = mssql_query($query, $db->Open($dsn));

             if(mssql_num_rows($result) > 0) {
             $row = mssql_fetch_array($result, MSSQL_ASSOC);
             $lat=$row["Latitude"];
             $lon=$row["Longitude"];
             $query=$db->Execute("SELECT * FROM PostalCodes WHERE (POW((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(lat-\"$lat\")),\"2\"))<($radius*$radius) ");
             $result = mssql_query($query);
             if(mssql_num_rows($result) > 0) {
             while($row = mssql_fetch_array($result, MSSQL_ASSOC)) {
             $zipArray[]=$row;
             }
             return $zipArray;
             }
             } else {
             return "Zip Code not found";
             }
       } // end func

 //$zipCode = $HTTP_POST_VARS["zipCode"];
 //$radius = $HTTP_POST_VARS["radius"];

 $zipArray = inRadius($zipCode,$radius);

 print "<h2>There are ".count($zipArray)." Zip codes within $radius Miles of $zipCode</h2>";
 foreach($zipArray as $row) {
 print "<br>ZipCode:$row[PostalCode] Lon:$row[Longitute] Lat:$row[Latitude] City: $row[CityName]";
 }

The result:

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Provider<br/><b>Description:</b> Type mismatch.' in D:\Inetpub\website\TEST2.php:27 Stack trace: #0 D:\Inetpub\website\TEST2.php(27): com->Open(NULL) #1 D:\Inetpub\website\TEST2.php(49): inRadius('63011', '25') #2 {main} thrown in D:\Inetpub\website\TEST2.php on line 27

The SQL type for the PostalCode field is nvarchar.

 Where am I going wrong?
Avatar of amit_g
amit_g
Flag of United States of America image

Change

$query=$db->Execute("SELECT * FROM PostalCodes WHERE (POW((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(lat-\"$lat\")),\"2\"))<($radius*$radius) ");

to

$query=$db->Execute("SELECT * FROM PostalCodes WHERE (POWER((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POWER((69.1*(lat-\"$lat\")),\"2\"))<($radius*$radius) ");

and also add

echo "SELECT * FROM PostalCodes WHERE (POWER((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POWER((69.1*(lat-\"$lat\")),\"2\"))<($radius*$radius) ";

If you get error, post that along with the line that cause that error.
Avatar of cbeaudry1
cbeaudry1

ASKER

I should have mentioned that Line 27 is

$result = mssql_query($query, $db->Open($dsn));

Modifiying the radius query spits out the same error because the script doesn't get that far.
If I modify the beginning of that script to echo the query results as follows:

$query=$db->Execute("SELECT * FROM PostalCodes WHERE PostalCode='$zipCode'");
echo "$query";
$result = mssql_query($query, $db->Open($dsn));

I get:

Object id #2
Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Provider<br/><b>Description:</b> Type mismatch.' in D:\Inetpub\epropper\TEST2.php:28 Stack trace: #0 D:\Inetpub\epropper\TEST2.php(28): com->Open(NULL) #1 D:\Inetpub\epropper\TEST2.php(52): inRadius('63011', '25') #2 {main} thrown in D:\Inetpub\epropper\TEST2.php on line 28

line 28 ----> $result = mssql_query($query, $db->Open($dsn));
Point increase....
The database connection is not opened. Try changing that portion

     //Connect to database
     $db = new COM("ADODB.Connection");
     $dsn = "DRIVER={SQL Server}; SERVER=server\dev;UID=uname;PWD=pass; DATABASE=database";
     $db->Open($dsn);

to

$msconnect=mssql_connect("server\dev","uname","pass");
$msdb=mssql_select_db("database",$msconnect);
Nada. That gives the following:

Warning: mssql_connect() [function.mssql-connect]: message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (severity 14) in D:\Inetpub\website\TEST2.php on line 15

Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: server\dev in D:\Inetpub\website\TEST2.php on line 15

Warning: mssql_select_db(): supplied argument is not a valid MS SQL-Link resource in D:\Inetpub\website\TEST2.php on line 16

Fatal error: Call to a member function Execute() on a non-object in D:\Inetpub\website\TEST2.php on line 25

The connection is being made to the SQL server with what was there before. Again, it is the line with $result being defined that spews out the error:

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Provider<br/><b>Description:</b> Type mismatch.' in D:\Inetpub\website\TEST2.php:27 Stack trace: #0 D:\Inetpub\website\TEST2.php(27): com->Open(NULL) #1 D:\Inetpub\website\TEST2.php(50): inRadius('63011', '25') #2 {main} thrown in D:\Inetpub\website\TEST2.php on line 27

Whay is it using the ANONYMOUS LOGON? How is your SQL Server configured? Does it only accept Windows Authentication? Could it be changed to accept the SQL Authentication also?
The server is configured to accept both SQL and Windows authentication. the uname/pass combo attempted were either SQL or Windows account. This login script works fine going into the database. This is not the issue. That connection is being used for other components on the website.

The issue is why the data is not being passed back.
If the database connection is proper and you have used it in some other places within the same website, could you show us the code from a working page? Also could you change the query to its simplest form i.e.

SELECT top 5 * FROM PostalCodes

and see if it works?
Sorry for the long delay in getting back to this. My mom died recently and I had to travel back to Canada for a while. Anyhow...the following works to retrieve the stores in the 19107 zip code:

<?php
$db = new COM("ADODB.Connection");
$dsn = "DRIVER={SQL Server}; SERVER=servername;UID=uname;PWD=pass; DATABASE=database";
$db->Open($dsn);
$rs = $db->Execute("SELECT * FROM resellers where reszip = '19107'");

while (!$rs->EOF)
{
$name = $rs->Fields['resname']->Value;
$add1 = $rs->Fields['resadd1']->Value;
$add2 = $rs->Fields['resadd2']->Value;
$add3 = $rs->Fields['resadd3']->Value;
$add4 = $rs->Fields['resadd4']->Value;
$zip = $rs->Fields['reszip']->Value;
$web = $rs->Fields['resweb']->Value;
$email = $rs->Fields['resemail']->Value;
$code = $rs->Fields['rescode']->Value;


   echo " $name <BR>";
   echo " $add1 <BR>";
   if ($add2 == null or $add2 == "") {
   echo "";
   }
   else {
   echo " $add2 <BR>";
   }
   if ($add3 == null or $add3 == "") {
   echo "";
   }
   else {
   echo " $add3 <BR>";
   }
   echo " $add4 <P>";
   $rs->MoveNext();
}
?>
The idea is to combine retrieving the data from the code above with the data within the postalcodes table to create a store locator using distance and zipcode (or postal code) entered by the user.
Sorry to hear about your mother.
Try to write the function in this way...

//Simulate data entry
     $zipCode = "19107";
     $radius = "25";

     //Connect to database
     $db = new COM("ADODB.Connection");
     $dsn = "DRIVER={SQL Server}; SERVER=server\dev;UID=uname;PWD=pass; DATABASE=database";
     $db->Open($dsn);
     
     function inRadius($zipCode,$radius)
     
      {
     global $db;
     global $zipCode;
     global $radius;
     
           $rs=$db->Execute("SELECT * FROM PostalCodes WHERE PostalCode='$zipCode'");
           $result = mssql_query($query, $db->Open($dsn));

         if (!$rs->EOF) {
               $lat=$rs->Fields['Latitude']->Value;
               $lon=$rs->Fields['Longitude']->Value;

               $rs2=$db->Execute("SELECT * FROM PostalCodes WHERE (POW((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(lat-\"$lat\")),\"2\"))<($radius*$radius) ");
               $result = mssql_query($query);
               
               while (!$rs->EOF) {
                     $zipArray[]=$rs->Fields['TheFieldName']->Value;;
               }

               return $zipArray;
           } else {
                 return "Zip Code not found";
           }
      } // end func
I'm not getting any errors anymore but am also not getting any results. I put in the right connection info for the server and that went through just fine. I entered "PostalCode" where "TheFieldName" is located in the script but it still yielded no results (blank html page). Even adding  

echo " $lat ";

at various points in the script did not return a value.

I also added       echo " $zipCode ";   at various points but it does not return any value on the resulting page after it is placed anywhere below the beginning of the inRadius function, even if I comment out the global variables.

If I comment out the function itself and do a straight call, I get:

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> ADODB.Connection<br/><b>Description:</b> Operation is not allowed when the object is open.' in D:\Inetpub\website\TEST2.php:27 Stack trace: #0 D:\Inetpub\website\TEST2.php(27): com->Open('DRIVER={SQL Ser...') #1 {main} thrown in D:\Inetpub\website\TEST2.php on line 27

Line 27 is

$result = mssql_query($query, $db->Open($dsn));

[scratching my head...]
Tracing down the bug in my script, the next step was to comment out the $results lines:

//Simulate data entry
     $zipCode = "19107";
     $radius = "25";

     //Connect to database
     $db = new COM("ADODB.Connection");
     $dsn = "DRIVER={SQL Server}; SERVER=servername;UID=uname;PWD=pass; DATABASE=database";
     $db->Open($dsn);
   
     //function inRadius($zipCode,$radius)
     
      //{
     //global $db;
     //global $zipCode;
     //global $radius;

           $rs=$db->Execute("SELECT * FROM PostalCodes WHERE PostalCode='$zipCode'");
               //$result = mssql_query($query, $db->Open($dsn));
               

        if (!$rs->EOF) {
             $lat=$rs->Fields['Latitude']->Value;
             $lon=$rs->Fields['Longitude']->Value;

             $rs2=$db->Execute("SELECT * FROM PostalCodes WHERE (POWER((69.1*(longitude-\"$lon\")*cos($lat/57.3)),\"2\")+POWER((69.1*(latitude-\"$lat\")),\"2\"))<($radius*$radius) ");
             //$result = mssql_query($query);
             
             while (!$rs->EOF) {
                  $zipArray[]=$rs->Fields['PostalCode']->Value;;
             }

             return $zipArray;
           } else {
                return "Zip Code not found";
           }
      //}
// end func

Result:

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Microsoft OLE DB Provider for ODBC Drivers<br/><b>Description:</b> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '-75.158663'.' in D:\Inetpub\epropper\TEST2.php:34 Stack trace: #0 D:\Inetpub\epropper\TEST2.php(34): com->Execute('SELECT * FROM P...') #1 {main} thrown in D:\Inetpub\epropper\TEST2.php on line 34

Line 34 is:

$rs2=$db->Execute("SELECT * FROM PostalCodes WHERE (POWER((69.1*(longitude-\"$lon\")*cos($lat/57.3)),\"2\")+POWER((69.1*(latitude-\"$lat\")),\"2\"))<($radius*$radius) ");
BTW, "-75.158663" is the longitude of that zip code...
Sorry about forgetting to remove $result = mssql_query from the last code I posted. Now there is no problem in the excecution. The error is from SQL server that the query is incorrect and is much easier to resolve.

Change

$rs2=$db->Execute("SELECT * FROM PostalCodes WHERE (POWER((69.1*(longitude-\"$lon\")*cos($lat/57.3)),\"2\")+POWER((69.1*(latitude-\"$lat\")),\"2\"))<($radius*$radius) ");

to

$sql = $db->Execute("SELECT * FROM PostalCodes WHERE (POWER((69.1*(longitude-\"$lon\")*cos($lat/57.3)),\"2\")+POWER((69.1*(latitude-\"$lat\")),\"2\"))<($radius*$radius) ");
echo $sql;
$rs2=$db->Execute($sql);

What do you get on the screen when you run it. I want to see the actual SQL that is getting executed.
still get the following:

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Microsoft OLE DB Provider for ODBC Drivers<br/><b>Description:</b> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '-75.158663'.' in D:\Inetpub\epropper\TEST2.php:34 Stack trace: #0 D:\Inetpub\epropper\TEST2.php(34): com->Execute('SELECT * FROM P...') #1 {main} thrown in D:\Inetpub\epropper\TEST2.php on line 34

The SQL in the file is (I removed the commented out portions):

 $zipCode = "19107";
     $radius = "25";

     $db = new COM("ADODB.Connection");
     $dsn = "DRIVER={SQL Server}; SERVER=server;UID=uid;PWD=pwd; DATABASE=databaseName";
     $db->Open($dsn);
   

           $rs=$db->Execute("SELECT * FROM PostalCodes WHERE PostalCode='$zipCode'");

        if (!$rs->EOF) {
             $lat=$rs->Fields['Latitude']->Value;
             $lon=$rs->Fields['Longitude']->Value;

            $sql = $db->Execute("SELECT * FROM PostalCodes WHERE (POWER((69.1*(longitude-\"$lon\")*cos($lat/57.3)),\"2\")+POWER((69.1*(latitude-\"$lat\")),\"2\"))<($radius*$radius) ");
echo $sql;
$rs2=$db->Execute($sql);
             
             while (!$rs->EOF) {
                  $zipArray[]=$rs->Fields['PostalCode']->Value;;
             }

             return $zipArray;
           } else {
                return "Zip Code not found";
           }

It looks like the error generated is caused because the SQL statement is looking for a column name called "-75.158663" where the statement is displaying \"$lon\"
It did not show you the SQL? Add

exit();

after

echo $sql;

Does it show SQL now? If so post that.
The script doesn't get that far since the echo statement is on line 35. The error occurs on the line before which is the SQL statement itself.
If I do (without the exit line):

$sql = $db->Execute("SELECT * FROM PostalCodes WHERE (POWER((69.1*(longitude-'$lon')*cos($lat/57.3)),2)+POWER((69.1*(latitude-'$lat')),2))<($radius*$radius) ");
echo $sql;

The result is:

Object id #3
Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Provider<br/><b>Description:</b> Type mismatch.' in D:\Inetpub\epropper\TEST2.php:36 Stack trace: #0 D:\Inetpub\epropper\TEST2.php(36): com->Execute(Object(variant)) #1 {main} thrown in D:\Inetpub\epropper\TEST2.php on line 36
That is why I asked you to put the select in $sql, echo it and then execute. Since even that did not show you the SQL, I asked you to put the exit before Execute so that you can at least see the SQL. The problem is in SQL - at least it was yesterday. Now new error seems to be something else. What is line 36?
Line 36 is

$rs2=$db->Execute($sql);
Add exit() after

echo $sql;

and post the SQL you get on screen.
That's all there is on the screen:

Object id #3
I am sorry I gave you wrong statement. Change

$sql = $db->Execute("SELECT * FROM PostalCodes WHERE (POWER((69.1*(longitude-\"$lon\")*cos($lat/57.3)),\"2\")+POWER((69.1*(latitude-\"$lat\")),\"2\"))<($radius*$radius) ");

to

$sql = "SELECT * FROM PostalCodes WHERE (POWER((69.1*(longitude-\"$lon\")*cos($lat/57.3)),\"2\")+POWER((69.1*(latitude-\"$lat\")),\"2\"))<($radius*$radius);
Here's the result:

SELECT * FROM PostalCodes WHERE (POWER((69.1*(longitude-"-75.158663")*cos(39.948927/57.3)),"2")+POWER((69.1*(latitude-"39.948927")),"2"))<(25*25)

If I then remove the exit(); code, I get the above with the following:

Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Microsoft OLE DB Provider for ODBC Drivers<br/><b>Description:</b> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '-75.158663'.' in D:\Inetpub\epropper\TEST2.php:33 Stack trace: #0 D:\Inetpub\epropper\TEST2.php(33): com->Execute('SELECT * FROM P...') #1 {main} thrown in D:\Inetpub\epropper\TEST2.php on line 33

Now, just for the heck of it, I took out all of the quotes around the declared variables and replaced them with parentheses to see what I'd get. I did this since we have a negative value for the longitude. This is what I got:

SELECT * FROM PostalCodes WHERE (POWER((69.1*(longitude-(-75.158663))*cos((39.948927)/57.3)),2)+POWER((69.1*(latitude-(39.948927))),2))<(25*25)
Fatal error: Maximum execution time of 60 seconds exceeded in D:\Inetpub\epropper\TEST2.php on line 38

Line 38 is:

$zipArray[]=$rs->Fields['PostalCode']->Value;;

Now there are about 800,000 records in the Postal Codes table (thanks to Canada...) so that may be why it's timing out. Or is it caught in an endless loop?

As you have already figured it out, quotes are not needed. Change the query to

SELECT top 10 * FROM PostalCode

to get only 10 records. Then test. If everything else is ok, then come back to the whole data.

BTW, what are you planning to do with 800000 records when you get it to $zipArray[]. Also shouldn't the where clause reduce that number to may be 10s or 20s?
It should only list 15-20 records if we cross match the distance with the reseller database. Because the longituade and latitude is incorporated into the SQL statement, howevere, it wouldn't return 800,000 records. It just has to search through them.

I'll try your suggestion tomorrow morning and see where it gets me.

Thanks for all the help.
I'm still getting timed out after 60 seconds so evidently, the statement is OK in and of itself, but it's either looping or taking too much time to process.

SELECT top 10 * FROM PostalCodes WHERE (POWER((69.1*(longitude-(-75.158663))*cos((39.948927)/57.3)),2)+POWER((69.1*(latitude-(39.948927))),2))<(25*25)
Fatal error: Maximum execution time of 60 seconds exceeded in D:\Inetpub\epropper\TEST2.php on line 38

Back to the drawing board....
OK, I decided to go back to the original script and incorporate some of the changes throughout this thread and got halfway where I wanted to be. I spaced this post out a bit to make it easier to understand. Here's what I did:

//Simulate data entry
     $zipCode = "19107";
     $radius = "25";

     //Connect to database
     $db = new COM("ADODB.Connection");
     $dsn = "DRIVER={SQL Server}; SERVER=servername;UID=uname;PWD=pass; DATABASE=database";
       $db->Open($dsn);
     function inRadius($zipCode,$radius)
     
      {
     global $db;
     global $zipCode;
     global $radius;
     
           $query=$db->Execute("SELECT * FROM PostalCodes WHERE PostalCode='$zipCode'");
           //$result = mssql_query($query, $db->Open($dsn));

           if (!$query->EOF) {
           //$row = mssql_fetch_array($result, MSSQL_ASSOC);
           $lat=$row["Latitude"];
           $lon=$row["Longitude"];
           $rs="SELECT * FROM PostalCodes WHERE (POWER((69.1*(longitude-($lon))*cos(($lat)/57.3)),2)+POWER((69.1*(latitude-($lat))),2))<($radius*$radius)";
           echo $rs;
           //$result = mssql_query($rs);  <------------ this line generates an error. See below
           if (!$rs->EOF) {
           //while($row = mssql_fetch_array($result, MSSQL_ASSOC)) { <------------ this line generates an error. See below
           $zipArray[]=$row;
           //} <------------ this line unnecessary when others commented out
           return $zipArray;
           }
           } else {
           return "Zip Code not found";
           }
      } // end func

 //$zipCode = $HTTP_POST_VARS["zipCode"];
 //$radius = $HTTP_POST_VARS["radius"];

 $zipArray = inRadius($zipCode,$radius);
 echo $ziparray;

 print "<h2>There are ".count($zipArray)." Zip codes within $radius Miles of $zipCode</h2>";
 foreach($zipArray as $row) {   //<------------ this line generates an error if the others above are uncommented.
 print "<br>ZipCode:$row[PostalCode] Lon:$row[Longitude] Lat:$row[Latitude] City: $row[CityName]";
 }





The resulting page displayed:

SELECT * FROM PostalCodes WHERE (POWER((69.1*(longitude-())*cos(()/57.3)),2)+POWER((69.1*(latitude-())),2))<(25*25)
There are 1 Zip codes within 25 Miles of 19107

ZipCode: Lon: Lat: City:





Commenting out the "while" statement let the script process through but if it was left in, it would generate the following error:

Warning: mssql_query() [function.mssql-query]: Unable to connect to server: (null) in D:\Inetpub\epropper\TEST4.php on line 34

Warning: mssql_query() [function.mssql-query]: A link to the server could not be established in D:\Inetpub\epropper\TEST4.php on line 34

Warning: mssql_fetch_array(): supplied argument is not a valid MS SQL-result resource in D:\Inetpub\epropper\TEST4.php on line 36

There are 0 Zip codes within 25 Miles of 19107

Warning: Invalid argument supplied for foreach() in D:\Inetpub\epropper\TEST4.php on line 53




Evidently, translating this script from MySQL to MSSQL has been a problem since line 34 is the one with for "mssql_query" and line 36 is the one with "mssql_fetch_array". Line 53 is "foreach($zipArray as $row) ". Taking those out does yield results but no loop is happening to display all records. Question is how to I incorporate that loop so that is adds all relevant records to the array, instead of just the first one.
OK. Errors are gone but data is not showing up....I've also combined the two tables in this exercised since I was focusing only on one before hand to get started. Might as well kill two birds....

The latest code:

<?php
$zipCode = "19107";
$radius = "25";
$db = new COM("ADODB.Connection");
$dsn = "DRIVER={SQL Server}; SERVER=server;UID=uname;PWD=pass; DATABASE=database";
$db->Open($dsn);
$rs=$db->Execute("SELECT * FROM PostalCodes WHERE PostalCode='$zipCode'");
               
        if (!$rs->EOF) {
             $lat=$rs->Fields['Latitude']->Value;
             $lon=$rs->Fields['Longitude']->Value;

$rs2 = "SELECT top 10 FROM resellers left join PostalCodes ON PostalCodes.PostalCode = resellers.reszip WHERE (POWER((69.1*(PostalCodes.longitude-($lon))*cos(($lat)/57.3)),2)+POWER((69.1*(PostalCodes.latitude-($lat))),2))<($radius*$radius))";
}
echo $rs2;
if (!$rs2->EOF) {
while (!$rs2->EOF)
{
$name = $rs2->Fields['resname']->Value;
$add1 = $rs2->Fields['resadd1']->Value;
$add2 = $rs2->Fields['resadd2']->Value;
$add3 = $rs2->Fields['resadd3']->Value;
$add4 = $rs2->Fields['resadd4']->Value;
$zip = $rs2->Fields['reszip']->Value;


   echo " $name <BR>";
   echo " $add1 <BR>";
   if ($add2 == null or $add2 == "") {
   echo "";
   }
   else {
   echo " $add2 <BR>";
   }
   echo " $add4 / $zip<P>";

}
}
?>


The result (the slash comes the script's last echo line):


SELECT top 10 FROM PostalCodes right join resellers ON PostalCodes.PostalCode = resellers.reszip WHERE (POWER((69.1*(PostalCodes.longitude-(-75.158663))*cos((39.948927)/57.3)),2)+POWER((69.1*(PostalCodes.latitude-(39.948927))),2))<(25*25))

/

/

/

/

/

/

.....


I know, I know. This doesn't work right since all I get are repeated blank records indefinitely. But at least I'm not getting the errors. I'm just not getting the data.
...and I also tried a right join on that sql statement.
>>The result (the slash comes the script's last echo line):

It means that for the give postal code and the condition given is where clause, there is no reseller in the table. You could execute the last echoed SQL directly in DB to confirm that

I think there is an error in the formula. What is the formula that you want to use. Explain in plain english, don't worry about the syntax.
I want the user to enter a zip and code and radius and find every reseller within that radius. To do that, I need to

1. retrieve the zip code's latitude and longitude from PostalCodes
2. use the formula to find all other zip codes within that radius
3. match all those zip codes with zip codes in the resellers table

Points 2 and 3 would be the second call to the database that would join the resellers and PostalCodes table on the zip code. Using the latitude and longitude retrieved previously, and the entered radius, we fetch the resellers in the area.

What is the formula in 2? Again don't worry about the SQL syntax. I guess something is wrong there otherwise you would get results.
Success! Kind of....

Here's the code I'm using:

$zipCode = "19107";
$radius = "25";
$db = new COM("ADODB.Connection");
$dsn = "DRIVER={SQL Server}; SERVER=servername;UID=uname;PWD=pass; DATABASE=database";
$db->Open($dsn);
$rs=$db->Execute("SELECT * FROM PostalCodes WHERE PostalCode='$zipCode'");
               
        if (!$rs->EOF) {
             $lat=$rs->Fields['Latitude']->Value;
             $lon=$rs->Fields['Longitude']->Value;
            
$rs2 = $db->Execute("SELECT top 10 * FROM PostalCodes right join resellers ON PostalCodes.PostalCode = resellers.reszip WHERE (POWER((69.1*(PostalCodes.longitude-($lon))*cos(($lat)/57.3)),2)+POWER((69.1*(PostalCodes.latitude-($lat))),2)<($radius*$radius))");

if (!$rs2->EOF) {
while (!$rs2->EOF)
{
$name = $rs2->Fields['resname']->Value;
$add1 = $rs2->Fields['resadd1']->Value;
$add2 = $rs2->Fields['resadd2']->Value;
$add3 = $rs2->Fields['resadd3']->Value;
$add4 = $rs2->Fields['resadd4']->Value;
$zip = $rs2->Fields['reszip']->Value;


   echo " $name <BR>";
   echo " $add1 <BR>";
   if ($add2 == null or $add2 == "") {
   echo "";
   }
   else {
   echo " $add2 <BR>";
   }
   echo " $add4 / $zip<P>";
      $rs2->MoveNext();
}
}
}

The results indicate that there's somethingwrong with the loop, however since it returns ten records but they show up as:

2 instances of the first result
3 instances of the second result
4 instances of the third result
1 instance of the fourth result (I would assume that without "top 10", it would show up five times)

ASKER CERTIFIED SOLUTION
Avatar of amit_g
amit_g
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Bingo.

Now I have to figure out a way to get the distance to show up but I think your help here has been enough for this question.

Thanks.