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?
PHP

Avatar of undefined
Last Comment
cbeaudry1

8/22/2022 - Mon
amit_g

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.
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.
cbeaudry1

ASKER
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));
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
cbeaudry1

ASKER
Point increase....
amit_g

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);
cbeaudry1

ASKER
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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
amit_g

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?
cbeaudry1

ASKER
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.
amit_g

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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
cbeaudry1

ASKER
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();
}
?>
cbeaudry1

ASKER
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.
amit_g

Sorry to hear about your mother.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
amit_g

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
cbeaudry1

ASKER
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...]
cbeaudry1

ASKER
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) ");
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
cbeaudry1

ASKER
BTW, "-75.158663" is the longitude of that zip code...
amit_g

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.
cbeaudry1

ASKER
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\"
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
amit_g

It did not show you the SQL? Add

exit();

after

echo $sql;

Does it show SQL now? If so post that.
cbeaudry1

ASKER
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.
cbeaudry1

ASKER
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
amit_g

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?
cbeaudry1

ASKER
Line 36 is

$rs2=$db->Execute($sql);
amit_g

Add exit() after

echo $sql;

and post the SQL you get on screen.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cbeaudry1

ASKER
That's all there is on the screen:

Object id #3
amit_g

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);
cbeaudry1

ASKER
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?

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
amit_g

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?
cbeaudry1

ASKER
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.
cbeaudry1

ASKER
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....
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cbeaudry1

ASKER
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.
cbeaudry1

ASKER
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.
cbeaudry1

ASKER
...and I also tried a right join on that sql statement.
Your help has saved me hundreds of hours of internet surfing.
fblack61
amit_g

>>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.
cbeaudry1

ASKER
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.

amit_g

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cbeaudry1

ASKER
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
amit_g

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
cbeaudry1

ASKER
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.